Connect your SQL Server to Azure Arc

Beginning in SQL Server 2022 (Preview), you can deploy an instance of SQL Server from the command prompt that is connected to Azure Arc. See Deploy - connected to Azure Arc.

You can connect your SQL Server instance to Azure Arc by following these steps.

Prerequisites

• Your machine has at least one instance of SQL Server installed
• The Microsoft.AzureArcData and Microsoft.HybridCompute resource providers has been registered.

To register the resource provider, 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'

Initiate the connection from Azure

If the machine with SQL Server is already connected to Azure Arc, you can register the SQL Server instances on that machine by installing the SQL Server extension (WindowsAgent.SqlServer). Once installed, the SQL Server extension will recognize all the installed SQL Server instances and register them with Azure Arc. The extension will run continuously to detect changes of the SQL Server configuration. For example, if a new SQL Server instance is installed on the machine, if will be automatically registered with Azure. See virtual machine extension management for instructions on how to install and uninstall extensions using the Azure portal, Azure PowerShell or Azure CLI.

Important

1. The Managed System Identity for the corresponding Server - Azure Arc must have the Azure Connected SQL Server Onboarding role at resource group level.
2. The SQL Server - Azure Arc resource for each SQL Server instance installed on the machine will be created in the same region and the resource group as the corresponding Server - Azure Arc resource.

Azure portal

To assign the Azure Connected SQL Server Onboarding role to Arc machine managed identity, use the following steps:

1. Select the resource group that contains the Server - Azure Arc resource
2. Select Access control (IAM) on the left side of the resource group page
3. Click + Add and select Add role assignment
   • For Role, select Azure Connected SQL Server Onboarding
   • For Assign access to, select User, group or service principal
   • For Select, search for your Server - Azure Arc name and select it.
4. Click Save.

To install the SQL Server extension, use the following steps:

1. Open the Server - Azure Arc resource.
2. Under Extensions, click + Add
3. Select WindowsAgent.SqlServer from the list and click Create.

PowerShell

To assign Azure Connected SQL Server Onboarding role to the machine's managed identity, run:

$spID = (Get-AzADServicePrincipal -DisplayName $arcMachineName).Id
New-AzRoleAssignment -ObjectId $spID RoleDefinitionName "Azure Connected SQL Server Onboarding" -ResourceGroupName {resource group name}

To install the SQL Server extension, run:

