At this year’s inaugural Ignite Conference in held in Chicago Microsoft announced that the next release of SQL Server, previously referred to as SQL Server vNext, will officially be SQL Server 2016.

You can download the trial so called evaluation version of MS SQL 2016  Here . try It  your self free for 180 days.

Here Are the Great Features of SQL server 2016

•  Always Encrypted
• Stretch Database
• Real-time Operational Analytics
• PolyBase into SQL Server
• Native JSON Support
• Enhancements to AlwaysOn
• Enhanced In-Memory OLTP
• Revamped SQL Server Data Tools

We will have look at them one by one in this Series

Always Encrypted

Always Encrypted” technology will ensure that data is encrypted even while someone is actually working on it, Microsoft said in a blog post. Many systems now encrypt data while it’s sitting in the database or some sort of data store- encryption at rest and also when it’s being transmitted around the network – encryption in transit. But when it’s being parsed, analyzed and manipulated? That was a potential attack opportunity

AS Per the Microsoft  post:

The new security layer addresses that vulnerability by keeping the data encrypted even during transactions and computations, and by only giving the client keys to decrypt it. That means that if anyone else, including a database or system administrator, tries to access that client’s database, the credit card information or other sensitive data would just look like gibberish.

Always Encrypted, based on technology from Microsoft Research, protects data at rest and in motion. With Always Encrypted, SQL Server can perform operations on encrypted data and best of all, the encryption key resides with the application in the customers trusted environment. Encryption and decryption of data happens transparently inside the application which minimizes the changes that have to be made to existing applications.

Here are some important things about this New feature

1. Data is encrypted at all times

   Okay, so this might seem obvious but lets look at what this really means.  In the diagram above you see that the data for one or more columns of a table is stored in an encrypted state.  When SQL Server acts on this data locally it acts only on the encrypted version.  It never decrypts it and so it’s encrypted in memory as well as on the wire as it transits the network (or Internet) on the way to the client.  SQL Server treats the encrypted data as if it were the raw field.  Only at the point where the data reaches the client is it decrypted for use in your applications.  This makes the encrypted data nearly impervious to man-in-the-middle attacks or file based decryption on the server.

2. Encryption keys are not stored on the server

   SQL Server does not hold the keys to be able to decrypt the data it stores in Always On Encrypted fields.  While you do register on the server where the certificate should be found on the clients, the actual certificate is not accessible on the server.  The client can store the encryption keys currently in the local certificate store and in time in Azure Key Vaults or Hardware Security Modules.  One factor to consider is that SQL Server DBAs will not be able to view any of the encrypted data during migrations, imports, etc.  The natural tendency will be to install the client certificate locally on the server but that would negate much of the security of Always On Security by giving a hacked server access to the encrypted data.

3. Always on Encrypted columns support equality operators only in version 1

   Because the data is encrypted in SQL Server only full column equality is supported.  That means you can use equality in WHERE, JOIN, and GROUP BY clauses but you can’t use LIKE or other aggregation or pattern matching features like SUM, SUBSTRING, etc.

4. You’ll need to upgrade your client software to .NET 4.6

   As the encryption and decryption is done at the client, your clients will need to upgrade to the new version of the .NET framework which is 4.6.  Version 1 currently only supports SQL Server Client driver but ODBC and JDBC drivers will be coming at a later date.

5. This is not TDE

   TDE (Transparent Data Encryption) is a feature of SQL Server that encrypts the data files themselves on the server.  This is basically encrypting your entire database.  While this provides security for stored or “at rest” data, once the file is decrypted by SQL Server the data remains decrypted in plain text in memory and when it’s sent across the wires.  Always On Encryption will encrypt the data in memory and on the wire but due to the performance hit on the client it’s not intended to secure every field in a table or database as TDE does.  So you’ll need to consider this when you decide which security feature to implement.

6. Encrypted columns take significantly more space

   While columns defined with Always On Encryption specify the size of the original decrypted data they actually store the encrypted value which can be much larger.  This is something to consider when deciding how many columns to store as well as how big the columns you want to store are.

For Reference watch this video

In the next post  we will have look on  Stretch Database