Arc SQL Server - How to setup

Costa Rica

brown9804

Last updated: 2025-07-17

Wiki

Table of Wiki (Click to expand)

Content

Table of Content (Click to expand)

Wiki
Content
How to setup Azure Arc on your on-premises SQL Server
How to setup Azure Arc on Azure SQL Managed Instance
How to setup Azure Arc for SQL Server Instances on Azure Virtual Machines VMs
Recommended Trainings

How to setup Azure Arc on your on-premises SQL Server

Centralized Management and Monitoring: All environments (on-premises and other cloud providers) are integrated into Azure using Azure Arc by:

Azure Policy: Apply governance and compliance policies consistently across all your environments.

Azure Monitor: Track the performance and health of your resources, whether they're on-premises, in other clouds, or in Azure.

Log Analytics Workspace: Collect and analyze log data from all your resources to gain insights and troubleshoot issues.
Azure Sentinel: Enhance security by using Azure Sentinel for SIEM (security information and event management), which helps detect, investigate, and respond to security threats across your entire infrastructure.

Prerequisite	Description
Azure Account	Ensure you have an active Azure subscription.
SQL Server	Make sure your SQL Server instance is running and you have administrative access. Supported versions include SQL Server 2012 and later.
Network	Open necessary firewall ports to allow communication with Azure services. Ensure outbound connectivity to the Azure Arc Data Processing Service on port 443.
Proxy	If using a proxy server, set the `NO_PROXY` environment variable to exclude proxy traffic for `localhost` and `127.0.0.1`.
Resource Providers	Register the `Microsoft.AzureArcData` and `Microsoft.HybridCompute` resource providers in your Azure subscription. - Go to Subscriptions. - Select your subscription - Under Settings, select Resource providers. - Search for `Microsoft.AzureArcData` and `Microsoft.HybridCompute`, click Register.

Step 1: Install the Azure Arc Agent
1. Download the Agent: Go to the Azure Arc page and download the Azure Arc agent installer.
2. Install the Agent:
  - The user or service principal must have read permission on the subscription and local administrator permission on the operating system to install and configure the Arc agent.
  - For SQL Server, the service account must be a member of the sysadmin fixed server role on each SQL Server instance.
  - Run the installer on your SQL Server machine. Follow the prompts to complete the installation.
  - Verify that the Arc connected machine agent is installed and running in full mode.
Step 2: Connect SQL Server to Azure Arc
1. Generate Onboarding Script:
  - In the Azure portal, navigate to Azure Arc > SQL Server > + Add.
  - Follow the wizard to generate an onboarding script. This script will connect your SQL Server instance to Azure Arc.
2. Run the Script: Execute the script on your SQL Server machine. This will install the necessary extensions and connect your SQL Server instance to Azure Arc.
Step 3: Validate the Connection
1. Check in Azure Portal:
  - Go to the Azure portal and navigate to Azure Arc > SQL Server.
  - Ensure your SQL Server instance appears in the list of connected servers.
Step 4: Configure and Manage
1. Configure Settings: Use the Azure portal to configure settings, apply policies, and manage your SQL Server instance.
  - Azure Monitor: Use Azure Monitor to track performance and events for systems running in Azure, on-premises, or in other clouds.
  - Azure Policy: Implement Azure Policy guest configuration to audit operating systems and machine configurations.
  - Azure Log Analytics: Utilize Azure Log Analytics for data analysis and visualization.
2. Monitor and Optimize: Utilize Azure tools to monitor performance, apply updates, and optimize configurations.
  - Activate Defender for Cloud: Once your machines are connected, activate Microsoft Defender for Cloud to monitor and secure your on-premises workloads.
  - Microsoft Sentinel: Use Microsoft Sentinel for intelligent security analytics and threat intelligence.

How to setup Azure Arc on Azure SQL Managed Instance

