Exploring SQL Server 2016’s Powerful New Features

Jeffrey J. Keller, Principal at MSSQLSage

by
Jeffrey J. Keller
Principal at MSSQLSage

Posted: March 22, 2016

With the release of SQL Server 2016’s Community Technology preview, we’re finally able to get an in-depth look at the exciting options SQL Server 2016 includes. Two of the most anticipated are Always Encrypted and In-memory OLTP. Always Encrypted is a new method of consistent encryption intended to greatly simplify data protection. In-memory tables, while technically included in SQL 2014, were painful to implement and had significant limitations; SQL Server 2016 removes many of these and makes in-memory OLTP a viable option.

In-Memory OLTP

First, let’s look at how SQL Server’s handling of In-memory OLTP has grown.

SQL Server 2014: Microsoft’s First Attempt

In SQL Server 2014, Microsoft introduced memory-optimized tables into the SQL database engine. Data stored within these constructs exists in memory; hard disk access is indirect, via writes to the transaction log. Locking and latching mechanisms are entirely absent. Instead, an optimistic version control system is used. By eliminating OLTP disk activity, tremendous performance benefits – as much as 30x – can be seen.

Natively compiled stored procedures are used in conjunction with memory-optimized tables. This subtype of stored procedure can only access in-memory objects; they are designed for maximum query efficiency, and are compiled at the time of creation.

