Monday, July 2, 2012

No muss and/or fuss SQL DB Mirroring

So I've never really known what "muss" is, but this doesn't have any of that. It's sans muss. (without muss)

I must say that the SQL DB Mirroring error messages (at least as of 2008R2) leave something to be desired. Because of this, I figured I'd write a straightforward HOWTO on the topic. I've performed these steps on SQL 2008R2, but I suspect it will work on other versions as well.

This guide assumes the following:

You have a DB on the Principal server that you wish to mirror

That DB is NOT on the mirror target server (drop/delete it if so)

You do NOT intend on using a witness server.... using one however won't invalidate this guide, but we don't address the extra couple steps required to do so.

Perform a full backup to disk of the database in question. Ensure this is a new backup that doesn't include any previous backups.

Perform a tranlog only backup on the same database. While this step shouldn't be required, I've found that restoring a tranlog backup after the full results in a successful mirror pairing substantially more often.

Copy the backup files to the Mirror server

On the Mirror server, perform a full restore of the desired database:

On the "General" page, ensure the "To database" is set to the exact same database name

On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"

Right click the newly restored DB and select "Tasks"->"Restore"->"Transaction Log"

On the "General" page, select the appropriate file

On the "Options" page, select "Leave the database non-operational..." "RESTORE WITH NORECOVERY"

On the Principal server, select "Tasks"->"Mirror"

Click "Configure Security"

Select "No" for "Include Witness Server" and click "Next>"

Leave the ports default unless there is a reason to do otherwise. Make sure this port is properly opened on all firewalls (including the local firewall) between the to servers!

Select the target server, set the settings per your requirements (default are fine) and click "Next>". Double check that all firewalls are open on and between both servers.

Enter the appropriate service account for this replication. While it is a lesser secure configuration, using the SQL instance domain service account (if you have one) works without issue. NOTE: If your SQL service instance is running as a local user, (including system) you must use certificate authentication which is NOT covered here.

Click "Next","Finish" then "Start Mirroring". All should work at this point. Note it is normal for the state on the Mirror server to be "Synchronized/Restoring".

Troubleshooting:
Explicitly grant connection rights to the "Mirroring" endpoint (note that's a name not a concept) to a given service account:
GRANT CONNECT ON ENDPOINT::Mirroring TO "Domain\Username"

Note that if you re-create the mirror it does not delete the endpoint. To delete the endpoint on each server: (assuming you took the default name of "Mirroring")
DROP ENDPOINT MIRRORING