Tag: AOAG

Manual Setup of an AlwaysOn Availability Group on SQL Azure Virtual Machines

· Leave a comment ·

Provision two SQL Server VMs.   Be sure to put the VMs on the existing domain’s VNET.  If you don’t have a domain, provision two domain controllers and create one.  Create a domain account to use that has enterprise and domain admin rights.

REMEMBER – the Azure uptime SLA is only applicable if the VMs are in an availability set.  This will prevent updates from occurring on both replicas at the same time and ensure they are run on separate hardware to prevent an outage in case of hardware failure. Also, it is a requirement for them to be in an Availability Set to add them to the Backend Pool of a Load Balancer which is required for an Availability Group Listener.

Join each of the VMs to the domain. This requires a restart – use your domain account to log in after they start back up:
clip_image002

clip_image004

Add the failover clustering feature to each VM:

Create the cluster with only one of the nodes.  UNCHECK THE BOX to add all available storage to the cluster.  Run all the validation tests:

Create a file share on another machine in the network.  The domain controller is a good choice:

Configure the file share witness:

Change the cluster IP address to a free, static ip within the subnet:

Add the other node to the cluster,  UNCHECK THE BOX to add all available storage to the cluster and run all the validation tests:

clip_image034

From SQL Configuration Manager, enable AlwaysOn Availability Groups on each node:

clip_image036

Change the SQL Server service account to your domain account.

Connect to SQL Server using the local account and add your domain account as a login and add it to the Sysadmin role.

Create a share for the database backups on the primary node.

Create a rule in Windows Firewall to open port 5022 on both nodes:

 

Use the wizard to create your availability group but don’t create the listener at this time.

Create an internal load balancer from the azure portal that is in the same resource group as your VMs.

  1. Static frontend ip
  2. Create health probe:  TCP Port: 59999
  3. Create load balancing rule: TCP Port: 1433 Backend port: 1433 Health probe: the one created in step 2.  Floating IP: enable.  Leave everything else at the default.
  4. Create backend pools.  Associate with the availability set of the VMs and the load balancing rule created in step 3.

Create the AlwaysOn Listener using the same ip address as the internal load balancer.

Test connectivity to the listener.  Failover and test again.

Congratulations, you now have an AOAG in Azure.