February has been a short, but exciting month for SQL Data Warehouse. You might have noticed improvements to stability, reliability, and scalability, as well as some of the customer-driven functionality listed below.

TSQL for implementing SSMS

One of the top requests we’ve received is the desire to use SQL Server Management Studio (SSMS) to query and manage SQL Data Warehouse. We are working hard to add support for SSMS, and in February we took some significant steps towards this. We enabled the following TSQL functions as part of SSMS support:

• SET NOCOUNT ON|OFF: Sets whether or not a message is returned with a count of the rows affected by a TSQL operation
• SET PARSEONLY ON|OFF: Checks code for errors without running or compiling it
• IS_MEMBER() and IS_ROLEMEMBER(): Queries to see if a database user is a member or a certain role/group
• COLLATIONPROPERTY: Returns the property of a specified collation in SQL Server 2016
• @@MICROSOFTVERSION: Returns the Microsoft Version of a SQL Database or SQL Data Warehouse

PolyBase public data sets

In addition to loading data from your personal storage accounts, PolyBase can now connect to and query public Azure blob storage containers without the need to specify a key or credential to access the data. This allows you to read large public data sets into SQL Data Warehouse quickly. For example, you could load a large amount of data for a trial. To access public data, simply create an external data source that points to the storage without specifying a credential. The update syntax for external data sources can be found here.

Self-service Geo-Restore

You will now be able to restore a SQL Data Warehouse from any of the geo-redundant backups automatically taken by the service. Data warehouses can be restored into any Azure region, and on any server. Since SQL Data Warehouse uses geo-redundant backups, you can recover a database even if the database is inaccessible due to an outage. This adds an additional layer of availability and reliability.

This operation was previously available, but recent updates make the process easy and widely available through the Azure Portal, PowerShell or REST APIs.

DWU Limit and DWU Used measures

We're often asked: How do you compare DWU to your workload? What’s the right amount of DWU to use?

We now have two new metrics (DWU Limit and DWU Used) you can use in the Azure portal to monitor DWU for your workload. DWU Limit and DWU Used are designed to help you understand your usage so you can better size your data warehouse and plan for future growth. The two metrics are setup as follows:

• DWU Limit: This metric shows how many DWU are currently assigned to your data warehouse. This is the maximum amount of DWU your workload can use at any time. You can increase or decrease the limit by scaling up or down.
• DWU Used/DWU Percentage: These metrics show the amount of DWU that their currently running workload is consuming. Since the two metrics driving DWU are IO and CPU usage, we use a special combination of these two measures taking into account all of the nodes of the service.

These measures are both exposed in the metrics section and the main page of SQL Data Warehouse as shown below. In the below workload you can see that 1000 DWU is provisioned, but the data warehouse is only using a little over 300 DWU on average. However, there are points within the past week where the workload needed the full power of the instance. Given this for your own workload, you can think about times you may want to scale up or down to minimize costs and better tune your data warehouse.

Documentation Updates

The SQL Data Warehouse documentation is now more clear and complete than ever before. The service team has migrated 600+ reference topics to MSDN and made numerous updates to our online documentation. The new “APPLIES TO” marker makes it easy to compare and contrast syntax for SQL Database, SQL Server and SQL Data Warehouse. As you can see below, the CREATE COLUMNSTORE INDEX applies to multiple products, whereas the SQL Data Warehouse has a separate CREATE DATABASE topic.

 

 

If you would like to contribute to how we improve SQL Data Warehouse in the future, submit suggestions for features or complete our feedback survey.