Disaster recovery techniques in SQL Server

4 September 2014

SQL Server 2012 training has proven a boon to employees working for enterprises with intense disaster recover needs.

Preparing for the worse and knowing how to get databases back online in the event of an outage is a crucial part of running an organization effectively. The Business Journals contributor Heinan Landa noted planning populating, logging and reviewing is a simple DR test every business should perform

Why SQL Server? SQL Server Pro contributor and DH2i Chief Technology Officer Thanh Ngo outlined several features that enhance database availability. When appropriately executed, the software can be an integral part of an enterprise's business continuity strategy. Ngo named two applications that are particularly useful in this regard.

AlwaysOn Availability Groups 
Server mirroring is a powerful capability featured in SQL Server's AlwaysOn Availability Groups. This particular tool allows a set of user databases to failover in a synchronized manner. This function requires a Windows Server Failover Clustering cluster to operate. Each set is comprised of two types of databases:

  • Primary replicas, which are capable of performing read-write tasks
  • Secondary clones, which can be configured to conduct reads

Availability Groups directly relates to database mirroring, an action that provides security and availability for user databases. It involves a mirror server carrying out the exact same actions as its counterpart, a principal server. It can operate in either one of the following modes:

  • High safety mode with automatic failover: Transaction is enacted on both servers during a synchronized operation, including a witness partner that coordinates automatic failover. 
  • High safety mode without automatic failover: The same function as above occurs, only without the presence of a witness partner. 
  • High Performance mode: Operations are asynchronous, and transactions are committed without having to wait for the mirror partner to write a log to a disk. 

This feature follows a publish-subscribe protocol. This transaction consists of a primary server (publisher) distributing information to one or more secondary databases (subscribers). Replication can be carried out in either one of three ways: 

  • Snapshot: Duplicates are generated and applied immediately following the creation of a subscription, or in concordance with a preset schedule after a publication is developed.
  • Transactional: Supports real-time availability – interactions can be allocated and implemented instantly at the subscriber level. 
  • Merge: Incremental updates that are synchronized between subscribers and publishers – replication is bi-directional. 

Replication and AvailabilityGroups provide a solid foundation for any DR initiative. With properly trained personnel, SQL Server can be a strong asset to a company's business continuity plan. 

Leave a Reply

Your email address will not be published. Required fields are marked *