What's New in SQL Server 2016, February Update

What's New in SQL Server 2016, February Update

 

Updated: January 29, 2016

Applies To: SQL Server 2016 Preview

needhelp Need help?  MSDN forum  |  stackoverflow  |  Log an issue or suggestion at Microsoft Connect

This topic summarizes the new features in the current monthly build of SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2). The topic also contains links to more detailed component level what's new information for the current month and previous monthly builds.

Download

» To download SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2), go to Evaluation Center.

» Have an Azure account? Then go Here to spin up a Virtual Machine with SQL Server 2016 already installed.

note For the current release notes, see SQL Server 2016 Release Notes.

Statistics are used to optimize queries that reference memory-optimized tables. In the latest update, the mechanisms that gather those statistics are improved. Statistics can now be updated automatically, and sampled statistics are supported for dealing with voluminous data more efficiently.

In the latest update, natively compiled modules have better support for large object (LOB) types, such as for nvarchar(max). Also, in natively compiled stored procedures, the OUTPUT clause can now be used with INSERT, UPDATE, and DELETE statements.

Stretch Database. Stretch Database now includes the following features and enhancements.

  • You can now add, alter, and drop columns on Stretch-enabled tables.

  • You can create, alter, and drop indexes on Stretch-enabled tables.

  • When you use the Enable Database for Stretch Wizard to create the remote Azure SQL Database, the wizard creates a database with a default 10 TB of storage, with the option to expand the database up to 60 TB.

  • You can now enable Transparent Data Encryption (TDE) on the remote Azure SQL Database if TDE is enabled on the SQL Server database. For more info, see Transparent Data Encryption (TDE) and Transparent Data Encryption with Azure SQL Database

  • The remote Azure SQL Database used by Stretch Database now supports geo-restore. Geo-restore provides the ability to restore a database from a geo-redundant backup to create a new database in any supported Azure region. Geo-restore works in the same way for Stretch Database and for Azure SQL Database. For more info, see Azure SQL Database Geo-Restore.

  • Stretch Database now provides full support for point in time restore. After you restore your SQL Server database to a point in time and reauthorize the connection to the remote Azure SQL Database, Stretch Database reconciles the remote data to the same point in time. For more info about point in time restore in SQL Server, see Restore a SQL Server Database to a Point in Time (Full Recovery Model). For info about the stored procedure that you have to run after a restore to reauthorize the connection to the remote SQL Database, see sys.sp_rda_reauthorize_db (Transact-SQL).

  • The option to use a federated service account for SQL Server to communicate with the remote Azure SQL Database server when certain conditions are true is now available in the Enable Database for Stretch wizard. In CTP 3.3 this feature still requires a trace flag. For more info, see Enable Database for Stretch Wizard.

  • The option to specify a predicate no longer requires a trace flag. For more info, see Enable Stretch Database for a table, Write an Inline Table-Valued Function to Select Rows to Migrate (Stretch Database), and ALTER TABLE (Transact-SQL).

  • In SQL Server Management Studio, when you disable Stretch Database at the table level, you can now choose whether to copy the remote data back to SQL Server or to abandon the remote data. For more info, see Disable Stretch Database and bring back remote data.

  • The option to unmigrate the data that Stretch Database has migrated to Azure no longer requires a trace flag. For more info, see Unmigrate remote data (Stretch Database).

  • The Enable Database for Stretch Wizard includes many improvements. Here are some highlights.

    • On the Select database tables page, the wizard identifies tables that have blocking issues. You can search for a table and sort the list of tables by various columns. You can select temporal history tables.

    • On the Configure Azure deployment page, the wizard supports both SQL Server and Azure Active Directory authentication. You can optionally use a federated service account for SQL Server to communicate with the remote Azure SQL Database server when certain conditions are met.

    • On the Enable network settings page, the wizard detects the IP and subnet range of the SQL Server.

    • On the Results page, the wizard provides info about Azure costs and pricing.

 Foreign Key Relationship Limits - A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references). SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2) increases the limit for the number of other table and columns that can reference columns in a single table (incoming references), from 253 to 10,000. For restrictions, see Create Foreign Key Relationships.

SERVERPROPERTY - Eight new properties are added to SERVERPROPERTY (Transact-SQL): InstanceDefaultDataPath, InstanceDefaultLogPath, ProductBuild, ProductBuildType, ProductMajorVersion, ProductMinorVersion, ProductUpdateLevel, and ProductUpdateReference.

For more detailed what's new information and information on previous months, see What's New in Database Engine.

The input length limit of 8,000 bytes for the HASHBYTES (Transact-SQL) function is removed.

For more detailed what's new information and information on previous months, see What's New in Analysis Services.

SSIS already supports the Always Encrypted feature in SQL Server. For more info, see the following blog posts.

The Microsoft® Change Data Capture Designer and Service for Oracle by Attunity for Microsoft SQL Server® 2016 have been released as part of the SQL Server 2016 Feature Pack. To download components of the Feature Pack, see Microsoft® SQL Server® 2016 Community Technology Preview 3.3 (CTP3.3) Feature Pack.

The Microsoft® Connector for SAP BW for Microsoft SQL Server® 2016 has been released as part of the SQL Server 2016 Feature Pack. To download components of the Feature Pack, see Microsoft® SQL Server® 2016 Community Technology Preview 3.3 (CTP3.3) Feature Pack.

The following components have been updated to provide full compatibility with SQL Server 2016. There is no new functionality and there are no changes in behavior.

  • ODBC Source and Destination

  • CDC (Change Data Capture) Control Task, Source, and Splitter Transformation

The Analysis Services Execute DDL Task has been updated to accept Tabular Model Scripting Language commands.

The Excel Connection Manager, the Excel Source, and the Excel Destination now provide explicit support for Excel 2016 data sources.

The method GetIdentificationStringByLineageID has been renamed to GetIdentificationStringByID Method. For more info, see Column names for errors in the data flow.

For more detailed what's new information and information on previous months, see What's New in Integration Services.

Save or Open Query Files in MDS Add-in for Excel

From the Entity Explorer page, you can click Excel to save the shortcut query files. Or you can open the query file stored on your computer, in the MDS Add-in for Excel. The saved file can be opened using QueryOpener application. For more information, see Shortcut Query Files (MDS Add-in for Excel).

The query file contains the filters and hierarchy information from the explorer page.

Business Rule Management Experience Redesigned

The business rule management experience in MDS has been completely redesigned to improve the experience. For more information about this feature, see Business Rules (Master Data Services).

Business Rule Management Functionality Removed from the MDS Add-in for Excel Removed

Business Rule management functionality has been removed from the MDS Add-in for Excel because we redesigned the experience.

For more detailed what's information on previous months, see What's New in Master Data Services (MDS).

For more detailed what's new information and information on previous months, see What's New in Reporting Services (SSRS).

What information are you looking for, and did you find it? We’re listening to your feedback to improve the content. Please submit your comments to sqlfeedback@microsoft.com

For more information about SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2), or to download the SQL Server 2016 Community Technology Preview 3.2 (CTP 3.2) data sheet, see SQL Server 2016 Preview.

Microsoft SQL Server 2016 data sheet

needhelp Need help?  MSDN forum  |  stackoverflow  |  Log an issue or suggestion at Microsoft Connect

Community Additions

ADD
Show:
© 2016 Microsoft