Connect your SQL Server to Azure Arc

Beginning with SQL Server 2022 (16.x) Preview you connect a new SQL Server instance to Azure Arc when you are installing it on Windows Operating System. See Install SQL Server 2022.

You can connect your existing 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 have been registered.

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

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 Azure extension for SQL Server. The Windows version of this extension can be found in the extension manager as "WindowsAgent.SqlServer". Once installed, Azure extension for SQL Server 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, it will be automatically registered with Azure Arc. See virtual machine extension management for instructions on how to install and uninstall extensions to Azure connected machine agent using the Azure portal, Azure PowerShell or Azure CLI.

Important

  1. The Managed System Identity used by the Azure connected machine agent must have the Azure Connected SQL Server Onboarding role at resource group level.
  2. The Azure resource with type SQL Server - Azurde Arc representing the SQL Server instance installed installed on teh machine machine will use the same region and the resource group as the Azure resources for Arc-enabled servers.

To assign the Azure Connected SQL Server Onboarding role the Managed System Identity, use the following steps:

  1. Select the resource group that contains the Arc-enabled Server resource
  2. Select Access control (IAM) on the left side of the resource group page
  3. Click + Add and select Add role assignment
  4. For Role, select Azure Connected SQL Server Onboarding and click Next.
  5. For Assign access to, select Managed identity
  6. Click +Select members
    • For Subscription, select the name of your subscription
    • For Managed identity, select Server - Azure Arc
    • For Select, select the name (only if you want to assign the role to a specific server)
  7. Click Close.

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

  1. Open the Azure Arc > Servers resource.
  2. Search for the connected server with the SQL Server instance that you want to connect to Azure
  3. Under Extensions, click + Add
  4. Select Azure extension for SQL Server and click Next.
  5. Specify the SQL Server instance(s) you want to exclude from registering (if you have multiple instances installed on the server) and click Review + Create
  6. Click Create

To install Azure extension for SQL Server for Linux operating system, run:

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

Azure extension for SQL Server for Linux is available for preview.


Note

The specified resource group must match the resource group where the corresponding connected server is registered. Otherwise, the command will fail.

Initiate the connection from the target machine

If the server that runs your SQL Server instance is not yet connected to Azure, you can initiate the connection from the target machine using the onboarding script. This script will connect the server to Azure and will install Azure extension for SQL Server.

Generate an onboarding script for SQL Server

  1. Go to Azure Arc > SQL Server and click + Add Start creation

  2. Select Connect SQL Server to Azure Arc

  3. Review the prerequisites and click Next: Server details

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

    Server details

  5. Click Tags to optionally add tags to the resource for your SQL Server instance.

  6. Click Run script to generate the onboarding script.

    Download script

  7. Click Download to download the script to your machine.

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 Azure extension for SQL Server. If the machine itself does not have the Azure connected machine agent installed, the script will install it first, then install Azure extension for SQL Server. Azure connected machine agent will register the connected server as an Azure resource of type Server - Azure Arc, and Azure extension for SQL Server will register the SQL Server instances as Azure resources of type SQL Server - Azure Arc.

Important

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

  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.

Validate your Arc-enabled SQL Server resources

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

Validate connected SQL server

Delete your Arc-enabled SQL Server resource

To delete your Arc-enabled SQL Server resource, go to Azure Arc > SQL Server, open the Arc-enabled SQL Server resource for that instance, and select the Delete button.

Important

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

Restore a deleted Arc-enabled SQL Server resource

If you deleted your Arc-enabled SQL Server resource by mistake, you can restore it with the following steps.

  1. If you also uninstalled the SQL Server extension by mistake, reinstall it. Select the correct version for your OS.
   az connectedmachine extension create --machine-name "{your machine name}" --location "{azure region}" --name "WindowsAgent.SqlServer" --resource-group "{your resource group name}" --type "{OS}Agent.SqlServer" --publisher "Microsoft.AzureData" --settings '{\"SqlManagement\":{\"IsEnabled\":true},  \"excludedSqlInstances\":[]}'

Important

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

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