Azure SQL Data Warehouse supports SSMS, higher DWUs and additional T-SQL features

14 Temmuz, 2016 tarihinde gönderildi

Program Manager, SQL Engineering

We’re excited to announce the General Availability of Azure SQL Data Warehouse! This announcement also comes with a set of new enhancements delivering richer performance, tool and monitoring experiences, along with several new T-SQL features:

  • SQL Server Management Studio (SSMS) built-in support
  • Additional data protection with geographically redundant backups
  • New high-end compute offerings DWU3000 and DWU6000
  • Improved performance with database compatibility level 130
  • Thousands of new database level collations
  • Expanded analytic functions and window frame
  • The ability to rename data warehouses

To learn more about Azure SQL Data Warehouse, visit our Overview page or just create a new database.

SQL Server Management Studio is now supported

One of the top feature requests since entering preview for SQL Data Warehouse has been to add support for SQL Server Management Studio (SSMS). We are pleased to announce support of SSMS. You can now use SSMS to connect to SQL Data Warehouse with all supported authentication methods, execute queries, browse the objects in your database with the object explorer as well as script objects like tables, views and functions. Templates can be used to create new objects and the Generate Scripts feature can be used to script your entire database. You can download the July 2016 release of SSMS from here.

Geo-Restore for SQL Data Warehouse

Geo-Backups support has been added to enable geo-resiliency of your data and is on by default. If you prefer not to have geo-redundancy of your backups, you will need to opt out of this feature. With this feature enabled, backups are available even in the case of a region-wide failure. This feature allows SQL Data Warehouse Geo-Backup to be restored to any region in Azure.

Elasticity enhancements

Allocate more processing power to your SQL Data Warehouse than ever before. With the option to scale data warehouse from a DW100 to a DW6000, you can improve performance by 60 times in just seconds by simply allocating more DWUs. Additionally, when you pause or scale, operations are logged to sys.dm_operation_status. This DMV can be used to monitor and view history of pause, resume and scale operations.

SELECT major_resource_id AS database_name, state_desc AS staus, percent_complete from sys.dm_operation_status 
WHERE major_resource_id = 'AdventureWorks2016'
AND operation = 'PAUSE DATABASE' 
--ALTER DATABASE for scale and --RESUME DATABASE for resume

Compatibility Level 130 upgrade

All new data warehouses are now running at compatibility level 130. This ensures the latest benefits from compatibility level 130 can be leveraged allowing performance improvements with the service.

Database collation support

SQL Data Warehouse has added support for over 3,800 database collations. Collations provide the locale, code page, sort order and character sensitivity rules for character-based data types. Once chosen, all columns and expressions requiring collation information inherit the chosen collation from the database setting. The default inheritance can be overridden by explicitly stating a different collation for a character-based data type.

To view collations available, choose one of the following methods:

To see the database collation use the DATABASEPROPERTYEX() function:

SELECT DATABASEPROPERTYEX('AdventureWorks2016', 'Collation') AS Collation;

With this change, the default for all new databases will be SQL_Latin1_General_CP1_CI_AS.

Support for analytic functions and window frame

Support for additional analytic functions and the ability to specify the window frame with ROWS or RANGE clause further bridges the T-SQL gap with SQL Server. Added support includes the following analytical functions: FIRST_VALUE, LAST_VALUE, CUME_DIST, PERCENT_RANK. In addition, one can use ROWS or RANGE inside the OVER clause with all the permitted usages of UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING and BETWEEN.

Here is an example query using the above analytic functions and window frame qualification on the sample database that comes with the SQL Data Warehouse (dbo schema):

SELECT EnglishProductName, ListPrice,
            FIRST_VALUE(EnglishProductName) OVER (ORDER BY ListPrice ASC) AS LeastExpensive,
            CUME_DIST() OVER (ORDER BY ListPrice ASC) AS CumeDist,
            PERCENT_RANK() OVER (ORDER BY ListPrice) AS PctRank                    
FROM dbo.DimProduct
WHERE ProductSubcategoryKey = 37;

Database rename

To change the database name, you can now use T-SQL to accomplish this. Running the ALTER DATABASE command below will kill all existing transactions and must be in the master database to succeed.

ALTER DATABASE CurrentDatabasename MODIFY NAME = NewDatabaseName;

We want your feedback 

In our feature planning we want to give priority to features that our users want most. We would love to hear from you on what features you would like us to add. Please let us know on our feedback site what features you want most. Users who suggest or vote for feedback will receive periodic updates on their request and will be the first to know when the feature is released. Another way to give us feedback is our feedback survey.

Learn more

Please check out the many resources for learning more about SQL Data Warehouse. Here are a few: