Register multiple SQL VMs in Azure with the SQL IaaS Agent extension

  • Article

Applies to: SQL Server on Azure VM

This article describes how to register your SQL Server virtual machines (VMs) in bulk in Azure with the SQL IaaS Agent extension by using the Register-SqlVMsAzure PowerShell cmdlet.

Alternatively, you can register all SQL Server VMs automatically or individual SQL Server VMs manually.

Note

SQL Server VMs deployed via the Azure marketplace after October 2022 have the least privileged model enabled by default. Management modes for the SQL IaaS Agent extension were removed in March 2023.

Overview

The Register-SqlVMs cmdlet can be used to register all virtual machines in a given list of subscriptions, resource groups, or a list of specific virtual machines. The cmdlet will register the virtual machines and then generate both a report and a log file.

The registration process carries no risk, has no downtime, and will not restart the SQL Server service or the virtual machine.

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. You can use bulk registration to register any SQL Server VMs that are not detected by the CEIP service.

For information about privacy, see the SQL IaaS Agent extension privacy statements.

Prerequisites

To register your SQL Server VM with the extension, you'll need the following:

Get started

Before proceeding, you must first create a local copy of the script, import it as a PowerShell module, and connect to Azure.

Create the script

To create the script, copy the full script from the end of this article and save it locally as RegisterSqlVMs.psm1.

Import the script

After the script is created, you can import it as a module in the PowerShell terminal.

Open an administrative PowerShell terminal and navigate to where you saved the RegisterSqlVMs.psm1 file. Then, run the following PowerShell cmdlet to import the script as a module:

Import-Module .\RegisterSqlVMs.psm1

Connect to Azure

Use the following PowerShell cmdlet to connect to Azure:

Connect-AzAccount -Environment AzureChinaCloud

All VMs in a list of subscriptions

Use the following cmdlet to register all SQL Server virtual machines in a list of subscriptions:

Register-SqlVMs -SubscriptionList SubscriptionId1,SubscriptionId2

Example output:

Number of subscriptions registration failed for 
because you do not have access or credentials are wrong: 1
Total VMs Found: 10
VMs Already registered: 1
Number of VMs registered successfully: 4
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 3
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

All VMs in a single subscription

Use the following cmdlet to register all SQL Server virtual machines in a single subscription:

Register-SqlVMs -Subscription SubscriptionId1

Example output:

Total VMs Found: 10
VMs Already registered: 1
Number of VMs registered successfully: 5
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the  guest agent on VM is not running: 2
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

All VMs in multiple resource groups

Use the following cmdlet to register all SQL Server virtual machines in multiple resource groups within a single subscription:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupList ResourceGroup1,ResourceGroup2

Example output:

Total VMs Found: 4
VMs Already registered: 1
Number of VMs registered successfully: 1
Number of VMs failed to register due to error: 1
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

All VMs in a resource group

Use the following cmdlet to register all SQL Server virtual machines in a single resource group:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1

Example output:

Total VMs Found: 4
VMs Already registered: 1
Number of VMs registered successfully: 1
Number of VMs failed to register due to error: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

Specific VMs in a single resource group

Use the following cmdlet to register specific SQL Server virtual machines within a single resource group:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -VmList VM1,VM2,VM3

Example output:

Total VMs Found: 3
VMs Already registered: 0
Number of VMs registered successfully: 1
Number of VMs skipped as VM or the guest agent on VM is not running: 1
Number of VMs skipped as they are not running SQL Server On Windows: 1

Please find the detailed report in file RegisterSqlVMScriptReport1571314821.txt
Please find the error details in file VMsNotRegisteredDueToError1571314821.log

A specific VM

Use the following cmdlet to register a specific SQL Server virtual machine:

Register-SqlVMs -Subscription SubscriptionId1 -ResourceGroupName ResourceGroup1 -Name VM1

Example output:

Total VMs Found: 1
VMs Already registered: 0
Number of VMs registered successfully: 1

Please find the detailed report in  file RegisterSqlVMScriptReport1571314821.txt

Output description

Both a report and a log file are generated every time the Register-SqlVMs cmdlet is used.

Report

The report is generated as a .txt file named RegisterSqlVMScriptReport<Timestamp>.txt where the timestamp is the time when the cmdlet was started. The report lists the following details:

Output value Description
Number of subscriptions registration failed for because you do not have access or credentials are incorrect This provides the number and list of subscriptions that had issues with the provided authentication. The detailed error can be found in the log by searching for the subscription ID.
Number of subscriptions that could not be tried because they are not registered to the resource provider This section contains the count and list of subscriptions that have not been registered to the SQL IaaS Agent extension.
Total VMs found The count of virtual machines that were found in the scope of the parameters passed to the cmdlet.
VMs already registered The count of virtual machines that were skipped as they were already registered with the extension.
Number of VMs registered successfully The count of virtual machines that were successfully registered after running the cmdlet. Lists the registered virtual machines in the format SubscriptionID, Resource Group, Virtual Machine.
Number of VMs failed to register due to error Count of virtual machines that failed to register due to some error. The details of the error can be found in the log file.
Number of VMs skipped as the VM or the gust agent on VM is not running Count and list of virtual machines that could not be registered as either the virtual machine or the guest agent on the virtual machine were not running. These can be retried once the virtual machine or guest agent has been started. Details can be found in the log file.
Number of VMs skipped as they are not running SQL Server on Windows Count of virtual machines that were skipped as they are not running SQL Server or are not a Windows virtual machine. The virtual machines are listed in the format SubscriptionID, Resource Group, Virtual Machine.

Log

Errors are logged in the log file named VMsNotRegisteredDueToError<Timestamp>.log, where timestamp is the time when the script started. If the error is at the subscription level, the log contains the comma-separated Subscription ID and the error message. If the error is with the virtual machine registration, the log contains the Subscription ID, Resource group name, virtual machine name, error code, and message separated by commas.

Remarks

When you register SQL Server VMs with the extension by using the provided script, consider the following:

  • Registration with the extension requires a guest agent running on the SQL Server VM. Windows Server 2008 images do not have a guest agent, so these virtual machines will fail and must be registered manually with limited functionality.
  • There is retry logic built-in to overcome transparent errors. If the virtual machine is successfully registered, then it is a rapid operation. However, if the registration fails, then each virtual machine will be retried. As such, you should allow significant time to complete the registration process - though actual time requirement is dependent on the type and number of errors.

Full script

For the full script on GitHub, see Bulk register SQL Server VMs with Az PowerShell.

Copy the full script and save it as RegisterSqLVMs.psm1.

Next steps

To learn more, review the following articles: