SQL 2016 – 5 (Real) reasons to upgrade, part 1

In my role as a consultant, many customers ask me for real reasons why to upgrade their existing SQL platforms to the next iteration of SQL Server. As migrating their existing environments always contain a risk, they are often looking for good reasons to upgrade or to stay at their current build level.

In this post I will not mention servers that are running a build lower then 2012, as they should upgrade as fast as possible for support reasons, but there might be some very good reasons why you should investigate upgrading your existing SQL2012 or SQL2014 builds as well. This is ofcourse easier when you have software assurance, but could be interesting for some older SQL2012 servers as well.

For new development or new servers, upgrading to SQL2016 should be a no-brainer because of all the new possibilities of the latest iteration but let’s look into good reasons to upgrade existing servers.

This blog post contains valid reasons why I would recommend upgrading an existing server to SQL2016, of course it is not complete but if your environment would be in one of these conditions, I would really recommend upgrading.

DTC Support

The good
Distributed transactions are supported with Always On Availability Groups. This applies to distributed transactions between databases hosted by two different SQL Server instances. It also applies to distributed transactions between SQL Server and another DTC-compliant server.

Distributed transactions are supported with Always On, this is a very straight forward one, up to now if you wanted to use DTC you had to stick with failover clusters or take the risks of having an unsupported/unstable environment. De reason why DTC wasn’t supported is fairly straightforward but could give a DBA a lot of headaches especially when you wanted to have a DR node in the cloud or in a remote datacenter.

Why doesn’t it work on SQL2014 and lower?
DTC transactions rely on the DTC manager to help determine the outcome of the transaction. When recovery occurs on a database the DTC manager may be contacted to determine if the transaction should be rolled forward or back.

Today the problem with DTC transactions and Availability Groups (AGs) is one of resource management identification. The SQL Server instance (IMPORTANT: which is different on the various cluster nodes) becomes a resource manager as part of the transaction. When resolving a DTC transaction during recovery the SQL Server instance communicates with the DTC manager.

For example:

1. Primary Node 1 (ResMgrId = 1) enlists in a DTC transaction (ABC) but the transaction is not yet committed
2. Secondary Node 2 (ResMgrId = 2) receives log blocks for the transaction but redo can’t commit or abort the transaction because it has not been committed or aborted at the primary
3. Primary Now Fails over and the Secondary (Node 2) becomes the new primary
4. Recovery needs to bring the database on the new primary (node 2) into writeable state. It knows about DTC transaction (ABC) but it is communicating with the DTC manager as ResMgrId = 2 and ResMgrId = 2 was not the SQL resource involved in the original transaction; ResMgrId = 1 was the SQL Instance
5. Node 2 can’t determine the proper outcome of the pending DTC transaction and locks up

The Bad
No Cross-database support within the same instance
Cross-database transactions within the same SQL Server instance are still not supported for Always On Availability Groups. This means that no two databases in a cross-database transaction may be hosted by the same SQL Server instance. This is true even if those databases are part of the same Availability Group.

The Ugly

• The availability group must run on Windows 2016 or Windows 2012R2+Kb3090973 otherwise it will not work..
• While creating the availability group, it must be created with DTC_SUPPORT = PER_DB
• You cannot alter an existing Availability group
• It doesn’t work with BASIC availability groups (so DTC on AG became an enterprise feature)

Query Store

Currently it is very difficult for a DBA to fix query plan choice regression, especially in a world where a lot of applications are using ORM/Frameworks. Due to this it takes a lot of effort to detect the issue and to find out the different plans. Once we found the query, it takes even more time to mitigate the issue, main reasons are that we’re not always allowed to (just) change the query or index strategy of a database.

In these circumstances the new query store feature becomes a real game changer. I am a great fan of how it allows us to see database usage patterns and assist DBA’s in quickly finding and fixing plan performance regression. It simplifies performance troubleshooting by enabling you to quickly find performance differences caused by changes in query plans. The feature automatically captures a history of queries, plans, and runtime statistics, and retains these for your review. It separates data by time windows, allowing you to see database usage patterns and understand when query plan changes happened on the server.

The query store presents information by using a Management Studio dialog box, and lets you force the query to one of the selected query plans.

