Sunday, 2 July 2017

SQL Server Failover Clustering in SQL Server 2016 with DNS in Azure

As we know that database mirroring is no longer supported in the latest version of SQL Server i.e. in MS SQL Server 2016.
So people are moving toward SQL Server failover clustering as they want to achieve High Availability of database.
Here we will setup the things in Azure cloud service, you can enjoy your free subscription of Azure cloud for 1 month.

So we can do this in two ways:
1. by using DNS Server which will cost you extra server need more money and maintenance.
2. By using workgroup i.e. without DNS Server this will take less cost in comparison to first one.
Best way to do implement SQL Server failover clustering

Its all up to your requirement if you have low budget then go without DNS and if have sufficient budget then go with DNS Server extra.
Both ways have their own pro and cons so we can't rigid with one way.
Steps by Steps to create a SQL Server Always On Availability Group on Azure Virtual Machines.

Create resource group

1)     Sign in to the Azure portal.
2)     Click + to create a new object in the portal.
3)     Type resource group in the Marketplace search window.
4)     Click Resource group.
5)     Click Create.

6)     In the Resource group blade, under Resource group name type a name for the resource group.  

For example, type SQLTEST-RG-WestUs


7)     If you have multiple Azure subscriptions, verify that the subscription is the Azure      subscription you want to create the availability group in.
8)     Select a location. The location is the Azure region where you want to create the availability group. For this tutorial, we are going to build all resources in one Azure location.
9)     Verify Pin to dashboard is checked. This optional setting places a shortcut for the resource group on the Azure portal dashboard.
10)     Click Create to create the resource group.

Create availability sets:

      Before creating virtual machines, you need to create availability sets. Availability sets reduce downtime for planned or unplanned maintenance events. An Azure availability set is a logical group of resources that Azure places on physical fault domains and update domains. A fault domain ensures that the members of the availability set have separate power and network resources. An update domain ensures that members of the availability set are not brought down for maintenance at the same time.  To create an availability set, go to the resource group and click Add. Filter the results by typing Availability Set. Click Availability Set in the results. Click Create.
       After you create the availability sets, return to the resource group in the Azure portal and create three SQL Virtual Machine (2 SQL+ 1 DNS)
       It will take few times to create all three machines and then do following steps:

1. Make one server as DNS server and to configure DNS please follow following steps:

Install DNS Role first by using Server Manager you will get link to install new role and feature.
You need to configure  new DNS server if you don't have already Follow this link to set up DNS