Configure SQL Server enabled by Azure Arc
Applies to: SQL Server
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
- Set the Extended Security Updates property
- Add to the Exclude instances list
Prerequisites
- 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.
Register resource providers
To register the resource providers, use one of the methods below:
- Select Subscriptions
- Choose your subscription
- Under Settings, select Resource providers
- Search for
Microsoft.AzureArcData
andMicrosoft.HybridCompute
and select Register
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-license-type.ps1
PowerShell script. It's published as an open source SQL Server sample and includes the step-by-step instructions.
Tip
Run the script from Azure Cloud shell because:
- It has the required Azure PowerShell modules pre-installed
- It automatically authenticates you
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 select SQL Server Configuration as shown.
Or
Open the Arc-enabled SQL Server overview page, and select Properties. Under SQL Server configuration, select the setting you need to modify:
- License type
- ESU subscription
- Automated updates
Set License Type property
Choose one of the license types. See License types for descriptions.
Set the Extended Security Updates property
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.
Note
- 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.
- If ESU is enabled License Type cannot be changed to
LicenseOnly
until the ESU subscription is cancelled.
Apply physical core license
Select this check-box if this is a virtual machine and you are leveraging the unlimited virtualization benefit for licensing the SQL Server software or for your SQL subscription. If selected, the p-core will take precedence and the SQL Server software costs or USU costs associated with this VM will be nullified.
Important
- The UV benefit is not supported for the VMs running on the listed providers' infrastructure. If you select this option for such a VM, this intent will be ignored and you will be charged for the v-cores of the VM. See Listed providers for details.
- If you are configuring a VM that is not subject to the above restriction, make sure the selected License type matches the Billing plan configured in the p-core license resource.
Add to the Exclude instances list
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.
Caution
SQL Server instances using Pay-as-you-go (PAYG) can't be excluded.
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 instances of SQL Server
This query identifies Azure Arc-enabled servers with SQL Server instances 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 instances, but the Arc SQL Server extension isn't 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.
Manage unlimited virtualization
To enable unlimited virtualization, SQL Server enabled by Azure Arc supports a special resource type: SQLServerLicense. This resource allows you to license many virtual machines with the installed SQL Server instances. For details of the licensing model, see licensing SQL Server instances with unlimited virtualization.
Prerequisites
Your RBAC role includes the following permissions:
- Microsoft. AzureArcData/SqlLicenses/read
- Microsoft. AzureArcData/SqlLicenses/write
- Microsoft.Management/managementGroups/read
- Microsoft.Resources/subscriptions/read
- Microsoft.Resources/subscriptions/resourceGroups/read
- Microsoft.Support/supporttickets/write
Create SQL Server license
To create the SQL Server license resource, use one of the methods below:
- Select Azure Arc
- Under Data Services, select SQL Server licenses
- Click +Create
- Select SQL Server physical core license
- Complete the creation wizard
Change SQL Server license properties
To change the SQL Server license property, for example activate it at a later date, use one of the methods below:
- Select Azure Arc
- Under Data Services, select SQL Server licenses
- Click on the license in question
- Select Configure under Management
- Make the changes and click Apply
Manage resources in scope
You can manage the resources in scope of a specific SQL Server physical core license using the following steps:
- Select Azure Arc
- Under Data Services, select SQL Server licenses
- Click on the license in question
- Select Resources in scope under Management
If the specific resources are not configured to use this license (Apply physical core license column displays "NO"), you can change that:
- Select the specific resource(s) on the list
- Click the Apply license tab.
- Read the disclaimer and click Confirm.
List Arc-enabled servers in scope of the SQL Server license
This query lists all Azure Arc-enabled servers in scope of the license and the relevant properties of each.
resources
| where type =~ 'Microsoft.HybridCompute/machines'
| where ('${scopeType}'!= 'Subscription' or subscriptionId == '${subscription}')
| where ('${scopeType}' != 'ResourceGroup' or (resourceGroup == '${resourceGroup.toLowerCase()}' and subscriptionId == '${subscription}'))
| extend status = tostring(properties.status)
| where status =~ 'Connected'
| join kind = leftouter
(
resources
| where type =~ 'Microsoft.HybridCompute/machines/extensions'
| where name == 'WindowsAgent.SqlServer' or name == 'LinuxAgent.SqlServer'
| extend machineId = substring(id, 0, indexof(id, '/extensions'))
| extend extensionId = id
)
on $left.id == $right.machineId
| where isnotnull(extensionId)
| project id, name, properties.status, resourceGroup, subscriptionId, Model = properties.detectedProperties.model, Manufacturer = properties.detectedProperties.manufacturer, kind, OSE = properties.osName, License_applied = properties1.settings.UsePhysicalCoreLicense.IsApplied
|order by name asc
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for