Deploy SQL Server on Azure VM using the Azure Portal

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

Note: You can refer to migration planning articles for understanding how to migrate from On-premises to Azure

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.

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.

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
- 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
  
  For this article, I have used Standard Ds1_V2 for deploying SQL Server on Azure VM
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

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.

Networking

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

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
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.

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
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
Automated patching: It is enabled by default for Sunday at 22:00
Optionally, you can choose to configure automated backup and R Services (SQL Server Machine Learning Services – Advanced analytics)

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.

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

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

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

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.

Open the RDP file and provide your VM administrator credentials.

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 Management Studio or Azure Data Studio for the database connection. To check the SQL Server version, run the following script:

1	Select @@version

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.

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

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.

1 2	select db_name(database_id) as SystemDatabase, type_desc, physical_name from sys.master_files

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

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.

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

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.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am 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 my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023