Rajendra Gupta
Deployment complete

Deploy SQL Server on Azure VM using the Azure Portal

August 11, 2021 by

This article guides you for deploying your first SQL Server on Azure VM using the Azure portal.

Introduction

The infrastructure requirement might vary in different organizations, applications. Therefore, planning is an essential aspect for an organization from on-premises to cloud infrastructure.

Azure offers two deployment choices for SQL Server.

  • SQL Server on Azure VM (IaaS)
  • Fully Managed Azure SQL Database (PaaS)

Suppose you require complete control over the Virtual machine, SQL Server configurations for specific applications or functionalities. In this case, SQL Server on Azure VM gives the benefit of the full version and control of SQL Server with your resources in Azure cloud.

  • It has complete administrative control over the OS, SQL Server instance
  • Quickly Scale up resources
  • Configuration of features such as transactional replication, always-on availability groups, log shipping, integration, analysis and reporting services similar to on-premises SQL
  • Disaster recovery solutions, backups, automated patching
  • Cross-database transaction execution

Create SQL Server on Azure VM using Azure portal

In this section, we deploy an Azure VM for SQL Server using the Azure portal. Azure offers SQL virtual machines as a pre-configured SQL Server for quick deployments. In the Azure portal, go to Marketplace and search for SQL.

Here, select the deployment option – SQL virtual machine. Currently, it does not show any description in the image section.

deployment option

Click on the image and select the appropriate image for your deployment. For example, here, I choose the image – Free SQL Server License: SQL Server 2019 developer on Windows Server 2019.

Select SQL and OS image

Click on create for providing input for virtual machine and SQL Server configurations. In the create virtual machines, do the following configurations.

Basics

  • Subscriptions: In this option, you need to select the subscription for which you want to deploy Azure resources
  • Resource group: It is a container for Azure resources
  • Instance details:
    • Virtual machine name
    • Region
    • Availability options: Select the Infrastructure redundancy option from the drop-down, if required

      Basic configuration

    • Image: It is the SQL Server image on the selected OS
    • Size: Azure provides various VM size that combines vCPU, RAM, Data Disks, Max IOPS, Temp storage. As shown below, you can compare these resources and their estimated cost per month for your suitable workload. You can refer to the virtual machine series for it

      VM size

      For this article, I have used Standard Ds1_V2 for deploying SQL Server on Azure VM

      VM size, image and region

  • Administrator user name and password: It is the credentials to connect with the Azure VM
  • Specify the Inbound rules for virtual machine ports that you wish to open. For example, for a remote desktop connection, it uses port 3389

    Administrator user name

Disks

Each Azure VM is equipped with one operating system disk with installed OS and a temporary disk for short-term storage for page or swap files. The number of data disks for SQL Server on Azure VM depends on the VM machine type.

Disks config

Networking

In the networking section, define the virtual network settings with inbound and outbound connectivity.

Networking

Management

In the management section, you can select the following options for SQL Server on Azure VM.

  • Boot diagnostics
  • OS guest diagnostics
  • Azure AD authentication

    Management

  • Auto-shutdown settings
  • Enable site recovery
  • Enable hot patch
  • Patch orchestration options

Advanced

You can choose to install a custom extension or specify a script using a configuration file in the Advanced configuration.

Advanced config

SQL Server setting for SQL Server on Azure VM

This section is specific to SQL Server configurations.

  • SQL Server port: default port is 1433
  • SQL authentication: By default, SQL authentication is disabled. You can enable the SQL authentication and configure the credentials. It uses VM administrative credentials for SQL authentication if we do not specify it
  • Azure key vault integration

    • Note: You cannot do SQL Server configurations such as system database locations, TempDB, MAXDOP, custom features using the image. It uses the pre-defined configurations defined in the templates

    SQL Server setting for SQL Server on Azure VM

  • Storage: By default, SQL Server VM configures storage for data files (SQL Data), log files(SQL Log) and TempDB (SQL TempDB). It uses a local SSD drive for the TempDB files. As shown below, it is using 5000 IPOS and 200 MB/s for both data and log files. Click on Change configuration and select your required storage options
  • SQL Server License: If you have already owned a SQL Server license, you can click on Yes and specify the details

    Storage configuration

  • Automated patching: It is enabled by default for Sunday at 22:00

    Automated patching

  • Optionally, you can choose to configure automated backup and R Services (SQL Server Machine Learning Services – Advanced analytics)

    Automated Backup

