Introduction to SQL Server 2016 CTP2

SQL Server 2016 Community Technology Preview 2.2 (CTP2) has been released for evaluation and trial use by Microsoft. It features highly-anticipated improvements to in-memory performance to deliver faster queries from its database engine, deeper enterprise insights through analytics with its analysis services, new always-encrypted security technology, and new hybrid cloud support.

Microsoft’s new SQL Server 2016 prioritizes mission-critical functionalities through its improvement of in-memory performance, boasting transaction speeds 30x faster and querying relational databases 100x faster than traditional disk-based database network systems. The new database engine supports in-memory OLTP (OnLine Transaction Processing) to handle user requests and improve responsiveness by defining a heavily queried table as memory optimized. OLTP tables support columnstore indexes, temporal tables, queries to Transact-SQL or Hadoop with PolyBase, MARS (Multiple Active Result Set) connections, natively compiled stored procedures, and familiar SQL Server syntax to offer truly real-time analytics to the enterprise. Although there is no UI support on SQL Server’s CTP2 for SQL Server Management Studio, Managed Backup to Microsoft Azure will support entire system databases on both custom and automated backup schedules by utilizing block blob storage for backup files in a Stretch Database. CTP2 also supports JSON query formatting, to take advantage of the minimal joins and schema changes required as an application is updated. The CTP2 database engine now creates multiple tempdb database files to retain integrity of real data as developers and users alter it. The new Query Store holds and monitors existing query plans and includes reports for performance history, while Live Query Statistics allows administrators to view execution of a running query in real-time.

SQL Server 2016 Analysis Services (SSAS) offers decision support and business intelligence (BI) solutions from compiled analytical data of internal and third-party implemented reporting tools for enterprise. New features like support of parallel processing of multidimensional databases and tabular modeled databases, configuration of computer accounts for administrators, a Database Consistency Checker (DBCC) that checks for both physical and structural corruption, and Power Pivot for running Excel data models in SharePoint, all come together to create a reliable data source for analysis and reporting. The SQL Server 2016 Reporting Services (SSRS) allows administrators to create, manage, and deploy reports to subscribed members. Reports can be compiled from relational, multidimensional, or XML-based data sources and visualized in high-DPI as charts, maps, and other custom graphical representations. SSRS supports browsers and has native applications for Windows, iOS and Android to increase accessibility.

SQL Server 2016 boasts Always Encrypted technology to protect sensitive enterprise data in the secure database while it is maintained by database administrators. Master keys seemingly manage themselves as they handle the process of both encryption and decryption of data from the driver level in an effort to minimize data compromise and ease the implementation of changes to existing database networks. In the database engine, row-level security can be utilized from its access control to manage access to data based on user metadata, while dynamic data masking is a new, policy-based, security feature that masks data queried across designated database fields from users that lack proper permissions.

SQL Server, Microsoft Azure, and the new Hybrid Cloud offer portability for enterprises looking to move into the cloud with the ability to continue to run code and house data on-premises with cloud access, or run entirely in the cloud. Azure’s Stretch Database holds operational tables in a secure environment to maintain historic data. PowerBI with on-premises data is a new interactive query that utilizes SQL Server’s Analysis Services coupled with their Always Encrypted technology. Hybrid Scenarios produces impact analysis with support to SSIS standalone or integration with Azure Data Factory. Managed Backup to Azure’s cloud service proves to reduce in-house data storage by 50% and supports larger databases with block blobs. Migration of on-premises SQL Server is as simple as point-and-click to Azure. Hybrid Cloud also offers AlwaysOn, which creates database replicas to support load-balancing.

These new functionalities in SQL Server 2016 CTP2 is just a preview of what can be utilized by enterprises for software performance optimization of database design and management, analytics, reporting, and cloud access. The final release of SQL Server 2016 will be even further optimized to meet unique business needs!