Ανάγνωση στα Αγγλικά Επεξεργασία

Κοινή χρήση μέσω

Automate management with the Windows SQL Server IaaS Agent extension

  • Άρθρο

Applies to: SQL Server on Azure VM

The SQL Server IaaS Agent extension (SqlIaasExtension) runs on SQL Server on Azure Windows Virtual Machines (VMs) to automate management and administration tasks.

This article provides an overview of the extension. To install the SQL Server IaaS Agent extension to SQL Server on Azure VMs, see the articles for Automatic registration, Register single VMs, or Register VMs in bulk.

To learn more about the SQL Server on Azure VM deployment and management experience, watch the following Data Exposed videos:

Overview

The SQL Server IaaS Agent extension allows for integration with the Azure portal, and unlocks a number of benefits for SQL Server on Azure VMs:

  • Feature benefits: The extension unlocks a number of automation feature benefits, such as portal management, license flexibility, automated backup, automated patching and more. See Feature benefits later in this article for details.

  • Compliance: The extension offers a simplified method to fulfill the requirement of notifying Microsoft that the Azure Hybrid Benefit has been enabled as is specified in the product terms. This process negates needing to manage licensing registration forms for each resource.

  • Free: The extension is completely free. There's no additional cost associated with the extension.

  • Integration with centrally managed Azure Hybrid Benefit: SQL Server VMs registered with the extension can integrate with Centrally managed Azure Hybrid Benefit, making it easy manage the Azure Hybrid Benefit for your SQL Server VMs at scale.

  • Simplified license management: The extension simplifies SQL Server license management, and allows you to quickly identify SQL Server VMs with the Azure Hybrid Benefit enabled using:

    You can use the SQL virtual machines resource in the Azure portal to quickly identify SQL Server VMs that are using the Azure Hybrid Benefit.

Enable auto upgrade to ensure you're getting the latest updates to the extension each month.

Feature benefits

The SQL Server IaaS Agent extension unlocks a number of feature benefits for managing your SQL Server VM, letting you pick and choose which benefit suits your business needs. When you first register with the extension, the functionality is limited to a few features that don't rely on the SQL IaaS Agent. Once you enable a feature that requires it, the agent is installed to the SQL Server VM.

The following table details the benefits available through the SQL IaaS Agent extension, and whether or not the agent is required:

Feature Description
Azure portal management Unlocks management in the portal, so that you can view all of your SQL Server VMs in one place, and enable or disable SQL specific features directly from the portal.

Included with basic registration.
Automated backup Automates the scheduling of backups for all databases for either the default instance or a properly installed named instance of SQL Server on the VM. For more information, see Automated backup for SQL Server in Azure virtual machines (Resource Manager).

Requires SQL IaaS Agent extension.
Automatic patching Automatically install Windows and SQL Server security updates (including Cumulative Updates for SQL Server) to your virtual machine during a configured maintenance window to avoid updating during peak times for your workload. For more information, see Automatic patching through Azure Update Manager.

Requires SQL IaaS Agent extension.
Azure Key Vault integration Enables you to automatically install and configure Azure Key Vault on your SQL Server VM. For more information, see Configure Azure Key Vault integration for SQL Server on Azure Virtual Machines (Resource Manager).

Requires SQL IaaS Agent extension.
Configure tempdb You can configure your tempdb directly from the Azure portal, such as specifying the number of files, their initial size, their location, and the autogrowth ratio. Restart your SQL Server service for the changes to take effect.

Requires SQL IaaS Agent extension.
Defender for Cloud portal integration If you've enabled Microsoft Defender for SQL, then you can view Defender for Cloud recommendations directly in the SQL virtual machines resource of the Azure portal. See Security best practices to learn more.

Requires SQL IaaS Agent extension.
Extended security updates Automatically receive security updates for your SQL Server on Azure VMs, up to three years after extended SQL Server lifecycle support ends.
Flexible licensing Save on cost by seamlessly transitioning from the bring-your-own-license (also known as the Azure Hybrid Benefit) to the pay-as-you-go licensing model and back again.

Included with basic registration.
Flexible version / edition If you decide to change the version or edition of SQL Server, you can update the metadata within the Azure portal without having to redeploy the entire SQL Server VM.

Included with basic registration.
I/O Analysis View an analysis of your I/O performance in the Azure portal to find issues that result from exceeding virtual machines and data disks limits. This feature is currently in preview.

Requires SQL IaaS Agent extension.
Microsoft Entra authentication Enhance the security of your SQL Server VM by using Microsoft Entra ID for authentication to your SQL Server VM.