Review + Create

On the next page, it performs a validation. You can review the configuration. It also gives hourly cost for running the SQL Server VM. For example, for my lab environment, it shows 0.1260 USD per hour. You can go back and make changes if required.

Validations status

Click on Create for deploying SQL Server on Azure VM. It starts deploying Azure VM resources such as VM, Disks, network, disk storage.

Deployment progress

Once the resource is deployed, you get the following screen.

Deployment complete

Click on Go to Resource. It takes you to the VM overview page. As shown below, the SQL VM is running.

VM overview page

Click on the Connect button from the overview page. It shows the public IP address and port number for the connection. Click on the Download RDP File button.

Download RDP file

Open the RDP file and provide your VM administrator credentials.

Open RDP file

Click on Yes. In the connected Azure VM RDP session. It has already a running SQL Server instance with the configuration we specified in the SQL Server settings page. However, most of the settings are as per the default Azure templates.

To view the installed services, go to Start and launch SQL Server Configuration Manager.

launch SQL Server Configuration Manager

Launch SQL Server Management Studio or Azure Data Studio for the database connection. To check the SQL Server version, run the following script:

As shown below, we have a running Microsoft SQL Server 2019 ( RTM-CU9) 15.0.4102.2 Developer edition on Windows Server 2019 Datacenter edition.

Check SQL Server version

The VM has the following drives:

  • Operating system drive or boot drive C
  • Temporary Storage drive or local SSD drive D
  • SQL Data files disk: F
  • SQL log file disk: L

Drives structure

Usually, for on-premises SQL Server installation, we choose the appropriate drives for system databases. However, the SQL Server Azure VM preinstalled it for us. Therefore, let’s verify the system databases file paths.

The query returns file paths for all system databases:

  • Master, Model, and MSDB databases files are in the C drive
  • TempDB files are stored in the D drive

System DB files

As per the best practice, DBA does not store database files in the OS drive. Therefore, you should move these files to data(F), and Log(G) drives. You can refer to How to move SQL database files (MDF and LDF) to another location to learn more about it.

Right-click on the connected SQL Server instance in SSMS and view the default configurations for default database locations and backup compressions. The following figure shows that new database files will be created in the F (data file) and G (log file). However, the default path for database backup is pointing to the C drive. You can configure an additional disk for storing database backups. You can also explore Azure Disk Backup or Azure Backup, Azure blob backup, Azure file share backups for backups.

Default directory

You can run the SQL Server Agent, SQL Server Analysis Services, SQL Server Launchpad from the SQL Server Configuration Manager, depending upon your requirements.

Start SQL Services

Conclusion

This article explored the deployment of SQL Server on Azure VM using the Azure portal. It installs SQL Server with pre-defined templates. You can customize your SQL Server configuration with complete control over its services, databases, and features in Azure IaaS VM for SQL Server.

Rajendra Gupta
Azure, SQL Azure

About Rajendra Gupta

As an MCSA certified and Microsoft Certified Trainer in Gurgaon, India, with 13 years of experience, Rajendra works for a variety of large companies focusing on performance optimization, monitoring, high availability, and disaster recovery strategies and implementation. He is the author of hundreds of authoritative articles on SQL Server, Azure, MySQL, Linux, Power BI, Performance tuning, AWS/Amazon RDS, Git, and related technologies that have been viewed by over 10m readers to date. He is the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups. Based on his contribution to the SQL Server community, he has been recognized with various awards including the prestigious “Best author of the year" continuously in 2020 and 2021 at SQLShack. Raj is always interested in new challenges so if you need consulting help on any subject covered in his writings, he can be reached at rajendra.gupta16@gmail.com View all posts by Rajendra Gupta

424 Views