Encryption and Mirroring

DbDefence can be used on mirrored databases. You may encrypt both databases: on the principal and on the mirror server. Or one of them. Encryption works independently.

Principal server

To encrypt the database on the principal server (the database to be mirrored), you need to break the mirror first. This is required because the encryption process needs to take the database offline, but SQL Server does not allow that for the mirrored database.

Steps to encrypt a mirrored database on the Principal Server:

1. Stop mirroring for the database.

2. Encrypt the database.

3. Backup the database. You need this to setup mirroring. If you don't plan to encrypt the mirror, create an unencrypted backup.

Mirror Server

1. Drop the mirror database.

2. Create an empty database and encrypt it with the same password as the source.

3. Restore the database from the encrypted backup (with the "WITH NORECOVERY" option as required for mirroring).

Setting up mirroring on Principal Server

If your favorite tool to manage a database is SSMS, then provide access for SSMS to the encrypted database. If you prefer SQL, then just unlock access to the encrypted database with the following query:

OPEN SYMMETRIC KEY dbdx DECRYPTION BY PASSWORD='MyPassword'

then run SQL script to set up a mirror.