SQL Server 2008 TDE

To use TDE, follow these steps.

1 Create a master key

2 Create or obtain a certificate protected by the master key

3 Create a database encryption key and protect it by the certificate

4 Set the database to use encryption


The following example illustrates encrypting and decrypting the AdventureWorks database using a certificate installed on the server named MyServerCert.


USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'
go
USE AdventureWorks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert
GO
ALTER DATABASE AdventureWorks
SET ENCRYPTION ON
GO

SQL 2008 Backup compression and Data Encryption

1. SQL backup compression and TDE encryption are mutually exclusive.
2. CPU impact (Expect 25% to 30% CPU utilization increase at least)
3. Require EE version, but any 2008 version can restore it
4. Compressed backup need to reside on the same media. Uncompressed backups reside on a different media. Compressed and uncompressed backups can not co exist in a media set
5. Previous bersions of SQL server can not read compressed backups.
6. NTbackups can not share a tape with compressed SQL server backups.


To calculate the compression ratio of a backup, use the values for the backup in the backup_size and compressed_backup_size columns of the backupset history table, as follows:

backup_size:compressed_backup_size