So in my previous post I went through the reasons why you might want to use SQL mirroring and how you could possibly sell the added expense to your management. In this post I’m going to cover the actual SQL commands for doing so. There is a wizard you can run through to actually set this up (right-click your database, go to Properties, click on Mirroring on the left side, run the “Configure Security” wizard, but what’s the fun in that? Plus I’ve had issues with the wizard not quite setting things up right before.
Pre-req’s: for the purpose of this article let’s assume that you have 3 servers (physical or VM, doesn’t matter), with Windows 2008 R2 installed, joined to your AD domain. You also have SQL 2008R2 installed, latest service pack. All instances of SQL are running the default instance and running under the same Windows AD service account.
Let’s assume your servers are called SQLRepl01, SQLRepl02, and SQLRepl03. We’ll use SQLRepl01 as the Principal instance, SQLRepl02 as the Mirror, and SQLRepl03 as the Witness. You can actually use any of the 3 for any of these roles and for your next database you could change it completely.
For my test I created a database called ReplicationTest and populated it with data using this wonderful article: http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/249/creating-random-sql-server-test-data.aspx
Okay, so now that all that’s done, let’s actually get started.
- First we need to create an EndPoint for each SQL server so that they can listen to the traffic for the Mirroring. This needs to be run on all 3 SQL instances. 5022 is the default port used for this, but you can specify any unused port you want, just make sure that the servers can talk to each other on this port (i.e. opened in the Windows Firewall or any other firewalls that exist):
CREATE ENDPOINT Mirroring STATE=STARTED AS TCP (LISTENER_PORT=5022) FOR DATABASE_MIRRORING (ROLE=ALL) GO
- Okay, so now that your endpoints are all created we need to do a backup of the existing database and then restore it on your mirror instance. This creates, in essence, a “seed” database. Run the following backup commands on SQLRepl01
USE MASTER GO BACKUP DATABASE ReplicationTest TO DISK='C:TEST.BAK' GO BACKUP LOG ReplicationTest TO DISK='C:TEST.LOG' GO
- Browse to C: on SQLRepl01 and copy test.bak and test.log to \SQLRepl02c$. Then login to SQLRepl02 and run the following commands. Note that you have to restore with NORECOVERY to leave the database in a state that allows us to replicate. If you don’t then you’ll need to restore the database again. This ensure that the 2 databases are a mirror of each other.
RESTORE DATABASE ReplicationTest FROM DISK='C:TEST.BAK' WITH NORECOVERY GO RESTORE LOG ReplicationTest FROM DISK='C:TEST.LOG' WITH NORECOVERY GO
- This is what the restored database will look like:
- Okay, now that the databases are there we can actually set up the mirror. IMPORTANT: run this command on the mirror instance (i.e. SQLRepl02). If you changed the port away from the default of 5022, change it in the code below.
ALTER DATABASE ReplicationTest SET PARTNER= 'tcp://SQLRepl01:5022' GO
- Run the following commands on the Principal server (i.e. SQLRepl01). The first one tells it that the second server is the Mirror and the 2nd command tells it that the 3rd server is the witness.
ALTER DATABASE ReplicationTest SET PARTNER='tcp://SQLRepl02:5022' GO ALTER DATABASE ReplicationTest SET WITNESS='tcp://SQLRepl03:5022' GO
At this point your Mirroring set up is complete. This is what it will look like on the Principal and Mirror:
You can test it by shutting down the primary server and making sure that your Principal automatically fails over. Or you can add data to the principal and take a snapshot of the secondary to ensure the data is getting there (because you can’t access the mirror directly this is the only way to query the data). Note that even though mirroring works with SQL standard, snapshots don’t so if you’re using standard the only way to view the data would be to failover:
CREATE DATABASE Test_snapshot ON (NAME = 'ReplicationTest', FILENAME = 'C:BackupsTest_Snapshot.SNP') AS SNAPSHOT OF ReplicationTest GO Select * from Test_snapshot GO
One of the true values of this is that you can set up 3 databases (or as many as you want) and have each one be Principal on any of the 3 servers and their Mirror and Witness on any of the other 2. Note that if you use the Wizard to create all of this it won’t set up the Endpoint on the Witness instance correctly. Since we created the endpoint with the code ROLE=ALL we can use each of the servers for any of the roles we want.
And that’s it! Let me know if you have any questions.