Daniel Calbimonte

The new SQL Data warehouse in Azure

March 8, 2016 by

Introduction

In previous chapters, we taught how to create SQL Databases in Azure. In this new chapter, we will show you the new SQL Data Warehouse Service in Azure.

The new SQL Data Warehouse is a Service provided by Microsoft Azure, where you can access to the dimensions and fact tables and you can create powerful reports with BI Power view (in Azure also). The Data Warehouse service is a columnar, petabyte-scale service.

Requirements

An Azure subscription already created.

Getting started

In this new article, we will create a new SQL Data Warehouse in Azure and show some properties. In future articles, we will talk about creating report and connecting to the data.

  1. In the Azure Portal, press the New option and select Data+Storage➜SQL Data Warehouse.


    Figure 1. The new SQL Data Warehouse

  2. Specify the SQL Data Warehouse new name. Make sure that the name is unique.


    Figure 2. The SQL Data Warehouse name.

  3. In the performance section, you will have the DWU and the price per hour. When you increase the DWUs, the price is also raised. The minimum value is 100. The DWUs are Data Warehouse Units. The DWU is used to measure how fast the rows are scanned, loaded and copied. If you need more performance, you need to increase the DWUs. The prices depend on the Region. It is usually cheaper to have your Data Warehouse in US than in Europe. In Austria is the more expensive region followed by Brazil.

  4. Click on the Server section. You can create a new server or use an existing one (if any). In this example, we will create a new server.


    Figure 3. Performance and Server options

  5. Specify a server name, a login and a password. You can specify the location to store the server. The last version of SQL Server is V12.


    Figure 4. The Server information

  6. In the select source, select the Sample option. This option will allow creating a database based on a sample database.


    Figure 5. Select source

  7. Select the Sample option. This option will let you select an already created database.


    Figure 6. Adding the sample database.

  8. Select the AdventureWorksDW. This will create the Adventureworks Database, which is a fictitious company similar to the adventurewordsDW used in Analysis Services. This is a classic database with several tables and views used for testing purposes. You could also create a blank database or load from a backup. Once selected, press the create button to create the database.


    Figure 7. The AdventureWorksDW

  9. In the menu, you will receive a message that the Database creation is in progress.


    Figure 8. Creating the SQL Data Warehouse

  10. Once created, in the SQL databases of the Azure Portal, click on the Data Warehouse database created. As you can see the normal SQL databases and the SQL Data Warehouse databases are in the same place, but the icons are different.


    Figure 9. The SQL databases

  11. You have the option to configure the settings, you can pause the database. The scale option (DWUs used) can be changed. You can open the database in Visual Studio or create reports using PowerBI. Here you have the options to restore the database or delete the database. You will be able to see the Group, location, subscription, server name, connection strings and a graphic of the query activity.


    Figure 10. The status and Data

  12. The audit logs store the errors, warnings and informational messages. If the error is critical, the category of the message is critical. The color for a critical error is red, an error is orange, a warning is yellow and an informational message is blue. It is possible to filter errors or hide the charts.


    Figure 11. The log messages.

  13. The check health shows the status of the database. If there is nobody connected, you will receive a message similar to the following:


    Figure 12. Check health

  14. If you connect to the database. You will receive a message of the status of the database. If you have problems, you have the option to troubleshoot or submit a ticket with a support request.


    Figure 13. The database health status.

  15. The troubleshot option will help you with common problems. It will give you links of pages with solution to your problems like slow queries or connectivity problems.


    Figure 14. Troubleshooting options

  16. The Troubleshooting pages will give you diferent possible solutions to your problem.


    Figure 15. The troubleshooting document

  17. If the troubleshooting option did not solve your problem, you can send a support request. This option will let you contact with someone from tech support from Microsoft. Sending s support request has 3 steps. The first one is to write the type of request. The second option is to describe the problem and finally you will send the contact information.


    Figure 16. Support request

  18. In the properties, you can see the DWU units used, the status of the database, the collection and the Max Size for the database.


    Figure 17. SQL Data Warehouse Properties

  19. The connection string will show the code required to connect to the data warehouse database using ADO.net, ODBC, PHP, JDBC connection.


    Figure 18. String connections

  20. In the properties, you can also see the server name, the location and the login. You can also see the group where it is located and the subscription information.


    Figure 19. More properties

  21. The scale option is one of the most important options. If you pay more, you have a better performance in few seconds. In the scale section, you can increase or decrease de DWU. For example, I may want to have 500 DWUs during peak hours and 200 DWUs during the rest of the day. I could configure the Task Scheduler of my operative system to run a PowerShell script to change it automatically. The minimum values is 100 DWUs and currently the maximum value is 2000 DWUs (at 0.7 and 14 USD per hour respectably).


    Figure 20. The Scale option

  22. The audit and threat detection properties are inherited from the server. If you discard the default option, you can configure your settings.


    Figure 21. Auditing settings.

  23. The transparent encryption allows you to encrypt your data in a secure way. By default, the data is not encrypted.


    Figure 22. Transparent data encryption

Conclusions

But as you can see, creating a new SQL Data Warehouse is a straightforward process. In future articles we will talk about creating reports and working with the data.

References

For more information about SQL Data Warehouse, refer to these links


Daniel Calbimonte

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams.

View all posts by Daniel Calbimonte
Daniel Calbimonte
SQL Azure

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He is also a writer for SQL Server training material for certification exams. View all posts by Daniel Calbimonte

973 Views