Mirroring with TDE

Recently I’ve had to set up a lot of database mirroring, and about half of those databases were encrypted. Setting up mirroring on unencrypted databases is normally a quick process, but encrypted mirroring requires a bit more work. While there are many examples of mirroring out there, it can be hard to find an example of encrypted mirroring that is not missing a step or two. You cannot use Object Explorer to do everything, so below are the necessary scripts to not only set up encrypted mirroring, but also how to setup Transparent Data Encryption.

1. Create and Backup Master Key and Certificate TDE requires a master key and a certificate, so those need to be created first and then immediately backed up. Backing these up is best practice, but the files will also need to be restored to the secondary server. If you already have TDE on the primary server, you can skip this step and go straight to Step 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
/********** On Principal **********/

--Create Master Key
 USE MASTER
 GO
 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StongP@ssword'
 GO


--Backup Master Key
 BACKUP MASTER KEY TO FILE = 'FilePath\MasterKey.KEY'
 ENCRYPTION BY PASSWORD = 'StrongP@ssword'
 GO

--Create Certificate
 CREATE CERTIFICATE CertName
 WITH SUBJECT = 'CertDesc', EXPIRY_DATE = '1/1/2900'
 GO

--Backup Cert; using a different key name than the original to avoid errors
 BACKUP CERTIFICATE CertName
 TO FILE = 'FilePath\CertName.CER'
 WITH PRIVATE KEY (FILE='FilePath\CertKey.KEY',
 ENCRYPTION BY PASSWORD = 'StrongP@ssword')
 GO
 CLOSE MASTER KEY
 GO


2. Enable Transparent Data Encryption
 The next step is to enable TDE using the new certificate, and then turn TDE on. If TDE has been enabled previously, you should have already skipped to Step 3.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
--Enable TDE on database
USE DbName
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE CertName
GO

--Turn TDE On
ALTER DATABASE DbName
SET ENCRYPTION ON
GO


3. Restore Key and Certificate to Secondary
This is your first step if you already have TDE turned on. Just make sure you copy the backup files to the secondary server so you can restore them.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
/********** ON MIRROR **********/

--Restore Master key to Secondary server
USE MASTER
GO
RESTORE MASTER KEY
FROM FILE = 'FilePath\MasterKey.KEY'
DECRYPTION BY PASSWORD = 'StrongP@ssword'
ENCRYPTION BY PASSWORD = 'StrongP@ssword'
GO

--Restore the Certificate to Secondary server
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword'
GO
CREATE CERTIFICATE CertName
FROM FILE = 'FilePath\CertName.CER'
WITH PRIVATE KEY ( FILE = 'FilePath\CertKey.key',
DECRYPTION BY PASSWORD = 'StrongP@ssword')
GO
CLOSE MASTER KEY
GO


4. Create Endpoints on Principal and Secondary
You can do this at any time that makes sense to you, but it makes sense to me to ensure all the settings are up to date before moving onto the mirroring setup. Encrypted mirroring requires an encrypted endpoint (seems logical enough) so notice the specified encryption algorithm in the script below. Many other mirroring examples leave this critical piece out.

1
2
3
4
5
6
7
8
/********** ON MIRROR & Principal **********/

--Create Mirroring Endpoint on Principal & Secondary
CREATE ENDPOINT Name
STATE = STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING (ROLE=PARTNER,ENCRYPTION=SUPPORTED ALGORITHM AES)
GO


5. Backup Database on Principal
You will need to perform a full backup along with a Log backup on the database you plan to mirror. There is nothing special about the backup, just make sure that you have the latest log backups and that you finish before the next scheduled backup starts, otherwise you will have to restore another log. I hate having to do that.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
/********** On Principal **********/

--Backup Primary Database - Full
BACKUP DATABASE [DbName]
TO DISK = 'FilePath\DbNameFULL.BAK'
WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10,
NAME = 'DbFullBackup'
GO

--Backup Primary Database - Log
BACKUP LOG [DbName]
TO DISK = 'FilePath\DbNameLOG.TRN'
WITH NOFORMAT,INIT,COMPRESSION,SKIP,NOREWIND,NOUNLOAD,STATS=10,
NAME = 'DbLogBackup'
GO


6. Restore Backup to Secondary
Restoring the backups for an encrypted database is only slightly more involved than normal. You will have to Open the Master Key before the restore, otherwise it will fail.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
/********** ON MIRROR **********/

--Restore the Full Backup on the Secondary
USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword' --Don't forget this!
RESTORE DATABASE [DbName]
FROM DISK = 'FilePath\DbNameFULL.BAK'
WITH NORECOVERY,NOUNLOAD,STATS=10
GO

--Restore the Log Backup on the Secondary
RESTORE LOG [DbName]
FROM DISK = 'FileName\DbNameLOG.TRN'
WITH NORECOVERY,NOUNLOAD,STATS=10
GO
CLOSE MASTER KEY --Remember to close the master key once complete
GO


7. Enable Mirroring on Secondary
 Once the database is in place, you may want to verify that the secondary database is in a Restoring state. This is required to mirror successfully. The scripts I’ve provided will restore correctly, but restoring the database improperly is one of the most common and embarrassing issues you’ll encounter while trying to turn on mirroring. Set the partner by using the fully qualified server name or by the IP address. Theoretically either will work, but it depends on your network.

The most important thing in the script below is to include the line to Add Encryption by Service. This can be done earlier and in a separate script, but I find it easiest to include while turning on mirroring. If you leave this step out, you’ll receive a misleading error after you run the script in Step 8 that will leave you depressed and frustrated because it won’t tell you anything useful. Something like:

Msg 1416, Level 16, State 31
Database "DbName" is not configured for database mirroring

So make sure you run the scripts with all the necessary lines!

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/********** ON MIRROR **********/

--Enable Encrypted Mirroring on Secondary
USE MASTER
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongP@ssword'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY  --Most Important step!!
ALTER DATABASE DbName
SET PARTNER = 'TCP://PrincipalServer:5022'
GO
CLOSE MASTER KEY
GO


8. Enable Mirroring on Principal
This is the moment of truth. Did you follow all the steps correctly? If so, you should be fine. If you receive a database mirroring is not configured error, refer back to Step 7 and verify that encryption was added when you enabled mirroring. There is also a chance you will need to do another log restore to bring the log chain up to date. That’s purely based on how often you do log backups and how big the database is though.

1
2
3
4
5
6
7
8
/********** On Principal **********/

--Enable Encrypted Mirroring on Principal
USE MASTER
GO
ALTER DATABASE DbName
SET PARTNER = 'TCP://SecondaryServer:5022'
GO


Summary
Setting up Encrypted Mirroring is not overly difficult, but there are some quirks to it, and you can easily forget an integral step. I always refer to saved scripts to keep track of where I am in the process, and to make the overall setup faster.

Advertisements