Database Mirroring with TDE

I have the requirement of mirroring a few databases and also using transparent data encryption (TDE) on them as our data must be encrypted while 'at rest'.

I have setup TDE on both the principal and the mirror. The problem I have comes into play when I am setting up the mirroring of the two databases. Since I am using TDE I don't know of a way to setup mirroring via the gui, so I am forced to use t-sql to get the job done.

Below is the code I have used on the mirrored server

--Restore the full backup to the mirrored mdf and ldf
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE DATABASE TDE
   FROM disk = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
      WITH NORECOVERY,
       REPLACE,
       MOVE 'TDE' TO 'E:\TDE.mdf',
      REPLACE,
      MOVE 'TDE_log' TO 'G:\TDE.ldf'
CLOSE MASTER KEY 
GO

--Restore the log backup to the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
RESTORE LOG TDE
    FROM DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
    WITH NORECOVERY;
CLOSE MASTER KEY
GO

--Drop/Create Mirroring endpoint on mirror
--DROP ENDPOINT TDE
CREATE ENDPOINT TDE
    STATE = STARTED
    AS TCP ( LISTENER_PORT = 7025 )
    FOR DATABASE_MIRRORING (
        ROLE = PARTNER
        );
GO

--Check the endpoints for the mirror
USE MASTER
SELECT * FROM sys.database_mirroring_endpoints
GO

--Set the principal on the mirrored db
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER DATABASE TDE SET PARTNER = 'TCP://PRINCIPAL.DOMAIN.local:7022'
GO
CLOSE MASTER KEY
GO

Below is the code that I use on the principal server.

```
----------------------Mirroring Section----------------------------------

--Full Backup of Principal
USE TDE
GO
BACKUP DATABASE TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_FULL.bak'
WITH COMPRESSION,
NAME = 'Full Backup of TDE';
GO

---Log Backup of Principal
USE TDE
GO
BACKUP LOG TDE
TO DISK = '\\SERVERNAME\SQL_Stuff\Backup\TDE_LOG.trn'
WITH COMPRESSION,
NAME = 'Log backu

Found a website with a comment on it.

I added the code to just after where I restore the key and cert

--Mumbojumbo to get mirroring to work
OPEN MASTER KEY DECRYPTION BY PASSWORD = '1Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
GO

It works like a charm, it makes a little sense that I had to encrypt the master key that I restored with the service master key of the new server. I guess.

shrug

StackExchange Database Administrators Q#2100, answer score: 11