SQL Server 2017 Details for the IT Decision Maker

SQL Server 2017, released in October, has introduced many new features that the IT decision maker role should be aware of. While I won't dive deep into technical details here, you should be mindful of the new capabilities and potential cost-savings involved.

The velocity of SQL Server development is changing. There were only 16 months between SQL Server 2016 and 2017, the shortest release window yet, and many features including all the basic tooling have their own rapid release cycles and have been removed from the .iso. New capabilities and improvements are being added faster ever, and supporting this is a dedication to backward compatibility and an incremental, and sometimes optional, feature set improvement. So if you're worried that you're still only running SQL Server 2012 or 2014, don't worry. 

Realistically, the only valid reasons to delay a SQL Server upgrade these days are vendor certification and support. Breaking changes between major SQL Server releases are remarkably few, and notable. Your software will almost certainly run fine on SQL Server 2017 if it was certified to run on as low as SQL Server 2005. This has been true for the last few releases. 

SQL Server 2014 introduced one of the only notable exceptions here, with improvements to the Cardinality Estimator that backfired in 10% or so of clients. (I have only encountered two clients that raised this concern, out of dozens.) The Cardinality Estimator issue is well documented, easily detected, and backward compatibility for pre-2014 behavior is a simple configuration option in SQL Server 2017, even more straightforward than the trace flag that was required in SQL Server 2014. In this way - upgrading from a pre-2014 version directly to SQL Server 2017 can be easier than more incremental steps. With the right knowledge of the features and settings here, there's no reason to be afraid of upgrading your SQL Servers.  

Let's walk through the features and capabilities you need to be aware of SQL Server 2017.

Cross-Platform Support

SQL Server 2017 is the first version to ship with native support for running on Linux. Red Hat Enterprise (RHEL) preferred, but Ubuntu and SUSE Linux Enterprise are also supported. SQL on Linux is just the latest in a bigger tent movement from Microsoft. If you already have Linux expertise in-house, you could save considerable OS licensing for your SQL infrastructure, and in the meanwhile, hybrid-platform solutions are possible.

More on Availability Groups later in this blog post, but yes, you can run Availability Groups between Linux+Linux, Windows+Linux, or Windows+Linux, that also span your on-prem and Azure-based VM's. 

SQL on Linux is almost fully featured. Some features are not available, however - for example, support for using the Windows Server Failover Cluster Manager, which doesn't exist on Linux, and tools based on Visual Studio don't run outside of Windows, such as SSMS.  Some other things are pleasant surprises - SQL Server on Linux has integrated Active Directory authentication, and a new cross-platform product called SQL Server Operations Studio (SSOS). SSOS, currently in beta, is based on the Visual Studio Code platform, which runs natively on Windows, MacOS, and Linux. SSOS isn't a complete replacement for SSMS (yet?), so other tooling solutions or remote, Windows-based administration is still a necessity right now.

SQL Server 2017 container images are also native for Docker, so if your IT team has growing experience with the new concept of containers, or perhaps Kubernetes, SQL Server can be spun up with Docker automation and ready on any OS within minutes.

High Availability

SQL Server Availability Groups have been a rich feature that allows for failover, readable secondaries, read-only routing, automatic corruption repair, and more since SQL Server 2012. They've helped enterprises achieve their RTO and RPO goals for years now, and successfully, including our clients in Houston during Hurricane Harvey.

New features included in SQL Server 2016 and 2017 for Availability Groups have broadened the opportunity to use Availability Groups in your environment.

• SQL Server 2016 introduced Basic Availability Groups in SQL Server Standard Edition. If you'd used Database Mirroring, then Basic Availability Groups will match the feature set, but with the modern tooling, infrastructure, and capabilities of Availability Groups. You don't get automatic failover, and can only have a pair of instances involved (instead of the usual cap of eight instances).

• SQL Server 2016 introduced Distributed Availability Groups, where an entire Availability Group can treat another Availability Group (usually geographically distant) as a replica, allowing a single primary database to communicate with up to 17(!) readable secondary replicas.

• SQL Server 2016 and 2017 have both expanded developer capacity to leverage the Distributed Transaction Coordinator and distributed transactions in databases inside Availability Groups. If this was a roadblock for your applications to leverage Availability Groups in the past - check again!

• SQL Server 2017 introduced Availability Groups on Linux, leveraging the Pacemaker cluster manager.

• Finally, and most substantially, SQL Server 2017 introduced clusterless Availability Groups, which do not leverage failover clustere managers like WSFC or Pacemaker, and instead communicate via SQL endpoint only. This is an especially useful new feature, because clusterless Availability Groups have all the features of a regular Enterprise-edition Availability Group except for Automatic Failover.

  • In documentation, you'll see these referred to as "Read-Scale Availability Groups," because without the ability for SQL-level automatic failover, they can't quite be considered a high availability solution, but you can still leverage secondary replicas to offload reporting workloads via a Listener.

The new Clusterless Availability Groups in SQL Server 2017 are going to be very useful to many clients for two reasons:

1. They are significantly less complex to configure, because we do not need to configure a failover cluster network, quorum settings, etc. Network Administrators and DBA's no longer need meetings and coordination to configure the OU permissions for service accounts, heartbeat networks, and more.

