Pause and Resume Transparent Data Encryption (TDE) in SQL Server

Transparent Data Encryption (TDE) was originally introduced in SQL Server 2008 (Enterprise Edition) with a goal to protect SQL Server data at rest. In other words, the physical data and log files along with the database backup sitting on file system are protected (encrypted).

Few things to be aware of when implementing TDE:

With TDE, The data transmitted over the network is not encrypted and the data at the object level remains unencrypted. In other words, if a user has select access to a table(s) within the TDE enabled database, he/she will be able to read data with simple select statements, as the name suggests, it’s transparent. TDE does not protect FILESTREAM data and any files related to Buffer Pool Extension (BPE) are not encrypted as well, you should use file system encryption tools like windows Bit-locker or any other third party tools for this purpose. Another caveat is TDE doesn’t support Instant File Initialization for database files. Also, when TDE is enabled on a user database your tempdb database gets encrypted behind the scenes.

Okay, let’s move on to our topic. In this article, we will see how to monitor and manage TDE progress, not essentially how to setup TDE on a user database. Before moving on to our main topic, here is a quick refresher on how Transparent Data Encryption works.

Image Source

Enabling TDE on a given database is a very straightforward process. 20,000-foot view of the process is basically creating a DMK (Master DB) which is protected by Service Master Key, Cert (Master DB), DEK (User DB) and enable TDE(User DB) and you are done. But things get little tricky when you are dealing with VLDBs. What if you have a ginormous database (Let’s say a 30 TeraBytes monster) on which you have to enable TDE? Enabling TDE is not instantaneous, the SQL Server Encryption Scanner has to read all the underlying database pages and encrypt them, For a 30 TB database it might take multiple days for SQL Server to encrypt the entire database and we as DBAs should monitor the encryption progress making sure there are no side effects. In this context I am not talking about server resources, I am talking about the impact on the transaction log file when encryption scanner is in progress. So, why should we worry about LDF file when TDE scanner is in progress? Well, SQL Server doesn’t truncate the transaction log file of your database when TDE Encryption Scanner is doing its job. Things get more complicated if you are not allowed to run TDE during business hours and let’s say you have nightly ETL loads or some other scheduled job(s) which generates a considerable amount of log records. In this article, let’s see how to monitor TDE progress and how to manage transaction log when TDE scanner is in progress.

Further reading : Monitoring/Managing TDE


Setting up TDE on SQL Server Failover Cluster

Back in 2011 I wrote couple of Blog posts when I was initially exploring Encryption options we have in SQL Server. Never really got a chance to work on TDE since then. Fast forward to 2016, I am participating in TDE project, where we are enabling TDE for few of our databases which are hosted on SQL Server Failover Clustered Instances. Currently we are in testting/POC phase. In this post, I will share what my findings are about TDE on clustered instances. Let’s get started…

Enabling TDE on a clustered database is no different than how we enable on a Standalone Instance. Steps are as follows:

Create database TDE_Test
--1.create a master key in master database.
USE master
ENCRYPTION BY PASSWORD = 'Very$ecurepwd123'
--2.Create a certificate(name it)
USE master
WITH SUBJECT = 'Transparent Data Encryption Certificate'
--3. Create a database encryption key. –Based on the certificate from Step 2–Can use AES, DES, Triple DES, RC4 etc
USE TDE_Test--The db to protect
CLUSTPRD1_TDECert--Cert from Step 2

--4. Turn it on!
USE TDE_Test--The db to protect
-----Backup Certs---V V Imp
--Backup SMK---VVV Important</pre>

Okay…so what happens when SQL gets failed over to another node? Will TDE work? Will the database be still in usable state? The answer is “YES” from my initial testing. Everything remains intact. Nothing breaks!

Note: This is different from the way how it works in Alwayson AGs.

Reason: See below Screenshot taken from BOL.


