Shulou(Shulou.com)06/01 Report--

New features and enhancements in AlwaysOn 2016

AlwaysOn availability groups were introduced as a new feature of SQL Server 2012, which enhances database mirroring and failover clustering technology, providing high availability and disaster recovery.

Some enhancements to AlwaysOn features in SQL Server 2016 are required to run on Windows Server 2016. However, if you are still running on Windows Server 2012 R2, there are still many enhancements available.

Some new features and enhancements to AlwaysOn 2016:

L support more failover targets

Better log transfer performance

L load balancing of readable copies

L support DTC

L database-level health monitoring

L support the service account managed by the group

L basic availability group

L no domain availability group

L distributed availability group

L support encrypting database

L supports SSIS directory

L BI enhancement

Support for more failover target

On AlwaysOn 2012 and 2014, only 2 replicas are allowed to be configured as automatic failover replicas (including the current master replica), and 2016 allows 3 replicas.

Automatic failover is usually used to support high availability, where synchronous data streams are close to zero data loss during failover.

Better log transfer performance

With the widespread use of high-speed hardware SSD, it provides greater throughput, which is important for writing transactions to secondary copies. As a result, Microsoft updated AlwaysOn's data synchronization process, simplifying the pipeline to have better throughput and less pressure on CPU. Most performance bottlenecks can occur in log capture (Log Capture) and redo (Redo) steps. Previously, log capture and redo steps used a single thread to process logs, but now these steps run in parallel with multiple threads, greatly improving performance.

Data synchronization is described as follows:

Transaction Occurs-> Log Flush-> Log Capture-> Send-> Log Received-> Log Cached-> Log Hardened-> Acknowledgement Sent-> Redo

Load balancing of readable copies

A great feature of AlwaysOn is the ability to use secondary copies for read-only operations. In versions prior to AlwaysOn 2016, listeners directed read-only requests to the first available copy, even though you might have multiple secondary copies, and you might set the routing table to first direct degree requests to replica 3 or 4 instead of replica 2. Now a readable copy of AlwaysOn 2016 is exposed to the listener in the form of polling.

1. Configure read-only access to the secondary copy

ALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ONN 'SQL16N2' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)) GOALTER AVAILABILITY GROUP [ag] MODIFY REPLICA ONN' SQL16N3' WITH (SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY)) GO

two。 Configure read-only routing URL

ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON NumberSQL16N2 'WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL = Naughty TCPVG GOALTER AVAILABILITY GROUP ag MODIFY REPLICA ON 1433'); GOALTER AVAILABILITY GROUP ag MODIFY REPLICA ON NumberSQL16N3' WITH (SQL16N3'); GO)

3.SQL Server 2016 introduces load balancing lists in read-only route lists

When SQL16N1 is the primary replica role, create a read-only route list:

ALTER AVAILABILITY GROUP ag MODIFY REPLICA ON SQL16N3', SQL16N1' WITH (PRIMARY_ROLE ('SQL16N3',' SQL16N2'), 'SQL16N1')

The above route list indicates a load-balanced read-only connection between SQL16N3 and SQL16N2. We have two embedded lists:

List 1: 'SQL16N3',' SQL16N2'

List 2: 'SQL16N1'

Work as follows:

1. Route to the copy in the first list

SQL16N3 and SQL16N2 are accessible to read-only connections. The first read-only connection is routed to SQL16N3, the second read-only connection is routed to SQL16N2, the third read-only connection is routed to SQL16N3, the fourth read-only connection is routed to SQL16N2, and so on, polled distribution using a read-only connection between two copies of the first list.

two。 If either copy is not available, the route will continue in the copy of the first list

If SQL16N3 or SQL16N2 becomes inaccessible for a read-only connection, the read-only connection will only be routed to an accessible read-only copy of the first list. For example, if SQL16N3 is not in synchronized state, or if ALLOW_CONNECTIONS is set to NO, then all read-only connections will be routed to SQL16N2. As long as one of the servers of the read-only connection is available, the read-only connection will not be routed to SQL16N1.

3. If all copies in the first list are inaccessible, they will be routed to the next list

