SQL Server High Availability and Disaster Recovery

Here is the shortened version of the HA/DR module. There are several high availability and disaster recovery options in SQL server depending on the version in addition. I need you to understand what each one of them are, at least high level. I hope I will be able to explain to a non-technical person also.

You need to be ready for any disaster. If you’re a DBA, it’s your job to protect the data. Secure it and make it available.

As we expect the exact same thing when you go to Facebook or Twitter or you go to your online banking, your expectation is that your data is right there and fairly available. We also expect that in case these application goes down for a while there should be no/minimal data loss.

Business continuity is of utmost importance in today’s world. An application downtime that’s even as low as a few minutes may result in potential revenue loss for companies such as Amazon and Flipkart. Downtime not only results in direct revenue loss as transactions are dropped, it also contributes to a bad user experience. Often, application downtime because of programming or functional issues which doesn’t affect the entire application and can be fixed by the developers quickly.

However, downtime caused by infrastructure or system failure affects the entire application and can’t be controlled functionally (by the developers). Restoration of system operation after a disaster is called Disaster Recovery.

Whereas Availability refers to the percentage of time that a system is available to a user.  When increasing the level of availability, your goal is to attain the level of “high availability”.

The type of HA and DR solution implemented by a business depends majorly on the service level agreement. The SLA defines the recovery point objective and recovery time objective.

Recovery Time Objective: – Recovery time objective, or RTO, is essentially the downtime a business can tolerate without any substantial loss. The choice of HA and DR solution depends on the RTO

Recovery Point Objective: – Recovery point objective, or RPO, defines how much data loss a business can tolerate during an outage. Essentially, this is the time difference between the last transaction committed before downtime and the first transaction committed after recovery.

---

High Availability and Disaster Recovery Options.

Backup and restore we all know about this one, even non-DBAs understand.

Log Shipping is one of the oldest SQL Server solutions, and is mostly used for DR and SQL Server migration. It takes transaction log backups from the primary server and restores them on one or more secondary servers. It is implemented using SQL Agent jobs.

---

Windows Server Failover Cluster Installation Commonly known as FCI, this requires SQL Server to be installed as a cluster service on top of the Windows failover cluster. The SQL Server service is managed by a Windows cluster resource.

---

Database mirroring is deprecated in SQL Server 2012. There are servers principle, mirroring, and witness. The database will be in the principle server and the application will be connect to the principle server then you will take backup (Full + log) from the database in the principle server the make restore to this backup with no recovery option in the mirroring server. You can setup the principle server in domain and the mirroring server in another domain     

---

Always-on is not a thing but is one of the newest and most impressive HA and DR features in SQL Server. It’s mainly a marketing term. DBAs always say Always-on availability groups or say Always-on failover cluster instances because both of those things are under the always-on marketing term.

---

Replication is a SQL server feature, it’s tremendously valuable in some situations but it’s copying code and tables from one place to another. Has to be set up manually, if you add a new table you have to go to secondary DB to add that manually, this is not a good HA option nor a DR option. it’s great for offloading some reporting. Replication replicates data from one database (commonly known as a publisher) to one or more databases (known as subscribers) in the same or different SQL Server instances.

---

Always on High availability group can be DR or HA or it can be both. It’s a combination of clustering and mirroring in theory, so it sounds like a fantastic solution. Other than being an HA and a DR solution, it can also be used to load-balance read workloads, for maintenance jobs such as backups, and for consistency checks on the secondary databases. With AlwaysOn Availability Groups, things are more complicated, perhaps I would like to make a separate article on this topic.

Thanks for reading

Keep reading, share your thoughts, experiences. Feel free to contact us to discuss more. If you have any suggestion / feedback / doubt, you are most welcome.

Stay tuned on Knowledge-Junction, will come up with more such articles