2. Many clients have the capability to use Automatic Failover of their SQL Availability Groups but do not, instead choosing to automate their failover with PowerShell scripting and other tools, so that more than the SQL Server layer can fail over. For example, would your applications be tenable if your SQL Server instance failed over to the secondary datacenter, but web and application servers did not? Some clients can't suffer than, and want to move the entire stack in the case of a cross-datacenter failover.

Finally, another new feature of SQL Server 2017 for Availability Groups is the number of Required Synchronized Secondary Replicas to Commit option, a safety setting for critical infrastructure. Configuring this setting up from the default of 0 requires that many synchronous secondary replicas to commit data before the primary can move on. This isn't a performance setting - it a safety setting that will prevent new transactions from committing if synchronous secondary replicas aren't responding. There are some client environments where this will definitely be put to use.

Performance

Two big performance-related buzz words to know are: Automatic Plan Correction, and Adaptive Query Processing.

Automatic Plan Correction leverages the Query Store (introduced in SQL Server 2016 and invaluable to DBA's and Developers) to detect the most expensive queries, query regression, and more. 

Before the Query Store, DBA's and developers could look at expensive query plans when doing performance tuning; now, we can look directly at queries, and how their performance changes with different plans. This is crucial to detecting sudden dips in performance that are entirely within the SQL Server.

The next logical step for SQL Server is then the ability to automatically take action, for example, on a query that has suddenly regressed because its plan has changed. Automatic Plan Correction is a feature that the PaaS Azure SQL Database offering has had for years - now we finally get it with on-prem SQL Server! Now, SQL 2017 can be configured to revert a query from a newer, slower plan to an older, faster plan, within seconds.

Adaptive Query Processing is a big topic for another blog post - but it delivers new performance improvements for SQL Server 2017 including this big mentionable: Batch Mode Execution with columnstore Indexes. This can result in massive performance increases for rowsets with millions of rows, potentially useful in any business intelligence application.

Service Model

No more SQL Server service packs. All patches are being pushed in the form of Cumulative Updates, usually every 60 days. If you're the IT Manager who always said "wait for the first Service Pack!," you'll need to instead pick an arbitrary number of CU's. We've had CU's as a more rapid alternative to Service Packs for years, but by focusing on rapid development of features and fixes in CU's only, we get more frequent, smaller SQL-based patches. 

Note that just like before SQL Server 2017, SQL CU's won't be deployed via Windows Update - as always you'll need to keep up with these, preferably manually when you can schedule a SQL Server outage. The CU outage will be relatively smaller than past Service Pack maintenance windows.

And just like SQL on Windows, SQL on Linux will receive CU's with the same increased frequency; however, SQL on Linux is patched by replacing the binaries in whole, not by an update process like with SQL on Windows.

Maintenance and Tooling

DBA's should be aware of new improvements in SQL Server that can decrease the time needed for regular maintenance windows, including resumable index maintenance operations, Integrity Checks with MAXDOP = 1, and dramatic improvements to the tooling around built-in Maintenance Plans in SSMS. 

Improvements to SSMS, now released every-other-month outside of the SQL Server product cycle (currently on version 17.5), include easy templates for Extended Events sessions that mimic and replace the built-in trace templates in SQL Server Profiler. That's right - no more need for Profiler or traces, you can use Extended Events for diagnostics and auditing your SQL Server that doesn't have nearly as much overhead or impact on production performance.

SQL Server 2017 Administration Inside Out

I had the distinct honor and months-long pleasure of being the lead author of an inimitable, international writing team for the latest Inside Out book series by Microsoft Press, SQL Server 2017 Administration Inside Out. The writing experience really made clear to me the vast number of improvements the SQL Server product team delivered just 16 months after SQL Server 2016, which itself was a huge jump forward.

If you're curious what each of the authors, including myself, liked most about SQL Server 2017, that was the fourth question in a fun author survey we did as the final edits were winding down in December. If you read the above, you'll not be surprised to know that my favorite new feature of SQL Server 2017 is Clusterless Availability Groups!

SQL Server 2017 Administration Inside Out by Microsoft Press is released globally on February 28 in softcover or ebook, along with accompanying training video produced by Microsoft.

Implementation

Many of the features I mentioned above are not enabled by default in SQL Server 2017. DBA's and developers need to be aware of these new features and changes to introduce them and their benefits into your SQL Server workloads. If you'd like training, knowledge transfer, or assistance with implementing, Sparkhound is ready to help.

We have a skilled team of DBA's, all of which are Microsoft certified professionals. We help many clients manage their SQL Servers remotely, and regularly interact, maintain, troubleshoot, and monitor SQL Servers from the Pacific Time zone to the Central European Time zone. Through consulting, we have designed, implemented, and supported large data integration projects, server migrations and upgrades, and custom developed solutions, working alongside our Application Services team of .NET Developers.

I hope you have found this blog post as informative as it was lengthy, and invite you to join us on an upcoming webinar and online Q&A, as well as future SQLSaturday events (including SQLSaturday Baton Rouge on August 11, 2018!)

Information and material in our blog posts are provided "as is" with no warranties either expressed or implied. Each post is an individual expression of our Sparkies. Should you identify any such content that is harmful, malicious, sensitive or unnecessary, please contact  marketing@sparkhound.com