SQL Server Clustering and mirroring/log shipping
SQL Server provides a variety of high availability solutions that you could implement on your environment.
Depending on your needs and of what type of hardware and software you dispose you can choose the best solution of high availability for your applications and productions.
With SQL Server you have the possibility even to combine different types of high availability solutions that you could implement.
The following article described what high availability solutions can coexist High Availability: Interoperability and Coexistence
Database mirroring can be used with the following features or components of SQL Server:
- Change data capture (and change tracking)
- Failover clustering
- Log shipping
- Full-text catalogs
- Database snapshots
- Replication
Log shipping can be used with the following features or components of SQL Server:
- Database mirroring
- Replication
As we can see not all the combinations of high availability solutions can coexist.
As mentioned at the beginning of this blog the most popular combination is clustering and another solution and the first thing we think is mirroring or log shipping.
We saw in the article mentioned above that clustering can coexist only with mirroring . The clustering cannot coexist with log shipping. For a general comparison of the SQL Server high-availability solutions, see High Availability Solutions Overview.
In the same article we have the following information about different high availability solutions that could coexist:
- Change Data Capture and Other SQL Server Features: Contains information about using change data capture (CDC) with database mirroring.
- Database Mirroring and Failover Clustering: Describes database mirroring as an easy-to-manage alternative to failover clustering. Database mirroring can be used between two different failover clusters, but not within a single cluster.
- Database Mirroring and Log Shipping: Describes how a single database can be configured as the principal database in a mirroring session and also as the primary database in a log shipping configuration.
- Database Mirroring and Cross-Database Transactions : Describes why database mirroring is not supported with either cross-database transactions or distributed transactions.
- Database Mirroring and Full-Text Catalogs: Describes how to mirror a database that has a full-text catalog.
- Database Mirroring and Database Snapshots: Describes how to create one or more database snapshots on a mirror database to permit clients to use the mirror database for limited report writing.
- Replication and Database Mirroring: Describes the effects of log shipping on replication behavior and the requirements and procedures for replicating from the secondary if the primary is lost.
- Replication and Log Shipping: Describes the effects of log shipping on replication behavior and the requirements and procedures for replicating from the secondary if the primary is lost.
Concerning one of the most used combination of high availability solutions: the Failover clustering and Database mirroring, we have the following articles and information that could help us better understand how they coexist and how can be configured:
1. Failover Clusters in Windows Server 2008
A failover cluster is a group of independent computers that work together to increase the availability of applications and services. The clustered servers (called nodes) are connected by physical cables and by software. If one of the cluster nodes fails, another node begins to provide service (a process known as failover). Users experience a minimum of disruptions in service.
Database mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. The simple and bulk-logged recovery models do not support database mirroring. Database mirroring is supported in SQL Server Standard and Enterprise.
Database mirroring offers substantial availability and provides an easy-to-manage alternative or supplement to failover clustering or log shipping. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover with no loss of data from committed transactions. During a typical mirroring session, after a production server fails, client applications can recover quickly by reconnecting to the standby server.
3.Database Mirroring and Failover Clustering
Typically, when mirroring is used with clustering, the principal server and mirror server both reside on clusters, with the principal server running on the failover clustered instance of one cluster and the mirror server running on the failover clustered instance of a different cluster. You can establish a mirroring session in which one partner resides on the failover clustered instance of a cluster and the other partner resides on a separate, unclustered computer, however.
If a cluster failover makes a principal server temporarily unavailable, client connections are disconnected from the database. After the cluster failover completes, clients can reconnect to the principal server on the same cluster, or on a different cluster or an unclustered computer, depending on the operating mode.
When deciding how to configure database mirroring in a clustered environment, the operating mode you use for mirroring is significant.
Comments
- Anonymous
January 01, 2003
Good source of information...Thanks for sharing - Anonymous
January 01, 2003
Hi you said Clustering and logshipping cannot co exists this is incorrect IMO. This scenario is supported by Microsoft and more reliable than mirroring. I have TB of database which logshipped and clustered - Anonymous
October 21, 2015
The comment has been removed