From what I’ve seen, Transparent Data Encryption (TDE) for SQL Server isn’t as heavily used a feature as many of the HA/DR options, but if your company has a requirement for encryption at rest, it’s a great feature to be able to access out of the box. On the MySQL side, I had done extensive testing with Gazzang, this was managed at the OS level, versus TDE at the database level.

Some things to note with TDE in SQL Server:

Requires SQL Server Enterprise Edition
There is some overhead, approx 3-5% (I need to run performance tests to validate this, but this is what I’ve read)
Any server where the database will be restored (either as a replica, or a standalone copy), will require the certificate restore & the Enterprise version of SQL Server
If the certificate for the database is lost, the database is not recoverable
Backup compression is not supported with TDE databases
Your tempdb database will also automatically be encrypted if any databases on an instance are encrypted.
You cannot add a TDE database to an availability group via the GUI, it must be added via T-SQL

I’m running my tests on a SQL 2012 Enterprise installation.

Initial Database Encryption

Step

Details

Backup database to be Encrypted

The database to be encrypted must be backed up prior to the initial encryptions

-- Create database TESTTDE
CREATE DATABASE [TESTTDE]
 ON  PRIMARY 
( NAME = N'TESTTDE', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TESTTDE.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'TESTTDE_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\TESTTDE_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [TESTTDE] SET  RECOVERY FULL 
GO
 
-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-NoEncrypt.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Create the Database Master Key

A certificate is required to encrypt the instance

Different certificates can be created for each database on an instance

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE1_Use1Strong2Password3Here!';
go
 
-- Check for the Database Master Key
SELECT    
    name,
    key_length,
    algorithm_desc,
    create_date,
    modify_date
FROM sys.symmetric_keys

name    key_length    algorithm_desc    create_date    modify_date
##MS_DatabaseMasterKey##    256    AES_256    2014-07-02 16:33:12.370    2014-07-02 16:33:12.370
##MS_ServiceMasterKey##    256    AES_256    2014-06-20 14:22:37.053    2014-06-20 14:22:37.053

Create & Backup the certificate

CREATE CERTIFICATE TDETestDBCert 
WITH SUBJECT = 'TDETest DB Certficate'
go
 
-- Check for Cert
select 
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'

name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    NULL

Create database Encryption Key

USE TESTTDE
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE TDETestDBCert
GO

Backup the Certificate

USE MASTER
GO
BACKUP CERTIFICATE TDETestDBCert
TO FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.cer'
WITH PRIVATE KEY (FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.pvk',
    ENCRYPTION BY PASSWORD='My1Secure2Password!')    
 
-- Check Backup
select 
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'

name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    2014-07-02 20:46:55.740

Encrypt Database

Use TestTDE
ALTER DATABASE TESTTDE
SET ENCRYPTION ON
GO
 
-- Check Status
USE master; 
GO
 
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;

Encryption State Key:

0 = No database encryption key present, no encryption

```
1 = Unencrypted
```
```
2 = Encryption in progress
```
```
3 = Encrypted
```
```
4 = Key change in progress
```
```
5 = Decryption in progress
```
```
6 = Protection change in progress 
```

name    is_encrypted    encryption_state    percent_complete    key_algorithm    key_length
tempdb    0    3    0    AES    256
TESTTDE    1    3    0    AES    128
model    0    NULL    NULL    NULL    NULL
WingTipToys2012    0    NULL    NULL    NULL    NULL
AdventureWorks2012    0    NULL    NULL    NULL    NULL
master    0    NULL    NULL    NULL    NULL
msdb    0    NULL    NULL    NULL    NULL
Jupiter    0    NULL    NULL    NULL    NULL
Neptune    0    NULL    NULL    NULL    NULL

Backup Encrypted Database

Use standard backup process

-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypted.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restoring Encrypted Database

A TDE database to be restored requires that the server have the certificate restored.

You will need to know the backed up certificate password, created in step 5 in the Initial Database Encryption section

Part 1: Restore Certificates

Step

Details

Copy database & certificate backups to new host

Encrypted database backup
Certificate backup file & key

Create the Database Master Key on the new instance

Icon

This is only required if the instance has not yet been configured with a Master Key

A certificate is required to encrypt the instance

Different certificates can be created for each database on an instance

USE master;
GO
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'INSTANCE2_Use1Strong2Password3Here';
go
 
-- Check for the Database Master Key
SELECT    
    name,
    key_length,
    algorithm_desc,
    create_date,
    modify_date
FROM sys.symmetric_keys

name    key_length    algorithm_desc    create_date    modify_date
##MS_DatabaseMasterKey##    256    AES_256    2014-07-02 17:12:01.127    2014-07-02 17:12:01.127
##MS_ServiceMasterKey##    256    AES_256    2014-06-13 16:13:05.383    2014-06-13 16:13:05.383

Restore Certificate

Icon

You will need the certificate backup, private key file backup, and the password used to create the backup

USE master;
GO
CREATE CERTIFICATE TDETestDBCert
    FROM FILE = 'C:\Restore\TDETestDBCert.cer'
    WITH PRIVATE KEY (FILE = 'C:\Restore\TDETestDBCert.pvk', 
    DECRYPTION BY PASSWORD = 'My1Secure2Password!');
GO
 
-- Verify Restore
select 
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'

name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    NULL

Backup the Certificate on the restored instance

USE MASTER
GO
BACKUP CERTIFICATE TDETestDBCert
TO FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.cer'
WITH PRIVATE KEY (FILE='C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\Backup\TDETestDBCert.pvk',
    ENCRYPTION BY PASSWORD='My2Secure2Password!')    
 
-- Check Backup
select 
    name,
    pvt_key_encryption_type_desc,
    [subject],
    [start_date],
    [expiry_date],
    pvt_key_last_backup_date
from sys.certificates
where name not like '##%'

name    pvt_key_encryption_type_desc    subject    start_date    expiry_date    pvt_key_last_backup_date
TDETestDBCert    ENCRYPTED_BY_MASTER_KEY    TDETest DB Certficate    2014-07-02 20:39:34.000    2015-07-02 20:39:34.000    2014-07-02 21:16:54.673

Part 2 A – Restore Database (Standalone)

Icon

NOTE – This is only for a standalone database. Please refer to Restore database with Availability Group if configuring a replica set.

Step

Details

Restore Database

Restore for Standard backup

Run Standard database restore

RESTORE FILELISTONLY FROM DISK='C:\Restore\TESTTDE-Encrypted.bak'
GO
 
RESTORE DATABASE TESTTDE 
FROM DISK='C:\Restore\TESTTDE-Encrypted.bak'
WITH MOVE 'TESTTDE' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE.mdf',
    MOVE 'TESTTDE_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE_log.ldf'
GO

LogicalName    PhysicalName    Type    FileGroupName    Size    MaxSize    FileId    CreateLSN    DropLSN    UniqueId    ReadOnlyLSN    ReadWriteLSN    BackupSizeInBytes    SourceBlockSize    FileGroupId    LogGroupGUID    DifferentialBaseLSN    DifferentialBaseGUID    IsReadOnly    IsPresent    TDEThumbprint
TESTTDE    C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE.mdf    D    PRIMARY    10485760    35184372080640    1    0    0    3DAD2405-E559-4E2E-B009-196F3DFCA37A    0    0    2424832    512    1    NULL    30000000033200197    3C8B27A5-69F9-4D00-AD03-B6BE6B43F5BD    0    1    0x527486CBFB44D8EF339195F76049DC95A436BEA8
TESTTDE_log    C:\Program Files\Microsoft SQL Server\MSSQL11.ALWAYSON\MSSQL\DATA\TESTTDE_log.ldf    L    NULL    10485760    2199023255552    2    0    0    7265BDCF-E038-4EEF-A1A4-C8BFF652CCFE    0    0    0    512    0    NULL    0    00000000-0000-0000-0000-000000000000    0    1    NULL

Processed 296 pages for database 'TESTTDE', file 'TESTTDE' on file 1.
Processed 2 pages for database 'TESTTDE', file 'TESTTDE_log' on file 1.
RESTORE DATABASE successfully processed 298 pages in 0.079 seconds (29.377 MB/sec).

Part 2 B – Restore Database to Existing Availability Group

TDE databases cannot be managed by SSMS Availability Group Wizard, and must be configured by command line & manual restore.

Icon

This is only required for configuring a replication set

Step

Details

Add Master to Primary in Availability Group

Assumptions:

Availability group (Group-Alpha) exists
Database is on the primary node of Group-Alpha.
1. If it’s not the primary node, failover to create as the primary node

USE master
GO
ALTER AVAILABILITY GROUP [Group-Alpha]
ADD DATABASE [TestTDE];
GO

Backup Primary database and log

-- Full Database backup
BACKUP DATABASE [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypt-AGSetup.bak'
WITH NOFORMAT, NOINIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
 
-- transaction log backup
BACKUP LOG [TESTTDE] TO  DISK = N'C:\Backup\TESTTDE-Encrypt-AGSetup-1.trn'
 WITH NOFORMAT, NOINIT,  
 SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Restore Database no recovery

Copy full & transactionlog backup to secondary
Run full restore with NORECOVERY
Run Transaction log restore with NORECOVERY

-- Test backup file
RESTORE FILELISTONLY FROM DISK='C:\Restore\TESTTDE-Encrypt-AGSetup.bak'
GO
 
-- Run full restore no recovery
RESTORE DATABASE [TESTTDE] FILE = N'TESTTDE'
FROM  DISK = N'C:\Restore\TESTTDE-Encrypt-AGSetup.bak'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO
 
-- Run transaction log restore - no recovery
RESTORE LOG [TESTTDE] 
FROM  DISK = N'C:\Restore\TESTTDE-Encrypt-AGSetup-1.trn'
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

Add Secondary to Availability Group

USE master
GO
 
ALTER DATABASE [TestTDE] SET HADR AVAILABILITY GROUP = [Group-Alpha]

5 Repeat on any additional Replicas Repeat steps 3 (restore database with norecovery) and step 4 (add secondary to availability group) for any other replicas

Additional Reference

Adding a TDE database to an Availability Group:

	Capturing Blocking \|… on Capturing Foreign Key Err…
	Capturing Blocking \|… on Capturing Deadlocks
	Capturing Foreign Ke… on Capturing Deadlocks
	dineshramitc on Group & Count Objects by D…
	dineshramitc on SQL Server Peer-to-Peer Replic…

The Polyglot DBA

Multiplatform DBA – mainly focusing on MySQL, SQL Server & MongoDB

Configuring a Database with Transparent Database Encryption & Availability Groups

Initial Database Encryption

Restoring Encrypted Database

Part 1: Restore Certificates

Part 2 A – Restore Database (Standalone)

Part 2 B – Restore Database to Existing Availability Group

Additional Reference

Leave a Reply Cancel reply

Initial Database Encryption

Restoring Encrypted Database

Part 1: Restore Certificates

Part 2 A – Restore Database (Standalone)

Part 2 B – Restore Database to Existing Availability Group

Additional Reference

Share this:

Like this:

Related

Leave a Reply Cancel reply