Author Archives: dbaduck

SQL Server TDE on Mirror and Log Shipping

Today in our Virtual Chapter meeting for VCDBA, I presented on SQL Server Encryption including TDE.  The question was posed as to whether or not the TDE would take affect if the mirror was established first and then you enabled TDE on the database.  I was not sure at the time only because I had not done any tests, so I did not want to say yes or no unless I could substantiate it.

I set off to do some tests.  First I created a Mirrored database using the demo code from the VC presentation.

use master
GO

CREATE DATABASE [TESTTDE] ON  PRIMARY 
( 
	NAME = N'TESTTDE', 
	FILENAME = N'C:\sqldata\S1\TESTTDE.mdf' , 
	SIZE = 10240KB , 
	FILEGROWTH = 10240KB 
)
 LOG ON 
( 
	NAME = N'TESTTDE_log', 
	FILENAME = N'C:\sqldata\S1\TESTTDE_log.ldf' , 
	SIZE = 10240KB , 
	FILEGROWTH = 10240KB 
)
GO

Now once the database is created on instance 1 I backed up the database and a log backup. Then restored it to the second instance with it’s log with no recovery. Once it was primed and ready, I established a mirror and saw the database was synchronized.  Now the trick is to use create the components to establish the Encryption Hierarchy.

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE1_Use1Strong2Password3Here!';
go

CREATE CERTIFICATE MyServerCert 
WITH SUBJECT = 'My DEK Certificate'
GO


Now that we have the MASTER KEY and the Certificate in place, you need to backup the certificate to a set of files and restore it to the second instance.

BACKUP CERTIFICATE MyServerCert
TO FILE='C:\SQLBACKUP\MyserverCert.cer'
WITH PRIVATE KEY (FILE='C:\SQLBACKUP\MyServerCert.pvk',
	ENCRYPTION BY PASSWORD='My1Secure2Password!')
GO

NOW CHANGE TO THE SECOND INSTANCE

-- Make sure that you change to the second instance

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE2_Use1Strong2Password3Here!';
GO
CREATE CERTIFICATE MyServerCert
    FROM FILE = 'C:\SQLBACKUP\MyServerCert.cer' 
    WITH PRIVATE KEY (FILE = 'C:\SQLBACKUP\MyServerCert.pvk', 
    DECRYPTION BY PASSWORD = 'My1Secure2Password!');
GO

Now that you have the Encryption Hierarchy established for both instances, let’s encrypt the database.

-- Make sure that you are in the Principal instance 
-- or this will not work because you won't be able 
-- to USE TESTTDE on the Mirror

USE TESTTDE
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO

ALTER DATABASE TESTTDE
SET ENCRYPTION ON;
GO

What will happen in the Mirror is that it will encrypt the database, even though you won’t be able to see it.  When it is fully encrypted, and you fail the database over in the Mirror, it will indicate that the database is encrypted. On the Principal you can see it with the query below.

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;

Now for the Log Shipping copy

To establish the test for the Log Shipping, do the same as you did for the mirror but restore the Full, then the Log with Standby so that you can see that it flags it as encrypted.  First establish the Log Shipping without encryption set up, and once you are in Standby on the Shipped side, you can use the existing Certificate to test the idea of encryption afterwards.

Steps:

  1. CREATE DATABASE
  2. SET Recovery Model to Full
  3. Backup Database
  4. Backup Log
  5. Change to second instance
  6. RESTORE DATABASE with NORECOVERY
  7. RESTORE LOG WITH STANDBY
  8. Change to first instance
  9. USE TESTTDELOGSHIPPING
  10. CREATE DATABASE ENCRYPTION KEY
  11. ALTER DATABASE TESTTDELOGSHIPPING SET ENCRYPTION ON
  12. BACKUP LOG
  13. Change to second instance
  14. RESTORE LOG TESTTDELOGSHIPPING WITH STANDBY
  15. Run the above query to see if the database is encrypted. You will see is_encrypted = 1

This shows that the Mirroring and Log Shipping processes are affected by the Database Encryption Process.

Happy Encrypting….!

sys.dm_db_index_physical_stats Easter Egg

I was reading through documentation on the DMV sys.dm_db_index_physical_stats and found that there are a couple of things you need to be aware of when using this DMV.  First, we know that there are parameters for the DMV. Parameters Database Id Object ID Index Id Partition Id Level of interrogation (DEFAULT, LIMITED, DETAILED, SAMPLED, NULL… Continue Reading

Speaking at Portland Maine #SQLSAT293

I am excited to speak at the SQL Saturday 293 in Maine this weekend. Hope to see you there.  There is a great lineup in the agenda. My talks will be SMO Internals for High Performace PowerShell and SQL Server TDE.  The links will take you to the session where I will upload my slide… Continue Reading

MCM SQL Server 2008 Achieved

Another step in my career as I have passed the Lab Exam and have become an MCM in SQL Server 2008.  I believed I could achieve it after passing my knowledge exam the first time. The first attempt at the lab was back in October and it was disappointing to find out that I did… Continue Reading

Skip to toolbar