HA and DR Techniques in sql server
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.
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:
Operating Mode Transaction safety Transfer mechanism Quorum required Witness server Failover Type
High Availability FULL Synchronous Y Y Automatic or Manual
High Protection FULL Synchronous Y N Manual only
High Performance OFF Asynchronous N N/A Forced only
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.
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.
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.
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
No comments:
Post a Comment