Skip to main content

Managing an Azure SQL Database

Hands-On Lab

 

Photo of Brian  Roehm

Brian Roehm

Azure Training Architect I in Content

Length

00:45:00

Difficulty

Intermediate

In this lab, we run through a range of common activities needed to properly configure an Azure SQL Database. These tasks are common steps and will help students prepare for Microsoft Certification Labs as well as build comfort in day-to-day operations.

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.

Managing an Azure SQL Database

Introduction

In this lab, we run through a range of common activities needed to properly configure an Azure SQL Database. These tasks are common steps and will help students prepare for Microsoft Certification Labs as well as build comfort in day-to-day operations.

Scenario

Microsoft has changed the focus of their exams to include hands-on labs. In this lab, we review common tasks that students will frequently encounter when taking exams or in the data engineering field.

The following tasks are part of this lab:

  • Configure
    • Configure SQL Server and SQL Database
    • Open the firewall
  • Security
    • Enable TDE
    • Enable auditing
    • Dynamic data masking
  • Performance
    • Create geo-replication
  • Backup
    • Configure weekly, monthly, and yearly backups

Connecting to the Lab

  1. Log in to the Azure Portal using the credentials provided on the lab instructions page.

Configure SQL Database and SQL Server

  1. Using the box at the top of the window, search for "SQL database".

  2. Click SQL databases from the list of options.

  3. Click Create SQL database.

  4. Using the combo box next to Resource group, select the provided resource group.

  5. Enter "test1" for the Database name in the box provided.

  6. Under the box for the Server, click Create new.

  7. Enter a unique Server name.

  8. Provide a Server admin login.

  9. Provide a Password and confirm it.

  1. Click the box under Location and select (US) West US.

  2. Click OK.

  3. Under Compute + storage, click Configure database.

  4. Click the link at the top for Looking for basic, standard, premium?

  5. Make sure the options are set for five DTUs and 2 GB of storage, then click Apply.

  6. Click Next: Networking.

  7. Click Next: Additional settings.

  8. For Use existing data, select the Sample option.

  9. Click Next: Tags.

  10. Click Next: Review + create.

  11. Click Create.

  12. Click Go to resource.

  13. Click the link for the server under Server name.

  14. In the left-hand pane, under Security, click Firewalls and virtual networks.

  15. Set Allow Azure services and resources to access this server to ON.

  16. At the top of the window, click + Add client IP.

  17. At the top of the window, click Save.

Configure Security

  1. In the left-hand pane, under Security, click Transparent data encryption. This screen could be used to create a unique key, however that is not part of this lab.

  2. In the left-hand pane, under Security, click Auditing.

  3. At the top of the Auditing pane, enable auditing by choosing ON.

  4. Select the checkbox next to Storage.

  5. Under Storage, click Configure.

  6. Click Configure required settings.

  7. Enter a unique name in the box under Name.

  8. Click OK.

  9. Under Retention (Days), enter a value of 365.

  1. Click OK.

  2. Once back in the Auditing settings view, select the checkbox next to Log Analytics (Preview).

  3. Under Log Analytics (Preview), click Configure.

  4. Click + Create New Workspace.

  5. Enter a unique name under Log Analytics Workspace.

  6. Click the combo box under Location and select West US.

  7. Click OK.

  8. Once back in the Auditing settings view, click Save.

  9. In the left-hand pane, under Settings, click SQL databases.

  10. Click the row for the SQL database.

  11. In the left-hand pane, under Security, click Dynamic Data Masking.

  12. At the top of the pane, click + Add mask.

  13. Use the combo box under Schema to select SalesLT.

  14. Use the combo box under Table to select Customer.

  15. Use the combo box under Column to select Phone (nvarchar).

  16. At the top of the pane, click Add.

  17. Click Save.

  18. Click OK.

Update for Performance

  1. In the left-hand pane, under Settings, click Geo-Replication.

  2. Click Central US from the table at the bottom.

  3. Under Target server, click Configure required settings.

  4. Enter a unique name under Server name.

  5. Provide a unique Server admin login.

  6. Enter a Password and confirm in under Confirm password.

  7. Click Select.

  8. Click OK.

Configure a Backup Policy

  1. Once the deployment is complete, in the left-hand pane, click Overview.

  2. Click the Server name.

  3. In the left-hand pane, under Settings, click Manage Backups.

  4. Select the available database and click Configure retention.

  5. Select the option for Weekly LTR Backups.

  6. Underneath that, enter a value of "2" in the first box and select Year(s) from the neighboring combo box.

  7. Select the option for Monthly LTR Backups.

  8. Underneath that, enter a value of "7" in the first box and select Year(s) from the neighboring combo box.

  9. Click Apply.

  1. Click Yes. This should generate a warning.

  2. In the left-hand pane, click Overview.

  3. In the left-hand pane, under Settings, click SQL databases.

  4. Click the available database.

  5. Hover over the menu icon in the top-left corner and select All resources.

  6. Click the link for the Central US database.

  7. Under Pricing tier, click Basic.

  8. Change the tier to Standard, and then click Apply.

  9. Once the change has compelted, hover over the menu icon in the top-left corner and select All resources.

  10. Click the link for the West US database.

  11. Under Pricing tier, click Basic.

  12. Change the tier to Standard, and then click Apply.

  13. Once the change has completed, click Overview in the left-hand pane.

  14. Click the link for the server under Server name.

  15. In the left-hand pane, under Settings, click Manage Backups.

  16. Select the available database and click Configure retention.

  17. Select the option for Weekly LTR Backups.

  18. Underneath that, enter a value of "2" in the first box and select Year(s) from the neighboring combo box.

  19. Select the option for Monthly LTR Backups.

  20. Underneath that, enter a value of "7" in the first box and select Year(s) from the neighboring combo box.

  21. Click Apply.

  22. Click Yes.

Conclusion

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