SQL Server AlwaysOn New features

Tags: SQL server/mssql server/Database/dba/

Overview

SQLSERVER2016 release version to now more than a year, the current stable version is the SP1 version. Let's take a look at what the 2016 has done on Alwyson, and remember that I wrote about a few improvements in the 2014Alwayson before I mentioned several issues that need to be improved in 2016.

I. Number of auto-failover replicas

Versions of automatic failover replicas up to 2016 can have up to 2 replicas configured and 3 on 2016.

Description: Automatic failover increased to three copies the impact is not very significant, it is not very important to increase the number of a failover replica also means that your job needs to maintain a copy more. Degree of importance (general).

Second, the data synchronization preferences

Prior to 2016, only three synchronization preferences were available when adding a secondary replica database: 1. Full database and log backup, 2 connection only, 3 skip initial data synchronization, 2016 added "automatic Seeding", and found that automatic seeding is synchronized like backup restore

Description: Selecting automatic seeding does not require that you specify a shared folder path for storing backup restore data. Degree of importance (general).

Third, AlwaysOn operation status Monitoring

Status monitoring can only be performed on the entire replica until 2016, and if there are multiple databases to synchronize one of the databases has a problem inaccessible does not trigger a replica failover. 2016 increased health monitoring at the database level and automatic failover if there is a problem with the database that cannot be accessed.

Description: A database-level check is necessary. Degree of importance (very important)

Iv. support for read operations on multiple read replicas at the same time

Only one read replica can exist at a time before 2016 to provide read-only operations, and only the preferred read replica fails to be routed to the next read replica. And 2016 can configure multiple preferred read-only copies to share the read-only operation, so that the real read-write separation and load balancing. The read-only routing configuration for 2016 is as follows:

ALTERAvailabilityGROUP [Alwayson21]MODIFY REPLICA onN'HD21DB01'  with(Primary_role (read_only_routing_list=(('HD21DB02','hd21db03','hd21db04'),'HD21DB01')));ALTERAvailabilityGROUP [Alwayson21]MODIFY REPLICA onN'HD21DB02'  with(Primary_role (read_only_routing_list=(('HD21DB01','hd21db03','hd21db04'),'HD21DB02')));

Note: You can find that read-only copies are evenly distributed over the first read-only pressure. Degree of importance (very important)

the specific configuration can refer to the read and write separation configuration article I wrote earlier : http://www.cnblogs.com/chenmh/p/7000236.html

Summary

The next two new features are very practical, which is also the two very important features of the 2016 version of AlwaysOn that need to be improved, so it is recommended that you upgrade to 2016 with the recommended conditions to upgrade.

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server AlwaysOn New features