In SQL Server 2016, a lot of high availability enhancements were introduced. Here is a brief overview of the major enhancements.
- AlwaysOn Basic Availability Groups supported on a primary and secondary replica in SQL Server 2016 Standard Edition.
- Read-intent connection request’s load-balancing supported in between a collection of read-only replicas.
- AlwaysOn is now supported for encrypted databases.
- Windows Server Failover Clusters in different availability groups can be combined into Distributed Availability Group.
- Rather than manual seeding in secondary replica, direct seeding can be used for a secondary replica for automatic seeding in the network.
Limitations and more information
AlwaysOn Availability Groups
AlwaysOn Availability Groups offer Database Mirroring features.
In this feature, the primary database is enabled to keep one replica.
This replica can be used in Synchronous-commit or asynchronous-commit mode.
When failover occurs, the secondary replica will be enabled until it will be inactive.
These availability groups can use in a hybrid environment which spans on-premises and Microsoft Azure.
Only two replicas.
The secondary replica does not contain read access.
Secondary replica backups can’t be taken.
Replicas can’t be supported for the servers running a version earlier to SQL Server 2016 CTP3.
Supports only one availability database.
Upgrade from basic availability groups to advanced availability groups is not possible.
This feature is only supported in the Standard Edition.
Setting load-balancing in read-only replicas
In previous versions, the first available replicas were only pointed by read-only routing when traffic occurs.
Now, configuring/setting load-balancing in the collection of read-only replicas helps to overcome the above situation.
The set of servers are identified by nested parentheses.
If the first one in the set is not available, the next server will be used for the process and so on.
It supports only the first level of nested parentheses.
AlwayOn Failover Clusters
This is supported by Group Managed Service Accounts
Distributed Transactions are supported by Alwayson Availability Groups.
For Distributed Transactions:
2. Availability groups should be created using WITH DTC_SUPPORT=PER_DB clause and we cannot alter an existing Availability Group.
Encrypted Databases also have the Alwayson feature.
The wizard for Availability groups ask you for a password for the database contain key, at the time of creating, altering and creating replicas for availability groups.
Distributed Availability Groups
We can combine two window server failover clusters in two different availability to groups as one distributed availability group.
The main advantage of this feature is disaster recovery when the main site is collapsed geographically from the DR site.
If the two availability groups are combined into a distributed availability group, the secondary availability group automatically changed as read-only and the primary availability group only accepts inserts and updates.
The same configuration of databases should be maintained in the primary and secondary availability groups.
Secondary availability group’s failover can’t be considered automatically.
Use SEEDING_MODE as AUTOMATIC for all replicas involved in the distributed availability group creation.
Direct Seeding for Secondary replicas
Use of this feature automatic seeding in secondary replica can be allowed over the network.
This direct seeding can be added in availability code by adding SEEDING_MODE=AUTOMATIC with the code used to create and alter availability groups.
Use GRANT CREATE ANY DATABASE in the alter availability group statement of each secondary replica which is used for direct seeding.