Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

      Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

    • 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

      Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

      Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

  • 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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

Networking

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

  • 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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

    Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

Open the RDP file and provide your VM administrator credentials.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

selectdb_name(database_id)asSystemDatabase,type_desc,physical_name

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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.

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

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

View all posts by Rajendra Gupta

Microsoft SQL Server 2022 installed on an Azure virtual machine is an example of PaaS

Is Azure SQL Server SaaS or PaaS?

Azure SQL Database is a relational database-as-a-service (DBaaS) hosted in the Azure cloud that falls into the industry categories of Software-as-a-Service (SaaS) and Platform-as-a-Service (PaaS). SQL database is built on standardized hardware and software that is owned, hosted, and maintained by Microsoft.

Is Azure SQL Server IaaS or PaaS?

Azure SQL Database is a relational database-as-a-service (DBaaS) hosted in Azure that falls into the industry category of Platform-as-a-Service (PaaS).

Is Azure Virtual Machine PaaS?

Azure VM IaaS is a cloud platform that provides a virtual machine (VM) instance that runs the Windows Server operating system. It is a platform-as-a-service (PaaS) offering. Azure VM IaaS is different from Azure PaaS in that Azure VM IaaS includes a VM instance that runs the Windows Server operating system.

What is PaaS SQL Server?

Option 2: Platform as a Service (PaaS) – Managed Instance Azure SQL Managed Instance is much like SQL Server installed on a server that you don't have to maintain. It gives you the ability to scale compute (4-80 vCores with 5GB of memory per) and storage (8TB max) up or down quickly with the click of a button.