Two AlwaysOn Enhancements With SQL Server 2016

SQL Server® AlwaysOn® was introduced with SQL Server 2012 and it has been one of the marquee capabilities when discussing High Availability with SQL Server. It is an awesome feature because it brought the best of all the available HA options that were already with SQL Server. In every release since then, this feature has been getting its very own dose of enhancements, and the next release of SQL Server is no exception.

In this blog, I discuss some of the key capabilities added to SQL Server AlwaysOn. Though these are not the only additional features, they are the ones that caught my attention.

Read scale-out enhancement

One of the biggest restrictions in previous versions of SQL Server for AlwaysOn was servicing read-only workloads as part of the routing list. This was less than optimal because even though we have more than one secondary server, which is available for read purposes, we couldn’t use them effectively.

From SQL Server 2016, we have an option to create a group of secondary replicas as part of the routing list that can be used in round-robin fashion. To get a feel for how this will be represented, see the following code:

ALTER AVAILABILITY GROUP SQL2016_RoutingList

MODIFY REPLICA ON ‘Node1’

WITH

(

PRIMARY_ROLE

(

READ_ONLY_ROUTING_LIST= ((‘Node2’, ‘Node3’), ‘Node4’)

)

);

Note the way we have represented Node2 and Node3 in parenthesis. This is a great addition because now the read-only workload would be load-balanced between Node2 and Node3 in a cyclic manner.

Support for distributed transactions

This improvement will enable support for DTC (Distributed Transaction Coordinator) transactions with Availability Groups (AGs). This will guarantee that the transactional (ACID) properties of DTC (distributed) transactions for DBs are part of the AGs. When I discussed this with a SQL Server administrator, he questioned how this can be enabled. Here are the steps:

1. Open the Component Services snap-in. To open Component Services, click Start. In the search box, type dcomcnfg, and press ENTER.
2. Expand the console tree to locate the DTC (for example, Local DTC) that you want to enable Network MS DTC access for.
3. On the Action menu, click Properties.
4. Click the Security tab, and make the following changes: In Security Settings, select the Network DTC Access check box. In Transaction Manager Communication, select the Allow Inbound and Allow Outbound check boxes.
5. Click OK. You will be prompted to restart the MSDTC service, click Yes.

Now these are the steps to enable MSDTC services.

In this blog, I have discussed two interesting features, which have been introduced with SQL Server 2016 for AlwaysOn. With every new version of SQL Server, such enhancements are needed as they address specific customer needs. Let me know if you find these enhancements interesting, and how you might use them in your environment.