5 Reasons to Execute a SQL 2016 Upgrade

Resistance to change is understandable when it comes to the Database, but the many benefits of Microsoft's SQL 2016 should make any IT leader consider the upgrade.

IT leaders have well worn justifications for resisting changes, even if it means reducing costs. When it comes to the database, adhering to the status quo is the golden rule. If the subject arises, an IT Director will often mumble how new licensing is too expensive and upgrading the database adds risk. After all, mitigating risk is at the core of the job. Taking on additional risk is not. Additional risk can cost one their job.

There are some valid reasons for not upgrading of course - you can’t get blood from a stone, there just isn’t any money to go around, and some applications might still be using distributing transactions. Besides who really has time for another project? We finally got the users to quiet down.

With the release of Microsoft’s SQL 2016, however, one might seriously consider challenging the status quo. An increase of $254 (3.6% increase) per core as compared to SQL 2012 will be considered chump change in comparison to the amount of risk you can mitigate by being on this version. Heck, you may even ask management to take it out of your paycheck.

If you champion this project, you get much more than a box of chocolates. For starters, SQL 2016 is now 100% 64-bit. There is no longer a dependency on .NET Framework 3.5. Also, SQL Enterprise Edition is no longer needed if you want to use Availability Groups. Microsoft’s goal is to get rid of database mirroring for good, thereby saying adios to the unreadable single secondary.


Additionally, Active Directory is no longer required. Pre SQL 2016, you needed to have a domain to build failover clustering. Now you can have multiple or no domains. A server with a DNS suffix will suffice. However, keep in mind that this feature is only available with SQL2016 installed on W2K16. So, if you are running an ERP software like JD Edwards EnterpriseOne, you would discover (as of May 2017) that Oracle supports SQL 2016, but not on W2K16. But let that go.


As I mentioned earlier, Standard Edition (SE) offers availability groups. This is a big win for the small shop. Don’t get too excited, though, as there are some caveats. For example, you’re restricted to a single database per availability group and you are limited to synchronous. SE also only offers 2 replicas. If you can live with the limitations of the SE, upgrading your database mirroring to Availability Groups will significantly improve performance. If you are currently leveraging Enterprise Edition, taking the $7k+ per core license fee off the OPEX is probably not going to happen.

Before getting its AlwaysOn nameplate, Microsoft called this feature HADR. No matter its name, you can say good bye to manual failover. Traditional SQL DR setups have a 3-node cluster. 2 nodes are typically in the primary datacenter for high availability, and one node is in the secondary for disaster recovery. Basically, there are 3 synchronous targets with 2 automatic fail over targets. If you lose one node, automatic failover will happen. If you lose another node, manual failover to the DR site is required. This leaves you exposed because it takes time to be notified of the failure and it will take time to execute the manual failover. In SQL 2016, auto failover happens for all 3 nodes. Failover goes from 2 fail over targets to 3 thereby matching your 3 synchronous targets.

Another item I'm definitely stoked about is SQL 2016’s Always Encrypted feature- Data Encryption at Rest, in Motion and in Memory! Now that's a movie title I’d like to watch! Since there aren’t too many companies who have Marcus Hutchins (WannaCry) available to protect their IT assets, protecting data over the internet has never been more important than it is today. Sure, you could use SQL’s Transparent Data Encryption, but that only protects the data at rest. To protect data in motion, lots of customers are using 3rd party companies like DbDefence to fill the gap, which can cost upwards of $50k per database. Say goodbye to that line item. And to keep it interesting, with SQL 2016, Microsoft gives you two types of encryption- randomized and deterministic. Now you can use Always Encrypted to protect data at rest, in motion and in memory. Life is good!

There's often confusion about how in-memory encryption works. Let me explain. The encrypted data is passed to SQL server which puts the encrypted data on disk and loads the encrypted value into memory when that page is in memory. The decryption only happens when a client possesses a certification with the ability to decrypt it. It does not happen in memory.

Always Encrypted gives you much more control over certifications, keys, and who can decrypt the data. Lots of our customers are very concerned about security. Their auditors are like Colonel Klink, constantly looking to limit the data we can see and the actions we can perform. With Always Encrypted, data can be protected from sysadmin. However, it cannot be protected from a sysadmin who also has windows security admin role. If you are using an ITaaS company like ours (Allari) to do your system administration tasks, this is a bonus.

There are so many more options you can benefit from in this release of SQL, like log transport performance, round robin, Azure GUI interface, and built-in Business Intelligence. It's worth checking out. And for the first time in Microsoft SQL history, a new version will be launched in an odd year.  That is correct. SQL2017 is due out May 31, 2017.