Skip to content

Latest commit

 

History

History
311 lines (217 loc) · 19.5 KB

manage-configuration.md

File metadata and controls

311 lines (217 loc) · 19.5 KB
title description author ms.author ms.reviewer ms.date ms.topic
Configure
Explains how to manage SQL Server licensing options. Also demonstrates how SQL Server enabled by Azure Arc can be billed from Microsoft Azure. Use to enable pay as you go licensing.
anosov1960
sashan
mikeray, randolphwest
12/07/2023
conceptual

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 [!INCLUDE ssazurearc] 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:

Azure portal

  1. Select Subscriptions
  2. Choose your subscription
  3. Under Settings, select Resource providers
  4. Search for Microsoft.AzureArcData and Microsoft.HybridCompute and select Register

PowerShell

Run:

Register-AzResourceProvider -ProviderNamespace Microsoft.AzureArcData

Azure CLI

Run:

az provider register --namespace 'Microsoft.AzureArcData'

License types

[!INCLUDE sqlserver]

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

[!INCLUDE sssql22-md] 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] Azure Arc-enabled SQL Server 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 are not 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.

[!INCLUDE license-types]

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

Note

If ESU is enabled License Type cannot be changed to LicenseOnly until the ESU subscrition 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.

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.

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.

Azure portal

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.

    :::image type="content" source="media/billing/overview-of-sql-server-azure-arc.png" alt-text="Screenshot of the Azure Arc-enabled Server in Azure portal." lightbox="media/billing/overview-of-sql-server-azure-arc.png" :::

    Or

  • 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

    :::image type="content" source="media/billing/sql-server-instance-configuration.png" alt-text="Screenshot of Azure portal SQL Server instance configuration." lightbox="media/billing/sql-server-instance-configuration.png" :::

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.

PowerShell

The following command will set the license type to "PAYG", enable the ESU subscription and add two instances to the exclusion list.

//Updated settings object
$Settings = @{ 
  SqlManagement = @{ IsEnabled = $true };
  ExcludedSqlInstances = @( "Foo","Bar"); 
  LicenseType="PAYG";
  enableExtendedSecurityUpdates = $True;
  esuLastUpdatedTimestamp = [DateTime]::UtcNow.ToString('yyyy-MM-ddTHH:mm:ss.fffZ')
}

// Command stays the same as before, only settings is changed above:
New-AzConnectedMachineExtension -Name "WindowsAgent.SqlServer" -ResourceGroupName {your resource group name} -MachineName {your machine name} -Location {azure region} -Publisher "Microsoft.AzureData" -Settings $Settings -ExtensionType "WindowsAgent.SqlServer"

Important

  • The update command overwrites all settings. If your extension settings have a list of excluded SQL Server instances, make sure to specify the full exclusion list with the update command.
  • If you already have an older version of the Azure extension installed, make sure to upgrade it first, and then use one the modify methods to set the correct license type. For details, see How to upgrade a machine extension for details.

Azure CLI

The following command will set the license type to "PAYG":

az connectedmachine extension update --machine-name "simple-vm" -g "<resource-group>" --name "WindowsAgent.SqlServer" --type "WindowsAgent.SqlServer" --publisher "Microsoft.AzureData" --settings '{"LicenseType":"PAYG", "SqlManagement": {"IsEnabled":true}}'    

Important

  • The update command overwrites all settings. If your extension settings have a list of excluded SQL Server instances, make sure to specify the full exclusion list with the update command.
  • If you already have an older version of the Azure extension installed, make sure to upgrade it first, and then use one the modify methods to set the correct license type. For details, see How to upgrade a machine extension for details.

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.

Next steps