Always On and TDE Example

Enabling TDE in AlwaysOn setup

1. Create MS SQL master key on primary server and replica server. Password can be the same or different.
2. Create MS SQL certificate on primary server and copy over to replica server. On Replica, restore the certificate.
3. Use script to enable TDE for each database. Make sure the database is fully encrypted before creating the backup. This can take a long time for large databases.
4. Create full and transaction log backup on primary server and copy over to replica server. Restore the database from backup on the replica server. Shrink database transaction log before creating backup.
5. Use script to add database to Availability Group on both the primary server and the replica server.
6. Make sure the Always On database is completely removed from the replica server first before adding the database to Always On on the primary server, or you will get the error “remote copy of database is not related to the local copy of the database” message.

Note:

• Can not setup via AlwaysOn wizard. Have to use script.
• You can setup multiple certificates on multiple servers for each database.
• If you have a large transaction database, might be good to run a transaction log backup and shrink the log file before setting up Always On.

Primary Server

Create master key and certificate.

USE master;  
GO  

CREATE MASTER KEY 
	ENCRYPTION BY PASSWORD = 'MyPassword01';  
GO  

CREATE CERTIFICATE Mycertificate01 
	WITH SUBJECT = 'Mycertificate01';  
GO  

BACKUP CERTIFICATE Mycertificate01   
TO FILE = 'D:\DatabaseSetup\TDE\MyMasterKey_DB01'  
WITH PRIVATE KEY   
(  
    FILE = 'D:\DatabaseSetup\TDE\MyMasterKey_DB01_Private',  
    ENCRYPTION BY PASSWORD = 'MyPassword01'  
);  
GO

Set specific database to TDE. Note: the TempDB will also be encrypted

USE TestAODB01;  
GO  

CREATE DATABASE ENCRYPTION KEY  
WITH ALGORITHM = AES_256  
ENCRYPTION BY SERVER CERTIFICATE Mycertificate01;  
GO  

ALTER DATABASE TestAODB01 
SET ENCRYPTION ON;  
GO  

TDE Check.

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;
GO

SELECT * FROM sys.dm_database_encryption_keys  
WHERE encryption_state = 3;  
GO 

USE master
GO

SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;

Create database backup .

BACKUP DATABASE [TestAODB01] 
TO  DISK = N'D:\DatabaseSetup\TDE\TestAODB01_Full.bak' WITH NOFORMAT, NOINIT,  
NAME = N'TestAODB01 Full Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
BACKUP LOG [TestAODB01] TO  DISK = N'D:\DatabaseSetup\TDE\TestAODB01_Log.trn' WITH NOFORMAT, NOINIT,  
NAME = N'TestAODB01 Log Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Add database to existing Availability Group.

USE master 
GO

ALTER AVAILABILITY GROUP AG01 ADD DATABASE [TestAODB01]

Replica Server

Create master key. The password can be the same or different from the primary server.

USE master
GO

CREATE MASTER KEY 
	ENCRYPTION BY PASSWORD = 'MyPassword01';  
GO 

Import certificate. First copy the certificate backup files to the replica server.

CREATE CERTIFICATE Mycertificate01
FROM FILE='D:\DatabaseSetup\TDE\MyMasterKey_DB01'
WITH PRIVATE KEY (
FILE = 'D:\DatabaseSetup\TDE\MyMasterKey_DB01_Private',
DECRYPTION BY PASSWORD='MyPassword01')

Restore database from backup files.

USE [master]

RESTORE DATABASE [TestAODB01] FROM  DISK = N'D:\DatabaseSetup\TDE\TestAODB01_Full.bak' 
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
GO
 
RESTORE LOG [TestAODB01] FROM  DISK = N'D:\DatabaseSetup\TDE\TestAODB01_Log.trn' WITH  FILE = 1,  
NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Add database to existing Availability Group.

USE master
GO 
ALTER DATABASE [TestAODB01] SET HADR AVAILABILITY GROUP = AG01;