Recently I’ve had to set up a lot of database mirroring, and about half of those databases were encrypted. Setting up mirroring on unencrypted databases is normally a quick process, but encrypted mirroring requires a bit more work. While there are many examples of mirroring out there, it can be hard to find an example of encrypted mirroring that is not missing a step or two. You cannot use Object Explorer to do everything, so below are the necessary scripts to not only set up encrypted mirroring, but also how to setup Transparent Data Encryption.
1. Create and Backup Master Key and Certificate TDE requires a master key and a certificate, so those need to be created first and then immediately backed up. Backing these up is best practice, but the files will also need to be restored to the secondary server. If you already have TDE on the primary server, you can skip this step and go straight to Step 3.
2. Enable Transparent Data Encryption The next step is to enable TDE using the new certificate, and then turn TDE on. If TDE has been enabled previously, you should have already skipped to Step 3.
3. Restore Key and Certificate to Secondary This is your first step if you already have TDE turned on. Just make sure you copy the backup files to the secondary server so you can restore them.
4. Create Endpoints on Principal and Secondary You can do this at any time that makes sense to you, but it makes sense to me to ensure all the settings are up to date before moving onto the mirroring setup. Encrypted mirroring requires an encrypted endpoint (seems logical enough) so notice the specified encryption algorithm in the script below. Many other mirroring examples leave this critical piece out.
5. Backup Database on Principal You will need to perform a full backup along with a Log backup on the database you plan to mirror. There is nothing special about the backup, just make sure that you have the latest log backups and that you finish before the next scheduled backup starts, otherwise you will have to restore another log. I hate having to do that.
6. Restore Backup to Secondary Restoring the backups for an encrypted database is only slightly more involved than normal. You will have to Open the Master Key before the restore, otherwise it will fail.
7. Enable Mirroring on Secondary Once the database is in place, you may want to verify that the secondary database is in a Restoring state. This is required to mirror successfully. The scripts I’ve provided will restore correctly, but restoring the database improperly is one of the most common and embarrassing issues you’ll encounter while trying to turn on mirroring. Set the partner by using the fully qualified server name or by the IP address. Theoretically either will work, but it depends on your network.
The most important thing in the script below is to include the line to Add Encryption by Service. This can be done earlier and in a separate script, but I find it easiest to include while turning on mirroring. If you leave this step out, you’ll receive a misleading error after you run the script in Step 8 that will leave you depressed and frustrated because it won’t tell you anything useful. Something like:
Msg 1416, Level 16, State 31
Database "DbName" is not configured for database mirroring
So make sure you run the scripts with all the necessary lines!
8. Enable Mirroring on Principal This is the moment of truth. Did you follow all the steps correctly? If so, you should be fine. If you receive a database mirroring is not configured error, refer back to Step 7 and verify that encryption was added when you enabled mirroring. There is also a chance you will need to do another log restore to bring the log chain up to date. That’s purely based on how often you do log backups and how big the database is though.
Summary Setting up Encrypted Mirroring is not overly difficult, but there are some quirks to it, and you can easily forget an integral step. I always refer to saved scripts to keep track of where I am in the process, and to make the overall setup faster.