Provision an Azure VM with SQL Server

In this IT Pro Challenge, learners will understand how to create a Microsoft SQL Server on an Azure virtual machine, add a data disk to the virtual machine, add an inbound security rule in the Network Security Group (NSG) to allow SQL Server connectivity for the virtual machine. Finally, as a proof of concept, learners will use the Azure portal to confirm SQL Server network connectivity to the Azure virtual machine. The skills acquired in this lab are important for careers as a network or system administrator.

Overview

The scenario for this lab is that you are a system administrator, and your company is migrating its database services from an on-premise datacenter to Azure. Your job is to create an Azure virtual machine that hosts Microsoft SQL Server, add a data disk to the virtual machine, and as a proof of concept, verify remote connectivity through firewall rules.

Microsoft SQL Server on Azure virtual machines is a cloud-based version of the traditional on-premise SQL Server application. With SQL Server Azure virtual machines, you can perform automated backups, save your data to blob storage, configure availability groups for high availability, and configure automated storage with different machine sizes and performance optimization.

Create an Azure VM with SQL Server

To begin this hands-on lab, you will sign in to the Azure portal, create an Azure virtual machine (VM) and configure it to use Windows Server 2016 Datacenter (include Microsoft SQL Server 2017 Developer). You will configure SQL Server, use Remote Desktop Protocol (RDP) to connect to the virtual machine, and then use SQL Server Management Studio (SSMS) to verify that the SQL server is running. SSMS is a database management tool that allows you to configure and manage components of SQL Server, Azure SQL Database, and SQL Data Warehouse.

Add a data disk to the Azure VM

Now you need a place to store SQL Server transaction logs so that you will add a data disk to the virtual machine. Then you will use RDP to connect to the virtual machine again so that you can format the new data disk and use Windows Disk Management to allocate a drive (**L:**). You will also create a database that uses the new disk that you just created and create a folder (Log). Next, you will use SSMS to change the default location for the transaction logs to L:\Log. You will use Windows File Explorer to verify that the transaction log file is in L:\Log.

Update firewall rules for SQL connectivity

Lastly, you will allow SQL Server connectivity by adding an inbound security rule to the Network Security Group (NSG) for the virtual machine. Then you will use RDP to access the virtual machine and Windows Server Manager to verify the Windows Firewall inbound rules for SQL Server network traffic. In the Azure portal, you will then confirm that SQL Server connectivity is enabled for the virtual machine by navigating to the Effective security rules link on the Networking page of the VM blade.

Summary Conclusion

By taking this lab, you will learn how to use the Azure portal to create an Azure virtual machine with SQL Server, add a data disk to the virtual machine, and verify the firewall rules for SQL Server connectivity.