Configuring SQL Server 2005 Database Mirroring (includes 8-minute demonstration)

  • Article

SQL Server 2005 Database Mirroring is a high availability feature that maintains a duplicate copy of a database (close by or across the globe), keeps it up to date (synchronously or asynchronously) and can even provide automatic failover.

There are other SQL Server technologies that can use be used in similar situations, like database clusters, log shipping and replication. Mirroring is unique because:

  • It does not require special SAN hardware (All you need is two servers connected via a network)
  • It works with geographically dispersed systems (Your data updates are sent across to network to the mirror server)
  • It is easy to configure (It works at the database level and there are no complex job configuration)
  • It provides an option for quick, automatic failover (It actually provides a faster failover than a failover cluster solution)
  • It can work both synchronously or asynchronously (For failover you want it to be synchronous, but for disaster recover you might not need that)
  • Your mirror copy can be accessed (You could create reports from the a database snapshot of the mirror database)

Mirroring also has some restrictions and it will not work in every scenario. Here are some of those:

  • You need at least three servers for automatic failover (The third server is called a witness. Failover clustering can do that with two servers. You can do manual mirroring failover with two servers)
  • You can only have two copies of the data: the main one is called the "principal" the standby copy is called the "mirror" (Replication allows multiple copies and they can all be accessible)
  • If your data changes a lot, it might not be the best choice, since all updates needed to be sent to the mirror (Failover clustering can work much faster in this scenario, since nodes share storage)
  • There’s no easy way to partition the data or selectively copy subsets of a database (Replication can accomplish that with both vertical and horizontal partitioning)
  • For automatic failover, you must be able to update your client data access components (Failover clustering requires no client change. Mirroring in SQL Server 2008, currently in beta, will address that)

If it sounds interesting, check this step-by-step demonstration of how to configure Mirroring in a set of SQL Servers (you can view it online or download the video for offline viewing):
https://www.livemeeting.com/cc/microsoft/view?id=SQLMirroring&pw=josebda

I hope you have the time to check the video. You could also get all the details about the technology, which can be found at:
https://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx