Provision a MS SQL Data Warehouse Instance in Azure

Hands-On Lab

 

Photo of Brian  Roehm

Brian Roehm

Azure Training Architect I in Content

Length

01:00:00

Difficulty

Beginner

In this hands-on lab, we will learn how to provision an Azure SQL Data Warehouse (SQL DW) instance two different ways. First, we will learn how to provision a SQL DW instance using the Azure Portal. Next, we will provision an instance using PowerShell. We will also go over key setup options.

What are Hands-On Labs?

Hands-On Labs are scenario-based learning environments where learners can practice without consequences. Don't compromise a system or waste money on expensive downloads. Practice real-world skills without the real-world risk, no assembly required.

Provision a SQL Data Warehouse Instance in Azure

Introduction

In this hands-on lab, we will learn how to provision an Azure SQL Data Warehouse (SQL DW) instance two different ways. First, we will learn how to provision a SQL DW instance using the Azure Portal. Next, we will provision an instance using PowerShell. We will also go over key setup options.

Logging In to the Environment

  1. Install SQL Server Management Studio or Azure Data Studio using the links provided on the lab instructions page.
  2. On the main lab page, click the Open Azure Portal button.
  3. Enter the username that was provided for you in the Credentials section of the lab instructions page.
  4. Click Next.
  5. Enter the password that was provided for you in the Credentials section of the lab instructions page.
  6. Click Next.
  7. In the Welcome menu, click Maybe later.

Create a SQL Data Warehouse Instance with the Azure Portal

Create the SQL Data Warehouse Instance

  1. In the left sidebar of the Azure dashboard, click All resources.
  2. Click Create resources.
  3. In the Azure Marketplace menu, click Databases > SQL Data Warehouse.
  4. Click the Resource group field, and select the existing resource group from the dropdown menu.
  5. Next to Data warehouse name, enter a unique name (for example, "Test11").
  6. Under Server, click Create new.
  7. In the New server menu, configure the following settings:
    • Server name: (Enter a unique name.)
    • Server admin name: (Enter a unique name.)
    • Password: (Enter a unique password.)
    • Confirm password: (Re-enter the password from the previous field.)
    • Location: East US
  8. Click Select.
  9. Under Performance level, click Select performance level.
  10. Make sure Gen2 is selected.
  11. Under Scale your system, slide the scale to the left until the level reaches DW200c.
  12. Click Apply.
  13. Click Next : Additional settings >.
  14. Click Next : Tags >.
  15. Under NAME, type "testsql dw".
  16. Under VALUE, type "test".
  17. Click Next : Review + create >.
  18. Click Create.

Encrypt Data at Rest

  1. Once the instance has finished provisioning, click Transparent data encryption.
  2. Click ON, then Save.

Test the Connection

  1. Click Overview.
  2. Click the clipboard icon next to Server name to copy it to your clipboard.
  3. Open Azure Data Studio.
  4. Click the icon at the top left of the screen to open the Connection menu.
  5. Under Connection Details, configure the following:
    • Server: (Paste the server name we just copied to clipboard.)
    • User name: (Enter the user name we set up in Step 7.)
    • Password: (Enter the password we set up in Step 7.)
  6. Click Connect.
  7. In the Create new firewall rule menu, under Azure account, click Add an account.
  8. Copy the Website link to your clipboard, and paste it into a new tab of your browser.
  9. Go back to Azure Data Studio.
  10. Copy the User code, and enter it in the Enter code menu of the new browser tab we opened.
  11. Click Next.
  12. Click your account name from the list.
  13. In Azure Data Studio, click OK.

Provision a SQL Data Warehouse Instance Using PowerShell

  1. From the Azure dashboard, click the Cloud Shell icon at the top of the page.
  2. Click PowerShell.
  3. Under Subscription, click Show advanced settings.
  4. Under Storage account, select Create new, and enter a unique name in the field below.
  5. Under File share, select Create new, and enter a unique name in the field below.
  6. Under Cloud Shell region, select South Central US.
  7. Click Create storage.
  8. Check the Azure subscription.
    Get-AzSubscription
  9. Check the resource group.
    Get-AzResourceGroup
  10. Create a logical server.
    New-AzSqlServer -ResourceGroupName ` [RESOURCE_GROUP_NAME]` -ServerName [UNIQUE_SERVER_NAME] -Location southcentralus
  11. At the prompt, enter the admin username and password.
  12. Check the newly created server.
    Get-AzSqlServer
  13. Create the SQL data warehouse.
    New-AzSqlDatabase ` -ResourceGroupName [RESOURCE_GROUP_NAME] ` -DatabaseName [SQL_DATA_WAREHOUSE_NAME] -Edition "DataWarehouse" ` -RequestedServiceObjectiveName "DW200c" ` -CollationName "SQL_Latin1_General_CP1_CI_AS" ` -MaxSizeBytes 10995116277760
  14. Enter your server name at the prompt.

Conclusion

Congratulations, you've successfully completed this hands-on lab!