Prerequisite	Description
Azure Subscription	Ensure you have an active Azure subscription.
Azure Data Studio	Install Azure Data Studio with the Azure Arc extension.
Azure CLI	Install the Azure CLI with the `arcdata` extension.
Kubernetes Cluster	Ensure you have a Kubernetes cluster where the SQL Managed Instance will be deployed.
Azure Arc Data Controller	Set up an Azure Arc Data Controller in your Kubernetes cluster.

Step 1: Configure the providers required within the subscription:

Resource Providers: Register the Microsoft.AzureArcData, Microsoft.ExtendedLocation, Microsoft.KubernetesConfiguration and Microsoft.Kubernetes resource providers in your Azure subscription.

Go to Subscriptions.

Select your subscription.

Under Settings, select Resource providers.

Search for Microsoft.AzureArcData, Microsoft.ExtendedLocation, Microsoft.KubernetesConfiguration and Microsoft.Kubernetes, click Register.

Step 2: AKS cluster needs to meet certain requirements

Category	Details
Kubernetes Version	Ensure your AKS cluster is running a supported version of Kubernetes. Azure Arc-enabled data services typically support the last three stable versions of Kubernetes.
Node Configuration	- Node Size: Use nodes with sufficient CPU and memory resources. For production workloads, it's recommended to use at least Standard_D4s_v3 or equivalent. - Node Count: Have a minimum of three nodes to ensure high availability and redundancy.
Storage	- Persistent Storage: Configure persistent storage for your AKS cluster. Azure Disk or Azure Files are commonly used for this purpose. - Storage Class: Ensure you have a default storage class set up in your cluster.
Networking	- Network Policies: Implement network policies to control traffic between pods and services. - Load Balancer: Ensure your cluster has a load balancer configured for external access.
RBAC	Enable RBAC in your AKS cluster to manage permissions and access control.
Azure Arc Extensions	Install the necessary Azure Arc extensions on your AKS cluster: `az extension add --name connectedk8s` `az extension add --name k8s-extension`
Security	Implement security best practices, such as using Azure Policy for Kubernetes, enabling Azure Defender for Kubernetes, and regularly updating your cluster and nodes.

Step 3: Install the necessary Azure Arc extensions on your AKS cluster:
1. Go to your AKS and run the instructions via Azure CLI:
  - Connect your AKS cluster to Azure Arc: az connectedk8s connect --name <clusterName> --resource-group <resourceGroupName> --location <location> --tags <key1=value1> <key2=value2> --correlation-id <correlationId>
  - Validate is connected:
  - Run the following command in the k8s: az k8s-extension create --name azuremonitor-containers --extension-type Microsoft.AzureMonitor.Containers --scope cluster --cluster-name <clusterName> --resource-group <resourceGroupName> --cluster-type connectedClusters
  - Validate that the extention was added:
  - Run the following command in the k8s: az aks enable-addons --addons monitoring --name <cluster-name> --resource-group <resource-group-name>

Step 4: Create Custom Location:

Install the required Azure CLI extensions: az extension add --name customlocation
Enable the custom locations feature: az connectedk8s enable-features -n <clusterName> -g <resourceGroupName> --features cluster-connect custom-locations

Create a custom location: az customlocation create --name <customLocationName> --resource-group <resourceGroupName> --namespace <namespace> --host-resource-id <hostResourceId> --cluster-extension-ids <extensionId> --location <location>

Parameter	Description
`<customLocationName>`	The name of your custom location.
`<resourceGroupName>`	The name of your resource group.
`<namespace>`	The namespace for the custom location.
`<extensionId>`	The ID of the cluster extension.
`<hostResourceId>`	The host resource ID of your connected cluster.

Get the Cluster Extension IDs: az k8s-extension list --cluster-name <clusterName> --resource-group <resourceGroupName> --cluster-type connectedClusters
Get the Host Resource ID: az connectedk8s show --name <clusterName> --resource-group <resourceGroupName>
If want to explore the a GUI method, you can go to the Azure portal, navigate to Custom locations under Azure Arc.
- Click on + Add and follow the prompts to create a custom location linked to your Kubernetes cluster.
- Validate that the custom location is created, you should see something like this:

Step 5: Set Up Azure Arc Data Controller

