Different High Availability Solutions in SQL Server.

While working on finding different High Availability solutions, I did quite a bit of research on various methods of DR solutions and I wanted to share following technologies are available in SQL Server for us to implement DR solution. In this cutting edge technology and as per the need of the business, applications need to be online 24×7, 365 days providing services to the clients and users. In order to achieve this requirement, high availability solution needs to be implemented. From the SQL Server database perspective, following are the high availability solutions being used.

•   SQL Server Clustering (Active/Active, Active/Passive)

•   Database Mirroring

•   Log Shipping

•   Replication

Each of these has their own advantages, disadvantages and based on their features, IT team of the corporate or department will select one that suits their requirement.

SQL Clustering:

SQL Clustering is a technology if implemented and set up properly takes all the responsibility of resources of the server that failed during the process and make the system up and running. Clustering in very generic term can be defined as a set up servers or group of servers that work together and represent themselves as a single virtual server in the network and to the clients. Very detail and good explanation of setting up and administering SQL Server 2008 is given in this link.  download.microsoft.com/…/SQLServer2008FailoverCluster.docx

Database Mirroring:

Database mirroring is a feature in SQL Server (2005, 2008) that provides high availability solution to databases that have mission critical applications. Basics of database mirroring:

Principal Server: Is a server that holds the principal or main database to mirror.

Mirror Server: Is a server that holds mirror database that is used to capture the changes made in the principal database.

Witness server: This is a server that is used as a witness and to make sure that two servers are communicating every now and then.

In database mirroring, principal database and mirror database should be identical. Editions of SQL Server Instance should be the same on principal and mirror. Witness server can have any editions of SQL Server. ‘

Database should be in full recovery mode. Full backup and transaction log backup should be taken on the principal server and then applied to the mirror server to initiate database mirroring. Database mirroring can be automatic or manual. Refer following link for database mirroring: http://databaseexpertise.com/2010/04/sql-server-2008-database-mirroring-abi-chapagai/

Log Shipping:

Log shipping is another DR solution. In log shipping, all the changes made in main or primary database will be shipped to another server database which is in standby. In log shipping following are the basic terminology:

Primary Server: This server holds the primary database and ships the transaction log record to another standby server.

Secondary Server: This server holds secondary database, and this database receives all the changes being transferred from primary and keep these two databases in synch. Full database backup and transaction log backup should be done on the primary server and then copied over to the secondary server. These backups should be restored on the secondary server database with NO RECOVERY option to initialize the log shipping process. Refer following link for the details http://databaseexpertise.com/2010/07/log-shipping-by-abi-chapagai-revised/

Replication:

Replication is the process of copying data between two databases on the same server or different servers on the same database. This is one of the methods to maintain the redundant database site for disaster recovery purpose. Refer following article for the Replication Introduction and basics of replication.  http://databaseexpertise.com/2010/07/sql-server-2008-replication-high-availability-solution-part-one-by-abi-chapagai/