The Master Key which we created on step 1 is protected by Service master key(SMK) which is scoped at Instance level is the root of our Hierarchy, And then the certificate and the keys are scoped at master and user database levels which will be failed over and moved between nodes.So….Nothing special is required for FCIs. Please correct me If I am wrong and post any thoughts in comments.



Microsoft SQL Server 2014 for sure is bringing a lot of cool features/enhancements to aid DBAs and Database Developers….and one of my favorite features it has to offer for a DBA is “Native Database Backup Encryption”.

Till SQL Server 2012:
Let’s say you need to send a backup to Vendor and you want it to be encrypted as per your company security policies. Well, there is no way you can create an encrypted SQL Server database backup natively without help of third party backup tools from vendors like RedGate, Dell, Idera etc. If we don’t have any of those backup tools in our shop, our choice will be pretty much restricted to enabling TDE on the database and take a backup which will be automatically encrypted as well(Unfortunately TDE is Enterprise Edition only feature). What if you are running on standard edition?

Starting SQL Server 2014:
All you’ve to do is select a checkbox while taking a backup(if you are using SSMS GUI) or providing few additional switches in your T-SQL backup statement 🙂
Note: We should create Master Key and Certificate to be able to Encrypt the database backup.

Is this Enterprise Only Feature?
Nope 🙂 You can take Encrypted backups in Enterprise,Business Intelligence and Standard Editions.
Do we need to Turn on TDE at database level?
Nope 🙂
What are the supported Encryption algorithms?
AES_128, AES_192, AES_256, and Triple DES.

So, Now you can Encrypt your database backup in Enterprise,Standard,BI,Developer Editions and an encrypted backup can be restored on any edition(as long as you’ve got certificates 😀 )

Okay folks, now It’s Demo time……I’ve SQL Server 2014 CTP2 Installed in my lab and I created a test database “Tst” for this demo purpose. As of now, it’s a brand new Instance and a brand new database without any keys and certificates.

Step 1:
We’ve to create a Master Key and a Certificate in MASTER Database.(Shown Below)


Step 2: (Am using SSMS for now).
Go to Database Tasks from object Explorer and select backup.  In “General” tab , select your database Backup Path and now navigate to “Media Options” tab. This is important…“Backup to existing Media set” is not supported for Encrypted Backups as of SSMS 2014 CTP2. You should select “Backup to a new media set“(Shown below). Am not sure if this intended or Just a bug which will be fixed in RTM.


If not, your encryption section under “Backup Options” tab will be greyed out as you can see in the below screenshot 😦


Step 3:
After selecting your backup path and selecting Backup to new media set, check the Encrypt Backup option in the “Backup Options” tab and select your desired Algorithm and choose the Certificate which we’ve created in Step 1.


Click “OK”. Tadaaaaaaaaaaa……That’s it guys, we’ve created an Encrypted SQL Server Database Backup 🙂

Btw, if you are interested in T-SQL Syntax, here it is…

BACKUP DATABASE [Tst] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\tst_Encrypt.bak' 
NAME = N'Tst-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, 

In the next post let’s see how to restore this encrypted backup.

SQL Server Encryption – Part5 Encrypting Data(TDE) vs Encrypting Connections

This is the final part of Encryption series where i’m going to show how to encrypt connections in SQL Server. In the previous 4 parts of this series we’ve seen how to enable TDE for your Database, what EFS is and what Bitlocker is. If you recall correctly, all we discussed was how to encrypt our Database(Physical Files on the Disk), not securing Connections. In other words the data will be encrypted as long as it stays on the disk but not encrypted while SQL is sending data over to clients as you can see below!!

Note: TDE encrypts Data on Disk, Connections are not Encrypted using TDE.

So… to encrypt data across Wires(in other words how to Encrypt Connections)?

We have to use 128 Bit SSL(Secure Socket Layer) within SQL Server to encrypt Connections btw SQL Server and Client(s), which leads to slower performance but your SQL Server is Very Very Secured! Basically we’ve to Install a trusted certificate in our windows Certificate Store on our Physical Server where our SQL Server Instance is living on and we’ve to let SQL know that “Hey Mr.SQL Server! we’ve a certificate for you..make use of it for every connection”.

