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.
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.
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.
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
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.
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:
The prerequisites for Single-domain clusters are unchanged from previous versions of Windows Server
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:
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.
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.