Introduction

SQL Server 2016 is here! Its new features seductively call out to you: Always Encrypted! Dynamic Data Masking! Table Stretching into Azure! And more! You see many benefits to your employer’s business. You want to rush out and use your Software Assurance benefit to upgrade to SQL 2016 today.

But then, you remember that you have a manager. And the manager says “N minus one!” Nothing in production that is the very latest release. You had a fight on your hands to install SQL 2014 SP1. Worse, your manager was proved right when Microsoft had to withdraw SP1 the next day because of a major problem. You were lucky that you hadn’t applied SP1 immediately upon release. Your dreams of that new Tesla are still viable.

The Wisdom of Not Leading the Way

The SQL 2012 and 2014 Service Pack debacles left many managers skittish and rightly so. For most of us, SQL Server supports a business and must be solid. Managers find that solidness by choosing to hang back from the bleeding edge. Patience is the order of the day when it comes to upgrading any third party software, especially a fundamental application-support product such as SQL Server. While much of what is written here may seem obvious, it is true and must be applied.

The philosophy of running a version (or patch) behind the latest is called “N Minus One;” we’ll call it “N-1” for the rest of the article. It’s a risk management strategy designed to prevent anomalies and downtime associated with an unproven new version of a technology. The scenario above demonstrates the wisdom of the strategy.

I can hear it now: “Don’t the CTP releases of SQL Server vet most or all of the problems?” They do indeed locate many issues and give Microsoft the opportunity to fix them prior to RTM. However, they are not comprehensive and are not reflective of actual production workloads. This is why waiting until SP1 (or later – see above) to spin up a new SQL Server version is wise.

When a company depends on its data (and virtually all companies do), it cannot afford to roll the bones. I work for a company in the data business, and our data kept in SQL Server are not only our most important asset, but they are also a part of valuable trade secrets and patentable / patented intellectual property. Data loss could be disastrous, and we take more pains than the average enterprise to protect our data from any form of corruption.

Is the thrill of being an early adopter a sufficient reason to undertake the risk? Not for us and not for many companies. Therefore, N-1 creates a healthy inertia against hasty technology upgrades.

The Wisdom of Using the New Version

N-1 runs smack into the feature-laden paradigm-shifting release that is SQL Server 2016. The changes from 2014 to 2016 are truly significant. For example:

• Dynamic data masking and Always Encrypted promise two major improvements to SQL Server data security. With so many databases containing SPII (Sensitive Personal Identifying Information), it becomes something of an imperative to secure the data from both internal and external compromise.
• Row level security doubles down on data security in a meaningful manner.
• Stretching SQL database tables into Azure promises an archival solution that could remove a great deal of DBA and developer time: DBA no longer have to design archival programs, and developers can treat old archived data as if it was in the same table – because it technically is in the same table.

My mouth waters at the prospect of the above features; and I’ve only identified the low-hanging fruit of this major update. So why not spin up SQL 2016 RTM and start testing production apps for compatibility? The migration from SQL 2014 to 2016 promises to be quite easy, or so it seems.

There will always be benefits to the newest version. Microsoft doesn’t add these features because it thinks that they will be useless. It serves DBA well to look into these benefits in a real and careful fashion.

Being forced onto the razor blade : There is another side to the N-1 coin: Forced N. For some products (SQL Server is not one of them), the vendor will only support the latest patch of the product. Our ITSM tool is one of these; and this policy makes both my bosses and me uneasy. The patches come out rapidly, and we have had more than one occasion where a patch breaks something that works. We don’t have that problem with Microsoft and SQL Server, so this mention is more of an aside for other products with which you might deal.

The DBA’s Conundrum and The Solution

The data is safe and the DBA should be at least a little happy about that. However, what does the DBA do to learn the tricks of the newest release? There is a path to get yourself to the latest and greatest, at least to learn the ropes, and to convince management of the benefits of a new version of the software.

Create a playpen: If you don’t have a spare server, then get yourself a copy of Visual Studio with MSDN. Your employer should pay for it. You can then install SQL 2016 developer edition on your own machine, or spin up a VM in the cloud and install SQL 2016 there – Azure does not restrict you to using only Azure SQL database. If you have a license, you can install SQL Server 2016 in a cloud VM. Azure also deploys instances with the software pre-installed.

Editor's Note: Developer Edition is now free, so you can download and install it at no charge.

Once you have a playpen server, play with it! Scrub a smaller copy of production to remove SPII, and try to use the new features. Get a feel for the new version, and see if there are things that will hinder you from using the new version in your shop. Some enterprising SQL superstars build a home lab to try out new things. David Klee comes immediately to mind; he has a home lab that will leave most of us quite green with envy. This is the way that every DBA can get familiar with the newest and the greatest.

Justify the upgrade: Once you know what the new product can do, begin to create a presentation to justify the upgrade to the managers. Be frank about the risks; if you write a proposal with nothing but benefits, management will see you as a cheerleader and assume that you have not carefully evaluated the upgrade. It is much better to go in with three well-justified benefits and 10 well-considered risks than to go in with 10 enthusiastic benefits and no risks.

Your proposal should include at least a 10,000-foot overview of a project plan. Your plan should include any needed hardware and or operating system upgrades, and the costs for the same if you can ascertain them. You should be able to describe the high-level steps of getting to the new version, and also to be able to give an estimate of how long it will take and how long production would be down for the upgrade.

Always include a plan to test in a lower environment! I am probably stating the obvious to anyone in an enterprise environment, but in smaller shops the idea of a lower environment may itself seem to be an extravagance. There is a famous internet meme featuring the Dos Equis “Most Interesting Man in the World” with the caption: “I don’t always test my code, but when I do, I test only in production.” In your dreams, pal! It takes a great deal of beer to think that testing is not crucial. I have that meme hanging at my desk to remind myself that testing is vital. Both the new SQL release and the applications depending on SQL Server need to be well-tested. This includes tool kits, monitoring and backup tools, all of it. The applications should be regression tested if the upgrade is a leap (e.g., SQL 2005 to SQL 2016).

Be ready to wait! You may find management receptive to your plan – but deciding to delay until SQL Server 2016 is at SP1. If that’s the case, keep calm and carry on. The best IT managers are risk management experts. They are also wise to be patient. The inertia created by the “if it ain’t broke, don’t fix it” mindset usually ends up creating stability by ensuring that upgrades are made into well-proven software.

Conclusion

There is wisdom in N minus one, but there is also wisdom in finding a place, be it a playpen at work or a lab at home, to test out the new releases and especially the features that you would find useful when it comes time to bring your shop onto the newest release.

----

John F. Tamburo is the Chief Database Administrator for Landauer, Inc., the world's leading authority on radiation measurement, physics and education. John can be found at @SQLBlimp on Twitter. John also blogs at www.sqlblimp.com.