SQL Server Database Mirroring

03 Nov

This feature available in Enterprise editions of SQL server is saweeeeet. It makes sense that its only in the enterprise version because most average joes wouldn’t have a need for a 100% uptime database. Yes that is 100% as in absolutely no lost transactions even when patching windows.

If you have three instances of sql server ready and a very important database mirroring can be setup in a matter of minutes… that is if you have already figured out all the setup quirks.

Before you dive in make sure you can at least make a connection string change in the application. You’ll need to set the Failover Partner=Whatever; Secondly, the application will have to be aware that mirroring is enabled and know to retry quires and connections for the few seconds it takes to failover. And for you really quick thinkers, if you are going to retry a transaction you need to also be able to know that it won’t get committed twice.

So enough about your important application that needs to be up all day and night, lets talk about setup. Here is the quick rundown:

1. take full backup
2. take log backup
3. restore full to 2nd server
4. restore log to 2nd server (don’t recover just yet)
5. make sure you have a mirroring user id
6. prinicipal database->properties database mirroring
7. configure security wizard (principal, mirror, witness)
8. open new sql ports in the firewall (5023 etc.)
9. start mirroring

When naming your servers don’t make the mistake of calling one “principal” and one “witness” as these are relative. When a failover occurs either by clicking the button or automatically via the witness the roles of the databases are changed. Also, mirroring is at the database level so you could have a handful of mirrors and a handful of principal databases on the same server.

To understand a bit better lets follow the high safety transaction.

1. user clicks submit in your app
2. app tries to open connection
3. app tries query
4. database server completes transaction on principal
5. sends transaction to mirror
6. principal sends response to user “success”

So lets say inbetween 3 and 4 someone has a typo in their remote shutdown command and kills your principal server

1. user clicks submit
2. app opens conn
3. app tries query
4. query times out
5. app check conn (closed)
6. app opens conn ( fail loop / wait 1 second )
7. witness notices principal is down and fails over
8. app opens conn to mirror which is now set to principal
9. transaction committed successfully OR primary key error and transaction already committed
10. return success

Now that you have done the necessary slapping upside the head, you bring your principal back online. The witness automatically recognizes this and sets it to mirror. Now, the new principal database copies all the transaction from the point it took over to the mirror database and just like a magic trick you mirroring session is back.

Leave a comment

Posted by on November 3, 2010 in SQL Admin


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: