Yes, a really nice new shiny feature where we have the ability to suspend and resume the encryption scan for TDE – Transparent Data Encryption which is available in SQL Server 2019. (Tested against the latest version CTP 2.4)
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.
TDE (Transparent Data Encryption) encrypts the data files at rest but don’t forget that it also encrypts your backup file too. I fancied looking inside a backup of my database before enabling TDE and comparing it to when enabled.
Working with a couple of databases that needed TDE I noticed when I enabled one of them that it was stuck on “encryption in progress” for quite a while.
I noticed that my backup was taking longer than usual.
I went to Perfmon to look at some counters, more specifically Physical disk:Avg sec/read(orange/yellow line) and Physical disk:Avg sec/write(blue line), yes it was very busy, much busier than normal.
Waits stats was painting the following picture: I was very intrigued with the sleep_bpool_flush so I headed straight over to the waits library.
You get this wait type when the checkpoint process realizes that it’s saturating the I/O subsystem. (https://www.sqlskills.com/help/waits/sleep_bpool_flush/), so it seemed that my backup could not even issue a checkpoint successfully. With that thinking I decided to do a manual checkpoint which just made the problem worse.
After some detective work I found the answer. Someone enabled TDE and it was going though the encryption process whilst I was trying to take a backup so my backup was never going to complete anytime soon.
The error message I was ended up with was “Msg 5901, Level 16, State 1, Line 7 One or more recovery units belonging to database ‘NEWdb’ failed to generate a checkpoint”.
You can use the following query to understand the state of the encryption process for your databases.
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,dm.key_algorithm,dm.key_length FROM sys.databases db LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id
When you have a state of 2 it means encryption is in progress, you may have heard of this as an encryption scan.
This behaviour is confirmed via Books on line and totally makes sense. (Last bullet point)
The following operations are not allowed during the CREATE DATABASE ENCRYPTION KEY, ALTER DATABASE ENCRYPTION KEY, DROP DATABASE ENCRYPTION KEY, or ALTER DATABASE…SET ENCRYPTION statements.
- Dropping a file from a filegroup in the database.
- Dropping the database.
- Taking the database offline.
- Detaching a database.
- Transitioning a database or filegroup into a READ ONLY state.
- Using an ALTER DATABASE command.
- Starting a database or database file backup.
You live and learn.
This week’s TSQL Tuesday is being held by the mighty Kenneth Fisher – Backup and Recovery- see this link, how can I not get involved with this one?
I have been blogging for about a year now and have covered most of Kenneth’s bullet points – except Internals and SSAS based stuff, so I decided to do something different but still about backups. This post is all about the importance of backing up certain objects when you are using TDE – Transparent Data Encryption.
To understand TDE see my older post – https://blobeater.blog/2016/11/22/playing-with-tde/
So I have enabled TDE and I have a Full backup – lets restore it to a different server.
USE [master] RESTORE DATABASE [DWHDB] FROM DISK = N'C:\temp\dwh.bak' WITH FILE = 1, STATS = 5 GO
Cannot find server certificate with thumbprint ‘0xC0367AC8E9AE54538C17ACB0F63070D6FF21316A’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
The dreaded cannot find server certificate with thumbprint message. Why is this happening?
Well if you read my post on TDE ( or anybody else’s) you would have noticed the following message:
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
So the point is – back them up
USE master GO BACKUP CERTIFICATE MyServerCert TO FILE = 'c:\data\MyServerCert.cer' WITH PRIVATE KEY (FILE = 'c:\data\certificate_MyServerCert.pvk', ENCRYPTION BY PASSWORD = 'xxxxxxxx')
Then on the restoring server you will need to issue the following (assuming you have a database master key too)
CREATE CERTIFICATE MyServerCert FROM FILE = 'c:\temp\MyServerCert.cer' WITH PRIVATE KEY (FILE = 'c:\temp\certificate_MyServerCert.pvk', DECRYPTION BY PASSWORD = 'xxxxxxxx'); GO USE [master] RESTORE DATABASE [DWHDB] FROM DISK = N'C:\temp\dwh.bak' WITH FILE = 1, STATS = 5 GO
RESTORE DATABASE successfully processed 13002 pages in 0.444 seconds (228.770 MB/sec) – the restore works a charm.