What do we need to know:
The Query Store’s internal tables are created in the PRIMARY file group during database creation and that configuration cannot be changed later. It captures it’s data in memory to minimize IO overhead, but it will then persist this asynchronously in the background but it’s management views merge both the in memory store and the on-disk content so that we can always see the ‘latest’ data. (sounds a bit like Hecaton to me ;-).
Due to the nature of the query store we have to be Careful with Ad-hoc queries

What can we do with it?
Performance auditing and troubleshooting

• Last n queries executed on the database
• Number of executions for each query
• The number of queries with the longest average execution time within last hour
• The number of queries that had the biggest average physical IO reads in last 24 hours, with corresponding average row count and execution count
• Queries with multiple plans
• Queries that recently regressed in performance (comparing different point in time)
• Queries that recently regressed in performance (comparing recent vs. history execution)

Maintaining Query Performance Stability
For queries that are executed multiple times you may notice that SQL Server used different plans which resulted in different resource utilization and duration. With Query Store you can easily detect when the query performance regressed and determine the optimal plan within a period of interest. Then you can force that optimal plan for future query execution.

You can also identify inconsistent query performance for a query with parameters (either auto- parameterized or manually parameterized). Among different plans you can identify plan which is fast and optimal enough for all or most of the parameter values and force that plan; keeping predictable performance for the wider set of user scenarios.

Clustering without a domain

In Windows Server 2012 R2 and previous versions, a cluster could only be created between member nodes joined to the same domain. Windows Server 2016 breaks down these barriers and introduces the ability to create a Failover Cluster without Active Directory dependencies. Failover Clusters can now therefore be created in the following configurations:

• Single-domain Clusters: Clusters with all nodes joined to the same domain
• Multi-domain Clusters: Clusters with nodes which are members of different domains
• Workgroup Clusters: Clusters with nodes which are member servers / workgroup (not domain joined)

The prerequisites for Single-domain clusters are unchanged from previous versions of Windows Server

• All servers must be running Windows Server 2016
• All servers must have the Failover Clustering feature installed
• All servers must use hardware that has been certified
• The collection of servers must pass all cluster validation tests

Gotcha’s

Management operations may only be performed using Microsoft PowerShell. The Failover Cluster Manager snap-in tool is not supported in these configurations. To create a new cluster (using the New-Cluster cmdlet) or to add nodes to the cluster (using the Add-ClusterNode cmdlet), a local account needs to be provisioned on all nodes of the cluster (as well as the node from which the operation is invoked) with the following requirements:

• The Failover Cluster needs to be created as an Active Directory-Detached Cluster without any associated computer objects. Therefore, the cluster needs to have a Cluster Network Name (also known as administrative access point) of type DNS
• Each cluster node needs to have a primary DNS suffix

Always on changes

Enterprise Edition
Load balancing in readable secondary’s: In SQL2016 we can now configure the read only nodes to round robin among a set of secondary’s.

More then two automatic failover targets: Any sync secondary can now be a target for automatic failover with a total of three automatic failover targets.

Standard Edition
AlwaysOn Basic Availability Groups replaces the deprecated Database Mirroring feature for SQL Server 2016 Standard Edition. Basic availability groups enable a primary database to maintain a single replica. This replica can use either synchronous-commit mode or asynchronous-commit mode. For more information about availability modes, see Availability Modes (AlwaysOn Availability Groups). The secondary replica remains inactive unless there is a need to failover. This failover reverses the primary and secondary role assignments, causing the secondary replica to become the primary active database. For more information on failover, see Failover and Failover Modes (AlwaysOn Availability Groups). Basic availability groups can operate in a hybrid environment that spans on-premises and Microsoft Azure.

Limitations

• Limit of two replicas (primary and secondary)
• No read access on secondary replica
• No backups on secondary replica
• No support for replicas hosted on servers running a version of SQL Server prior to SQL Server 2016 Community Technology Preview 3 (CTP3)
• No support for adding or removing a replica to an existing basic availability group
• Support for one availability database only
• Basic availability groups cannot be upgraded to advanced availability groups. The group must be dropped and re-added to a group that contains servers running only SQL Server 2016 Enterprise Edition