In this video we are going to talk about transparent data encryption. Transparent data encryption is a feature in SQL server that protects data at rest. Data at rest means your MDF and LDF files. These files reside on the hard disk and are easy prey to anyone with access the file system. By simply copying the files onto another machine they will be able to bypass all security measures implemented by the DBA. This becomes especially important in the cloud where disk are being provisioned and DE provisioned thousands of times in a day.
When first enabled transparent Data encryption requires some amount of time in order to scan through all the pages residing in the database before they can be encrypted into the hard disk. The data is loaded into ram from where encryption is performed and written back to disk. During this time transaction log files and MDF encounter significant IO and can result in performance degradation. Another aspect of transparent Data encryption that you need to be aware of the fact that the TEMP database also get encrypted. Depending on the size of the database transparent encryption can take anywhere between few minutes to many hours in order to encrypt the entire set of pages with the database? During this operation it is undesirable to have performance issues and as a result in sequel server 2019 Microsoft provides an option to pause and resume transparent data encryption when the server encountered heavy workloads.
After the initial scan for the database all remaining encryption happens in real time and therefore there would be significant locking blocking issues as might be encountered during the initial scan. Having said that due to the real-time encryption after TDE has finished the initial scan there is to the possibility of a slight CPU overhead for all queries subsequently. The good thing about transparent Data encryption is it is complete in sense that even backups are encrypted and therefore you need to space special attention today encryption keys as even backups would be restorable without them.
In order to Perform TDE we need to create a Master Key and A certificate in the master database.
Make sure to back up the master and Certificate once it is created since all future backups and restore will depend on them. Lose them and you can give up trying to bring that database back.
Once enabled we can see it takes some time to complete the operation and the status can be found using the DMV sys.dm_database_encryption_keys
If there is a significant performance degradation the DBA after querying the above DMV can then decide to Suspend TDE or resume it later as needed. As a best practice start these operations only when you have a suitable window available.
Unlike SET Encryption OFF (which decrypts the entire set of pages and un-does any TDE) SUSPEND and RESUME still retains encrypted information mid-state.
USE master;GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';GO
CREATE CERTIFICATE MyServerCert
WITH SUBJECT = 'My DEK Certificate';GO
USE AdventureWorks2012;GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyServerCert;GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;GO
SELECT *
FROM sys.dm_database_encryption_keys
ALTER DATABASE < db_name >
SET ENCRYPTION SUSPEND;
ALTER DATABASE < db_name >
SET ENCRYPTION RESUME;
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15
Please Consider Subscribing
