In my current position in Quality Assurance, I sometimes field some odd questions from our Support Team. Recently, they had a client inquire about the feasibility of our product on a mirrored database. Not being a seasoned DBA (or really even an unseasoned one), I proceeded to look up what the heck a mirrored database is.
Database mirroring is a technique of creating a failover of a specific database to increase availability. While a Failover Cluster is implemented at the server level, Database Mirroring is implemented at the database level (I know, who knew?).
There are two modes that a mirrored session can run: Asynchronous or Synchronous. In an asynchronous session, any changes or transactions to the server are committed without waiting for a response from the mirror that it has written the log to disk. In a synchronous session, the changes are committed to both partners, which decreases the likelihood of data loss, but negatively impacts performance.
OK, so that’s a quick and dirty explanation of what a mirrored database is, but how do you set one up? Well, it’s relatively simple. For this example, we’re going to set up an asynchronous mirror session using only a target and a mirror. Check out MSDN for details on the other types (including sessions that involve a “witness”).
Before we start, a couple points:
- Must be on the same version of SQL Server.
- Must be on different instances of different hosts.
- Credential must exist on the MASTER database of both servers.
- Your principle database must be in FULL recovery mode.
Step 1: Create a full back-up the database you want to mirror. You can do this using the T-SQL command or through the GUI. If you’re reading about mirrored databases, I assume you don’t need a step-by-step on backing up a database or are at least proficient enough with Google to, um, google it.
Step 2: Restore the backup on the instance you want the mirror on using NORECOVERY mode. Again, see Google if necessary.
Step 3: On your principal server instance, right click on the database you want to mirror and select Tasks => Mirror. This brings up the “Mirroring” page of the Properties dialog box.
Step 4: Click “Configure Security” to launch the security wizard.
Step 5: Do you want to configure security to include a witness? Not today, chief.
Step 6: Set the endpoint or accept what Management Studio provides for you.
Step 7: Ensure your operating mode is set to High Performance (asynchronous).
Step 8: If everything so far has gone as planned, when you click “Start Mirroring”, you should have a running database mirror.
Some things to confirm if problems arise:
- Security permissions between the two servers.
- Network connectivity between the servers. This is a big one, as there could be many things to trip it up, like the firewall blocking your port (make sure good ‘ole 1433 is open (or whatever port your company uses if not the default)), TCP connections not enabled on the server, etc.
I hope this little post helps you through setting up the mirror. It’s not as daunting as I first thought, though my only previous experience with high-availability SQL was setting up a clustered, virtual environment (six months into the job, and I had just learned what a virtual environment was. “You can actually do that?!”). Thanks for reading!