Requires SQL IaaS Agent extension.
SQL best practices assessment Enables you to assess the health of your SQL Server VMs by using configuration best practices. For more information, see SQL best practices assessment.

Requires SQL IaaS Agent extension.
View disk utilization in portal Allows you to view a graphical representation of the disk utilization of your SQL data files in the Azure portal.

Requires SQL IaaS Agent extension.

Permissions models

By default, the SQL IaaS Agent extension uses the least privilege mode permission model. The least privilege permission model grants the minimum permissions required for each feature that you enable. Each feature that you use is assigned a custom role in SQL Server, and the custom role is only granted permissions that are required to perform actions related to the feature.

The following table defines the SQL Server permissions and custom roles used by each feature of the extension:

Feature Permissions Custom role (Server / DB)
Automated backups Server permission - CONTROL SERVER
Database permission - db_ddladmin on master, db_backupoperator on msdb		 SqlIaaSExtension_AutoBackup
Availability group portal management sysadmin
Azure Backup Service sysadmin to account NT SERVICE\AzureWLBackupPluginSvc
Credential management Server permission - CONTROL SERVER SqlIaaSExtension_CredentialMgmt
I/O related best practices Server permission - CONTROL SERVER SqlIaaSExtension_ThrottlingAssessment
SQL best practices assessment Server permission - CONTROL SERVER SqlIaaSExtension_Assessment
SQL Server instance settings Server permission - ALTER ANY LOGIN, ALTER SETTINGS SqlIaaSExtension_SqlInstanceSetting
Storage configuration Server permission - ALTER ANY DATABASE SqlIaaSExtension_StorageConfig
Status reporting Server permission - VIEW ANY DEFINITION, VIEW SERVER STATE, ALTER ANY LOGIN, CONNECT SQL SqlIaaSExtension_StatusReporting

SQL Server VMs deployed prior to October 2022 use the older sysadmin model where the SQL IaaS Agent extension takes sysadmin rights by default. For SQL Server VMs provisioned before October 2022, you can enable the least privilege permissions model manually.

Σημείωση

The option to enable least privilege mode is only available for SQL Server VMs provisioned before October 2022. If this option is not visible in your environment, it's because your SQL Server VM already has least privilege mode enabled by default.

To enable the least privilege permissions model, go to your SQL virtual machines resource, choose Security Configuration under Security and then check the box next to Enable least privilege mode:

Screenshot of the Azure portal SQL virtual machines resource, Security Configuration page, enable least privilege highlighted.

Installation

When you register your SQL Server VM with the SQL IaaS Agent extension, binaries are copied to the VM. Once you enable a feature that relies on it, the SQL IaaS Agent extension is installed to the VM and has access to SQL Server. By default, the agent follows the model of least privilege, and only has permissions within SQL Server that are associated with the features that you enable - unless you manually installed SQL Server to the VM yourself, or deployed a SQL Server image from the marketplace prior to October 2022, in which case the agent has sysadmin rights within SQL Server.

Deploying a SQL Server VM Azure Marketplace image through the Azure portal automatically registers the SQL Server VM with the extension. However, if you choose to self-install SQL Server on an Azure virtual machine, or provision an Azure virtual machine from a custom VHD, then you must register your SQL Server VM with the SQL IaaS Agent extension to unlock feature benefits. By default, self-installed Azure VMs with SQL Server 2016 or later are automatically registered with the SQL IaaS Agent extension when detected by the CEIP service. SQL Server VMs not detected by the CEIP should be manually registered.

When you register with the SQL IaaS Agent extension, binaries are copied to the virtual machine, but the agent isn't installed by default. The agent will only be installed when you enable one of the SQL IaaS Agent extension features that require it, and the following two services will then run on the virtual machine:

  • Microsoft SQL Server IaaS agent is the main service for the SQL IaaS Agent extension and should run under the Local System account.
  • Microsoft SQL Server IaaS Query Service is a helper service that helps the extension run queries within SQL Server and should run under the NT Service account NT Service\SqlIaaSExtensionQuery.

There are three ways to register with the extension:

Registering your SQL Server VM with the SQL Server IaaS Agent extension creates the SQL virtual machine resource within your subscription, which is a separate resource from the virtual machine resource. Deleting the extension from your SQL Server VM removes the SQL virtual machine resource from your subscription but doesn't drop the underlying virtual machine.

Multiple instance support

