Database Mirroring and Other High-Availability Solutions


Database mirroring is another weapon in the arsenal of SQL Server high-availability solutions. There are at least four high-availability solutions that SQL Server 2005 provides. Of course, each solution has some overlaps with the others, and each has some advantages and disadvantages. The following is the list of these technologies.

  • Failover clustering: This is a typical solution for high availability, with a two-node Windows failover cluster with one SQL Server instance. Clustering is discussed in more detail in Chapter 20.

  • Database mirroring: For this discussion, we will consider the high-safety mode with witness.

  • Log shipping: SQL Server built-in log shipping. Log shipping is discussed in detail in Chapter 19.

  • Transactional replication: For comparison purposes, we'll consider a separate distribution server with a single subscriber server as a standby if the publisher fails.

We will compare database mirroring with these other technologies here.

Database Mirroring and Clustering

Obviously, the most distinct difference between database mirroring and a cluster solution is the level at which each provides redundancy. Database mirroring provides protection at database level, as we have seen, whereas a cluster solution provides protection at the server-instance level.

As we discuss in the "Mirroring Multiple Databases" section, if your application requires multiple database access, clustering is a better solution. If you need to provide availability of one database at a time, mirroring is better solution and has many advantages (for example, ease of configuration) compared to clustering.

Unlike clustering, database mirroring does not require proprietary hardware and does not have a potential failure point with shared storage. Database mirroring brings the standby database into service much faster than any other high-availability technology and works well with new capabilities in ADO.NET and SQL Native Access Client for client-side failover.

Another important difference is that in database mirroring, the principal and mirror servers are separate SQL Server instances with distinct names, whereas a SQL Server instance on a cluster gets one virtual server name and IP address that remains the same no matter what node of the cluster is hosting the instance.

You cannot use database mirroring within a cluster, although you can consider using database mirroring as a method for creating a hot standby for a cluster instance database. If you do, be aware that because a cluster failover is longer than the timeout value on database mirroring, a high-availability mode mirroring session will react to a cluster failover as a failure of the principal server. It would then put the cluster node into a mirroring state. You can increase the database mirroring timeout value by using following command:

 ALTER DATABASE <db_name> SET PARTNER TIMOUT <interger_value_in_seconds> 

Database Mirroring and Transactional Replication

The common process between database mirroring and transactional replication is reading the log on the originating server. Although the synchronization mechanism is different, database mirroring directly initiates IO to the log file to transfer the log.

Transactional replication can be used with more than one subscriber, while database mirroring is a one-database-to-one-database solution. You can read near real time data on the subscriber database, while you cannot read data on mirrored databases, unless you create database a snapshot, where you can read static data as of the create time of the snapshot.

Database Mirroring and Log Shipping

Database mirroring and log shipping both rely on moving the log and restoring it. In database mirroring, the mirror database is constantly in a recovering state, and that's why you cannot query the mirrored database. In log shipping, the database is in standby mode, so you can query the database if the log is not being restored at that time. Also, log shipping supports the bulk-logged recovery model, while mirroring does not.

If your application relies on multiple databases for its operation, you may want to consider log shipping for failover. Although sometimes it is little bit tedious to set up log shipping going the other way once a failover has occurred, mirroring is easy in that aspect.

You can use log shipping and mirroring together. You can use log shipping to ship the log to a remote site for disaster recovery and have a database-mirroring, high-availability configuration in house.

In the high-performance mode, there is a potential for data loss if the principal fails and the mirror is recovered using a forced service recovery. If you are log shipping the old principal, and if the transaction-log file of the old principal is undamaged, you can make a "tail of the log" backup of the principal to get the last set of log records from the transaction log. If the standby log-shipping database has had every other transaction-log backup applied to it, you can then apply the tail of the log backup to the standby server and not lose any of the old principal's data. You can then compare the data in the log-shipping standby server with the remote database and potentially copy missing data to the remote server.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net