This article guides you for deploying your first SQL Server on Azure VM using the Azure portal.
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.
- 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
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
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.
In the networking section, define the virtual network settings with inbound and outbound connectivity.
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
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:
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.
select db_name(database_id) as SystemDatabase, type_desc, physical_name
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.
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.
- DTU and vCore based models for Azure SQL Databases - September 27, 2021
- Custom Azure Policy definitions for Azure SQL Databases backup retention periods compliance - September 22, 2021
- Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server - September 16, 2021