High Availability and Disaster recovery Techniques in sql server

This blog is the introduction and brief overview for high availability and Disaster recovery techniques available in sql server, and its my understanding on it. I will write detail blog on each technique will later

There are different ways of achieving this is sql server each has its own advantage and disadvantages. following methods are used for HA and DR :

1. Clustering
2. Database Mirroring.
3. Log Shipping
4. Replication
5. Backup and restore
6. Detach and Attach

1. Clustering:-
   1. Cluster is a very high level technique to achieve HA.
   2. Cluster may not be good for DR plan as here both host should be close to each other
   3. Clustering require few seconds to move to other node if one node failed for high availability.
   4. Earlier to SS2005, this can only be possible at “Enterprise Edition” which itself cost expensive.
   5. At SS2005 2 node clustering is possible at standard edition as well.
   6. Different types of cluster are available as one node cluster (active/passive), two node cluster(active/active). Multi node clustering.
   7. Can be configured very easily below the steps to setup clustering.
   8. Clustering requires both nodes to be of same configuration.
   9. Active/active clustering can also be used for load balancing.

Reference:

http://msdn.microsoft.com/en-us/library/ms179530.aspx

http://www.microsoft.com/downloads/details.aspx?FamilyID=818234dc-a17b-4f09-b282-c6830fead499&displaylang=en

http://support.microsoft.com/kb/254321

http://www.mssqltips.com/tip.asp?tip=1541

http://technet.microsoft.com/en-us/library/cc917693.aspx

http://technet.microsoft.com/en-us/magazine/2007.03.sqlclusters.aspx

1. 2. Database Mirroring:-
   1. Database mirroring is introduced in SS2005 sp1.
   2. This is much cost efficient technique unlike clustering this can be as HA. As here mirror server does not required to be of same configuration.
   3. Here Principle / Witness/Mirror servers, Principle and witness could be on same server.
   4. The setup for Mirroring is here
   5. Mirroring is configured with three ways:

Reference:

http://blogs.msdn.com/suhde/archive/2009/07/13/step-by-step-guide-to-configure-database-mirroring-between-sql-server-instances-in-a-workgroup.aspx

http://www.sqlskills.com/BLOGS/PAUL/post/SQL-Server-2008-Performance-boost-for-Database-Mirroring.aspx

http://weblogs.sqlteam.com/tarad/archive/2007/02/13/60091.aspx

http://community.winsupersite.com/blogs/itprotips/archive/2008/02/28/sql-server-2008-database-mirroring.aspx

http://msdn.microsoft.com/en-us/library/ms188712.aspx

http://www.mssqltips.com/tip.asp?tip=1280

http://msdn.microsoft.com/en-us/library/ms177412.aspx

http://technet.microsoft.com/en-us/library/cc917681.aspx

http://technet.microsoft.com/en-us/library/cc917680.aspx

3. Log Shipping:-

1. Log shipping is well known term for HA and DR plan and is available from SS2000 onwards. In fact we can setup the custom log shipping using backup and restore technique.
2. Log shipping is available at enterprise edition on Sql server 2000 and is available at Standard edition for SS2005 onwards.
3. Log shipping is very efficient and easy technique the setup.
4. To use this for HA may require little downtime… manual intervention to make secondary server as primary, move logins and other activity. And same is applied for DR plan.

Reference:

http://www.sql-server-performance.com/articles/clustering/log_shipping_70_p1.aspx 

http://support.microsoft.com/kb/314515 

http://www.mssqltips.com/tip.asp?tip=1158 

http://www.youtube.com/watch?v=2k003Dj7fHo 

http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1309728,00.html

http://www.informit.com/articles/article.aspx?p=331402

http://technet.microsoft.com/en-us/library/cc917705.aspx

http://www.sql-articles.com/articles/high-availability/ten-steps-to-configure-log-shipping

4. Replication:-

1. a. Replication is generally used as a technique to load balance the production server and distinguish the high activity production with reporting server.
2. b. Replication can also be used as HA or DR technique but not especially for it. Can be achievable but like logshipping it also require manual intervention.

Reference:

http://vinay-thakur.spaces.live.com/?_c11_BlogPart_BlogPart=summary&_c=BlogPart&partqs=cat%3dReplication

http://blogs.msdn.com/repltalk/default.aspx

http://www.replicationanswers.com/

1. 5. Backup and restore:-
   1. a. This is a standard way for HA and DR.
   2. b. This requires DBA skill.
   3. c. Time consuming process.

Reference:

http://msdn.microsoft.com/en-us/library/ms177429.aspx

http://www.sqlteam.com/article/backup-and-restore-in-sql-server-full-backups

http://articles.techrepublic.com.com/5100-10878_11-1041267.html

1. 6. Detach and attach database:-
   1. a. This is not a technique for HA or DR but can be possible as now a days things are on SAN and high end storage devices which are redundant and can recovery the data. in such cases if we could recovery sql server data and log file(optional) we can attach the file can be used.
   2. b. Steps are here

Reference:

http://www.mssqlcity.com/Articles/Adm/attach_database.htm

http://support.microsoft.com/kb/224071

http://www.sqlservercentral.com/articles/Administration/spattachdb/169/