The SQL IaaS Agent extension supports the following environments:

  • One default instance.
  • If there are multiple instances, only the default instance is supported and managed by the extension in the Azure portal. Environments with multiple named instances without a default instance aren't supported.
  • One named instance, if it's the only installed instance.

Named instance support

If you want to manage a single named instance in the Azure portal, install SQL Server with a nondefault name to an Azure virtual machine and then register it with the SQL IaaS Agent extension.

If you want to manage a single named instance in a SQL Server image from Azure Marketplace, you must first uninstall the existing SQL Server instance, install SQL Server with a named instance, and then register it with the SQL IaaS Agent extension.

To use a single named instance with SQL Server on Azure VMs, follow these steps:

  1. Deploy a SQL Server VM from Azure Marketplace.
  2. Delete the SQL IaaS Agent extension from the SQL Server VM.
  3. Connect to the virtual machine and uninstall SQL Server completely.
  4. Restart the virtual machine.
  5. Connect to the virtual machine and then use the setup media (typically located in C:\SQLServerFull) to install a named SQL Server instance.
  6. Restart the virtual machine.
  7. Register the VM with the SQL IaaS Agent Extension.

Failover Clustered Instance support

Registering your SQL Server Failover Clustered Instance (FCI) is supported with limited functionality. Due to the limited functionality, SQL Server FCIs registered with the extension don't support features that require the agent, such as automated backup, patching, Microsoft Entra authentication and advanced portal management.

If your SQL Server VM has already been registered with the SQL IaaS Agent extension and you've enabled any features that require the agent, you'll need to delete the extension from the SQL Server VM and register it again after your FCI is installed.

Verify status of extension

Use the Azure portal, Azure PowerShell or the Azure CLI to check the status of the extension.

Verify the extension is installed in the Azure portal.

Go to your Virtual machine resource in the Azure portal (not the SQL virtual machines resource, but the resource for your VM). Select Extensions under Settings. You should see the SqlIaasExtension extension listed, as in the following example:

Screenshot from the Azure portal of the status of the SQL Server IaaS Agent extension.

Management modes

Before March 2023, the SQL IaaS Agent extension relied on management modes to define the security model, and unlock feature benefits. In March 2023, the extension architecture was updated to remove management modes entirely, instead relying on the principle of least privilege to give customers control over how they want to use the extension on a feature-by-feature basis.

Starting in March 2023, when you first register with the extension, binaries are saved to your virtual machine to provide you with basic functionality such as license management. Once you enable any feature that relies on the agent, the binaries are used to install the SQL IaaS Agent to your virtual machine, and permissions are assigned to the SQL IaaS Agent service as needed by each feature that you enable.

Limitations

The SQL IaaS Agent extension only supports:

  • SQL Server VMs deployed through the Azure Resource Manager. SQL Server VMs deployed through the classic model aren't supported.
  • SQL Server VMs deployed to the public or Azure Government cloud. Deployments to other private or government clouds aren't supported.
  • TCP/IP must be enabled in SQL Server Configuration Manager and for the VM for the extension to work with your SQL Server on Azure VMs.
  • SQL Server FCIs with limited functionality. SQL Server FCIs registered with the extension don't support features that require the agent, such as automated backup, patching, and advanced portal management.
  • VMs with a default instance, or a single named instance when no default instance is present.
  • If the VM has multiple named instances, then one of the instances must be the default instance to work with the SQL IaaS Agent extension.
  • SQL Server instance images only. The SQL IaaS Agent extension doesn't support Reporting Services or Analysis services, such as the following images: SQL Server Reporting Services, Power BI Report Server, SQL Server Analysis Services.

Privacy statements

When using SQL Server on Azure VMs and the SQL IaaS Agent extension, consider the following privacy statements:

  • Automatic registration: By default, Azure VMs with SQL Server 2016 or later are automatically registered with the SQL IaaS Agent extension when detected by the CEIP service. Review the SQL Server privacy supplement for more information.

  • Data collection: The SQL IaaS Agent extension collects data for the express purpose of giving customers optional benefits when using SQL Server on Azure Virtual Machines. Microsoft will not use this data for licensing audits without the customer's advance consent. See the SQL Server privacy supplement for more information.

  • In-region data residency: SQL Server on Azure VMs and the SQL IaaS Agent extension don't move or store customer data out of the region in which the VMs are deployed.

Related content

To install the SQL Server IaaS extension to SQL Server on Azure VMs, see the articles for Automatic installation, Single VMs, or VMs in bulk. For problem resolution, read Troubleshoot known issues with the extension.

To learn more, review the following articles: