[Notes from Pinal]: When it is about High Availability or Disaster Recovery, I often see people getting confused. There are so many options available that when the user has to select what is the most optimal solution for their organization they are often confused. Most of the people even know the salient features of various options, but when they have to figure out one single option to use they are often not sure which option to use. I like to give ask my dear friend time all these kinds of complicated questions. He has a skill to make a complex subject very simple and easy to understand.
Linchpin People are database coaches and wellness experts for a data driven world. In this 26th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains in a very simple words the best High Availability Option for your SQL Server.
Working with SQL Server a common challenge we are faced with is providing the maximum uptime possible. To meet these demands we have to design a solution to provide High Availability (HA). Microsoft SQL Server depending on your edition provides you with several options. This could be database mirroring, log shipping, failover clusters, availability groups or replication.
Each possible solution comes with pro’s and con’s. Not anyone one solution fits all scenarios so understanding which solution meets which need is important. As with anything IT related, you need to fully understand your requirements before trying to solution the problem. When it comes to building an HA solution, you need to understand the risk your organization needs to mitigate the most.
I have found that most are concerned about hardware failure and OS failures. Other common concerns are data corruption or storage issues. For data corruption or storage issues you can mitigate those concerns by having a second copy of the databases. That can be accomplished with database mirroring, log shipping, replication or availability groups with a secondary replica. Failover clustering and virtualization with shared storage do not provide redundancy of the data.
I recently created a chart outlining some pros and cons of each of the technologies that I posted on my blog.
I like to use this chart to help illustrate how each technology provides a certain number of benefits. Each of these solutions carries with it some level of cost and complexity. As a database professional we should all be familiar with these technologies so we can make the best possible choice for our organization.
If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.
Note: Tim has also written an excellent book on SQL Backup and Recovery, a must have for everyone.
Reference: Pinal Dave (https://blog.sqlauthority.com)
I experienced data corruption once with MSMQ (and it was a disaster and I was not able to recover the information), but haven’t ever seen or even heard of it with SQL Server. Is that something anyone has ever encountered in real life?