Customers today need their SQL data to be highly available. With technologies built into the current versions of SQL Server, highly available databases are easier than ever to achieve.
Making data highly available has progressed through a series of technologies in SQL Server. So, let’s rewind for a minute while I explain how. Prior to SQL 7.0, Microsoft and Sybase worked together in a joint effort to produce a relational database which allowed database mirroring. This proved to be extremely cost-consuming on resources, and therefore when Microsoft broke away with their own version of SQL Server in 1998 they removed the ability to mirror databases. While industries still needed to have data highly available, Microsoft released several technologies to make data redundant.
Today, SQL Server 2017 runs with Always On Availability Groups. Always On Availability Groups combines the functionality of Failover Clustering with Database Mirroring and relies on the underlying features of Windows Clustering. It’s a high-availability and disaster recovery solution that provides enterprise-level functionality, redundancy, and improved resource use.
So how does this work?
The illustration below shows an availability group that contains one primary replica and four secondary replicas. Up to eight secondary replicas are supported, including one primary replica and two synchronous-commit secondary replicas.
The clustering function allows for the cluster node to always be the Primary node. When the Primary node fails, the Secondary node can be setup to automatically failover and become the Primary node. Failover can be setup as Synchronous or Asynchronous. Synchronous data is written to each node together. This, of course, means that there will be some performance loss with the writing to both nodes. Asynchronous can have some data loss and, therefore, the ability to dynamically failover to the secondary node is not an option. The AlwaysOn feature also provides the ability to create a disaster recovery (DR) copy of the data. While making the data highly available, customers can choose to store the secondary node in another geographically disparate data center. Microsoft Azure creates an ideal solution for High Availability and getting a DR site setup in a single step.
When setting up the secondary node, we can mark that as a non-readable copy. By marking the secondary node non-readable and because only one node can be read at a time, we are reducing the need to purchase another SQL Server license. Software Assurance is required to take advantage of not being required to purchase additional licensing for the secondary copy of data. As long as the Primary server and the secondary server are both On-Prem or both in the cloud, the requirement of having only one server licensed applies.
Microsoft has made a lot of strides in making data highly available.
Interlink’s SQL certified engineers at have been working with SQL AlwaysOn since it was introduced in SQL 2012. By leveraging our expertise, we can help to determine and implement the best methods when building redundancy into your database environment. Contact us today.