$Settings = @{\"SqlManagement\":{\"IsEnabled\":true},  \"excludedSqlInstances\":[]}
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"

Azure CLI

To assign the Azure Connected SQL Server Onboarding role to Arc machine managed identity, run:

spID=$(az resource list -n <ArcMachineName> --query [*].identity.principalId --out tsv)
az role assignment create --assignee $spID --role 'Azure Connected SQL Server Onboarding' --scope /subscriptions/<mySubscriptionID>/resourceGroups/<myResourceGroup>

To install the SQL Server extension, run:

   az connectedmachine extension create --machine-name "{your machine name}" --location {"azure region"} --name "WindowsAgent.SqlServer" --resource-group "{your resource group name}" --type "WindowsAgent.SqlServer" --publisher "Microsoft.AzureData" --settings '{\"SqlManagement\":{\"IsEnabled\":true},  \"excludedSqlInstances\":[]}'

Note

The specified resource group must match the resource group of the corresponding Server - Azure Arc resource. Otherwise, the command will fail.

Initiate the connection from the target machine

If you want to customize the process of connecting the SQL Server instance to Azure Arc, you can initiate the connection from the target machine using the onboarding script.

Generate a onboarding script for SQL Server

If the machine with SQL Server is already connected to Azure Arc, you can register the SQL Server instances on that machine by installing the SQL Server extension (WindowsAgent.SqlServer). Once installed, the SQL Server extension will recognize all the installed SQL Server instances and register them with Azure Arc. The extension will run continuously to detect changes of the SQL Server configuration. For example, if a new SQL Server instance is installed on the machine, the extension automatically registers it with Azure. See virtual machine extension management for instructions how to install and uninstall extensions using Azure portal, Azure PowerShell, or Azure CLI.

1. Search for SQL Server - Azure Arc resource type and add a new one through the creation blade.

2. Review the prerequisites and go to the Server details tab.

3. Select the subscription, resource group, Azure region, and the host operating system. If required, also specify the proxy that your network uses to connect to Internet.

   Important

   If the machine hosting the SQL Server instance is already connected to Azure Arc, make sure to select the same resource group that contains the corresponding Server - Azure Arc resource.

   

4. Go to the Run script tab and download the onboarding script. The portal generates the script for the hosting OS you specified.

   

Connect SQL Server instances to Azure Arc

In this step you will take the script you downloaded from Azure portal and execute it on the target machine. The script installs the SQL Server extension. If the machine itself does not have the guest configuration agent installed, the script first installs it then installs the SQL Server extension. The guest agent and the SQL extension will in turn register the connected server and the SQL Server instances on it as the Server - Azure Arc and SQL Server - Azure Arc resources respectively.

Important

Make sure to execute the script using an account that meets the minimum permission requirements described in prerequisites.

Windows

1. Launch an admin instance of powershell.exe and sign in your PowerShell module with your Azure credentials. Follow the sign in instructions.

2. Execute the downloaded script

   & '.\RegisterSqlServerArc.ps1'
   

   Note

   If you haven't previously installed the Az PowerShell module and see issues the first time you run it, follow the instructions in the script and run it again.

Linux

1. Use Azure CLI to sign in with your Azure credentials. Follow the sign in instructions

2. Grant the execution permission to the downloaded script and execute it.

   sudo chmod +x ./RegisterSqlServerArc.sh
   ./RegisterSqlServerArc.sh
   

Validate the SQL Server - Azure Arc resources

Go Azure portal and open the newly registered SQL Server - Azure Arc resource to validate.

Disconnect your SQL Server instance

To disconnect your SQL Server instance from Azure Arc, go to Azure portal, open the SQL Server - Azure Arc resource for that instance, and click the Unregister button. It will delete this resource and instruct the SQL Server extension on the machine to stop monitoring this SQL Server instance.

Important

Because there could be multiple SQL Server instances installed on the same machine, the Unregister button will not uninstall the SQL Server extension. To uninstall it, follow the uninstall extension steps.

Restore a deleted SQL Server - Azure Arc resource

If you disconnected your SQL Server instance by mistake, you can restore its SQL Server - Azure Arc resource with the following steps.

1. If you also uninstalled the SQL Server extension by mistake, reinstall it.

   az connectedmachine extension create --machine-name "{your machine name}" --location {"azure region"} --name "WindowsAgent.SqlServer" --resource-group "{your resource group name}" --type "WindowsAgent.SqlServer" --publisher "Microsoft.AzureData" --settings '{\"SqlManagement\":{\"IsEnabled\":true},  \"excludedSqlInstances\":[]}'

Note

The location property must match the location of the Server - Azure Arc resource for the server specified by the --machine-name parameter.

2. Check to make sure your instance is in the exclusion list (see the value of the excludedSqlInstances property).

    az connectedmachine extension show --machine-name "{your machine name}" --resource-group "{your resource group name}" -n WindowsAgent.SqlServer

3. Make sure to remove your instance from the exclusion list and update the extension settings.

    az connectedmachine extension create --machine-name "{your machine name}" --location {"azure region"} --name "WindowsAgent.SqlServer" --resource-group "{your resource group name}" --type "WindowsAgent.SqlServer" --publisher "Microsoft.AzureData" --settings '{\"SqlManagement\":{\"IsEnabled\":true},  \"excludedSqlInstances\":[\"{named instance 1}\",\"{named instance 3}}\"]}'

The instance will be restored after the next sync with the agent. For information on how to manage vm extensions using Portal or PowerShell, see virtual machine extension management.

Next steps

• Configure advanced data security for your SQL Server instance

• Configure on-demand SQL assessment for your SQL Server instance