Configure SQL Server enabled by Azure Arc
Each Azure Arc-enabled server includes a set of properties that apply to all SQL Server instances installed in that server. You can configure these properties after the Azure extension for SQL Server is installed on the machine. However, the properties only take effect if a SQL Server instance or instances are installed. In Azure portal, the SQL Server enabled by Azure Arc Overview reflects how the SQL Server Configuration affects a particular instance.
Azure portal SQL Server Configuration allows you to perform the following management tasks:
- Configure SQL Server license type
- Subscribe to Extended Security Updates
- Exclude SQL Server instances from onboarding to Azure Arc
- You're in a Contributor role in at least one of the Azure subscriptions your organization created. Learn how to create a new billing subscription.
- You're in a Contributor role for the resource group in which the SQL Server instance will be registered. See Managed Azure resource groups for details.
- The Microsoft.AzureArcData and Microsoft.HybridCompute resource providers are registered in each subscription you use for SQL Server pay-as-you-go billing.
To register the resource providers, use one of the methods below:
- Select Subscriptions
- Choose your subscription
- Under Settings, select Resource providers
- Search for
Microsoft.HybridComputeand select Register
Applies to: SQL Server
SQL Server license type identifies the type of license for SQL Server instances on a specific virtual machine or physical server. It includes an option to pay for the SQL software usage directly through Microsoft Azure using a pay-as-you-go billing.
License type is a required parameter when you install Azure Extension for SQL Server and each supported onboarding method includes the license type options. It allows you to track your SQL Server license inventory from using Azure Resource Graph. You can also track the software usage in the Cost Management + Billing portal.
For your convenience, Overview of each Arc-enabled SQL Server resource shows the license type under Host License Type.
SQL Server 2022 (16.x) allows you to select the license type, including the pay-as-you-go billing option, during setup. See Install Azure Extension for SQL Server from the Installation Wizard.
The following license types are supported:
|License type||Long description||Short description|
|PAYG||Standard or Enterprise edition with pay-as-you-go billing through Microsoft Azure||Pay-as-you-go|
|Paid||Standard or Enterprise edition license with Software Assurance or SQL Subscription||License with software assurance|
|LicenseOnly||Developer, Evaluation, Express, Web, Standard or Enterprise edition license only without Software Assurance||License only|
PAYG: Pay for your SQL Server software usage through Microsoft Azure. See SQL Server prices and licensing.
For correct billing, servers that use PAYG license type should stay continuously connected to Azure.
Intermittent connectivity disruptions are tolerated with built-in resilience.
Paid and LicenseOnly: Use an existing license agreement. Usage implies that you already have the necessary licenses. In these cases, your software usage will be reported to you using a free meter. You can analyze your usage in the Cost Management + Billing portal to make sure you have enough licenses for all your installed SQL Server instances.
The following table identifies features enabled depending on license type:
|Feature||License only 1||License with Software Assurance
or SQL subscription
|Connect to Azure||Yes||Yes||Yes|
|SQL Server inventory||Yes||Yes||Yes|
|Best practices assessment||No||Yes||Yes|
|Detailed database inventory||No||Yes||Yes|
|Microsoft Entra ID authentication||Yes||Yes||Yes|
|Microsoft Defender for Cloud||Yes||Yes||Yes|
|Govern through Microsoft Purview||Yes||Yes||Yes|
|Automated backups to local storage (preview)||No||Yes||Yes|
|Failover cluster instances (preview)||Yes||Yes||Yes|
|Always On availability groups (preview)||Yes||Yes||Yes|
1 License only includes SQL Server instances that are Developer, Express, Web, or Evaluation Edition and instances using a Server/CAL license.
Billing for SQL Server software
The value of License Type indicates if you already have a SQL Server license or prefer paying for it with a pay-as-you-go method. If you already have a license or use a free SQL Server edition, the software usage will be reported using a free meter. If you selected the pay-as-you-go method, a non-zero pay-as-you-go meter will be used.
The billing granularity is one hour. Pay-as-you-go charges are calculated based on the SQL Server edition and the size of the hosting server during that hour. The size is measured in cores if the SQL Server instance is installed on a physical server, and logical cores (vCores) if the SQL Server instance is installed on a virtual machine. When multiple instances of SQL Server are installed on the same OS, the following rules apply:
Only one instance must be licensed per OS for the full size of the host, subject to minimum core size. See SQL Server licensing guide for details. The following rules apply:
The instance with the highest edition defines what license is required.
If two or more instances of the same edition are installed, the first instance in alphabetical order is billed.
The combination of the Host License Type and the winning SQL Server edition defines which billing meters will be sent every hour.
The next table shows the meter SKUs that are used for different license types and SQL Server editions:
|Installed edition||Projected edition||License type||AG replica||Meter SKU|
|Enterprise Core||Enterprise||PAYG||No||Ent edition - PAYG|
|Enterprise Core||Enterprise||Paid||No||Ent edition - AHB|
|Enterprise Core||Enterprise||LicenseOnly||Yes or No||Ent edition - License only|
|Enterprise Core||Enterprise||PAYG or Paid||Yes||Ent edition - DR replica|
|Enterprise 1||Enterprise||PAYG||No||Ent edition - PAYG|
|Enterprise 1||Enterprise||Paid||No||Ent edition - AHB|
|Enterprise 1||Enterprise||LicenseOnly||Yes or No||Ent edition - License only|
|Enterprise 1||Enterprise||PAYG or Paid||Yes||Ent edition - DR replica|
|Standard||Standard||PAYG||No||Std edition - PAYG|
|Standard||Standard||Paid||No||Std edition - AHB|
|Standard||Standard||LicenseOnly||No||Std edition - License only|
|Standard||Standard||PAYG or Paid||Yes||Std edition - DR replica|
|Evaluation||Evaluation||LicenseOnly||Yes or No||Eval edition|
|Developer||Developer||LicenseOnly||Yes or No||Dev edition|
1 When Enterprise edition is installed, it indicates that the Server/CAL licensing model is used. Because the conversion to the core-based licensing model does not require an upgrade to the Enterprise Core, we treat this edition as Enterprise Core. The instances that have not converted to the core-based model and use a Server/CAL license must set the license type to LicenseOnly.
In addition to billing differences, license type determines what features will be available to your Arc-enabled SQL Server. The following features are not included in the LicenseOnly license type:
- Licensing benefit for fail-over servers. Azure extension for SQL Server supports free fail-over servers by automatically detecting if the instance is a replica in an availability group and reporting the usage with a separate meter. You can track the usage of the DR benefit in Cost Management + Billing. See SQL Server licensing guide for details.
- Detailed database inventory. You can manage your SQL database inventory in Azure portal. See View databases for details.
- Managing automatic updates of SQL Server from Azure.
- Best practices assessment. You can generate best practices reports and recommendations by periodic scans of your SQL Server configurations. See Configure your SQL Server instance for Best practices assessment.
Subscribe to Extended Security Updates
Extended Security Updates (ESU) is available for qualified SQL Server instances that use License with Software assurance or Pay-as-you-go as the license type. If the license type is license only, the option to activate the ESU subscription is disabled. See Extended Security Updates for SQL Server.
If ESU is enabled License Type cannot be changed to
LicenseOnly until the ESU subscrition is cancelled.
You can exclude certain instances from the at-scale onboarding operations driven by Azure policy or by automatic onboarding processes. To exclude specific instances from these operations, add the instance names to the Skip Instances list. For details about at-scale onboarding options, see Alternate deployment options for SQL Server enabled by Azure Arc.
Modify SQL Server configuration
You can use Azure portal, PowerShell or CLI to change all or some configuration settings on a specific Arc-enabled server to the desired state.
To modify the SQL Server Configuration for a larger scope, such as a resource group, subscription, or multiple subscriptions with a single command, use the Modify SQL Server Configuration PowerShell script. It is published as an open source SQL Server sample and includes the step-by-step instructions.
Run the script from Azure Cloud shell as it has the required Azure PowerShell modules pre-installed and you will be automatically authenticated. For details, see Running the script using Cloud Shell.
There are two ways to configure the SQL Server host in Azure portal.
Open the Arc-enabled Server overview page and click SQL Server Configuration as shown.
Open the Arc-enabled SQL Server overview page, and select Properties. Under Host configuration properties, select the setting you need to modify:
- License type
- ESU Status
- Automated patching
Set License Type property
Choose one of the license types. See License types for descriptions.
Set the Extended Security Updates property
You can enable or disable ESU. This setting is optional and only applies to the qualified versions of SQL Server. To learn more, see What are Extended Security Updates for SQL Server?.
To activate an ESU subscription, the license type must be set to Pay-as-you-go or License with Software assurance. If it is set to License only, the Extended Security Updates options will be disabled.
Add to the Exclude instances list
If you want to exclude specific instances from the at-scale onboarding operations driven by Azure policy or automated onboarding processes, add those instances under Skip Instances. This setting is optional.
Save the updated configuration
After you verify the license type, ESU setting, and any instance to exclude, select Save to apply changes.
Query SQL Server configuration
You can use Azure Resource Graph to query the SQL Server configuration settings within a selected scope. See the following examples.
Count by license type
This example returns the count by license type.
resources | where type == "microsoft.hybridcompute/machines/extensions" | where properties.type in ("WindowsAgent.SqlServer","LinuxAgent.SqlServer") | extend licenseType = iff(properties.settings.LicenseType == '', 'Configuration needed', properties.settings.LicenseType) | summarize count() by tostring(licenseType)
Identify instances where license type is undefined
This query returns a list of instances where the license type is null.
resources | where type == "microsoft.hybridcompute/machines/extensions" | where properties.type in ("WindowsAgent.SqlServer","LinuxAgent.SqlServer") | where isnull(properties.settings.LicenseType) | project ['id'], resourceGroup, subscriptionId
List configuration details for each SQL Server instance
This query identifies many details about each instance, including the license type, ESU setting and enabled features.
resources | where type == "microsoft.hybridcompute/machines"| where properties.detectedProperties.mssqldiscovered == "true"| extend machineIdHasSQLServerDiscovered = id | project name, machineIdHasSQLServerDiscovered, resourceGroup, subscriptionId | join kind= leftouter ( resources | where type == "microsoft.hybridcompute/machines/extensions" | where properties.type in ("WindowsAgent.SqlServer","LinuxAgent.SqlServer") | extend machineIdHasSQLServerExtensionInstalled = iff(id contains "/extensions/WindowsAgent.SqlServer" or id contains "/extensions/LinuxAgent.SqlServer", substring(id, 0, indexof(id, "/extensions/")), "") | project Extension_State = properties.provisioningState, License_Type = properties.settings.LicenseType, ESU = iff(notnull(properties.settings.enableExtendedSecurityUpdates), iff(properties.settings.enableExtendedSecurityUpdates == true,"enabled","disabled"), ""), Extension_Version = properties.instanceView.typeHandlerVersion, Excluded_instances = properties.ExcludedSqlInstances, Purview = iff(notnull(properties.settings.ExternalPolicyBasedAuthorization),"enabled",""), Entra = iff(notnull(properties.settings.AzureAD),"enabled",""), BPA = iff(notnull(properties.settings.AssessmentSettings),"enabled",""), machineIdHasSQLServerExtensionInstalled)on $left.machineIdHasSQLServerDiscovered == $right.machineIdHasSQLServerExtensionInstalled | where isnotempty(machineIdHasSQLServerExtensionInstalled) | project-away machineIdHasSQLServerDiscovered, machineIdHasSQLServerExtensionInstalled
List Arc-enabled servers with SQL Server
This query identifies Azure Arc-enabled servers with SQL Server discovered on them.
resources | where type == "microsoft.hybridcompute/machines" | where properties.detectedProperties.mssqldiscovered == "true" //| summarize count()
This query returns Azure Arc-enabled servers that have SQL Server, but the Arc SQL Server extension is not installed. This query only applies to Windows servers.
resources | where type == "microsoft.hybridcompute/machines" | where properties.detectedProperties.mssqldiscovered == "true" | project machineIdHasSQLServerDiscovered = id | join kind= leftouter ( resources | where type == "microsoft.hybridcompute/machines/extensions" | where properties.type == "WindowsAgent.SqlServer" | project machineIdHasSQLServerExtensionInstalled = substring(id, 0, indexof(id, "/extensions/WindowsAgent.SqlServer"))) on $left.machineIdHasSQLServerDiscovered == $right.machineIdHasSQLServerExtensionInstalled | where isempty(machineIdHasSQLServerExtensionInstalled) | project machineIdHasSQLServerDiscoveredButNotTheExtension = machineIdHasSQLServerDiscovered
For more examples of Azure Resource Graph Queries, see Starter Resource Graph queries.