Using Azure Policy to onboard multiple SQL Servers at scale to Azure Arc-enabled SQL Server - Part 1

The demand for deploying agents in bulk on on-premise SQL servers is increasing, and manual deployment of the agent in bulk is a highly time-consuming task.

Azure Arc-enabled SQL Server extends Azure services to SQL Server instances hosted outside of Azure: in your data center, in edge site locations like retail stores, or any public cloud or hosting provider. It's worth noting that the SQL Server instance that you intend to enable with Azure Arc can be installed on a virtual or physical machine running either Windows or Linux.

To onboard a Azure Arc-enabled SQL server, If your SQL Server instance is not yet connected to Azure, you can initiate the connection process from the target machine by using the onboarding script. This script connects the server to Azure and installs the Azure extension specifically for SQL Server. Assuming that your server is already connected to Azure, you can proceed directly to the section titled "When the machine is already connected to an Arc-enabled Server."

You can refer to the table below to compare each method and decide which one suits your deployment needs best. For more in-depth information, click on the links to view the steps for each topic.

If you're planning to onboard hundreds of instances, it's recommended to scale the Arc extension installation either through Azure policy or with a PowerShell script remotely. However, using Azure policy would be a much easier approach for managing a large number of instances, compared to manually executing a PowerShell script. Using Azure Policy to install the SQL Server extension at scale on Arc-enabled SQL Server instances can be an efficient and effective solution, particularly for large environments with many servers to manage. When the policy is applied to a group of servers, it will automatically trigger the installation of the SQL Server extension on each server in the group. This can save a significant amount of time and effort compared to manually installing the extension on each server individually.

In this particular blog post, I covered on how to simplify onboarding multiple SQL Servers to Azure Arc-enabled SQL Server at scale using Azure policy.

Prerequisites

• Have an Azure account with an active subscription. If needed, create a free Azure Account
  
• Verify Arc connected machine agent prerequisites. The Arc agent must be running in the typical 'full' mode.
  
• Verify Arc connected machine agent network requirements
  
• Register resource providers. Specifically:
  
  • Microsoft.AzureArcData
    
  • Microsoft.HybridCompute
    

  [*]For instructions, see Register resource providers.

  [*]To Connect SQL Servers on Azure Arc-enabled servers at scale using Azure policy for you to create an Azure Policy assignment, your subscription requires the Resource Policy Contributor role assignment for the scope that you're targeting. The scope may be either subscription or resource group.

Use Azure Policy to connect multiple SQL Servers at Scale

1. Navigate to the ArcSql-Levelup resource group, and then select the "Policies" option as depicted in the screenshot below. It's worth mentioning that policies can be enabled at either subscription or management group level, but for the purpose of this lab, we will enable them at the resource group level.
   

1. Go to Assignments and click on Assign policy.
   

1. Click on ellipsis against Policy definition field to select and assign policy.
   

1. Enter "SQL Server extension" in the search field to search for SQL Server extension and assign policy, select “Configure Arc-enabled machines running SQL Server to have SQL Server extension installed” policy and click Add.
   

• You have the option to automatically connect SQL Server instances on multiple Arc-enabled machines by utilizing an Azure policy definition called "Configure Arc-enabled machines running SQL Server to have SQL Server extension installed."
  
• By default, this policy definition isn't assigned to any specific scope. However, once you assign this policy definition to a chosen scope, it installs the Azure extension for SQL Server on all Azure Arc-enabled servers where SQL Server is installed. Once the extension is installed, it connects the SQL Server instances on the machine with Azure and continuously runs to detect changes to the SQL Server configuration and synchronize them with Azure.
  

1. Leave the policy name suggested by Azure portal and make sure policy is enabled and click on Next.
   

1. Click on Next again until you are in Remediation tab as shown in the screenshot below and select “Create a remediation task” check box to address non-compliance of SQL Servers. Review all the parameters highlighted and make sure they match in your policy definition and click Next.
   

1. Enter resource non-compliant message as shown below or chose your own message and click “Review + Create”.
   

8. Review all the policy information and click Create.

1. Once policy is created you will see this policy under Policy assignments as shown in the screenshot below.
   

1. Click on Remediation under Policy Authoring and then select Remediation tasks tab as shown the in the screenshot below to view Remediation task status. This will take some time to assess Arc-enabled servers for SQL Server extension status and remediate by installing SQL Server extension.
   

11. Allow sometime to apply remediation and check status by clicking Refresh.

1. Click anywhere on the remediation task to view remediated resources.
   

1. Click on one of the servers to view SQL Server extension installed on the Arc-enabled server resource.
   

1. Go to ArcSql-Levelup resource group and verify Arc-enabled SQL Server resources are created.
   

Summary:

In this blog post, I covered on how to simplify Onboarding multiple SQL Servers to Azure Arc-enabled SQL Server at scale using Azure policy. Azure Policy can certainly be an efficient way to install the SQL Server extension at scale on Arc-enabled SQL Server instances.

In case you're interested in learning more about deployment options for Azure Arc enabled SQL Server, you can refer to the following Microsoft Docs article.

Continue reading...