The Azure Arc data controller is a key component of Azure Arc-enabled data services. It allows you to run Azure data services on-premises, at the edge, and in multi-cloud environments using Kubernetes.

Feature	Description
Provisioning and Management	Consistent way to provision, manage, and monitor data services across environments.
Elastic Scaling	Scale data services up or down based on demand.
Automated Updates	Ensures data services are always up-to-date with automated updates.
High Availability and Backup	Built-in high availability and backup capabilities for data resilience.
Azure Integration	Integrates with Azure for additional functionalities like monitoring and security.

Understanding Azure Arc data controller types of connection

Feature/Aspect	Directly Connected Mode	Indirectly Connected Mode
Real-time Integration	Yes, real-time integration with Azure services like Azure Monitor, Security Center, etc.	No, periodic synchronization with Azure
Azure Portal Access	Full access, manage and monitor directly from the Azure Portal	Read-only access, view inventory and details but cannot manage directly
Updates	Automatic updates and patches	Manual updates and patches
Use Cases	Ideal for environments with reliable internet connectivity and real-time monitoring needs	Suitable for highly secure environments or those with limited internet connectivity
Kubernetes Requirement	Yes, requires a Kubernetes cluster connected to Azure	No, the Azure Arc data controller can be deployed without the Kubernetes cluster being connected to Azure
Network Requirements	Stable and continuous internet connection	Periodic internet connectivity
Security	Integrated security features	More isolation, manual security management
Management Overhead	Lower, due to automation and real-time management	Higher, due to manual updates and limited Azure Portal access

Enable both features:

az connectedk8s enable-features -n <cluster-name> -g <resource-group> --features cluster-connect custom-locations
Create the Data Controller:

az arcdata dc create --name <data-controller-name> --resource-group <resource-group> --custom-location <custom-location> --connectivity-mode <direct|indirect> --location <location> --k8s-namespace <namespace>
- You can also use the Azure Portal:
- Choose the connection mode:
- Complete all the required information:
Connect SQL Managed Instance to Azure Arc
- Navigate to Azure Arc Data Services: Go to the Azure portal. In the left-hand menu, select Azure Arc > Data services.
- Add SQL Managed Instance:
  - Click on + Add.
  - Select SQL Managed Instance.
  - Choose your Subscription, Resource group, and Custom location.
  - Specify the Name, vCores, Storage, and other settings for your SQL Managed Instance.
  - Click Review + create and then Create.
  - Validate the Connection: Go back to the Azure portal, navigate to Azure Arc > Data services, and ensure your SQL Managed Instance appears in the list of Arc-enabled data services.

Step 6: Manage and Monitor
- Utilize the Azure portal to manage and monitor your SQL Managed Instance.
- You can apply policies, monitor performance, and manage security settings.

How to setup Azure Arc for SQL Server Instances on Azure Virtual Machines (VMs)

Once connected, you can manage your SQL Server instances from the Azure portal. This includes:

Viewing detailed inventory of SQL Server instances.

Running best practices assessments.

Utilizing Microsoft Entra ID (formerly Azure Active Directory) for authentication.

Prerequisite	Description
Azure Subscription	Ensure you have an active Azure subscription.
Azure Data Studio or SSMS	Install Azure Data Studio with the Azure Arc extension or SQL Server Management Studio (SSMS)
Azure CLI	Install the Azure CLI with the `arcdata` extension.

Steps to Create the VM

