Edit

Configure SQL Server enabled by Azure Arc

  • Article

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:

  1. Configure SQL Server license type
  2. Set the Extended Security Updates property
  3. Add to the Exclude instances list

Prerequisites

Register resource providers

To register the resource providers, use one of the methods below:

  1. Select Subscriptions
  2. Choose your subscription
  3. Under Settings, select Resource providers
  4. Search for Microsoft.AzureArcData and Microsoft.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.

    Screenshot of the SQL Server enabled by Azure Arc in Azure portal.

    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

    Screenshot of Azure portal SQL Server instance configuration.

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

  1. 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.
  2. 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:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server licenses
  3. Click +Create
  4. Select SQL Server physical core license
  5. 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:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server licenses
  3. Click on the license in question
  4. Select Configure under Management
  5. 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:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server licenses
  3. Click on the license in question
  4. 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:

  1. Select the specific resource(s) on the list
  2. Click the Apply license tab.
  3. 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