There are 4 High Availability options in SQL Server 2008 that we will cover; Failover Clustering, Database Mirroring, Log Shipping and Replication. Each one has its own features and benefits.
The Failover Clustering supports server level high availability whereas Database Mirroring and Log Shipping are per database and Replication is object level.
Failover Clustering
This provides server-level redundancy on a certified Microsoft Windows Cluster Services Configuration. A failover cluster is setup with a minimum of two servers. In the cluster setup, each server is called a node. All the nodes are connected to shared disk resources. Only one node is active at a time for a SQL Server instance and serves all the requests for that instance of SQL Server. When the active node fails, failover takes place and one of the other available nodes will become the active node. More information can be read from this MSDN library article.
Use:
- This is an appropriate option for mission critical applications where automatic failover is needed for the entire instance of SQL Server.
Database Mirroring
This provides database redundancy by transferring data from the transaction log to another instance of SQL Server. There are several operating modes that can be used such as: high performance, high safety with automatic failover and high safety without automatic failover. Based on the operating mode, we can have complete or almost complete database redundancy. More information can be read from this MSDN library article.
Use:
- This is a good option when there is the need to have automatic failover for a database. It can be near real time failover of a database depending on the options used. Also a good choice if there is a need to provide database connectivity with minimal downtime.
Log Shipping
It provides database redundancy by sending transactional log backups periodically to a standby server or servers for the entire database. Transaction logs are automatically backed up, copied, and restored on the standby server(s). If the active server goes down, the standby server can be brought up by restoring any remaining shipped logs and then the database is recovered for use. More info on Log Shipping can be read from this MSDN library article.
Use:
It is good option when we want to have multiple secondary failover servers for a database.
Replication
Replication is a set of technologies for copying and distributing data and database objects from one database to another. Replication is a desired option when we want to have the same data on multiple database servers. There are different types of replication and more info on Replication can be read from this MSDN library article.
Use:
- It is good option when we want to have multiple server updates with disconnected options. It can be near real time. It can also be used to implement load balancing for a database.
Sources:
https://www.mssqltips.com/sqlservertip/2337/sql-server-2008-high-availability-options/
Comments