Will You Upgrade to SQL Server 2016?

Stretch Database technology EDITOR’S NOTE: This blog has been updated several times since SQL Server 2016 became generally available on June 1, 2016, including on Aug. 2, 2017. This includes notice that the second release candidate of SQL Server 2017 is now available, and links to additional resources.

Microsoft announced the release of SQL Server 2017 later this year, with the second release candidate of SQL Server 2017 becoming generally available on Aug. 2. And with SQL Server 2016 generally available, will your organization commit to an upgrade? Are you having problems migrating from older versions?

Microsoft’s SQL Server Team posted a blog on April 5, 2017, giving you “The top 5 reasons to upgrade to SQL Server 2016.”

These reasons included:

Seamless step-up without rewriting apps
Take back your weekend
Unparalleled level of data security
Free your users from their desks
Upgrade without headaches

Head over to the blog for more on these reasons.

Thomas LaRock, a Head Geek and author, has his own set of reasons to upgrade that he posted on his blog a few weeks later. These include:

New features, such as Always Encrypted and Dynamic Data Masking
Supportability
Vendor Requirements
Company or Industry Standard
Scalability

Head over to his blog to get the scoop on why those are important – and to another post on tasks you must complete before upgrading. (Do you know another source for good advice and tips on upgrading? Share it with us.)

But now with the release of SQL Server 2017 announced for later this year for both Linux and Windows — and a community preview release — decision-making didn’t get any easier. Microsoft’s Travis Wright reveals what’s new in the database engine in this video.

The Microsoft SQL Server Migration Forum should be helpful if you have problems that seem to defy Microsoft instructions.

On July 25, 2016, Microsoft released a cumulative update for SQL Server 2016, the first since its general release the month before.

Are you running a prior version of SQL Server? Then you’ll need to run a set of upgrade rules against your SQL Server database to pinpoint breaking and behavior changes and deprecated features? Microsoft offers its standalone SQL Server 2016 Upgrade Advisor Preview for download.

In news related to SQL Server 2016, Microsoft made available for download SQL Server Management Studio 16.5.3. SSMS offers an integrated environment for accessing, configuring, managing, administering and developing all components of SQL Server. For those of you who like to live dangerously, a release candidate can be found on that page as well. The rest of us won’t use the RC for production.

On Feb. 1, 2017, Microsoft made the latest revision to an article on SQL Server 2016 improvements in handling some data types and uncommon operations. This tells you how to upgrade your database compatibility to increase precision when dealing with floating-point types. Here’s a hint: upgrade to 130 compatibility with Microsoft SQL Server 2016 and Microsoft Azure SQL Database.

As I wrote several months ago, any organization still using SQL Server 2005 needed to plan an update before April 2016, when even extended support by Microsoft ended. Considering mainstream support ended in 2011, this shouldn’t come as a surprise, especially as plenty of bloggers have been offering steady reminders this year.

So, what update choice did you make? Many companies picked MS SQL Server 2012, which appears to be a safe choice. Released four years ago, its mainstream support end date won’t come until 2017. But that’s less than two years away. True, its extended support will reach 2022. It still seems as that would be sticking with old technology, especially now that we have SQL Server 2016.

The previous full release was MS SQL Server 2014, which is just over two years old. Mainstream support won’t end until 2019, a respectable three years away. Extended support takes its effective life cycle to 2024.

SQL Server 2014 has a lot going for it. Microsoft introduced in-memory OLTP (online transaction processing) with this edition. Called Hekaton, it lets users take advantage of large memory and dozens of cores, and cut processing times by 30 to 40 times.

Microsoft built In-Memory OLTP into SQL Server 2016. Starting with SP1, which was released in November 2016, you can use all these objects in any edition of SQL Server. Pay attention to memory quotas in Express or Standard – but if this really is something you plan to use, it’s more likely you’ll make use of the Developer or Enterprise editions.

Using Virtual Tables to Simplify SQL-based Data Analysis Without Views

Create and configure your customer’s analytics experience through an administrative UI.

“In-memory OLTP provides row-based data access to memory-optimized tables, making it possible to support high-performance workloads, without requiring specialized hardware or software,” according to Robert Sheldon, a TechTarget contributor.

This technology improves throughput and reduces latency.

SQL Server Debuted When Memory Cost Plenty

Database management systems such as SQL Server were designed when main memory was expensive and data resided on disk. However, in the past 30 years, memory prices dropped by a factor of 10 every 5 years. Microsoft developed in-memory OLTP to take advantage of these advances for its SQL Server 2014 release.

Now that SQL Server 2016 has become generally available (as of June 1, 2016), an update to it makes more sense. (You can download it from TechNet Evaluation Center, MSDN Subscriber Downloads, Visual Studio Dev Essentials, or your own Volume License server(s).)

Joseph Sirosh, corporate vice president of the Data Group at Microsoft, said it “includes new innovations for mission-critical performance with In-Memory OLTP and real-time Operational Analytics, first-in-market Always Encrypted, built-in SQL Server R Services, JSON support, and federated query from relational to Hadoop with PolyBase, and active archive of cold data to Azure with Stretch Database.”

Many limitations to in-memory OLTP found in SQL Server 2014 have been eliminated, which is promising. Basit Farooq, another TechTarget contributor, said the same transactions that run against SQL Server 2014 memory-optimized tables will run 30 times faster when running against SQL Server 2016 memory-optimized tables.

And Robert Sheldon said the upcoming version offers support for greater memory, more sockets, and multithreaded logging, as well as the ability to alter memory-optimized tables and natively compiled procedures. All of these improvements could convince SQL Server 2014 users to upgrade.

Microsoft announced just a few weeks after SQL Server 2016 became generally available that it removes the size limitation on user data in memory-optimized tables.

“You can grow your memory-optimized tables as large as you like as long as you have enough available memory,” said Microsoft’s Jos de Bruijn. “This means that with Windows Server 2016 you can leverage all 12TB (Terabytes) of available memory in a given server with In-Memory OLTP.”

Additional SQL Server 2016 Improvements

Maximum memory for memory-optimized tables jumps from a recommendation of 256GB to 2TB
Collation support
Alter memory-optimized tables (after creation)
Alter natively compiled stored procedures
Parallel plan for operations accessing memory-optimized tables
Transparent Data Encryption (TDE)

For a complete comparison of in-memory OLTP limitations and features, check out Artemakis Artemis’s chart on his “Simple Talk” blog.

Of course, there are other improvements to SQL Server 2016, such as data always being encrypted, and SQL Server users will be able to dynamically stretch their data to Microsoft Azure to increase performance.

If you don’t like to adopt the latest technology so soon after its release, and avoid software updates until other people find all of the biggest bugs and problems, SQL Server 2014 would appear to be the best choice. Even with its limitations, it offers improvements over all previous versions. For some organizations, this might mean a delay in upgrading to SQL Server 2016 for several more years after its release.

But organizations using SQL Server 2012 may choose to skip right to the latest version. Few IT workers would want to go through multiple upgrades, considering the expense, including staff time involved. That has to be weighed against the need for a powerful processing engine for intensive database operations coming in SQL Server 2016 that should enable the organization can meet the needs of managing the rapid growth of data.

What are your organization’s plans for SQL Server?