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:

  1. Configure SQL Server license type
  2. Subscribe to Extended Security Updates
  3. Exclude SQL Server instances from onboarding to Azure Arc

Prerequisites

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

License types

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.

Note

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.

    Important

    SQL Server enabled by Azure Arc Pay-As-You-Go (PAYG) provides a flexible subscription-based access to SQL Server. The servers with SQL Server instances using PAYG must be continuously connected to Azure. Intermittent connectivity disruptions for up to 30 days are tolerated with built-in resilience. After 30 days of dysconnectivity the PAYG subscription will expire. Please be advised that once your subscription expires, you aren't authorized to use the software.

  • 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
Pay-as-you-go
Connect to Azure Yes Yes Yes
SQL Server inventory Yes Yes Yes
Best practices assessment No Yes Yes
Migration assessment (preview) 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
Point-in-time-restore (preview) No Yes Yes
Automated patching No Yes Yes
Failover cluster instances (preview) Yes Yes Yes
Always On availability groups (preview) Yes Yes Yes
Monitoring (preview) No Yes Yes
Operate with least privilege (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
Web Web LicenseOnly n/a Web edition
Express Express LicenseOnly n/a Express 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 doesn't require an upgrade to the Enterprise Core, we treat this edition as Enterprise Core. The instances that haven't 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 aren't included when the LicenseOnly license type is selected:

  • Licensing benefit for failover servers. The Azure extension for SQL Server supports free failover servers. Specifically, the extension:

    • Automatically detects if the instance hosts a replica in an availability group
    • Reporting the usage with a separate meter.

    To qualify for this benefit, the replica must be fully passive. All of its databases must be part of the same group. If one or more databases aren't part of the group, the instance is treated as active and billed based on its edition. For more information, see the SQL Server licensing guide.

  • Detailed database inventory. You can manage your SQL database inventory in the Azure portal. See View databases for details.

  • Managing automatic updates of SQL Server from Azure.

  • Best practices assessment. 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.

Note

If ESU is enabled License Type cannot be changed to LicenseOnly until the ESU subscription is cancelled.

Exclude instances

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.

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's published as an open source SQL Server sample and includes the step-by-step instructions.

Tip

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 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 patching

    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

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?.

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.

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