For read-only connections, if SQL16N3 and SQL16N2 become inaccessible, then all read-only connections will only be routed to a copy of the next list, which is SQL16N1.

4. If any copy of the first list is available, the route to the first list will be restored

Because for read-only connections, the secondary copies in the first accessible list have higher priority, subsequent read-only connections will connect to them appropriately.

In addition to configuring the availability group route list, you must also make sure that the client's application connection string adds the ApplicationIntent parameter to ReadOnly when connecting to the AG listener. If it is not set in the client application connection string, the connection will be automatically directed to the master copy. The following is an example of a read-only connection string:

Server=tcp:AGListener,1433; Database=AdventureWorks;IntegratedSecurity=SSPI; ApplicationIntent=ReadOnly

It is also best not to mix synchronous and asynchronous replicas in the same load balancing group.

Support for DTC

Note: only on Windows Server 2016 or Windows Server 2012 R2 with KB3090973 patch upgraded

If your client application needs to perform transactions across multiple instances, you need a distributed transaction coordinator (DTC). DTC is part of the operating system and is used to ensure consistency when your database engine performs transactions across multiple instances.

USE AdventureWorks2012;GOBEGIN DISTRIBUTED TRANSACTION;-- your tsql statement hereDELETE FROM AdventureWorks2012.HumanResources.JobCandidateWHERE JobCandidateID = 13th GOCOMMIT TRANSACTION;GO

Your application can perform transactions not only between multiple SQL Server instances, but also on other compatible DTC servers, such as WebSphere or Oracle.

Cross-database transactions and distributed transactions for AlwaysOn availability groups and database mirroring.

Https://msdn.microsoft.com/en-us/library/mt748186.aspx https://blogs.technet.microsoft.com/dataplatform/2016/01/25/sql-server-2016-dtc-support-in-availability-groups/

Https://docs.microsoft.com/zh-cn/sql/database-engine/availability-groups/windows/transactions-always-on-availability-and-database-mirroring?view=sql-server-2017

In order to be able to execute distributed transactions in AlwaysOn 2016, the availability group creation statement CREATE AVAILABILITY GROUP has a WITH DTC_SUPPORT = PER_DB clause.

CREATE AVAILABILITY GROUP AGSQL2016WITH (DTC_SUPPORT = PER_DB) FOR DATABASE [Database1, Database2, Database3] REPLICA ON'SQLSRVTST1' WITH-substitute node name (ENDPOINT_URL = 'TCP://SQLSRVTST1.:7022',AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = AUTOMATIC),' SQLSRVTST2' WITH-substitute node name (ENDPOINT_URL = 'TCP://SQLSRVTST2.:7022',AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = AUTOMATIC); GO

Database-level health monitoring

In previous AlwaysOn 2012 and 2014, a failover was triggered if there was a health problem with the instance. If there is a problem with a database, as long as the instance OK, the availability group will not fail over. Therefore, if you have a database that is offline, abnormal, or corrupted, it will not trigger a failover.

In AlwaysOn 2016, a failover occurs whether there is a problem with one instance or one or more databases. However, this is not the default setting. In the create available Group configuration Wizard, you can select the "Database Level Health Detection" check box to specify. The corresponding parameter for creating an availability group is DB_FAILOVER = ON

You can also adjust the FailureConditionLevel property setting for when database failover is triggered. Adjust the default values as needed. Please refer to: https://msdn.microsoft.com/en-us/library/ff878667.aspx

Support the service account managed by the group

In SQL Server 2012, Microsoft added group-managed service account enhancements to make service account passwords easier to manage. You can now create a separate service account for your SQL Server instance, manage passwords in AD and assign proxy permissions to each server. This feature is useful for AlwaysOn AG because passwords and access to specific resources, such as shared files, can be managed by a single account rather than configured independently by each instance. Using a group-managed service account in AlwaysOn AG is more secure than using a normal domain user account.

Reference:

Https://docs.microsoft.com/en-us/windows-server/security/group-managed-service-accounts/group-managed-service-accounts-overview

Https://blogs.msdn.microsoft.com/markweberblog/2016/05/25/group-managed-service-accounts-gmsa-and-sql-server-2016/

Basic availability group

