SQL Server AlwaysOn Availability Groups Using an Azure Template


By:   |   Updated: 2017-10-03   |   Comments (1)   |   Related: More > Availability Groups

Problem

Are there any templates on Azure that we can use to quickly set up an Azure AlwaysOn Availability Group for SQL Server? If so, can you show the detailed steps of how to setup a SQL Server AlwaysOn Availability Group on Azure?

Solution

There is already a template on the Azure marketplace for setting up an AlwaysOn Availability Group. In just a few easy steps you can get a working AlwaysOn Availability Group setup, configured and running. This tip describes the steps to quickly set it up.

Search for the template on Azure marketplace

First, log on to the Azure portal: https://portal.azure.com/.

If you don’t have an account already, you can setup a trial https://azure.microsoft.com/en-us/free/ and get a $200 credit for 30 days. Once you login, follow the sequence of steps shown below.

New Microsoft Azure SQL Server AlwaysOn Cluster

Once you select “SQL Server AlwaysOn Cluster”, you will get this screenshot below. Click on “Create”.

Microsoft SQL Server AlwaysOn Cluster on Azure

Begin the SQL Azure Configuration Options for AlwaysOn

Just read the description in the section above of the SQL AlwaysOn configuration to get an understanding of the Azure architecture. Once you click on “Create”, it will take you to the next screen where you begin to specify the Basic configuration options.

Basic Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

For the Basic configuration screen specify the values as shown and change accordingly as per your requirements. I am already using an existing resource group. You may create a new resource group if you require.

Enter the following items and once done, click “OK”.

  • Administrator User Name
  • Password
  • Subscription
  • Resource Group
  • Location
Configure the Basic SQL Azure Configuration Options for AlwaysOn

Domain and Network Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

For the Domain and Network Settings Configuration screen you will get a set of default values. You may retain the default values or modify if you require. Here, I left the default values and clicked on “OK”.

The following properties are configured at this step:

  • Forest root domain name
  • Virtual Network name
  • Domain Controller subnet name
  • SQL Server subnet name
Configure Domain and Network Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

Availability Group Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

For the Availability Group Settings you will setup the Availability group name, Availability group listener name and Availability group listener port.

Configure the Availability Group Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

VM Size and Storage Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

On the Virtual Machine Size and Storage screen you will get a default set of values to configure the following properties:

  • SQL Server virtual machine size
  • Domain controller virtual machine
  • File Share Witness virtual machine
  • SQL Storage account - where you can specify the storage account as shown below
  • SQL Server data disk size in TB
  • Storage optimization

You will get a set of default values. It's recommended to leave the virtual machines size to the defaults as the template automatically chooses the virtual machine size as per best practice recommendations for performance, security and availability. For the “SQL Storage Account”, enter the required details as per your requirement. For storage optimization, you may leave it to “General workload”. Once done, click “OK”.

Configure the Availability Group Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

SQL Server Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

On the SQL Server Settings screen the following parameters are configured:

  • SQL Server Name Prefix
  • SQL Server version
  • SQL Server service account username and password
  • SQL Auto Patching maintenance schedule
  • SQL Auto Patching maintenance start hour

I changed the “SQL Server Name Prefix” from default to “TestAGServer”. I also changed the “SQL Server version” to “SQL Server 2016 Developer” as I am using this setup only for test purposes. The other SQL Server versions available are as shown below.

SQL Server versions available for Azure

You may leave the maintenance schedule as per the defaults.  Once done, click “OK”.

SQL Server Settings for Microsoft SQL Server AlwaysOn Cluster on Azure

Summary for Microsoft SQL Server AlwaysOn Cluster on Azure

For the Summary section, review all the specifications and once you are satisfied with the configuration click “OK”.

Summary for Microsoft SQL Server AlwaysOn Cluster on Azure

Purchase the Microsoft SQL Server AlwaysOn Cluster on Azure Solution

On the Buy screen, review the terms of use, accept the terms and click on “Purchase”.  Once selected, the deployment of the configuration will start. This process will take around an hour to complete.

Purchase the Microsoft SQL Server AlwaysOn Cluster on Azure Solution

Once the SQL Server AlwaysOn Cluster on Azure is complete, you will see a notification like the below image:

SQL Azure Successfully Completed

Validate the Microsoft SQL Server AlwaysOn Cluster on Azure Solution

Once the deployment is complete, it is time to validate the configuration and begin working with your new solution.  First, in Azure go to the SQL Server to check the AlwaysOn configuration and click on sqlserver-0 and use RDP to connect to the server. Once done, you will be able to review the AlwaysOn configuration.

Validate the Microsoft SQL Server AlwaysOn Cluster on Azure Solution

You can use SQL Server Management Studio to review the configuration as well.

Review SQL Azure AlwaysOn Configuration
Next Steps
  • With this Azure template, you are able to easily configure AlwaysOn Availability Groups on Azure Virtual machines in under an hour.
  • Try the steps in this tip using your own Azure subscription.
  • To get familiar with Azure, refer the numerous tips available at this link.
  • To get familiar with SQL Server Availability Groups, refer to the numerous tips available at this link.


Last Updated: 2017-10-03


get scripts

next tip button



About the author
MSSQLTips author Mohammed Moinudheen Mohammed Moinudheen is a SQL Server DBA with over 6 years experience managing production databases for a few Fortune 500 companies.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, December 13, 2019 - 6:58:15 PM - laura Back To Top
Tip Comments Pending Approval


download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools