A fairly stable Community Technology Preview version 2.2 of SQL Server 2016 has been available since July 2015. With the newest release, Microsoft wants to optimize the features that have already been on board since SQL Server 2014. Microsoft places great emphasis on providing databases quickly and efficiently for analysis. This includes in-memory data processing. In this process, SQL Server stores frequently used tables directly in the working memory to provide faster access. It should also be possible to process real-time data better and faster in the future.

In addition to real-time processing, Microsoft has also worked on availability of data and expanded the business intelligence (BI) capabilities accordingly. If you have been using a BI system based on SQL Server up to now, it usually only received its data for processing at certain times – often just once a day. For example, if you use the BI system to analyze payment transactions, the analyses could only be carried out previously if the data was transferred to the server. However, in SQL Server 2016, you can access the source data from the data warehouse in real time using in-memory Columnstore and in-memory online transactional processing (OLTP), allowing you to analyze the data in the BI system in real time. This application is just one example of many.

Generally, with SQL Server 2016, Microsoft wants a massive speedup in the analysis of real-time data and transactions and to make it possible for many applications to store data in-memory. Applications that rely on this database (e.g., to analyze data) use these features transparently. The analysis applications themselves therefore don't need to support the real-time analysis process, because the underlying database manages the technology itself. The application just needs to access the processed data. SQL Server 2016 also monitors and optimizes queries via the Query Store and can store them for later use.

SQL Server 2016 is optimized for use with Windows Server 2016 and its new features. Particularly interesting in this regard is the support of 12TB of memory. SQL Server 2016 can cope better with CPU cores and manage database caches more efficiently, allowing you to work in multiprocessor systems in each instance with multiple temporary databases for data caching. Microsoft promises significant performance improvement in this regard because all temporary databases can be processed in parallel.

Data Encryption During Processing

With SQL Server 2016, Microsoft aims to increase data security. In previous versions, information was only encrypted on the hard disk; with SQL Server 2016, you also have the option of encrypting data during use. This is a really important safety aspect when connecting to the cloud – especially with Microsoft Azure. Security also plays an important role when using in-memory technologies: Data can now be encrypted on the fly without a performance penalty.

In-memory tables based on OLTP support transparent data encryption (TDE), and stored tables are also encrypted in the working memory. If you use encrypted data, for example, with local SQL servers and replicate the data on the cloud via the Internet, the data is not decrypted at any point. The data remains protected at all times – from storage, to replication, and transfer to the cloud. The applications that access the encrypted data receive the access key, which remains saved in the application; even administrators do not have access to it. The data can only be read in the application itself.

If information in tables changes, SQL Server 2016 can store versions of the modified data. Like SharePoint, versions can now also be created for databases and tables and the changes tracked. The overwritten data ends up in temporal tables that are also available for queries. Unlike temporary tables, temporal tables are available after rebooting, thus storing a history of your data (Figure 1). Overwritten data can also be used in reports and for analysis. This ability is interesting, for example, if the names of certain products change and you want to create reports that use both the old and new names.

Figure 1: SQL Server 2016 can create versions for tables and thus also store history data.

Restricted Requests and Data Masking

In addition to the comprehensive encryption options, SQL Server 2016 provides further innovations in terms of security. You can now define filters in the databases and determine whether users can view certain data or not. You can use this function to prevent users from reading individual data from databases that they may in principle be authorized to access, but whose detailed content they are not allowed to know. The filters can be set up on the basis of usernames in Active Directory or Azure Active Directory.

With dynamic data masking, on the other hand, you can specify that certain content is not sent at all. If, for example, developers write an application that has access to sensitive data, such as personnel, credit card, or bank account numbers, the requested data can be masked. Although the contents of the table are shown to the developers, they are either entirely or partially masked. Only users who log on to the corresponding application and have the right to access the data can see the data. This means that developers might be able to access databases and identify the stored information, but they cannot read any of the private data. To ascertain later which user has changed what, SQL Server 2016 can monitor and store all database operations, also with the use of filters, if necessary.

Improved High Availability

In addition to data protection, availability is also a crucial factor for database servers. In the new version, Microsoft has improved high availability with AlwaysOn and allows a greater number of replications. In SQL Server 2016, you can use up to three synchronous replicas for automatic failover, even across domains, whereas SQL Server 2014 only supports two synchronous replicas. The 2016 version also provides automatic failover mechanisms. If the server identifies a database that is no longer consistent, the server can activate replicas for production use. In the new version, SQL Server Integration Services (SSIS) supports AlwaysOn technology and can also work with the replicas. If you use multiple instances of SQL Server, you can also use the replicas for round robin load balancing.