Use Azure portal to configure an availability group (Preview) for SQL Server on Azure VM
APPLIES TO: 
SQL Server on Azure VM
Tip
Eliminate the need for an Azure Load Balancer for your Always On availability (AG) group by creating your SQL Server VMs in multiple subnets within the same Azure virtual network.
This article describes how to use the Azure portal to configure an availability group for SQL Server on Azure VMs within a single subnet.
Use the Azure portal to create a new cluster or onboard an existing cluster, and then create the availability group, listener, and internal load balancer.
This feature is currently in preview.
While this article uses the Azure portal to configure the availability group environment, it is also possible to do so using PowerShell or the Azure CLI, Azure Quickstart templates, or Manually as well.
Note
It's now possible to lift and shift your availability group solution to SQL Server on Azure VMs using Azure Migrate. See Migrate availability group to learn more.
Prerequisites
To configure an Always On availability group using the Azure portal, you must have the following prerequisites:
- An Azure subscription.
- A resource group with a domain controller.
- One or more domain-joined VMs in Azure running SQL Server 2016 (or later) Enterprise edition in the same availability set or different availability zones that have been registered with the SQL IaaS Agent extension in full manageability mode and are using the same domain account for the SQL Server service on each VM.
- Two available (not used by any entity) IP addresses. One is for the internal load balancer. The other is for the availability group listener within the same subnet as the availability group. If you're using an existing load balancer, you only need one available IP address for the availability group listener.
Permissions
You need the following account permissions to configure the availability group by using the Azure portal:
- An existing domain user account that has Create Computer Object permission in the domain. For example, a domain admin account typically has sufficient permission (for example: account@domain.com). This account should also be part of the local administrator group on each VM to create the cluster.
- The domain user account that controls SQL Server. This should be the same account for every SQL Server VM you intend to add to the availability group.
Configure cluster
Configure the cluster by using the Azure portal. You can either create a new cluster, or if you already have an existing cluster, you can onboard it to the SQL IaaS Agent extension to for portal manageability.
Create a new cluster
If you already have a cluster, skip this section and move to Onboard existing cluster instead.
If you do not already have an existing cluster, create it by using the Azure portal with these steps:
Sign into the Azure portal.
Navigate to your SQL virtual machines resource.
Select High Availability under Settings.
Select + New Windows Server failover cluster to open the Configure Windows Failover cluster page.
Name your cluster and provide a storage account to use as the Cloud Witness. Use an existing storage account or select Create new to create a new storage account. Storage account name must be between 3 and 24 characters in length and use numbers and lower-case letters only.
Expand Windows Server Failover Cluster credentials to provide credentials for the SQL Server service account, as well as the cluster operator and bootstrap accounts if they're different than the account used for the SQL Server service.
Select the SQL Server VMs you want to add to the cluster. Note whether or not a restart is required, and proceed with caution. Only VMs that are registered with the SQL IaaS Agent extension in full manageability mode, and are in the same location, domain, and on the same virtual network as the primary SQL Server VM will be visible.
Select Apply to create the cluster. You can check the status of your deployment in the Activity log which is accessible from the bell icon in the top navigation bar.
For a failover cluster to be supported by Microsoft, it must pass cluster validation. Connect to the VM using your preferred method (such as Remote Desktop Protocol (RDP)) and validate that your cluster passes validation before proceeding further. Failure to do so leaves your cluster in an unsupported state. You can validate the cluster using Failover Cluster Manager (FCM) or the following PowerShell command:
Test-Cluster –Node ("<node1>","<node2>") –Include "Inventory", "Network", "System Configuration"
Onboard existing cluster
If you already have a cluster configured in your SQL Server VM environment, you can onboard it from the Azure portal.
To do so, follow these steps:
Sign into the Azure portal.
Navigate to your SQL virtual machines resource.
Select High Availability under Settings.
Select Onboard existing Windows Server Failover Cluster to open the Onboard Windows Server Failover Cluster page.
Review the settings for your cluster.
Select Apply to onboard your cluster and then select Yes at the prompt to proceed.
Create availability group
After your cluster was either created or onboarded, create the availability group by using the Azure portal. To do so, follow these steps:
Sign into the Azure portal.
Navigate to your SQL virtual machines resource.
Select High Availability under Settings.
Select + New Always On availability group to open the Create availability group page.
Enter a name for the availability group.
Select Configure listener to open the Configure availability group listener page.
Fill out the values, and either use an existing load balancer, or select Create new to create a new load balancer. Select Apply to save your settings and create your listener and load balancer.
Choose + Select replica to open the Configure availability group replicas page.
Select the virtual machines you want to add to the availability group, and choose the availability group settings that best suit your business needs. Select Apply to save your settings.
Verify your availability group settings and then select Apply to create your availability group.
You can check the status of your deployment in the Activity log which is accessible from the bell icon in the top navigation bar.
Note
Your Synchronization health on the High Availability page of the Azure portal will show as Not healthy until you add databases to your availability group.
Add database to availability group
Add your databases to your availability group after deployment completes. The below steps use SQL Server Management Studio (SSMS) but you can use Transact-SQL or PowerShell as well.
To add databases to your availability group using SQL Server Management Studio, follow these steps:
Connect to one of your SQL Server VMs by using your preferred method, such as Remote Desktop Connection (RDP).
Open SQL Server Management Studio (SSMS).
Connect to your SQL Server instance.
Expand Always On High Availability in Object Explorer.
Expand Availability Groups, right-click your availability group and choose to Add database....
Follow the prompts to select the database(s) you want to add to your availability group.
Select OK to save your settings and add your database to the availability group.
After the database is added, refresh Object Explorer to confirm the status of your database as
synchronized.
After databases are added, you can check the status of your availability group in the Azure portal:
Add more VMs
To add more SQL Server VMs to the cluster, follow these steps:
Sign into the Azure portal.
Navigate to your SQL virtual machines resource.
Select High Availability under Settings.
Select Configure Windows Server Failover Cluster to open the Configure Windows Server Failover Cluster page.
Expand Windows Server Failover Cluster credentials and enter in the accounts used for the SQL Server service, cluster operator and cluster bootstrap accounts.
Select the SQL Server VMs you want to add to the cluster.
Select Apply.
You can check the status of your deployment in the Activity log which is accessible from the bell icon in the top navigation bar.
Configure quorum
Although the disk witness is the most resilient quorum option, it requires an Azure shared disk which imposes some limitations to the availability group. As such, the cloud witness is the recommended quorum solution for clusters hosting availability groups for SQL Server on Azure VMs.
If you have an even number of votes in the cluster, configure the quorum solution that best suits your business needs. For more information, see Quorum with SQL Server VMs.
Modify availability group
You can Add more replicas to the availability group, Configure the Listener, or Delete the Listener from the High Availability page in the Azure portal by selecting the ellipses (...) next to your availability group:
Remove cluster
Remove all of the SQL Server VMs from the cluster to destroy it, and then remove the cluster metadata from the SQL IaaS Agent extension. You can do so by using the latest version of the Azure CLI or PowerShell.
First, remove all of the SQL Server VMs from the cluster. This will physically remove the nodes from the cluster, and destroy the cluster:
# Remove the VM from the cluster metadata
# example: az sql vm remove-from-group --name SQLVM2 --resource-group SQLVM-RG
az sql vm remove-from-group --name <VM1 name> --resource-group <resource group name>
az sql vm remove-from-group --name <VM2 name> --resource-group <resource group name>
If these are the only VMs in the cluster, then the cluster will be destroyed. If there are any other VMs in the cluster apart from the SQL Server VMs that were removed, the other VMs will not be removed and the cluster will not be destroyed.
Next, remove the cluster metadata from the SQL IaaS Agent extension:
# Remove the cluster from the SQL VM RP metadata
# example: az sql vm group delete --name Cluster --resource-group SQLVM-RG
az sql vm group delete --name <cluster name> --resource-group <resource group name>
Troubleshooting
If you run into issues, you can check the deployment history, and review the common errors as well as their resolutions.
Check deployment history
Changes to the cluster and availability group via the portal are done through deployments. Deployment history can provide greater detail if there are issues with creating, or onboarding the cluster, or with creating the availability group.
To view the logs for the deployment, and check the deployment history, follow these steps:
Sign into the Azure portal.
Navigate to your resource group.
Select Deployments under Settings.
Select the deployment of interest to learn more about the deployment.
Common errors
Review the following common errors and their resolutions.
The account which is used to start up sql service is not a domain account
This is an indication that the resource provider could not access the SQL Server service with the provided credentials. Some common resolutions:
- Ensure your domain controller is running.
- Validate the credentials provided in the portal match those of the SQL Server service.
Next steps
Once the availability group is deployed, consider optimizing the HADR settings for SQL Server on Azure VMs.
To learn more, see:
Povratne informacije
Pošalјite i prikažite povratne informacije za