Despite the potential, memory-optimized tables suffered from severe limitations in SQL Server 2014. Among the most notable restrictions:

  • No support for LOBS (Nvarchar(MAX), Varchar(MAX), or Varbinary(MAX)
  • No indexes permitted on nullable columns
  • No support for TDE (Transparent Data Encryption)
  • Only BIN2* collations were supported
  • Significant limits to query keywords available in natively-compiled stored procedures; the subqueries OR, NOT, DISTINCT, and OUTER JOIN were all unavailable

SQL Server 2016: A Viable Option

In SQL Server 2016, the above limitations have been lifted. However, there is still a laundry list of unsupported T-SQL commands. Additionally, memory-optimized tables are specific to the SQL Server Enterprise edition. High-availability features are limited – mirroring is not supported, and replication has specific restrictions.

Despite the remaining limitations, SQL Server 2016’s performance benefits may outweigh the negatives. Implementing memory-optimized tables is similar to standard disk-based tables, but with some additional preparation:

  1. A memory-optimized filegroup is required to house the objects.
  2. The table must be created with MEMORY_OPTIMIZED = ON.
  3. The table must contain a non-clustered primary key.
  4. Durability must be considered, as it is set at creation time.

Memory-optimized tables have two object-level durability options: SCHEMA_ONLY and SCHEMA_AND_DATA. SCHEMA_ONLY preserves the structure of the table, but not the data; data is lost on a service restart. SCHEMA_ONLY tables do not have logged transactions and are only appropriate for transient data. SCHEMA_AND_DATA potentially provides the same level of protection as disk-based tables. Durability options can be configured at various levels.

Here are the steps for creating a memory-optimized table:

USE MemDB
GO

-- First, Add a Memory-optimized filegroup specifying MEMORY_OPTIMIZED_DATA
ALTER DATABASE MemDB ADD FILEGROUP MemDB_MemFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

-- Add a file to the new filegroup
ALTER DATABASE MemDB ADD FILE 
(name='MemDB_Mem', filename='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP3\MSSQL\DATA\MemDBWMem') 
TO FILEGROUP MemDB_MemFileGroup
GO

-- Create a durable memory-optimized table.  Requires a Nonclustered Primary Key
CREATE TABLE MyInMemoryTable (MemID INT IDENTITY PRIMARY KEY NONCLUSTERED, MemChar Char(1))
WITH (MEMORY_OPTIMIZED = ON,
          DURABILITY = SCHEMA_AND_DATA)
GO

Memory-optimized tables are a powerful technology, and they’ve come a long way since SQL Server 2014. There are still limitations, but the potential performance improvements are too significant to ignore when designing new SQL-based systems.

In Vertabelo, the in-memory options can be specified under Additional properties:

The memory tables setup can be done in the Additional SQL scripts section of the Model properties panel.

If you’d like to see how the final result looks like, here you’ve got our sample in-memory table. To check its properties, click the “Edit model” button.




The generated SQL script for the above model contains the in-memory tables setup:

-- Created by Vertabelo (http://vertabelo.com)
-- Last modification date: 2016-03-22 09:31:43.399


USE MemDB
GO

-- First, Add a Memory-optimized filegroup specifying MEMORY_OPTIMIZED_DATA
ALTER DATABASE MemDB ADD FILEGROUP MemDB_MemFileGroup CONTAINS MEMORY_OPTIMIZED_DATA
GO

-- Add a file to the new filegroup
ALTER DATABASE MemDB ADD FILE 
(name='MemDB_Mem', filename='C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016CTP3\MSSQL\DATA\MemDBWMem') 
TO FILEGROUP MemDB_MemFileGroup
GO


-- tables
-- Table: InMemoryTable
CREATE TABLE InMemoryTable (
    MemID int  NOT NULL,
    MemChar char(1)  NOT NULL,
    CONSTRAINT InMemoryTable_pk PRIMARY KEY NONCLUSTERED (MemID)
)
WITH ((MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA))
;


-- End of file.

Always Encrypted

New to SQL Server 2016, Always Encrypted is a reshaping of the way encryption is handled by SQL Server.

SQL Server Encryption Before 2016

In older versions, DBAs were responsible for data system management, but they could also view the underlying data. Since at-rest encryption configuration was handled entirely by SQL Server, it was unrealistic to prevent a DBA from accessing it – particularly if he or she had access to the encryption keys or source code containing the pertinent information. (At-rest encryption is the encrypting of data when it’s being stored – Transparent Data Encryption and column-level encryption are SQL Server examples.)

In-transit encryption was handled at the protocol level by using certificates. In-transit encryption is the encrypting of data is it moves across the network or Internet.

SQL Server Encryption Using Always Encrypted

Always Encrypted separates the roles of data management and ownership by shifting control of encryption to a client-based Always Encrypted driver. This driver handles encryption and decryption, passing only encrypted data to the SQL Server engine. The database engine never sees unencrypted data, and data in transit is always protected as well.

The driver is currently accessed by way of Microsoft .NET framework (4.6). Calls to Always Encrypted columns in SQL Server require this middle tier, as it is responsible for encryption/decryption operations. Access via ODBC is available as of SQL Server 2016 CTP 3.4, and JDBC is available in all versions of SQL Server 2016 CTP.

Configuring Always Encrypted starts by initiating the encryption wizard in SSMS:

After starting the wizard, you’ll select which columns are to be encrypted, and the encryption type. There are two types: deterministic and randomized.

Deterministic encryption will always generate the same encrypted value based on a plaintext value. This type still allows filtering (equivalence only), grouping, and joining on the encrypted columns.

Randomized encryption is less predictable – similar to salting. Filtering, grouping, and joining are unavailable.

If this is the first time initializing Always Encrypted on the SQL instance, the wizard will also create keys at this time. Column master keys are stored in the local key store via certificate. Column encryption keys are stored in the database system catalog views.

Once the column is encrypted, reads and writes must be performed from an ADO.NET application in accordance with the Always Encrypted architecture. Attempting to insert data directly from SSMS will result in an error message similar to the one shown below:

Msg 206, Level 16, State 2, Line 25
Operand type clash: varchar is incompatible with varchar(8000) encrypted
with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name =
'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK_Auto1',
column_encryption_key_database_name = 'MemDB') collation_name =
'SQL_Latin1_General_CP1_CI_AS'

To enable an application on another computer to access the encrypted columns, the column master key must be exported first. During the initialization of Always Encrypted, a certificate was created in the local cert store. This can be exported to the system running the .NET application. Once the export is completed, the calling application will be able to decrypt the stored data.

Always Encrypted is intended as a complete solution to end-to-end encryption; it should be used in any situation where sensitive data is stored and transmitted in a SQL Server environment.

 
 

Try our online database modeler. No registration. No commitments.

 
 
Tags
 
 
Subscribe to our newsletter

If you find this article useful, join our weekly newsletter to be notified about the latest posts.

 
 
 
New SQL Course! Find out how to identify all the factors of a successful visualization: think like your audience, find the right chart type, and take out the meaning of the data. View course Discover our other courses: