Редагувати

Поділитися через

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.

Prerequisites

Register resource providers

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

  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.

Use physical core license

Select this checkbox if you're configuring a virtual machine, and you're using the unlimited virtualization benefit for licensing the SQL Server software or for your SQL subscription. It will set the Host configuration property UsePhysicalCoreLicense to True. If selected, the p-core license takes precedence, and the SQL Server software costs will be nullified.

Important

If the physical core license is configured with a pay-as-you-go billing plan, the selected License type should be set as pay-as-you-go. This will not trigger additional charges at the VM level, but it will ensure the uninterrrupted licensing and billing in an event of the p-core license's de-activation or deletion.

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. Select Subscribe to Extended Security Updates. The subscription will be activated after you click Save.

Note

  • To activate an ESU subscription, the license type must be set to Pay-as-you-go or License with Software assurance. If it's set to License only, the Extended Security Updates options will be disabled.
  • If ESU is enabled License Type can't be changed to LicenseOnly until the ESU subscription is canceled.

Use physical core ESU license

Select this checkbox if you're configuring a virtual machine, and you're using the unlimited virtualization benefit when enabling the ESU subscription. It sets UseEsuPhysicalCoreLicense to true. If selected, the p-core license takes precedence, and the SQL Server ESU charges at the VM level will be nullified.

Unsubscribe from Extended Security Updates

You can cancel Extended Security Updates enabled by Azure Arc at any time. The cancellation immediately stops the ESU charges. Select Unsubscribe from to Extended Security Updates. The subscription will be terminated after you click Save.

Add to the excluded 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.

Important

The unlimited virtualization benefit for SQL Server software or SQL Server ESU subscription is not supported on the listed providers' infrastructure. If you are running SQL Server in a listed provider's VM and select this option, your intent will be ignored and you'll be charged for the v-cores of the VM. See Listed providers for details.

Subscribe to Extended Security Updates at scale using Azure Policy

You can activate the ESU subscription on multiple Arc-enabled machines using an Azure policy definition called Subscribe eligible Arc-enabled SQL Servers instances to Extended Security Updates. When you create an assignment of this policy definition to a scope of your choice, it enables ESU on all Arc-enabled machines that have the Azure extension for SQL Server installed. If any of these machines have a qualified SQL Server instance, the ESU subscription is activated immediately.

Use the following steps to activate this policy:

  1. Navigate to Azure Policy in the Azure portal and choose Definitions.
  2. Search for Subscribe eligible Arc-enabled SQL Servers instances to Extended Security Updates and right-click on the policy.
  3. Select Assign policy.
  4. Select a subscription and optionally a resource group as a scope.
  5. Make sure the policy enforcement is set to Enabled.
  6. On the Parameters tab, set the value of Enable Extended Security Updates to True.
  7. On the Remediation tab:
    1. Select Create remediation task for this policy to be applied to existing resources. If not selected, the policy is applied to the newly created resources only.
    2. Select Create a Managed Identity and choose System assigned managed identity (recommended) or User assigned managed identity, which has Azure Extension for SQL Server Deployment and Reader permissions.
    3. Select the identity's location.
  8. Select Review + Create.
  9. Select Create.

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 query samples.

List Arc-enabled SQL Server instances subscribed to ESU

The following example shows how you can view all eligible SQL Server 2012 (11.x) instances and their ESU subscription status.

resources
| where type == 'microsoft.azurearcdata/sqlserverinstances'
| extend Version = properties.version
| extend Edition = properties.edition
| extend containerId = tolower(tostring (properties.containerResourceId))
| where Version contains "2012"
| where Edition in ("Enterprise", "Standard")
| where isnotempty(containerId)
| project containerId, SQL_instance = name, Version, Edition
| join kind=inner (
    resources
    | where type == "microsoft.hybridcompute/machines"
    | extend machineId = tolower(tostring(id))
    | project machineId, Machine_name = name
)
on $left.containerId == $right.machineId
| join kind=inner (
    resources
    | where type == "microsoft.hybridcompute/machines/extensions"
    | where properties.type in ("WindowsAgent.SqlServer","LinuxAgent.SqlServer")
    | extend machineIdHasSQLServerExtensionInstalled = tolower(iff(id contains "/extensions/WindowsAgent.SqlServer" or id contains "/extensions/LinuxAgent.SqlServer", substring(id, 0, indexof(id, "/extensions/")), ""))
    | project machineIdHasSQLServerExtensionInstalled, 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
)
on $left.machineId == $right.machineIdHasSQLServerExtensionInstalled
| project-away machineId, containerId, machineIdHasSQLServerExtensionInstalled

List Arc-enabled servers that host a billable SQL Server instance

This query identifies the machines (virtual or physical) that host SQL Server instances that are billable or require a license for SQL Server software. It provides the details of the SQL Server configuration, including the license type, ESU setting, size in v-core or p-cores and other relevant parameters.

resources
| where type =~ 'Microsoft.HybridCompute/machines'
| extend status = tostring(properties.status)
| where status =~ 'Connected'
| extend machineID = tolower(id)
| extend VMbyManufacturer = toboolean(iff(properties.detectedProperties.manufacturer in (
        "VMware",
        "QEMU",
        "Amazon EC2",
        "OpenStack",
        "Hetzner",
        "Mission Critical Cloud",
        "DigitalOcean",
        "UpCloud",
        "oVirt",
        "Alibaba",
        "KubeVirt",
        "Parallels",
        "XEN"
    ), 1, 0))
