This article is contributed. See the original author and article here.

Running SQL Server on Azure Virtual Machines provides a suite of free manageability capabilities that are available only on Azure, and which make it easier to run in a cost effective, secured and optimized manner. Microsoft introduced the Azure SQL family of database services to provide customers with a consistent, unified experience across an entire SQL portfolio and a full range of deployment options from edge to cloud. As a SQL Server customer, you can migrate your SQL workloads to SQL Server on Azure Virtual Machines while making the most of your current SQL Server license investments and benefit from the manageability benefits that SQL Server virtual machines offer today. Running SQL Server on an Azure virtual machine provides the best lift-and-shift experience for workloads where OS-level access is required.

 

A SQL Server instance on an Azure Virtual Machine is the cloud equivalent of an on-premises SQL Server, running your mission critical applications on Azure. Azure offers unique Windows Server and SQL Server licensing benefits through Azure Hybrid Benefit, giving you the ability to run the same SQL Server with a better total cost of ownership (TCO), with low effort and great performance. Azure also offers that ability for you to automatically manage your Windows Servers using our recently announced Azure Automanage preview offering. Read more about it here.

How can you leverage these benefits?

All of the security, manageability and cost-optimization benefits mentioned above are enabled through the SQL Server IaaS Agent Extension. To ensure that you’re receiving full manageability benefits, this extension can be installed on a SQL Server virtual machine that is already running or any new SQL Server virtual machine that you create in the future.

The screenshot below shows the different manageability options that are enabled when the SQL Server IaaS extension is enabled.

 

SQL IaaS Extension CapabilitiesSQL IaaS Extension Capabilities

 

 

 

Optimize Cost

The SQL Server IaaS extension enables administrators to reduce cost and manage inventory with less effort. There are a number of cost optimization capabilities that a SQL Server administrator can leverage with SQL Server virtual machines which are made possible through our “License type” feature. As an administrator, you will have the ability to:

  • See all your SQL Server virtual machine deployments in a single dashboard to make inventory management easier
  • Get a snapshot of how many SQL Server on Azure Virtual Machines are leveraging Azure Hybrid Benefit
  • Switch between Pay-as-you-go and Azure Hybrid Benefit licensing models to optimize the use of your licenses in the cloud
  • Leverage FREE passive SQL Server core benefits that enable using Azure as a Disaster Recovery site for your on-premises SQL Server at no additional license cost for customers with Software Assurance or SQL Server subscription licenses
  • Leverage FREE passive SQL Server core benefits for High Availability and Disaster Recovery scenarios for primary replicas hosted in Azure for customers with Software Assurance or SQL Server subscription licenses
  • Run SQL Server Reporting Services virtual machines with Pay-as-you-go licensing or Azure Hybrid Benefits

The License type for a SQL Server virtual machine provides three options: Pay As You Go, Azure Hybrid Benefit and Disaster Recovery (as seen in the screenshot below). The Disaster Recovery toggle gives you the ability to leverage the free Disaster Recovery license type benefit without having to track the deployments separately, making license management and inventory a lot easier.

 
 

Configure hybrid DR using free SQL Server VM licensesConfigure hybrid DR using free SQL Server VM licenses

 

This benefit applies to all releases of SQL Server starting from SQL Server 2008 to SQL Server 2019 as long as you are using licenses covered with Software Assurance or subscription licenses. You can learn more about these benefits here.

 

As a SQL Server user, you will have the ability in the near future to deploy a SQL Server Reporting Services virtual machine for your BI reporting requirements and also leverage a flexible licensing model through Pay-as-you-go and Azure Hybrid Benefit licensing. Also, you can deploy your Power BI Report Server virtual machines with Azure Hybrid Benefit licenses which makes it easier to rehost your relational and reporting workloads into Azure.

Enhance Security

Security is a key area of interest for any database administrator who is responsible for securing and protecting business data stored in a SQL Server database. Sometimes compliance requirements drive security needs like encryption of data at rest. SQL Server on Azure Virtual Machines make security management easier through patching and easily implement encryption features for better security and compliance.

 