The AlwaysOn basic availability group is available in SQL Server 2016 Standard Edition. The function is the same as database mirroring, which has been deprecated and will be removed in future versions. The basic availability group provides failover of a single database, there can be only two replicas within the group, data synchronization can be in synchronous or asynchronous mode, and read-only access and backup support is not provided in the secondary copy. Create a basic availability group that can use CREATE

The AVAILABILITY GROUP statement has a WITH BASIC clause. CREATE AVAILABILITY GROUP BAGSQL2016WITH (AUTOMATED_BACKUP_PREFERENCE = PRIMARY,BASIC,DB_FAILOVER = OFF,DTC_SUPPORT = NONE) FOR DATABASE [Database1, Database2, Database3] REPLICA ON'SQLSRVTST1' WITH-- substitute node name (ENDPOINT_URL = 'TCP://SQLSRVTST1..com:5022',FAILOVER_MODE = AUTOMATIC,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)),' SQLSRVTST2' WITH-- substitute node name (ENDPOINT_URL = 'TCP://SQLSRVTST2..com:5022',FAILOVER_MODE = AUTOMATIC,AVAILABILITY_MODE = SYNCHRONOUS_COMMIT SECONDARY_ROLE (ALLOW_CONNECTIONS = NO)) GO

Reference:

Https://blogs.technet.microsoft.com/msftpietervanhove/2017/03/14/top-5-questions-about-basic-availability-groups/

No domain availability group

Note: can only be run on Windows Server 2016

While most companies run in a single domain environment, some companies run in multiple domain environments and can deploy availability groups across multiple domains so that multiple servers can act as DR replicas. Some organizations are not running in a domain environment at all.

In Windows Server 2016, WSFC does not require cluster nodes to be in the same domain, or no domain at all (can be in a workgroup). SQL Server 2016 can now deploy AlwaysOn availability groups in the following environments:

All nodes are in a single domain

L nodes in multiple fully trusted domains

L node in multiple untrusted domains

L node is not in the domain

Flexibility is improved by removing domain constraints from the cluster. Reference: https://blogs.msdn.microsoft.com/clustering/2015/08/17/workgroup-and-multi-domain-clusters-in-windows-server-2016/

Distributed availability group

Distributed availability groups that extend AlwaysOn AG across two different WSFC.

Distributed AG is also another way to migrate to a new configuration or upgrade SQL Server. Because distributed AG supports different underlying AG on different architectures, for example, you can change from SQL Server 2016 running on Windows Server 2012 R2 to SQL Server 2017 running on Windows Sever 2016.

Reference:

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups

Support for encrypting databases

In previous versions of AlwaysOn, databases were allowed to be encrypted, but they could not be added by creating a new availability group, and they could not be accessed if they switched to a secondary replica. In SQL Server 2016, encrypted databases can be added through a wizard and can be accessed after a failover. This is because when creating the availability group, the wizard sp_control_dbmasterkey_password each copy and creates the credentials using the database master key for each instance. After a failover, SQL Server searches for the correct credentials, knowing that the database master key can be decrypted.

There are some restrictions on adding encrypted databases to the AlwaysOn availability group. Reference: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/encrypted-databases-with-always-on-availability-groups-sql-server?view=sql-server-2017

Support for SSIS directories

In SQL Server 2016, you can, like other databases, add the SSIS directory (SSISDB) and its contents (projects, packages, etc.) to the AlwaysOn availability group to enhance high availability and disaster recovery.

There are some specific prerequisites and configurations for adding SSISDB to AlwaysOn availability groups, refer to: https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-2017#always-on-for-ssis-catalog-ssisdb

BI enhancement

With AlwaysOn availability groups, the database repository load points to one or more readable secondary replicas, while the master replica is used to support critical business applications. Reports and data analysis are resource-intensive applications, so load pointing to non-production servers can improve overall performance. Another enhancement is that Microsoft optimizes the data synchronization process, and the data synchronization latency in the data warehouse is so low that near-real-time analysis becomes a reality.

Conclusion

The latest version of the AlwaysOn availability group improves functionality, scalability, manageability, and robustness in terms of high availability and disaster recovery.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.