IS Department

Database Mirroring

Posted in Network,Programming by Brian Russell on November 13, 2006

Database mirroring only provides protection at the database level, not the server level.  This means that Jobs and Logins are not mirrored.  This downside is usually handled by scripting by the clients she has dealt with.  Both servers should be identical if possible.  This ensures the maximum speed possible.  They should also have the same version of SQL Server.

Mirroring compared to Clustering: Clusters usually require 30-90 seconds to come online when failure occurs.  This is compared to the few seconds required for a mirror to come online (usually 3-6 seconds).  Clusters share the same data, so there is no data safety besides normal backups.  Mirrored servers do not share the same data, so there is redundancy built in.  Clustering requires special hardware to failover.  Mirroring only requires normal server hardware.  To operate mirroring in high availability mode, you will need a 3rd server to operate as the witness.  This server only serves as a heartbeat server and would only run SQL Express edition.  Mirroring requires a dedicated TCP endpoint.  No matter which choice you go with, session and transaction state are always lost.

When running in Synchronous mode (High Availability and High Protection), you have a small performance hit, but you have the best data protection available.  In Asynchronous mode, the data is moved to the mirror whenever the server has time.  This has the potential for data loss during failure if the principle hasn’t sent some operations over to the mirror.  You can easily switch between Asynchronous and Synchronous by just setting Partner Safety to Off or On respectively.

Licensing is a bit tricky.  The good news is that if you use the mirror only for mirroring purposes, you do not need a separate SQL Server license.  If you do anything else on the server, then you would need a license.  So, your SQL backups will still have to run on the principle server, not the mirror.  Jobs would run on the principle, but not the mirror.  If you have a failure on your principle and it fails over to the mirror, the mirror becomes the licensed server.  You have over a month to switch back over to the original principle server.

There is a good diagram of how high availability mode operates in the following PowerPoint presentation:

When setting up the principle and mirror, you need to backup the principle, and restore to the mirror with NO RECOVERY option.  This is important because if you don’t do it, you will have to re-create a backup and do it again.

For developers developing .NET applications, the SQL providers already provide Transparent Client Redirect.  When a connection is lost, it will automatically go over to the mirrored server.  The developers can define the mirrored server name by putting “…; FailoverPartner=<server name>;” into the connection string.

To be thorough, there are WMI events that allow people to tap into mirroring state changes if needed.


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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: