Graphic explanation of new functions of SQL Server 2016 Alwayson, 2016 alwayson

Graphic explanation of new functions of SQL Server 2016 Alwayson, 2016 alwayson

Overview

It has been more than a year since SQLServer2016 was released. The latest stable version is SP1. Next, let's take a look at the improvements made in Alwyson in 2016. I remember that I mentioned several issues that need improvement when I wrote alwayson in 2016.

I. Number of automatic failover copies

In versions earlier than 2016, automatic failover replicas can be configured with up to two replicas, and changed to three on 2016.

Note: It is not very important to add three copies for automatic failover. Adding one copy for failover also means that you need to maintain one more copy for your job. Importance (average ).

Ii. Data Synchronization preferences

Before 2016, when adding secondary replica databases, there are only three synchronization preferences available: 1. full database and log backup, 2 only connections, 3 skip initial data synchronization; 2016 added "automatic Seed Setting", and found that automatic seed setting is also synchronized in a way similar to Backup Recovery

Note: If you select automatic seed setting, you do not need to specify a shared folder path to store backup and restore data. Importance (average ).

Iii. Alwayson operation status monitoring

Before 2016, status monitoring can only be performed on the entire replica. If there are multiple databases synchronizing one of the databases, access failure will not trigger replica failover. 2016 added database-level operation status monitoring. If the database has problems and cannot be accessed, it will automatically fail over.

Note: database-level check is very necessary. Importance (very important)

4. Multiple read-only copies are supported at the same time to provide read Operations

Before 2016, only one read-only copy of the listener can be used to provide read-only operations. Only when the preferred read-only copy fails will it be routed to the next read-only copy. In 2016, multiple read-only replicas can be configured at the same time to share read-only operations, achieving real read/write splitting and load balancing. The read-only route configuration of 2016 is as follows:

ALTER AVAILABILITY GROUP [Alwayson21]MODIFY REPLICA ONN'HD21DB01' WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB02','HD21DB03','HD21DB04'),'HD21DB01')));ALTER AVAILABILITY GROUP [Alwayson21]MODIFY REPLICA ONN'HD21DB02' WITH(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=(('HD21DB01','HD21DB03','HD21DB04'),'HD21DB02')));

Note: Read-Only copies are evenly distributed. Importance (very important)

Specific configuration can refer to I have previously written read/write splitting configuration article: http://www.bkjia.com/article/124827.htm

The following two new functions are very practical. These are also two important functions that alwayson of earlier versions urgently needs to improve. Therefore, we recommend that you upgrade alwayson to 2016 if necessary.

Summary

The above is a graphic explanation of the new features of SQL Server 2016 Alwayson introduced by the editor. I hope it will help you. If you have any questions, please leave a message and I will reply to you in a timely manner. Thank you very much for your support for the help House website!