Skip navigation
Image of a padlock

Upgrading to SQL Server 2016: New Data Privacy and Security Features

Released last June, Microsoft SQL Server 2016 has been available for about eight months, even longer if you were using one of the CTP versions as far back as 2015. Now well into the New Year, if they’re not already, most infrastructure teams and management, including yours, will soon be evaluating the costs, benefits, and risks of upgrading to SQL Server 2016.

When it comes to upgrading mission-critical server software, such as SQL Server, there are some challenges to overcome. The first is the “if it ain’t broke, don’t fix it” mentality. If things are already working, business leaders are not always willing to spend resources to upgrade to the latest version. Even SQL Slammer is making a comeback due to companies running old, unpatched versions of SQL Server.

It’s not just the cost of the new software—it’s also the cost of a new operating system, and possibly new hardware. There could be licensing changes as well. When all of these costs are added together, you may find that they outweigh the benefits. That’s when a company usually decides that staying with an older version is worth the risk.

However, something that you need to consider in your analysis is the cost of an upgrade versus the cost of a data security breach. The cost of your next data breach may be more than you realize (or budget for). Chances are, it is less expensive to upgrade to SQL Server 2016 than to suffer a data breach. With data security and privacy issues on the rise, the decision to upgrade to SQL Server 2016 should be an easy one to make. 

Always remember, data is the most critical asset your company owns. As such, you should take every reasonable step to protect your data. SQL Server 2016 offers three new features to help you protect your data that may alone make it worth the time and effort to upgrade. 

The first feature is called Always Encrypted. Just like the name suggests, the data inside SQL Server remains encrypted, even in memory (unlike Transparent Data Encryption, which only encrypts data at rest). This means that no one, including the DBA with sysadmin rights, will be able to see unencrypted data without the proper certificate key. The data is encrypted/decrypted at the client only. This makes Always Encrypted ideal for the most sensitive data your company owns. 

The next feature is called Dynamic Data Masking. This feature applies a mask to columns as they are returned to the client, making the performance impact small, and helping to protect sensitive data from ending up on a spreadsheet left behind on a subway train. This feature allows for the masking to take place on the server side, reducing the need for extra client code to perform this function. It’s also a great way to protect sensitive production data from being exposed in your test and development environments. 

The third feature is called Row Level Security. This feature applies a set of predicate rules to filter out data that the end-user does not need to see. The end-user never knows that any rows have been filtered. You can even use Row Level Security to block updates to the database. Historically, this type of filtering was either handled through views or inside the client application. With this feature and Dynamic Data Masking, you can move these security layers inside the database, reducing the security surface area and thus reducing risk. 

When used together, these three features complement each other to help form the basis of a proper data security and privacy strategy. Also worth noting is that with SQL Server 2016 SP1, all three of these features are now available in all editions of SQL Server. That’s right, all editions

Here are a few quick tips to help you get started with the new data security and privacy features in SQL Server 2016:

If Microsoft is taking data privacy and security this seriously, why aren’t you? Don’t wait to get that upgrade project started in 2017. Get started now, because the next breach you suffer may be your last.


Thomas LaRock is Head Geek at SolarWinds. Industry Perspectives is an occasional series of guest expert contributors talking about their craft and industry. Learn more about contributing to IT Pro Industry Perspectives.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.