Technical Savant – Jason Jones

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.

1. 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

2. 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

3. Browse to C:\ on SQLRepl01 and copy test.bak and test.log to \\SQLRepl02\c$. 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

4. This is what the restored database will look like:



5. 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

6. 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:\Backups\Test_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.

Tags: AD, cluster, mirroring, replication, sql, windows

So you’ve made the decision that you want to use SQL Mirroring for your DR solution. You have several options for DR for SQL databases: you can use Windows clustering, log shipping, or mirroring. My preference in any scenario is to just use Windows Clustering, but there are reasons why you could go with another solution. Clustering is nice because it’s outside of SQL and just sits at the OS-level, but by definition you are going to have some hardware sitting there idle even if you go Active/Active (assuming you planned for 1 server going down and allocated your CPU/RAM appropriately. Plus doing it at the OS level doesn’t require you to be an expert or have a fair bit of knowledge of SQL. Using mirroring also allows you to be fully HA as you can have both servers sitting in different sites on different subnets and not have to worry about having your network guys stretch a subnet geographically, which they never want to do.

Note that SQL 2008 (and all versions, for that matter) cannot be used on a Windows 2008 geo-cluster that is on different subnets.

And in case you weren’t aware: HA = Highly Available, DR = Disaster Recovery. Separate, but not necessarily mutually exclusive technologies.

All that being said, let me give you a quick rundown on SQL Mirroring. I can’t say it better than Microsoft, with their gaggle of technical writers, so here’s an article from them on the benefits of Database Mirroring:

http://msdn.microsoft.com/en-us/library/ms189852.aspx

A few items of note that I always like to tell my clients:

• You need a minimum of 3 instances of SQL for full redundancy on Mirroring (Principal, Mirror, Witness). You can technically do it in 2, but you lose the capability for automatic failover without the witness. Think of it as the 3^rd vote if this were a cluster.
• All SQL servers need to be a member of an AD domain and running under the same service account
• There is a copy of the database on both the Principal and the Mirror.
• The Witness allows for automatic failover in the event of a server failure
• Mirroring is on a per-database level, not per-server level. A SQL server can be a mixture of single databases plus mirror instances.
• The database can be set in synchronous or asynchronous mode
• Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance
• Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency

There are also a few selling points for Mirroring, if you’re having trouble selling having all these servers to your management or to your clients.

• Since mirroring requires 3 servers be active at all times, you can run databases on all 3 servers, some mirrored and some not. You can also have any combination of Principal/Mirror/Witness running across the 3 servers (i.e. Active/Active/Active)
• Mirroring can be done across multiple sites and subnets, thus allowing for full HA/DR

In my next article I’ll get into the nitty-gritty of how to actually set this up and show you all the SQL commands for this.

Tags: AD, cluster, mirroring, replication, sql, windows