Skip to main content

Provision a MS SQL Database 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 practice provisioning an Azure SQL Database (SQL DB) instance two ways. First, we will provision a SQL DB instance using the Azure Portal. Next, we will provision an instance using PowerShell.

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 Database Instance

Introduction

In this hands-on lab, we will practice provisioning an Azure SQL Database (SQL DB) instance two ways. First, we will provision a SQL DB instance using the Azure Portal. Next, we will provision a SQL DB instance using PowerShell.

Log 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 Database Instance Using the Azure Portal

Create a SQL Database

  1. In the search bar at the top of the page, enter "SQL database".
  2. Select SQL databases from the dropdown.
  3. Click Create sql database.
  4. In the Create SQL Database menu, configure the following settings:
    • Resource group: (Select the existing resource group from the dropdown)
    • Database name: testsqldb
  5. Under Server, click Create new.
  6. In the New server menu, configure the following settings:
    • Server name: (Enter a unique name, e.g., "test1552".)
    • Server admin login: (Enter a unique name, e.g., "admin1552".)
    • Password: (Enter a unique password, e.g., "Test123456".)
    • Confirm password: (Enter a unique password, e.g., "Test123456".)
    • Location: East US
  7. Click Select.
  8. For Want to use SQL elastic pool?, select No.
  9. Under Compute + storage, click Configure database.
  10. Click the Looking for basic, standard, premium? tab.
  11. Click the Standard tab.
  12. Make sure the DTUs slider is set to 10.
  13. Set the Data max size slider to 50 GB.
  14. Click Apply.
  15. Click Next : Additional Settings >.
  16. Under DATA SOURCE, click Sample.
  17. Click Next : Tags >.
  18. Under NAME, type "testsql".
  19. Under VALUE, type "test".
  20. Click Next : Review + create >.
  21. Click Create.
  22. Click the bell icon at the top of the page to open the Notifications menu.
  23. Click Deployment in progress to view the status of the deployment.

Configure Geo-Replication

  1. Click All resources in the left sidebar.
  2. Click the name of the SQL database we created to open it.
  3. Under Settings, click Geo-Replication.
  4. Under TARGET REGIONS, click West US.
  5. In the Create secondary menu, click Target server.
  6. In the New server menu, configure the following settings:
    • Server name: (Enter a unique name, e.g., "test124576".)
    • Server admin login: (Enter a unique name, e.g., "admin124576".)
    • Password: (Enter a unique password, e.g., "Test123456".)
    • Confirm password: (Enter a unique password, e.g., "Test123456".)
  7. Click Select.
  8. Click Pricing tier.
  9. Leave the settings as their defaults, and click Apply.
  10. Click OK.

Encrypt Data at Rest

  1. Under Security, click Transparent data encryption.
  2. Under Data encryption, select ON.

Enable Dynamic Data Masking

  1. Under Security, click Dynamic Data Masking.
  2. Click + Add mask.
  3. In the Add masking rule menu, configure the following settings:
    • Schema: SalesLT
    • Table: Customer
    • Column: EmailAddress (nvarchar)
    • Masking field format: Email (aXXX@XXXXX.com)
  4. Click Add.
  5. Click Save.

Create a SQL Database Instance Using PowerShell

Create a SQL Server

  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. In the PowerShell terminal window, run the following command to check the Azure subscription:
    Get-AzSubscription
  9. Check the resource group and its current location.
    Get-AzResourceGroup
  10. Create a new SQL server.
    New-AzSqlServer
  11. At the ServerName prompt, enter a unique name (e.g., test1990235).
  12. At the User prompt, enter a unique name (e.g., admin1990235).
  13. At the Password prompt, enter a unique password (e.g., Test123456).
  14. At the Location prompt, enter southcentralus.
  15. Scroll up in the PowerShell window to the output of the Get-AzResourceGroup command.
  16. Copy the ResourceGroupName value to your clipboard.
  17. Scroll back down to the command line, and paste in the ResourceGroupName from your clipboard.
  18. Wait a few minutes for the server to finish deploying.

Create a SQL Database

  1. Run the following command:
    New-AzSqlDatabase ` -DatabaseName [DATABASE_NAME] ` -MaxSizeBytes 53687091200 ` -Edition "Standard" -ResourceGroupName [RESOURCE_GROUP_NAME] -RequestedServiceObjectiveName S0
  2. At the ServerName prompt, enter the unique server name you assigned earlier (e.g., Test1990235).

Conclusion

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