Step 1: Install Certificate(We can work with our Server/Network Team on this)

Step2: Go to your SQL Server Config Manager and enable Encryption as shown below.(I’ve 3 Instances on this Server, I’ll show you on PROD Instance)

Navigate to network Configuration, Right click on Network Protocols and select Properties as shown below.

Now, all your Certificates will be shown in the drop down menu under “Certificates” tab as shown below(I don’t have any on my Server)

Now, on Flags tab you can choose either Force Encryption to Yes or leave to default No as shown below.

If you Choose YES – Clients who are not encrypted are not allowed to connect.( In other words, any client which doesn’t allow encryption is declined to connect)

If you choose NO – Clients will be seeing encryption as optional.

Note: SSMS(Let’s assume it as a client application now for time being) knows how to encrypt connections. We can choose an Option to Encrypt my connection while connecting to the Database Engine as shown below.

As you can see, I’m checking the Box to Encrypt my connection from SSMS client while connecting to SQL Server DB Engine.

Hope this helps for a Kick Start on Encryption!!

SQL Server Encryption – Part4(How to Restore an (TDE enabled Database) encrypted Backup?)

Until now we’ve seen what TDE is, how to enable it on a Database, what are the key considerations which we should never forget as a DBA while dealing with Encryption stuff..bla bla…!! In this post of the Encryption Series, let’s actually see how to Restore a Backup of a database which has TDE enabled on it.

As i mentioned earlier, we can’t restore a backup on a different instance without Certificates. We’ve to restore the Cert from Source Server , then we should be able to restore the Database from the Backup.

Note: Enabling TDE on a Database encrypts every single portion of the database. It includes mdf, ldf and any ndf’s as well. All your Database Backups are encrypted as well once you turn ON TDE!! Heyyy…that’s awesome!! One thing you should be aware of is, If you are using FileStreams, they are not going to be encrypted with this TDE.

My Scenario: I’ve a Encrypted Database “Adventureworks” in my PROD Instance and i took a Full backup of it(Adv_TDE.bak). Let’s try to restore on my DR Instance as a brand new Database. As you can see below it is throwing “certificate related error Messages” when i am trying to restore the database.(You’ll get the Same error message if you try to use detach/attach method or Copy DB Wizard)

To keep it real simple I’m taking all the defaults(which will fail in real case without MOVE) while restoring Database in my T-SQL Restore Syntax. Remember this blog post is not about normal DB Restores Syntax(es) 😀

Soo…..what should we do???

All We’ve to do is restore our Certificate from source(And you have to create a DMK on destination if you don’t have one), Once you are done with that piece of puzzle, the rest is just performing your normal DB restore process what you do on any other non encrypted user database(s)! So..Basically all we need to do is creating the certificates on the destination Instance from the backup of your certificates which we created in earlier post.

On DR Instance: 

Create a Master Key(You don’t have to do this if you already have DMK) and Create a certificate from the Cert Backup and providing Private Key file and Password to decrypt it as shown below

USE master;
    WITH PRIVATE KEY (FILE = 'D:\Backs\TDEkey.bkey',
    DECRYPTION BY PASSWORD = 'VeryStrongPassword');

/*the Certificate encryption piece on PROD was
BACKUP CERTIFICATE TDEDEMOCERT /*this was the certificate which we created earlier*/
WITH PRIVATE KEY ( FILE = 'D:\Backs\TDEkey.bkey',
ENCRYPTION BY PASSWORD = 'VeryStrongPassword' )

Now let’s do the actual restore and see how it goes….

Perfect!!! Once we created certificates based on the Cert backup from PROD Instance, we were able to do Restore!! Soo….Key thing to remember is to Backup your certificate and store it in a safe as soon as you’ve Encrypted your database. I want to stress again – If you loose it, you lost everything related to your Database(You are safe as long as you can access the source database)!!!