Sql-server – Enabling TDE without breaking existing mirror

mirroringsql serversql-server-2008-r2transparent-data-encryption

I've been trying to enable TDE in a mirrored SQL Server 2008 R2 configuration. Enabling TDE is not so difficult, though when I enable TDE on the principal, the mirror database goes in to a suspended state.

There are a few good articles here and here as well as many others, but they all demonstrate enabling TDE on a mirrored configuration with importing the database in the mirror before enabling encryption. I've not yet found a way to simply turn on TDE in an existing mirrored system.

I've gone so far as to export the service master key and service keys from the Principal and import them in the mirror. I've tried transact-sql'ing mirroring in the hopes that what I was seeing in the GUI was erroneous due to some advanced mirroring things going on, with no success.

Is enabling TDE across an existing mirrored SQL Server setup possible?

Best Answer

From the looks of this article, you would probably have to break the mirror first, setup TDE, and then setup the mirror again. Not ideal, but it would work.

Related Solutions

SQL Server 2008 – Database Mirroring with TDE

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

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.

Best Answer

Related Solutions

SQL Server 2008 – Database Mirroring with TDE

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

Related Question