In this article,we discuss the improvements introduced in SQL Server 2016 edition with respect to AlwaysOn Availability groups.
The AlwaysOn Availability Groups or the Availability Groups feature was launched first in the 2012 edition of SQL Server. This feature is mainly used to increase the availability of a database and efficiently work towards disaster recovery. Since the launch of this feature it has continued to receive constant updates every year. This feature was launched to tackle several pain points regarding availability of data and disaster recovery. Microsoft opted to work on these availability groups rather than introducing a whole new functionality to tackle the problem. Finally in the 2016 edition of SQL Server, all the pain points of the customers have been taken away.
The following advancements have been introduced in AlwaysOn Availability Groups in the 2016 edition of SQL Server.
- Database Health causing failovers: In SQL Server’s versions between 2012 and 2014, there used to be no failovers due to the health of the database, this has changed with the latest version of the software. In the 2016 edition, if there is a database that is a part of the availability group but is still offline due to some reason, the whole of the availability group will failover. This will happen even if other database are available online and operational.
- Support for Distributed Transaction Coordinator: This has been one of the most in-demand enhancements in AlwaysOn Availability Groups, till the last iteration there was no support for DTC in SQL Server. In the 2016 edition, this has changed and it now supports DTC, which helps in managing transactions across multiple databases. DTC is a Microsoft product, so in order to have support for DTC in SQL Server, it is important to have it on Windows Server 2016 operating system.
- Synchronous replicas: Availability Groups consist of two different types of secondary replicas, Synchronous and Asynchronous. In case of synchronous replication, the transaction has to be recorded in replica’s log first, and then an acknowledgment will be sent to primary replica. To make a secondary replica a target for automatic failover, it will have to be synchronous replication. SQL Server 2013 allows for turning all three synchronous replicas as failover targets.
- Optimized log transport: At times Availability Groups are deployed in large numbers in highly transactional environment, which then becomes challenging. In the current edition, the Redmond based software giant has worked to achieve better log-data throughput while utilizing SQL Server AlwaysOn Availability Groups.
- Load balancing: Out of all the secondary replicas, the first one often becomes the one that is read the most. This happens because the first one is the one which is tried the most by the Availability Group listener mechanism. In the latest edition of SQL Server, there is a dedicated read only routing list for each replica.
AlwaysOn Availability Groups are Susceptible to Several Hard and Soft Errors
While AlwaysOn Availability groups go a long way in keeping your data accessible at nearly all times; they are by no means completely full proof. Both hard and soft errors can occur in an Availability Group Implementation and possible incidents of data loss can occur. In case you suspect some records to be missing, you can use a mdf repair tool like DataNumen SQL Recovery to extract the records from the primary replica. In addition the tool can effectively recover sparse columns and indexes with aplomb.
Alan Chen is President & Chairman of DataNumen, Inc., which is the world leader in data recovery technologies, including access recovery and sql recovery software products. For more information visit www.datanumen.com