Faster, Safer, Mobile

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.

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.

Business Intelligence, R, and Big Data

For data analysis, SQL Server 2016 can access different data sources. In addition to traditional relational databases, you can query non-relational databases for analysis and reports with Transact-SQL (T-SQL). T-SQL also easily integrates Hadoop and queries Hadoop data. In this way, you can create data lakes in which all your data is stored. Power Query for Excel is also a good option for analyzing and generating reports. Here again, the requested data can be obtained from different sources and be integrated in different data models. Therefore, if you have created integration projects using Power Query, you can continue to use them in the integration services.

Those who use T-SQL or applications that rely on T-SQL can continue to use SQL Server 2016 and, thanks to the Hadoop connection, receive much more data that can continue to be processed efficiently by SQL tools. BI analyses can also be performed on the go in SQL Server 2016. The new server particularly supports Windows Mobile 10, but apps are also available for Android and iOS devices.

In addition to access via smartphones and tablets, you can evaluate data from a browser. In this case, communication between the end device and the SQL server occurs via HMTL5. You can create dashboards and, once created and saved, they are accessible on all end devices and can be touch optimized. The ability to optimize dashboards for various device classes is an advantage of the new environment, allowing you to create analyses and dashboards on a desktop and then process them using a tablet or smartphone on the go.

Microsoft has integrated the R language in SQL Server 2016 for data analysis with in-database R Services. You can use existing R models or download R scripts via the Azure Marketplace [1] and run them in SQL Server 2016. You can also offer your own R scripts in this way. If scientists at your company analyze their data with R, you can provide them with corresponding interfaces in which they can continue to use the data with SQL tools.

Master Data Services (MDS) have also been updated. Power was increased, according to Microsoft, and permissions can be assigned in greater detail. You can also specify various archiving options for transaction logs in the new version and use entities in MDS on different models. Finally, SQL Server 2016 now supports native JSON as a data format. This means JSON files can be created directly from databases and continue to be used in other applications.

Power BI Analyses

Power BI [2] provides a newly developed analysis platform that lets you create your own dashboards without the need for an experienced developer (Figure 2). The cloud service supports the use of data both on the cloud or with Azure, and local SQL servers can be connected. Power BI allows real-time data analysis. Using agents and connectors, businesses can easily make databases, enterprise resource planning (ERP) systems, and other server solutions accessible on the internal network. SQL Server 2016 provides an advantage in this case, because the new version provides good support for Power BI, particularly with regard to data encryption and an improved connection to Azure that makes the cloud more secure and more efficient. SQL Server 2016 itself, however, does not have any Power BI functions.

Even without Power BI, the SQL Server 2016 Reporting Services and Analysis Services provide comprehensive analysis functions. Microsoft has improved performance and support for common browsers. To create an optimal web interface for Reporting Services, you can also use CSS stylesheets that allow visually appealing websites. Applications that access the reporting services can be reused, and Reporting Services can even access local data from SQL servers or data in Microsoft Azure.

Local and Cloud Tables

As with previous versions of SQL Server, Microsoft has further improved cooperation with Microsoft Azure. The focus of the new SQL version is on companies that use hybrid cloud environments – that is, companies that want to operate local database servers and cloud databases or database servers. The wizard that effects the cooperation between local servers and the cloud was enhanced and simplified. Only a few steps are required to migrate SQL databases into the cloud. In SQL Server 2016, you can run tables locally and in the cloud, and you can expand local tables into the cloud on servers running SQL Server 2016. The table is stored locally and in the cloud and can be accessed from local applications. This is particularly useful for data that is not needed continuously and for which high performance is not required. The use of tables in Azure is transparent for applications; no adjustments are required.

Conclusions

With SQL Server 2016, Microsoft is taking another step toward the cloud and mobility. In the future, databases are to be used not just as storage for locally run business applications, but also for use on multiple devices worldwide. In this way, the databases can be used more efficiently and can create analyses more quickly. Because both performance and security are improved, companies should take a look at the new version. Connecting to the cloud is optional; SQL Server 2016 can generally be run as a local database server without problem – like its predecessors, just more secure and faster.