| extend VMbyModel = toboolean(iff(properties.detectedProperties.model in (
        "OpenStack",
        "Droplet",
        "oVirt",
        "Hypervisor",
        "Virtual",
        "BHYVE",
        "KVM"
    ), 1, 0))
| extend GoogleVM = toboolean(iff((properties.detectedProperties.manufacturer =~ "Google") and (properties.detectedProperties.model =~ "Google Compute Engine"), 1, 0))
| extend NutanixVM = toboolean(iff((properties.detectedProperties.manufacturer =~ "Nutanix") and (properties.detectedProperties.model =~ "AHV"), 1, 0))
| extend MicrosoftVM = toboolean(iff((properties.detectedProperties.manufacturer =~ "Microsoft Corporation") and (properties.detectedProperties.model =~ "Virtual Machine"), 1, 0))
| extend billableCores = iff(VMbyManufacturer or VMbyModel or GoogleVM or NutanixVM or MicrosoftVM, properties.detectedProperties.logicalCoreCount, properties.detectedProperties.coreCount)        
| join kind = leftouter // Join Extension
        (
        resources
        | where type =~ 'Microsoft.HybridCompute/machines/extensions'
        | where name == 'WindowsAgent.SqlServer' or name == 'LinuxAgent.SqlServer'
        | extend extMachineID = substring(id, 0, indexof(id, '/extensions'))
        | extend extensionId = id
        )
        on $left.id == $right.extMachineID
        | join kind = inner       // Join SQL Arc
            (
            resources
            | where type =~ 'microsoft.azurearcdata/sqlserverinstances'
            | extend sqlVersion = tostring(properties.version)
            | extend sqlEdition = tostring(properties.edition) 
            | extend is_Enterprise = toint(iff(sqlEdition == "Enterprise", 1, 0))
            | extend sqlStatus = tostring(properties.status)
            | extend licenseType = tostring(properties.licenseType)
            | where sqlEdition in ('Enterprise', 'Standard') 
            | where licenseType !~ 'HADR'
            | where sqlStatus =~ "Connected"
            | extend ArcServer = tolower(tostring(properties.containerResourceId))
            | order by sqlEdition
            )
            on $left.machineID == $right.ArcServer
            | where isnotnull(extensionId)
            | summarize Edition = iff(sum(is_Enterprise) > 0, "Enterprise", "Standard") by machineID
            , name
            , resourceGroup
            , subscriptionId
            , Model = tostring(properties.detectedProperties.model)
            , Manufacturer = tostring(properties.detectedProperties.manufacturer)
            , License_Type = tostring(properties1.settings.LicenseType)
            , OS = tostring(properties.osName)
            , Uses_UV = tostring(properties1.settings.UsePhysicalCoreLicense.IsApplied)
            , Cores = tostring(billableCores)
            , Version = sqlVersion
            | project-away machineID
            | order by Edition, name asc

Manage unlimited virtualization benefit for SQL Server

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 following methods:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server licenses
  3. Select +Create
  4. Select SQL Server physical core license
  5. Complete the creation wizard

Update SQL Server license resource

To change the SQL Server license property, for example activate it at a later date, use one of the following methods:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server licenses
  3. Select on the license in question
  4. Select Configure under Management
  5. Make the changes and select Apply

Manage resources in scope of the p-core license

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. Select on the license in question
  4. Select Resources in scope under Management

If the specific resources aren't configured to use this license (Apply physical core license column displays "NO"), you can change that:

  1. Select the specific resources on the list
  2. Select the Apply license tab
  3. Read the disclaimer and select 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

Manage unlimited virtualization benefit for SQL Server ESU subscription

To enable unlimited virtualization for ESU subscription, SQL Server enabled by Azure Arc supports a special resource type: SQLServerEsuLicense. This resource allows you to enable an ESU subscription for a set of physical hosts with unlimited number of virtual machines running the out-of-support 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 ESU license resource

To create the SQL Server ESU license resource, use one of the following methods:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server ESU licenses
  3. Select +Create
  4. Complete the creation wizard

Update SQL Server ESU license resource

To change the SQL Server ESu license properties, for example terminate the subscription, use one of the following methods:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server ESU licenses
  3. Select on the license in question
  4. Select Configure under Management
  5. Make the changes and select Apply

Manage resources in scope of the ESU p-core license

You can manage the resources in scope of a specific SQL Server ESU license using the following steps:

  1. Select Azure Arc
  2. Under Data Services, select SQL Server ESU licenses
  3. Select on the license in question
  4. Select Resources in scope under Management

This view will only show the connected machines in scope that host an out-of-service SQL Server instance with the version that matches the version of the p-core ESU license you are managing. If the specific resources aren't configured to use this license (Physical core license applied column displays "NO"), you can change that:

  1. Select the specific resources on the list
  2. Select the Subscribe to ESUs tab to subscribe or the Unsubscribe from ESUs to unsubscribe.
  3. Read the disclaimer and select Confirm

List Arc-enabled servers in scope of the SQL Server ESU 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.UseEsuPhysicalCoreLicense.IsApplied
        |order by name asc

Related content