The Automated Patching feature allows a SQL Server administrator to select a maintenance window schedule for applying Important Windows Server and SQL Server updates that are distributed through the Windows Update channel. You have the ability to select the maintenance window duration and the start of the window.

 

Patch your Azure SQL VM using scheduled windowsPatch your Azure SQL VM using scheduled windows

The SQL connectivity feature allows you to do the following:

  • Set the port for the SQL Server instance to ensure that you are able to listen on another port other than a well-known port.
  • Configure the connectivity rules to make it as restrictive like allowing local connectivity only or opening it up to public internet for external applications and clients to connect to the SQL Server instance (see screenshot below).
  • Enable SQL Authentication for the SQL Server instance if your applications and users require this authentication method.
  • Configure Azure Key Vault for the SQL Server instance to leverage Key Vault for Transparent Database Encryption, Column Level Encryption and Always Encrypted features of SQL Server to enable encryption of data at rest and in motion.

 

Increase Uptime

SQL Server virtual machines provide various management capabilities to make configuration of the environment a lot easier for business continuity and disaster recovery scenarios.

The new High Availability feature allows you to create a new cluster or onboard an existing cluster, and then create the availability group, listener, and internal load balancer.

 

Configure High Availability easily with Azure SQL VMConfigure High Availability easily with Azure SQL VM

 

Additionally, SQL Server on Azure Virtual Machines also help you reduce your cost of deploying a highly available environment if you have Software Assurance or you are using SQL Server subscription licenses. Let us consider an example where you were running a SQL Server virtual machine topology having one primary replica, one synchronous passive secondary replica and one asynchronous passive replica. You would need to license only the primary replica. It is assumed that the number of cores on all three replicas are the same. The example below uses Always On Availability Group as the High Availability and Disaster Recovery feature, but you can leverage this benefit with other SQL Server features like Failover Cluster Instances, Log Shipping, Database Mirroring and Backup and Restore. More details available here.

 

The Automated Backup feature allows you to setup SQL Server backups with various options like encrypting backups, set a retention period, backup system databases, configuring a manual backup schedule or setting up an automated backup. This is great for SQL Server virtual machines where you don’t want to attach a backup software but just backup all the databases on the instance to support point-in-time restore for creating copies of the database environment or to recover from user errors.

Manageability

SQL Server on Azure Virtual Machines provides a number of free manageability benefits which make administration tasks for a SQL Server instance a lot easier. In addition to that a number of best practices are surfaced through the wizards to make SQL Server virtual machines run with the most optimal configurations.

 

SQL Server on Azure Virtual Machines provides the ability to simplify storage configuration while setting up the virtual machine through the use of pre-configured storage profiles as seen in the screenshot below. This ensures that you are picking the right storage configuration for your data, log and tempdb files. You can read more about it here.

 

Configure storage settings for your Azure SQL VMConfigure storage settings for your Azure SQL VM

 

 

Once the SQL Server virtual machine is running, you have the option of increasing the storage capacity of your disks and the wizard will help you determine if you are picking a configuration setting which could be the victim of a capacity limit (see highlighted section in screenshot below).

 

Configure your storage settings on Azure SQL VMConfigure your storage settings on Azure SQL VM

 

 

You also have the ability to manage your SQL Server virtual machines through the SQL Server IaaS extension through the use of Azure CLI or PowerShell. Azure Runbooks or Azure Automation scripts can be created to leverage these benefits at scale across a fleet of SQL Server virtual machines.

If you leverage in-database Machine Learning for your SQL Server instances, SQL Server virtual machines provide the ability to configuring advanced analytics for your instance.

Next Steps

Running SQL Server in an Azure virtual machine gives you the same capabilities and experience you are used to with on-premises SQL Server, plus the additional ease of use and management benefits available only on Azure. Ensure that your virtual machines are running the SQL Server IaaS Extension to enable all these benefits, and check out the following links for more information about running SQL Server in Azure virtual machines.

 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.