In-Depth

Microsoft Unveils SQL Server's Future

Microsoft packed a number of major announcements, including SP1 improvements and Linux support, in this month's key note address by Scott Guthrie at their Connect() virtual event.

• By Joey D'Antoni
• 11/28/2016

Microsoft's main talking point with regards to its SQL Server at November's Connect() developer event was that Microsoft is providing tools for any developer to write any app on any platform. In between major announcements like Visual Studio for Mac and Microsoft joining the Linux foundations, Microsoft also made some major announcements regarding SQL Server. The public preview of SQL Server v.Next (that's not a typo) on both Linux and SQL Server was announced, along with major changes to SQL Server's long-standing edition model, beginning with Service Pack 1 for SQL Server 2016. Finally, as part of its commitment to honoring open source principals, Microsoft announced it was making the SQL Server JDBC driver open source.

SQL Server Standard Edition Gets an Upgrade
The biggest news for many independent software vendors is that SQL Server is moving to a more unified development model. Before this month, SQL Server reserved many key features (In-Memory OLTP, Columnstore indexes, data compression and partitioning to name a few) for the Enterprise Edition of the product. This meant many independent software vendors (ISVs) would write their applications to not take advantage of these features in the RDBMS in order to give customers the ability to run on any edition. By offering a more complete model in the on-premises version of SQL Server, Microsoft gives ISVs a single development and allows them to take advantage of the features that differentiate the platform from open source competitors such as MySQL and PostgreSQL. Microsoft calls this the consistent programming surface area (CPSA) and it is a big deal for ISVs.

What features remain only in Enterprise Edition (EE)? Mostly scalability features -- while Standard Edition can only address 128GB of RAM, EE is only subject to the limits of the operating system. There are also limits around the amount of memory and CPU resources allocated for columnstore and in-memory OLTP. Several other internal features like advanced scanning and online table and index rebuilds remain Enterprise-only. Microsoft also has not changed the model for high availability and disaster recovery -- Standard Edition still only supports two-node failover clusters, and limits the capabilities of AlwaysOn Availability Groups. In enterprise edition, customers can use their secondary replicas to offload read workloads. However, in Standard Edition the secondary replica (customers are limited to one) is limited to failover uses only.

Other SP1 News
There were a several other major enhancements to SQL Server 2016 SP1. For anyone who has built views or stored procedures in SQL Server, you have experienced the pain of changing code for revision or deployment. SP1 introduces 'CREATE OR ALTER' syntax that allows developers to automate the deployment of their database objects and fits better into the continuous deployment model that many modern applications use. The other major enhancement is the addition of DBCC CLONEDATABASE command to SQL Server 2016. This command was a late addition to SQL Server 2012 and 2014, and with SP1 it gets added to SQL Server 2016 with some extra features. The biggest extra feature is the ability to clone the data from the Query Store, which is an execution plan management feature. This allows an ISV to profile their application in a test environment, identify and fix problematic execution plans and then clone that data to their customers. Note, that the CLONEDATABASE feature is limited to metadata -- you would still need to use backup and restore or import and export. There are several other new internal features which expose more metadata about query operations, which should make tuning problematic queries more intuitive for DBAs and developers.

SQL Server on Linux and v.Next
SQL Server on Linux was announced in March 2016. This was big news when it happened and it was mentioned that it would be released sometime in 2017. However it was not clear how the versions would align with SQL Server 2016 that was released in June. Well, with this month's announcements everything became clear; there will be a new release of SQL Server on both Linux and Windows in 2017. While the full set of upgraded features hasn't been released, Microsoft announced support for graph functionality and adaptive query execution for Azure SQL Database at PASS Summit in October, which is usually an indication that those features are coming to the on-premises product.

What does this mean for customers who just upgraded to SQL Server 2016? Well, if you have software assurance, you can upgrade for free. Upgrades are a fact of life with any modern software. With SQL Server 2016 and above Microsoft has given you the tools to make these upgrades easier, low risk, and require minimal outages.

SQL Server on Linux also made its public debut last week. Even though it had been announced in March, access had been limited to a select group of private preview customers. Microsoft is supporting many Linux platforms including Red Hat Enterprise Linux, Ubuntu, and, interestingly, Docker containers. The SQL Server Docker containers allow a developer running on a MacBook to use Visual Studio Code develop against SQL Server in a Windows-free environment. 

The best way to describe SQL Server on Linux is that Microsoft's goal with the release is for it the same SQL Server no matter what OS is runs on. A couple of features (SQL Agent, Always On Availability Groups) are not in the product, but are slated to be added in the forthcoming community technical previews. The areas that are different are around high availability and disaster recovery, which are dependent on Windows Server Failover Clustering in Windows. In Linux, these features will use open source options like Pacemaker and Corosync . In the end, the process of developing and administering SQL Server will be the same on all platforms. You can interact with SQL Server on Linux just like you would on Windows, whether it's using BCP or SQLCMD on the Linux command line, Visual Studio Code, or SQL Server Management Studio on Windows. 

Microsoft also introduced a command line tool called "DMVTOOL" that allows SQL Server's dynamic management views to be accessed from the command line. This allows system administrators who may have limited knowledge of how to write SQL queries to get performance information without touching the database.

What Is the End Game?
Microsoft has shown that they want to support all apps on all platforms, and engage a generation of developers who have mostly worked with open source tools throughout their training and career. This has been one of the hallmarks of Satya Nadella's Microsoft. Microsoft seems to be doing the same with SQL Server -- one platform whether your running on Linux or Windows, or even Azure. The Azure SQL Database product is not 100 percent feature-complete with SQL Server, but it has trended towards being so over time. Having the CPSA allows that to happen, it greatly simplifies development effort and, in my opinion, it also shows Microsoft wants to be thought of the premier database across all platforms whether Linux, Windows or containers.