Sign in to the Azure Portal: Go to the Azure Portal and sign in with your Azure account.
Create a New Virtual Machine:
- In the Azure Portal, select Create a resource.
- Search for SQL Server and select SQL Server 2019 on Windows Server 2019 (or your preferred version).
- Click Create.
Configure Basic Settings:
- Subscription: Select your Azure subscription.
- Resource Group: Create a new resource group or select an existing one.
- Virtual Machine Name: Enter a name for your VM.
- Region: Choose the region closest to your location.
- Image: Ensure the selected image is SQL Server on Windows Server.
- Size: Choose a VM size based on your performance needs.
Administrator Account, and configure public inbound ports:
- Username: Enter a username for the VM.
- Password: Enter a strong password and confirm it.
Disks: Choose the type of disk (Standard SSD, Premium SSD, etc.) based on your performance requirements.
Networking:
- Configure the network settings, including virtual network, subnet, and public IP address.
Management, Monitoring, Advanced: Configure all the necessary settings to ensure optimal performance and security according based on your use case.
SQL Server Settings: Configure SQL Server settings such as SQL authentication mode, SQL connectivity, and storage configuration.
- Ensure the SQL Server port (1433) is open for inbound traffic.
Review + Create: Review all the settings and click Create to deploy the VM.

Post-Deployment Steps

Connect to the VM:
- Once the VM is deployed, go to the Virtual Machines section in the Azure Portal.
- Select your VM and click Connect to download the RDP file.
- Use the RDP file to connect to the VM.
Configure SQL Server:
- After connecting to the VM, open SQL Server Management Studio (SSMS).
- Connect to the SQL Server instance using the credentials you set during the VM creation.
- Configure any additional settings or restore databases as needed.

Steps to Enable Azure Arc

You can perform all these following steps using the Azure Cloud Shell, which is an integrated CLI environment available directly in the Azure portal.

Azure Arc Data Controller: Not required for this scenario.

Azure Connected Machine Agent: Used to onboard the Azure VM to Azure Arc.

Azure Extension for SQL Server: Installed to manage the SQL Server instance through Azure Arc.

Open Azure Cloud Shell:
- Go to the Azure Portal.
- Click on the Cloud Shell icon in the top-right corner (it looks like a command prompt or terminal icon).
Choose Bash or PowerShell: Select either Bash or PowerShell. For these steps, Bash is recommended.
Register the Resource Providers: In the Cloud Shell, run the following commands to register the necessary resource providers.
```
az provider register --namespace Microsoft.AzureArcData
az provider register --namespace Microsoft.HybridCompute
az provider register --namespace Microsoft.GuestConfiguration
az provider show -n Microsoft.HybridCompute
```
You can also install it via the Azure GUI or validate the installation under Resource providers:
Outbound Connectivity: The Azure Arc agent requires outbound connectivity to Azure services over TCP port 443. This is essential for secure communication.

Important

To connect a SQL Server running on an Azure VM to Azure Arc, you don't need to create an Azure Arc Data Controller. You should use the Azure Connected Machine Agent to onboard the VM to Azure Arc and then install the Azure extension for SQL Server.

Install the Azure Connected Machine Agent:
- Download the agent: Go to the Azure Portal, navigate to Azure Arc > Servers > Add.
- Generate the script: Follow the instructions to generate a script that installs the Connected Machine agent on your VM.
- Run the script: Execute the script on your Azure VM to install the agent and connect the VM to Azure Arc.
Install the Azure Extension for SQL Server:
- Once the VM is connected to Azure Arc, the Azure extension for SQL Server can be installed automatically.
- If the extension is not installed automatically, you can manually install it by following the instructions in the Azure Portal under Azure Arc > SQL Server > Add.
Verify the Connection:
- After the extension is installed, verify that the SQL Server instance is registered with Azure Arc.
- Go to Azure Arc > SQL Server in the Azure Portal and check for the newly registered SQL Server instance.
Generate the Onboarding Script: Follow this steps to create the script.

Steps Visual guidance

1

2

3
Run the Onboarding Script:
- Download the generated script from the Azure portal.
- Run the script on the target SQL Server VM. This will connect the SQL Server instance to Azure Arc. Click [here to see an example of the script](
  As mentioned, this script will do the following :
  - Download the agent from the Microsoft Download Center.
  - Install the agent on the server.
  - Create the Azure Arc-enabled server resource and associate it with the agent.
Verify the Connection:
- After running the script, go back to the Azure portal.
- Navigate to Azure Arc > SQL Server.
- Check if your SQL Server instance is listed and verify its status.
  
  Before the setup After the setup