SQL Server 2017 Encrypted Backups And Compression

Let’s work through some code to do an encrypted backup. This feature is available to you if you are using SQL Server 2014 onwards but I decided to use SQL Server 2017.

To encrypt during backup, you must specify an encryption algorithm, and an “encryptor” to secure the encryption key. I have decided to use the following options:

  • Encryption Algorithm: AES 256
  • Encryptor: A certificate

Continue reading

[Video] SQL Server Deadlocks

SQL Server Deadlocks – Also known as “The deadly Embrace” occurs when there are 2 processes and neither can advance and get the relevant lock it needs because the other process involved is preventing it from getting the resource it requires.

deadlocks

In this quick video clip I create a deadlock scenario and check how to get some deadlock information via system health extended events session.

Below is the T-SQL for querying the system health session. This was against a SQL Server 2014 install so you may need to change the file location.

SELECT top(1)
CONVERT(xml, event_data).value('(event[@name="xml_deadlock_report"]/@timestamp)[1]','datetime2') AS [TimeOfDeadlock],
CONVERT(xml, event_data).query('/event/data/value/child::*') AS [DeadlockGraph]
FROM sys.fn_xe_file_target_read_file('C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\system_health*.xel', null,  null, null)
WHERE object_name = 'xml_deadlock_report'
ORDER BY [TimeOfDeadlock] DESC

SQL Server TDE – Is TempDB Encrypted?

A really quick one today, something that made me think for a minute and I thought it might make others think too. So you have enabled TDE  – Transparent Data Encryption (you can see these previous posts here: https://blobeater.blog/?s=tde&submit=Search) on your SQL Server database and in the back of your mind you know TempDB gets encrypted too.

Continue reading

Unable To Call Into C Compiler !?

Following on from my previous blog post I mentioned that I had to find a solution to the “unable to call into C compiler” message. This meant that the specific SQL Server database in question (that contained in-memory tables) went into recovery pending mode, meaning that recovery needed to run but something was preventing it from even starting.

Continue reading

I Escaped from a SQL Server Service Pack Nightmare

I hope you take backups or have a recovery plan in place before a SQL Server service pack upgrade,  if not you might want to reconsider. Initially I wasn’t going to capture the screen shots during this recovery because my focus was to fix the issue but I thought I would just in case others experience the same thing.

I wanted to move from SQL Server 2014 SP1 to SP2, as you can see I had a clear issue.

Continue reading