SIOS SANless clusters

SIOS SANless clusters High-availability Machine Learning monitoring

  • Home
  • Products
    • SIOS DataKeeper for Windows
    • SIOS Protection Suite for Linux
  • News and Events
  • Clustering Simplified
  • Success Stories
  • Contact Us
  • English
  • 中文 (中国)
  • 中文 (台灣)
  • 한국어
  • Bahasa Indonesia
  • ไทย

On-Demand Webinar: Voice of Experience: SQL Expert Shares 5 Best Practices for SQL Server HA in the Cloud

May 18, 2019 by Jason Aw Leave a Comment

Voice of Experience SQL Expert Shares 5 Best Practices for SQL Server High Availability in the Cloud

On-Demand Webinar: Voice of Experience: SQL Expert Shares 5 Best Practices for SQL Server High Availability in the Cloud

Everyone is moving to the cloud! But are you thinking about high availability?

In this webinar, Heath Carroll discusses some of the best high availability options available to you when it comes to protecting Microsoft SQL Server in a cloud environment. Heath reviews templates you should get familiar with as well as tips to get it right, every time.

Register Webinar: Voice of Experience: SQL Expert Shares 5 Best Practices for SQL Server HA in the Cloud

Filed Under: News and Events Tagged With: Azure Cloud, HA clusters-cloud, High Availability, SQL Server Failover Cluster

A Guide To Configure A SQL Server Failover Cluster Instance in Azure

March 31, 2019 by Jason Aw Leave a Comment

Step-By-Step: How To Configure A SQL Server 2008 R2 Failover Cluster Instance in Azure

If you need a guide Configure A SQL Server Failover Cluster Instance in Azure, you probably are still using SQL Server 2008/2008 R2. And, want to take advantage of the extended security updates that Microsoft is offering if you move your SQL Server 2008/2008 R2 into Azure. I previously wrote about this topic in this blog post.

You may be wondering how to make sure your SQL Server Failover Cluster instance remains highly available once you make the move to Azure. Today, most people have business critical SQL Server 2008/2008 R2 configured as a clustered instance (SQL Server FCI) in their data center. When looking at Azure you have probably come to the realization that due to the lack of shared storage it might seem that you can’t bring your SQL Server FCI to the Azure cloud. However, that is not the case thanks to SIOS DataKeeper.

SIOS DataKeeper enables you to build a SQL Server Failover Cluster instance in Azure, AWS, Google Cloud, or anywhere else where shared storage is not available or where you wish to configure multi-site clusters where shared storage doesn’t make sense. DataKeeper has been enabling SANless clusters for Windows and Linux since 1999. Microsoft documents the use of SIOS DataKeeper for SQL Server Failover Cluster instance in their documentation: High availability and disaster recovery for SQL Server in Azure Virtual Machines.

I’ve written about SQL Server FCI’s running in Azure before, But I never published a Step-by-Step Guide specific to SQL Server 2008/2008 R2. The good news is that it works just as great with SQL 2008/2008 R2 as it does with SQL 2012/2014/2016/2017 and the soon to be released 2019. Also, regardless of the version of Windows Server (2008/2012/2016/2019) or SQL Server (2008/2012/2014/2016/2017) the configuration process is similar enough that this guide should be sufficient enough to get you through any configurations.

If your flavor of SQL or Windows is not covered in any of my guides, don’t be afraid to jump in and build a SQL Server FCI and reference this guide, I think you will figure out any differences and if you ever get stuck just reach out to me on Twitter @daveberm and I’ll be glad to give you a hand.

This guide uses SQL Server 2008 R2 with Windows Server 2012 R2. As of the time of this writing I did not see an Azure Marketplace image of SQL 2008 R2 on Windows Server 2012 R2, so I had to download and install SQL 2008 R2 manually. Personally I prefer this combination, but if you need to use Windows Server 2008 R2 or Windows 212 that is fine. If you use Windows Server 2008 R2 don’t forget to install the kb3125574Convenience Rollup Update for Windows Server 2008 R2 SP1. Or if you are stuck with Server 2012 (not R2) you need the Hotfix in kb2854082.

Don’t be fooled by this article that says you must install kb2854082 on your SQL Server 2008 R2 instances. If you start searching for that update for Windows Server 2008 R2 you will find that only the version for Server 2012 is available. That particular hotfix for Server 2008 R2 is instead included in the rollup Convenience Rollup Update for Windows Server 2008 R2 SP1.

PROVISION AZURE INSTANCES

I’m not going to go into great detail here with a bunch of screenshots, especially since the Azure Portal UI tends to change pretty frequently, so any screenshots I take will get stale pretty quickly. Instead, I will just cover the important topics that you should be aware of.

FAULT DOMAINS OR AVAILABILITY ZONES?

In order to ensure your SQL Server instances are highly available, you have to make sure your cluster nodes reside in different Fault Domains (FD) or in different Availability Zones (AZ). Not only do your instances need to reside in different FDs or AZs, but your File Share Witness (see below) also needs to reside in a FD or AZ that is different than that one your cluster nodes reside in.

Here is my take on it. AZs are the newest Azure feature, but they are only supported in a handful of regions so far. AZs give you a higher SLA (99.99%) then FDs (99.95%), and protect you against the kind of cloud outages I describe in my post Azure Outage Post-Mortem. If you can deploy in a region that supports AZs then I recommend you use AZs.

In this guide I used AZs which you will see when you get to the section on configuring the load balancer. However, if you use FDs everything will be exactly the same, except the load balancer configuration will reference Availability Sets rather than Availability Zones.

WHAT IS A FILE SHARE WITNESS YOU ASK?

Without going into great detail, Windows Server Failover Clustering (WSFC) requires you configure a “Witness” to ensure failover behaves properly. Windows Server Failover Clustering supports three kinds of witnesses: Disk, File Share, Cloud. Since we are in Azure a Disk Witness is not possible. Cloud Witness is only available with Windows Server 2016 and later, so that leaves us with a File Share Witness. If you want to learn more about cluster quorums check out my post on the Microsoft Press Blog, From the MVPs: Understanding the Windows Server Failover Cluster Quorum in Windows Server 2012 R2

ADD STORAGE TO YOUR SQL SERVER INSTANCES

As you provision your SQL Server instances you will want to add additional disks to each instance. Minimally you will need one disk for the SQL Data and Log file, one disk for Tempdb. Whether or not you should have a separate disk for log and data files is somewhat debated when running in the cloud. On the back end the storage all comes from the same place and your instance size limits your total IOPS. In my opinion there really isn’t any value in separating your log and data files since you cannot ensure that they are running on two physical sets of disks. I’ll leave that for you to decide, but I put log and data all on the same volume.

Normally a SQL Server 2008 R2 FCI would require you to put tempdb on a clustered disk. However, SIOS DataKeeper has this really nifty feature called a DataKeeper Non-Mirrored Volume Resource. This guide does not cover moving tempdb to this non-mirrored volume resource, but for optimal performance you should do this. There really is no good reason to replicate tempdb since it is recreated upon failover anyway.

As far as the storage is concerned you can use any storage type, but certainly use Managed Disks whenever possible. Make sure each node in the cluster has the identical storage configuration. Once you launch the instances you will want to attach these disks and format them NTFS. Make sure each instance uses the same drive letters.

NETWORKING

It’s not a hard requirement, but if at all possible use an instance size that supports accelerated networking. Also, make sure you edit the network interface in the Azure portal so that your instances use a static IP address. For clustering to work properly you want to make sure you update the settings for the DNS server so that it points to your Windows AD/DNS server and not just some public DNS server.

SECURITY

By default, the communications between nodes in the same virtual network are wide open, but if you have locked down your Azure Security Group you will need to know what ports must be open between the cluster nodes and adjust your security group. In my experience, almost all the issues you will encounter when building a cluster in Azure are either caused by blocked ports.

DataKeeper has some some ports that are required to be open between the clustered instance. Those ports are as follows:
UDP: 137, 138
TCP: 139, 445, 9999, plus ports in the 10000 to 10025 range

Failover cluster has its own set of port requirements that I won’t even attempt to document here. This article seems to have that covered. http://dsfnet.blogspot.com/2013/04/windows-server-clustering-sql-server.html

In addition, the Load Balancer described later will use a probe port that must allow inbound traffic on each node. The port that is commonly used and described in this guide is 59999.

And finally if you want your clients to be able to reach your SQL Server instance you want to make sure your SQL Server port is open, which by default is 1433.

Remember, these ports can be blocked by the Windows Firewall or Azure Security Groups, so to be sure to check both to ensure they are accessible.

JOIN THE DOMAIN

A requirement for SQL Server 2008 R2 FCI is that the instances must reside in the same Windows Server Domain. So if you have not done so, make sure you have joined the instances to your Windows domain

LOCAL SERVICE ACCOUNT

When you install DataKeeper, it will ask you to provide a service account. You must create a domain user account and then add that user account to the Local Administrators Group on each node. When asked during the DataKeeper installation, specify that account as the DataKeeper service account. Note – Don’t install DataKeeper just yet!

DOMAIN GLOBAL SECURITY GROUPS

You will be asked to specify two Global Domain Security Groups as you install SQL 2008 R2. You might want to look ahead at the SQL install instructions and create those groups now. Also, create a domain user account and place them in each of these security accounts. You will specify this account as part of the SQL Server Cluster installation.

OTHER PRE-REQUISITES

You must enable both Failover Clustering and .Net 3.5 on each instance of the two cluster instances. When you enable Failover Clustering, also be sure to enable the optional “Failover Cluster Automation Server”. This is required for a SQL Server 2008 R2 cluster in Windows Server 2012 R2.

CREATE THE CLUSTER AND DATAKEEPER VOLUME RESOURCES

We are now ready to start building the cluster. The first step is to create the base cluster. Because of the way Azure handles DHCP, we MUST create the cluster using Powershell and not the Cluster UI. We use Powershell because it will let us specify a static IP address as part of the creation process. If we used the UI, it would see that the VMs use DHCP and it will automatically assign a duplicate IP address. Therefore to avoid that situation, let’s use the Powershell as shown below.

New-Cluster -Name cluster1 -Node sql1,sql2 -StaticAddress 10.0.0.100 -NoStorage

After the cluster creates, run Test-Cluster. This is required before SQL Server will install.

Test-Cluster

You will get warnings about Storage and Networking. Thankfully, you can ignore those as they are expected in a SANless cluster in Azure. However, address any other warnings or errors before moving on.

After the cluster is created, you will need to add the File Share Witness. On the third server we specified as the file share witness, create a file share and give Read/Write permissions to the cluster computer object we just created above. In this case $Cluster1 will be the name of the computer object that needs Read/Write permissions at both the share and NTFS security level.

Once the share is created, you can use the Configure Cluster Quorum Wizard as shown below to configure the File Share Witness.

INSTALL DATAKEEPER

It is important to wait until the basic cluster is created before we install DataKeeper, since the DataKeeper installation registers the DataKeeper Volume Resource type in failover clustering. If you jumped the gun and installed DataKeeper already that is okay. Simply run the setup again and choose Repair Installation.

The screenshots below walk you through a basic installation. Start by running the DataKeeper Setup.

The account you specify below must be a domain account. It must be part of the Local Administrators group on each of the cluster nodes.

When presented with the SIOS License Key manager you can browse out to your temporary key. Or if you have a permanent key, you can copy the System Host ID and use that to request your permanent license. If you ever need to refresh a key, the SIOS License Key Manager is a program that will be installed that you can run separately to add a new key.

CREATE DATAKEEPER VOLUME RESOURCE

Once DataKeeper is installed on each node you are ready to create your first DataKeeper Volume Resource. The first step is to open the DataKeeper UI and connect to each of the cluster nodes.

If everything is done correctly the Server Overview Report should look something like this.

You can now create your first Job as shown below.

After you choose a Source and Target you are presented with the following options. For a local target in the same region, the only thing you need to select is Synchronous.

Choose Yes and auto-register this volume as a cluster resource.

Once you complete this process open up the Failover Cluster Manager and look in Disk. You should see the DataKeeper Volume resource in Available Storage. At this point WSFC treats this as if it were a normal cluster disk resource.

SLIPSTREAM SP3 ONTO SQL 2008 R2 INSTALL MEDIA

SQL Server 2008 R2 is only supported on Windows Server 2012 R2 with SQL Server SP2 or later. Unfortunately, Microsoft never released a SQL Server 2008 R2 installation media that that includes SP2 or SP3. Instead, you must slipstream the service pack onto the installation media BEFORE you do the installation. If you try to do the installation with the standard SQL Server 2008 R2 media, you will run into all kinds of problems. I don’t remember the exact errors you will see. But I do recall they didn’t really point to the exact problem. You will waste a lot of time trying to figure out what went wrong.

As of the date of this writing, Microsoft does not have a Windows Server 2012 R2 with SQL Server 2008 R2 offering in the Azure Marketplace. Do bring your own SQL license if you want to run SQL 2008 R2 on Windows Server 2012 R2 in Azure. If they add that image later, or if you choose to use the SQL 2008 R2 on Windows Server 2008 R2 image, you must first uninstall the existing standalone instance of SQL Server before moving forward.

I followed the guidance in Option 1 of this article to slipstream SP3 on onto my SQL 2008 R2 installation media. You will of course have to adjust a few things as this article references SP2 instead of SP3. Make sure you slipstream SP3 on the installation media we will use for both nodes of the cluster. Once that is done, continue to the next step.

INSTALL SQL SERVER ON THE FIRST NODE

Using the SQL Server 2008 R2 media with SP3 slipstreamed, run setup and install the first node of the cluster as shown below.

If you use anything other than the Default instance of SQL Server, you will have some additional steps not covered in this guide. The biggest difference is you must lock down the port that SQL Server uses since by default a named instance of SQL Server does NOT use 1433. Once you lock down the port you also need to specify that port instead of 1433 whenever we reference port 1433 in this guide, including the firewall setting and the Load Balancer settings.

Here make sure to specify a new IP address that is not in use. This is the same IP address we will use later when we configure the Internal Load Balancer later.

As I mentioned earlier, SQL Server 2008 R2 utilizes AD Security Groups. If you have not already created them, go ahead and create them now as show below before you continue to the next step in the SQL install

Specify the Security Groups you created earlier.

Make sure the service accounts you specify are a member of the associated Security Group.

Specify your SQL Server administrators here.

If everything goes well you are now ready to install SQL Server on the second node of the cluster.

INSTALL SQL SERVER ON THE SECOND NODE

One the second node, run the SQL Server 2008 R2 with SP3 install and select Add Node to a SQL Server Failover Clustering Instance.

Proceed with the installation as shown in the following screenshots.

Assuming everything went well, you should now have a two node SQL Server 2008 R2 cluster configured that looks something like the following.

However, you probably will notice that you can only connect to the SQL Server instance from the active cluster node. The problem is that Azure does not support gratuitous ARP .Your clients probably cannot connect directly to the Cluster IP Address. Instead, the clients must connect to an Azure Load Balancer, which will redirect the connection to the active node. To make this work there are two steps: Create the Load Balancer and Fix the SQL Server Cluster IP to respond to the Load Balancer Probe and use a 255.255.255.255 Subnet mask. Those steps are described below.

CREATE THE AZURE LOAD BALANCER

I’m going to assume your clients can communicate directly to the internal IP address of the SQL cluster. Let’s go ahead to create an Internal Load Balancer (ILB) in this guide. If you need to expose your SQL Instance on the public internet, use a Public Load Balancer instead.

In the Azure portal, create a new Load Balancer following the screenshots as shown below. The Azure portal UI changes rapidly. Bbut these screenshots should give you enough information to do what you need to do. I will call out important settings as we go along.

Here we create the ILB. The important thing to note on this screen is you must select “Static IP address assignment”. Specify the same IP address that we used during the SQL Cluster installation too.

Since I used Availability Zones, I see Zone Redundant as an option. If you used Availability Sets your experience will be slightly different.

In the Backend pool be sure to select the two SQL Server instances. You DO NOT want to add your File Share Witness in the pool.

Here we configure the Health Probe. Most Azure documentation uses port 59999, so we will stick with that port for our configuration.

Then we will add a load balancing rule. In our case we want to redirect all SQL Server traffic to TCP port 1433 of the active node. It is also important that you select Floating IP (Direct Server Return) as Enabled.

RUN POWERSHELL SCRIPT TO UPDATE SQL CLIENT ACCESS POINT

Now we must run a Powershell script on one of the cluster nodes to allow the Load Balancer Probe to detect which node is active. The script also sets the Subnet Mask of the SQL Cluster IP Address to 255.255.255.255.255 so that it avoids IP address conflicts with the Load Balancer we just created.

# Define variables
$ClusterNetworkName = “” 
# the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of 
higher to find the name)
$IPResourceName = “” 
# the IP Address resource name 
$ILBIP = “” 
# the IP Address of the Internal Load Balancer (ILB) and SQL Cluster
Import-Module FailoverClusters
# If you are using Windows Server 2012 or higher:
Get-ClusterResource $IPResourceName | Set-ClusterParameter 
-Multiple @{Address=$ILBIP;ProbePort=59999;SubnetMask="255.255.255.255";
Network=$ClusterNetworkName;EnableDhcp=0}
# If you are using Windows Server 2008 R2 use this: 
#cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  
subnetmask=255.255.255.255

This is what the output will look like if run correctly.

windows server failover cluster

You probably notice that the end of that script has a commented line of code to use if you are running on Windows Server 2008 R2. Running Windows Server 2008 R2? Ensure you run the code specific for Windows Server 2008 R2 at a Command prompt, it is not Powershell.

NEXT STEPS

You’re not the first if  you get to this point and you still cannot connect to the cluster remotely. There are a lot of things that can go wrong in terms of security, load balancer, SQL ports, etc. I wrote this guide to help troubleshoot connection issues.

In fact, I ran into some strange issues in terms of my SQL Server TCP/IP Properties in SQL Server Configuration Manager. When I looked at the properties I did not see the SQL Server Cluster IP address as one of the addresses it was listening on. As such I had to add it manually. I’m not sure if that was an anomaly. Although it certainly was an issue I had to resolve before I could connect to the cluster from a remote client.

As I mentioned earlier, one other improvement you can make to this installation is to use a DataKeeper Non-Mirrored Volume Resource for TempDB. If you set that up please be aware of the following two configuration issues people commonly run into.

The first issue is if you move tempdb to a folder on the 1st node, you must be sure to create the exact same folder structure on the second node. If you don’t do that, when you try to failover SQL Server will fail to come online since it can’t create TempDB.

The second issue occurs anytime you add another DataKeeper Volume Resource to a SQL Cluster after the cluster is created. You must go into the properties of the SQL Server cluster resource and make it dependent on the new DataKeeper Volume resource you added. This is true for the TempDB volume and any other volumes you may decide to add after the cluster is created.

If you have any questions about this configuration or any other cluster configurations please feel free to reach out to me on Twitter @DaveBerm

Reproduced with permission from Clusteringformeremortals.com

Filed Under: Clustering Simplified, Datakeeper Tagged With: failover cluster, SQL Server, SQL Server Failover Cluster, Windows Server Failover Clustering

Azure ILB In ARM For SQL Server Failover Cluster Instances

June 15, 2018 by Jason Aw Leave a Comment

Configuring The #AZURE ILB In ARM For SQL Server Failover Cluster Instance Or AG Using AZURE Powershell 1.0

Configuring The #AZURE ILB In ARM For SQL Server Failover Cluster Instance Or AG Using AZURE Powershell 1.0

In an earlier post I went into some great detail about how to configure the Azure ILB in ARM for SQL Server Failover Cluster or AG resources. The directions in that article were written prior to the GA of Azure PowerShell 1.0. With the availability of Azure PowerShell 1.0 the main script that creates the ILB needs to be slightly different. The rest of the article is still accurate. However if you are using Azure PowerShell 1.0 or later the script to create the ILB described in that article should be as follows.

#Replace the values for the below listed variables
$ResourceGroupName ='SIOS-EAST' # Resource Group Name in which the SQL nodes are deployed
$FrontEndConfigurationName = 'FEEAST' #You can provide any name to this parameter.
$BackendConfiguratioName = 'BEEAST' #You can provide any name to this parameter.
$LoadBalancerName = 'ILBEAST' #Provide a Name for the Internal Local balance object
$Location ='eastus2' # Input the data center location of the SQL Deployements
$subname = 'public' # Provide the Subnet name in which the SQL Nodes are placed
$ILBIP = '10.0.0.201' # Provide the IP address for the Listener or Load Balancer
$subnet = Get-AzureRMVirtualNetwork -ResourceGroupName $ResourceGroupName | 
Get-AzureRMVirtualNetworkSubnetConfig –name $subname
$FEConfig=New-AzureRMLoadBalancerFrontendIpConfig -Name $FrontEndConfigurationName 
-PrivateIpAddress $ILBIP -SubnetId $subnet.Id
$BackendConfig=New-AzureRMLoadBalancerBackendAddressPoolConfig 
-Name $BackendConfiguratioName
New-AzureRMLoadBalancer -Name $LoadBalancerName -ResourceGroupName $ResourceGroupName 
-Location $Location -FrontendIpConfiguration $FEConfig 
-BackendAddressPool $BackendConfig

The rest of that original article is the same, but I have just copied it here for ease of use…

Using GUI

Now that the ILB is created, we should see it in the Azure Portal in Resource Group. See pic below.

Azure ILB In ARM For SQL Server Failover Cluster Instances

The rest of the configuration can also be completed through PowerShell, but I’m going to use the GUI in my example.

If you want to use PowerShell, you could probably piece together the script by looking at this article. Unfortunately, this article confuses me. I’ll figure it out some day and try to document it in a user friendly format. As of now, I think the GUI is fine for the next steps.

Let’s Get Started

Follow along with the screen shots below. If you get lost, follow the navigation hints at the top of the Azure Portal to figure out where we are.

First Step

  • Click Backend Pool setting tab. Selects the backend pool to update the Availability Set and Virtual Machines. Save your changes.

Azure ILB In ARM For SQL Server Failover Cluster Instances

  • Configure Load Balancer’s Probe by clicking Add on the Probe tab. Give the probe a name and configure it to use TCP Port 59999. I have left the probe interval and the unhealthy threshold set to the default settings. This means it will take 10 seconds before the ILB removes the passive node from the list of active nodes after a failover. Your clients may take up to 10 seconds to be redirected to the new active node. Be sure to save your changes.

Azure ILB In ARM For SQL Server Failover Cluster Instances

Next Step

  • Navigate to the Load Balancing Rule Tab and add a new rule. Give the rule a sensible name (SQL1433 or something). Choose TCP protocol port 1433 (assuming you are using the default instance of SQL Server). Choose 1433 for the Backend port as well. For the Backend Pool, we will choose the Backend Pool we created earlier (BE). For the Probe that we will also choose the Probe we created earlier.

We do not want to enable Session persistence but we do want to enable Floating IP (Direct Server Return). I have left the idle timeout set to the default setting. You might want to consider increasing that to the maximum value. Reason is that I have seen some applications such as SAP log error messages each time the connection is dropped and needs to be re-established.

Azure ILB In ARM For SQL Server Failover Cluster Instances

  • At this point the ILB is configured. There is only one final step that needs to take place for SQL Server Failover Cluster. We need to update the SQL IP Cluster Resource just the exact same way we had to in the Classic deployment model. To do that you will need to run the following PowerShell script on just one of the cluster nodes. Make note, SubnetMask=“255.255.255.255” is not a mistake. Use the 32 bit mask regardless of what your actual subnet mask is.

One Final Note

In my initial test I still was not able to connect to the SQL Resource name even after I completed all of the above steps. After banging my head against the wall for a few hours I discovered that for some reason the SQL Cluster Name Resource was not registered in DNS. I’m not sure how that happened or whether it will happen consistently, but if you are having trouble connecting I would definitely check DNS and add the SQL cluster name and IP address as a new A record if it is not already in there.

And of course don’t forget the good ole Windows Firewall. You will have to make exceptions for 1433 and 59999 or just turn it off until you get everything configured properly like I did. You probably want to leverage Azure Network Security Groups anyway instead of the local Windows Firewall for a more unified experience across all your Azure resources.

Good luck and let me know how you make out.

Head over here to see how SIOS helped companies across the globe with creating SQL Server Failover Cluster.

Reproduced with permission from Clustering For Mere Mortals.

Filed Under: Clustering Simplified Tagged With: SQL Server Failover Cluster

Why Build A SQL server Failover Cluster Instance In Azure Cloud?

March 13, 2018 by Jason Aw Leave a Comment

Why Would You Want To Build A SQL server Failover Cluster Instance In The Azure Cloud?

There was an interesting discussion happening today in the Twitterverse. Basically, someone asked the question “Has anyone set up a SQL server Failover Cluster Instance in Azure?” The ensuing conversation involved some well respect SQL Server experts. And it led to the following question, “Why would you want to build a SQL Server AlwaysOn Failover Cluster instance in the cloud?”

That question could be interpreted in two ways: “Why do you need High Availability in the Cloud” or “Why wouldn’t you use AlwaysOn Availability Groups instead of Failover Cluster Instances?”

Let’s address each question one at a time.

Question 1 – Why do you need High Availability in the Azure Cloud?

  • You might think that just because you host your SQL Server instance in Azure, that you are covered by their 99.95% uptime SLA. If you think that, you would be wrong. In order to take advantage of the 99.95% SLA, you have to have at least two instances of SQL running in an Availability Set. With a single instance of SQL running, you can definitely expect that there will minimally be downtime during maintenance periods. But, you are also susceptible to unplanned failures.
  • Two instances of SQL Server cannot generally be load balanced. You have to implement some sort of mechanism to keep the servers in sync. To ensure that if there is a problem with one of the servers, the other server will be able to continue to service the requests. High Availability solutions like AlwaysOn Availability Groups, AlwaysOn Failover Cluster Instances and even the deprecated Database Mirroring can provide high availability for SQL Server in that scenario. Other solutions like log shipping and transactional replication may be able to help keep data synchronized between servers. But they are not typically considered high availability solutions and will not ensure the availability of your SQL Server.
  • Microsoft does occasionally need to perform maintenance on Azure that could bring down an entire Upgrade Domain and all the instances running in that Upgrade Domain. You don’t have any say on when this will happen. So, you need to have a mechanism in place to ensure that if they do have to bring down your primary SQL Server instance, you can expect that your secondary SQL Server instance will take over the workload without missing a beat. All of the high availability solutions mentioned above can ensure that you will continue to run in the event that Microsoft is doing maintenance on the Upgrade Domain of your primary server. Microsoft will only do maintenance on a single Upgrade Domain at a time. This ensures that your secondary server will still be online assuming you put the both in the same Availability Set.
  • What do you do if YOU want to performance maintenance on your production SQL Server? Maybe you want to install a Service Pack or other hotfix? Without a secondary server to fail over to, you will have to schedule planned downtime. One of the primary benefits of any high availability solution is the ability to do rolling upgrades, minimizing the impact of planned downtime.

Question 2 – Why wouldn’t you use AlwaysOn Availability Groups instead of Failover Cluster Instances?

  • Save Money! SQL Server AlwaysOn Availability Groups requires Enterprise Edition of SQL Server. Why not save money and deploy SQL Server Standard Edition and build a simple 2-node Failover Cluster Instance? Unless you need Enterprise Edition for some other reason, this is a no brainer.
  • Protect the ENTIRE SQL Server instance. AlwaysOn Availability Groups only protects user defined databases; you cannot protect the System and MSDB databases. If you build a SQL Server Failover Cluster Instance instead, you are protecting the ENTIRE instance, including the System and MSDB databases.
  • Ease Administration. In Azure, you are limited to just on client listener. This limits you to just one Availability Group. In contrast, with a Failover Cluster Instance one client listener is all you need, so there is no limitation.
  • Worker Thread Exhaustion. With AlwaysOn AG, you have to keep an eye on the available worker threads. The available worker threads limit the number of databases you can protect with AlwaysOn AG. In contrast, AlwaysOn Failover Clustering with DataKeeper block level replication does not consume more resources for each database you add. This means you can scale to protect hundreds of databases without the additional overhead associated with AlwaysOn AG.
  • Distribute Transaction Support. AlwaysOn AG does not support distributed transactions (DTC). If your application requires DTC support, you are going to have to look at an AlwaysOn Failover Cluster Instance instead.
  • Support of Other Replication Technologies. If you plan on setting up Peer to Peer replication between two databases protected by AlwaysOn AG you can forget about it. In fact, there are many restrictions you have to be aware of once you deploy AlwaysOn Availability Groups. AlwaysOn FCI’s do not have any of those restrictions.

Conclusion

Knowing what you know above, shouldn’t the question really be “Why would I want to implement AlwaysOn AG in the Cloud when I can have a much more robust and inexpensive solution building an AlwaysOn Failover Cluster instance?”

If you are interested in building an AlwaysOn Failover Cluster Instance in Azure, check out my blog post Step-by-Step: How to configure a SQL Server Failover Cluster Instance (FCI) in Microsoft Azure IaaS #SQLServer #Azure #SANLess

Reproduced with permission from https://clusteringformeremortals.com/2015/03/05/why-would-you-want-to-build-a-sqlserver-failover-cluster-instance-in-the-azure-cloud/

Filed Under: Clustering Simplified Tagged With: failover cluster, High Availability, SQL Server Failover Cluster, SQL Server Failover Cluster Instance

Configure SQL server failover cluster instance In Microsoft Azure IAAS

March 9, 2018 by Jason Aw Leave a Comment

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

7/19/2016 Update – The steps below describe a deployment in Azure “Classic”. If you are deploying a SQL Cluster in Azure Resource Manager (ARM) then you should see my article here. https://clusteringformeremortals.com/2016/04/23/deploying-microsoft-sql-server-2014-failover-clusters-in-azure-resource-manager-arm/

Before we begin, we are going to make some assumptions that you are at least slightly familiar with failover clustering and Microsoft Azure and have already signed up for an Azure account. Throughout this Step-by-Step guide, we will refer to additional resources for additional reading. Included in this guide are screen shots and code examples. Azure is a rapidly developing product, so your experience may be different than that described. But you should be able to adapt and adjust as needed. I will attempt to keep this article up to date my adding additional comments as time progresses. The new Azure Portal is still in the Preview stage as of the writing of this article. Therefore, we will use the currently supported portal along with PowerShell in all of our examples.

At a high level, these are the following steps that need to be taken in order to create a highly available SQL Server deployment on Azure IaaS. If you already have a functioning domain in Azure IaaS you can skip items 1-3.

We will take a closer look at each of these steps below.

  • Create your Virtual Network
  • Create A Cloud Service
  • Create Storage Account
  • Create your Azure VMs and Storage
  • Configure Active Directory
  • Create a Cluster
  • Create Replicated Cluster Volume Resources
  • Install SQL into the Cluster
  • Create an Internal Load Balancer
  • Update the Client Listener

Overview

These instructions assume you want to create a highly available SQL Server deployment entirely within one Azure region. It is entirely possible to configure SQL Server clusters that span different geographic regions within Azure, or even Hybrid Cloud configurations that span from on premise to the Azure Cloud or visa-versa. It is not my intent to cover those types of configurations in this document. Instead, the configuration I will focus on the configuration is illustrated in in Figure 1.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS
Figure 1 – SQL Server Failover Cluster in Azure

This article will describe how to create a cluster that spans two different Fault Domains and Update Domains within an Azure region. Spanning different Fault Domains eliminates downtime associated with Unplanned Downtime. Spanning different Update Domains eliminates failures associated with Planned Downtime.

For additional overview information, you may want to watch the webinar I did on SQLTIPS that discusses this topic in detail. It can be viewed at http://www.mssqltips.com/sql-server-video/360/highly-available-sql-server-cluster-deployments-in-azure-iaas/

Create Your Virtual Network

In order for this to work, you will need to have all of your VMs within a Virtual Network. Creating a Virtual Network is pretty straight forward. The screen shots below should help guide you through the process.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

At this point I like to add the Google DNS Server address of 8.8.8.8. I have experienced weird connectivity issues when trying to download updates from Microsoft when using their default DNS servers. After we have downloaded all of the updates that these servers need we will come back and replace the DNS server IP address with the IP address of our AD controller. But for now, add 8.8.8.8 and all of your VMs provisioned in this Virtual Network will receive this as a DNS server via the DHCP service. This forum post describes the problem I have experienced without adding this DNS server entry. Before adding all of your servers to the domain I have found that you need to delete this 8.8.8.8 address and replace it with the IP address of the first domain controller that you create.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You will see that I created one subnet in this Virtual Network and labeled it Public. Later on when we create our VMs we will use the Public network. While Azure recently added support for multiple NICs per VM, I have found that adding multiple subnets and NICs to an Azure VM can be problematic. The main problem is that each NIC is automatically assigned a Gateway address, which can causes routing problems due to multiple Gateways being defined on the same server.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

It will take a few minutes for your Virtual Network to be created.

Create A Cloud Service

Your VMs will all reside in the same “Cloud Service”. Good luck finding the definition of an Azure “Cloud Service”, since Azure overall is a “Cloud Service”. However, this is a very specific think specific to Azure IaaS that you need to create before you start deploying VMs. The screen shots below will walk you through the process.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Make sure you put the Cloud Service in the same Region as your Virtual Network.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Create Storage Account

Before you begin provisioning VMs you must create a Storage Account. Follow the steps below to create a storage account.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Make sure you create a Storage Account in the same Location as your Virtual Network

Create Your Azure VMS And Storage

If you have not downloaded and installed Azure PowerShell yet, do that now. Also, make sure you set your default subscription and CurrentStorageAccountName.

We will start with provisioning the first VM which will become the Domain Controller (DC). In our example, we will also use the DC as a file share witness, so we will create an Availability Set that will include the Domain Controller and the two nodes in the cluster. The following is an example script which will create the VM and assign it a “Static Address”.

$AVSet=”SQLHA”

$InstanceSize=”Large”

$VMName=”DC1″

$AdminName=” myadminaccount”

$AdminPassword=”mypassword”

$PrimarySubnet=”Public”

$PrimaryIP=”10.0.0.100″

$CloudService=”SQLFailover”

$VirtualNetwork=”Azure-East”

$ImageName=”a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-201412.01-en.us-127GB.vhd”

$image = Get-AzureVMImage -ImageName $ImageName

$vm = New-AzureVMConfig -Name $VMName -InstanceSize $InstanceSize -Image $image.ImageName –AvailabilitySetName $AVSet

Add-AzureProvisioningConfig –VM $vm -Windows -AdminUserName $AdminName -Password $AdminPassword

Set-AzureSubnet -SubnetNames $PrimarySubnet -VM $vm

Set-AzureStaticVNetIP -IPAddress $PrimaryIP -VM $vm

New-AzureVM -ServiceName $CloudService –VNetName $VirtualNetwork –VM $vm

Tech Note – I say “Static IP Address”, but it really just creates a DHCP “Request”. I call it a DHCP “Request” and not “Reservation” because it really is only a best effort request. If this server is offline and someone starts a new server, the DHCP server could hand out this address to someone else, making it unavailable when this server is turned on.

Once you create your 1st VM you are ready to create the two SQL VMs used in the cluster. You will see that I tried to make the script easy to use by allowing you to specify the different variables. I highlighted the variables you need to change for each VM.

$AVSet=”SQLHA”

$InstanceSize=”Large”

$VMName=”SQL1″

$AdminName=”myadminaccount”

$AdminPassword=”P@55w0rd”

$PrimarySubnet=”Public”

$PrimaryIP=”10.0.0.101″

$CloudService=”SQLFailover”

$VirtualNetwork=”Azure-East”

$ImageName=”a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-201412.01-en.us-127GB.vhd”

$image = Get-AzureVMImage -ImageName $ImageName

$vm = New-AzureVMConfig -Name $VMName -InstanceSize $InstanceSize -Image $image.ImageName –AvailabilitySetName $AVSet

Add-AzureProvisioningConfig –VM $vm -Windows -AdminUserName $AdminName -Password $AdminPassword

Set-AzureSubnet -SubnetNames $PrimarySubnet -VM $vm

Set-AzureStaticVNetIP -IPAddress $PrimaryIP -VM $vm

New-AzureVM -ServiceName $CloudService –VNetName $VirtualNetwork –VM $vm

Run the script once again to provision the 2nd cluster node

$AVSet=”SQLHA”

$InstanceSize=”Large”

$VMName=”SQL2″

$AdminName=” myadminaccount”

$AdminPassword=”mypassword”

$PrimarySubnet=”Public”

$PrimaryIP=”10.0.0.102″

$CloudService=”SQLFailover”

$VirtualNetwork=”Azure-East”

$ImageName=”a699494373c04fc0bc8f2bb1389d6106__Windows-Server-2012-R2-201412.01-en.us-127GB.vhd”

$image = Get-AzureVMImage -ImageName $ImageName

$vm = New-AzureVMConfig -Name $VMName -InstanceSize $InstanceSize -Image $image.ImageName –AvailabilitySetName $AVSet

Add-AzureProvisioningConfig –VM $vm -Windows -AdminUserName $AdminName -Password $AdminPassword

Set-AzureSubnet -SubnetNames $PrimarySubnet -VM $vm

Set-AzureStaticVNetIP -IPAddress $PrimaryIP -VM $vm

New-AzureVM -ServiceName $CloudService –VNetName $VirtualNetwork –VM $vm

You see that each of these VMs are all placed in the same Availability Set, which I called “SQLHA”. By placing the VMs in the same Availability Set you take advantage of Fault Domains and Update Domains as described here. http://blogs.technet.com/b/yungchou/archive/2011/05/16/window-azure-fault-domain-and-update-domain-explained-for-it-pros.aspx

Once you have created your VMs, your Azure Portal should look like this.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Words Of Wisdom About Fault Domains

Fault Domains are a great concept; however Microsoft dropped the ball by not guaranteeing (As of Jan 2015) that you will always get three fault domains per Availability Set. In fact, most of the time I only get two Fault Domains. If you wind up with just two Fault Domains you will want to consider putting your File Share Witness in a different region just to be 100% sure that you don’t have a majority of your cluster votes sitting in the same rack. Once Windows Server 10 is GA this will no longer be a problem as you will be able to use a Cloud Witness instead of a File Share Witness. If you would like to see three Fault Domains be the standard, follow this link and VOTE for that idea on Azure idea website.

Configure Active Directory

First we will connect to DC1 via RDP and enable active directory. Use the “Connect” button to download the RDP connection to DC1. Use the username and password that you specified when you created your Azure VM. Promote DC1 to a Domain Controller.

INSIDER TIP – I HAVE ALSO FOUND THAT DNS RESOLUTION WORKS BEST IF YOU REMOVE ALL DNS FORWARDERS ON THE DNS SERVER AND JUST USE ROOT HINTS. AZURE CAN SOMETIME HAVE PROBLEM RESOLVING MICROSOFT WEB PROPERTIES IF YOU USE THEIR DNS SERVERS ARE FORWARDERS.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS
Figure 2 – Remove all forwarders for reliable name resolution

Create A Cluster

Once you have configured DC1 as a Domain Controller, you will connect to SQL1 and SQL2 and add them to the domain. However, before you do that, you will need to change the DNS Server of the Virtual Network to that of the DC1 Server (10.0.0.100) and reboot both SQL1 and SQL2. Once SQL1 and SQL2 have 100.0.0.100 as their DNS Server you will be able to join the domain.

Once you are joined to the domain you will have to complete steps illustrated below to create a SQL Server Failover Cluster Instance (FCI).

First, enable .Net 3.5 Framework on each node.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

If you find that .Net Framework cannot be installed, refer to my tip about DNS.

Enable Failover Cluster

Now that .Net 3.5 is enabled, you will then need to enable the Failover Cluster Feature on both SQL1 and SQL2.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Validation

Once the cluster feature is enabled, you will need to create the cluster. The first step is to run cluster Validation. I am assuming you are familiar with clustering, so I will just post a few of the screen shots and make note of things you need to consider.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

The Validation will complete, but there will be some warnings. Most of the warnings will be around storage. You can ignore those as we will be using replicated storage rather than a shared disk. Also, you may get a warning about the network. You can also ignore that warning as we know that Azure has network redundancy built in at the physical layer.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Create Cluster Access Point

11/24/2015 UPDATE – I have found that creating a cluster via Powershell avoids all this issues described in the GUI steps show below because you can specify the IP Address of the Cluster as part of the creation process. The two PowerShell commands below replace all the steps show in the GUI screen shots that follow in this section. Make sure the StaticIaddress parameter

Test-Cluster –Node Server1, Server2

New-Cluster –Name MyCluster –Node Server1, Server2 –StaticAddress 10.0.0.200

If you ran the Powershell Script above then you can skip the rest of this section and jump right to the next section on creating the file share witness.

I would advise creating the Click Finish to start the cluster creation process. First choose a name for the cluster.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You will see that there are some warnings if you click View Report. You can ignore the warning as we will be creating a File Share Witness.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You may get the following message after the cluster creates. “The action ‘Validate Configuration…’ did not complete.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Fix Cluster Access Point IP Address

The underlying issue here is that the cluster is not resolving the cluster name properly due to an IP address conflict. What you will find is that Azure DHCP actually gives out a duplicate IP address to the cluster computer object that you just created. This is just one of the weird Azure idiosyncrasies that you will have to deal with as shown below.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You may need to open the Failover Cluster GUI on SQL2 in order to connect. Once you are able to connect to the cluster you will see that the cluster grabbed the same IP address as one of the cluster nodes. This of course causes IP address conflicts.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

What we need to do is change the 10.0.0.102 IP address to another IP address not used in this subnet.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You will see I picked 10.0.0.200 as my address. This address is NOT reserved in the DHCP scope as there is currently no way to control the DHCP scope or add reservations. I just pick an address at the upper end of the DHCP scope and make sure that I don’t provision enough VMs within this subnet to ever reach that IP address.

Now that the Cluster IP address is fixed you will be able to connect to the cluster using Failover Cluster Manager from either node.

Create File Share Witness

Next we will create a File Share Witness for the cluster quorum. For a complete description of cluster quorums read my blog post on MSDN press, http://blogs.msdn.com/b/microsoft_press/archive/2014/04/28/from-the-mvps-understanding-the-windows-server-failover-cluster-quorum-in-windows-server-2012-r2.aspx

The file share witness will be created on the Domain Controller. Essentially you need to create a file share on DC1 and give read/write permissions to the cluster computer account “sioscluster”. Make sure to make these changes to both the Share and Security permissions as shown below.

The following steps are done on DC1.

Create a new folder.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Make sure you search for Computer objects and pick the cluster computer object name, in our case, SIOSCLUSTER

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Make sure you give it Change permissions.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You also need to change the Security to allow the cluster computer object Modify permissions on the folder.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Once you create the shared folder, you will add the File Share Witness using the Windows Server Failover Cluster interface on either of the nodes as shown below.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Install DataKeeper

DataKeeper Cluster Edition from SIOS Technology is needed in order to provide the replication and cluster integration that will allow you to build a failover cluster instance without shared storage. First, you will install DataKeeper Cluster Edition on both of the nodes of your cluster. Run through the setup as shown below.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

For demonstration purposes I used the domain administrator account. The only requirement is that the user account used is in the local administrator group in each server.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Create A DataKeeper Volume Resource

After you install the software on each cluster node (SQL1 and SQL2) you are ready to create you first replicated volume resource. Launch the DataKeeper GUI on either node and follow the steps below to create a DataKeeper Volume Resource.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

After you connect to both servers, click on the Server Overview Report. It should look like the following.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You’ll notice that you are connected to both servers, but there are no volumes listed. Next we will need to add additional storage to each cluster node. Do this through the Azure portal as shown below.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

After you have added the additional volume to each VM and created a formatted partition, you DataKeeper GUI should look like this.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You are now ready to launch the Create a Job Wizard and create the DataKeeper Volume resource as shown below.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Create the job and give it a name and optional description.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Install SQL Into The Cluster

Now that you have the cluster configured and a DataKeeper Volume in Available Storage, you are ready to begin the SQL Server Cluster Installation. This process is exactly the same as if you were installing a SQL Server Failover Cluster Instance using shared storage. Since the DataKeeper Replicated Volume resource is a Storage Class resource, failover clustering treats it like a Physical Disk resource. Follow the steps pictured below to install SQL Server into the cluster.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

You can use SQL Server 2014 Standard Edition to build a 2-node Failover Cluster. In this scenario DataKeeper can also replicate Data to a 3rd node, but that node cannot be part of the cluster. If you want to create a 3+ node cluster you will need to use SQL Server 2014 Enterprise Edition. Earlier versions of SQL work perfectly fine as well. I have tested SQL 2008 through SQL 2014.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Before clicking Next, click on the Data Directories tab.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Once SQL is installed on the first node, you will then need to run the installation on the second node.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Create An Internal Load Balancer

Once the cluster is configured, you will need to create the internal load balancer(ILB) which will be used for all client access. Clients that connect to SQL Server will need to connect to the ILB instead of connecting directly to the cluster IP address. If you were to try to connect to the cluster directly at this point you would see that you cannot connect to the cluster from any remote system. Even SQL Server Management Studio will not be able to connect to the cluster directly at this point.

Run this Powershell Command from your local desktop to create your Internal Load Balancer (ILB).

# Define variables

$IP = “10.0.0.201” # IP address you want your Internal Load Balancer to use, this should be the same address as your SQL Server Cluster IP Address

$svc=”SQLFailover” # The name of your cloud service

$vmname1=”sql1″ #The name of the VM that is your first cluster node

$epname1=”sql1″ #This is the name you want to assign to the endpoint associated with first cluster node, use anything you like

$vmname2=”sql2″ #The name of the VM that is your second cluster node

$epname2=”sql2″ #This is the name you want to assign to the endpoint associated with second cluster node, use anything you like

$lbsetname=”ilbsetsqlha” #use whatever name you like, this name is insignificant

$prot=”tcp”

$locport=1433

$pubport=1433

$probeport=59999

$ilbname=”sqlcluster” #this is the name your clients connect to, it should coincide with you SQL cluster Name Resource

$subnetname=”Public” #the name of the Azure subnet where you want the internal load balancer to live

# Add Internal Load Balancer to the service

Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ilbname -SubnetName $subnetname -ServiceName $svc –StaticVNetIPAddress $IP

# Add load balanced endpoint to the primary cluster node

Get-AzureVM -ServiceName $svc -Name $vmname1 | Add-AzureEndpoint -Name $epname1 -LBSetName $lbsetname -Protocol $prot -LocalPort $locport -PublicPort $pubport -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

# Add load balanced endpoint to the secondary cluster node

Get-AzureVM -ServiceName $svc -Name $vmname2 | Add-AzureEndpoint -Name $epname2 -LBSetName $lbsetname -Protocol $prot -LocalPort $locport -PublicPort $pubport -ProbePort $probeport -ProbeProtocol tcp -ProbeIntervalInSeconds 10 –DirectServerReturn $true -InternalLoadBalancerName $ilbname | Update-AzureVM

Assuming the script ran as planned, you should see the following output.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

 

Update The Client Listener

Once the internal load balancer is created we will need to run a Powershell script on SQL1 to update the SQL Server Cluster IP address. The script references the Cluster Network name and the IP Resource Name. The pictures below show you were to find both of these names in Failover Cluster Manager.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

The script below should be run on one of the cluster nodes. Make sure to launch Powershell ISE using Run as Administrator.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

# This script should be run on the primary cluster node after the internal load balancer is created

# Define variables

$ClusterNetworkName = “Cluster Network 1” # the cluster network name

$IPResourceName = “SQL IP Address 1 (sqlcluster)” # the IP Address resource name

$CloudServiceIP = “10.0.0.201” # IP address of your Internal Load Balancer

Import-Module FailoverClusters

# If you are using Windows 2012 or higher, use the Get-Cluster Resource command. If you are using Windows 2008 R2, use the cluster res command which is commented out.

Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{“Address”=”$CloudServiceIP”;”ProbePort”=”59999″;
SubnetMask=”255.255.255.255″;”Network”=”$ClusterNetworkName”;
”OverrideAddressMatch”=1;”EnableDhcp”=0}

# cluster res $IPResourceName /priv enabledhcp=0 overrideaddressmatch=1 address=$CloudServiceIP probeport=59999 subnetmask=255.255.255.255

Assuming your script ran as expected, the output should look like below. You see that in order for the changes to be applied, you will need to bring your cluster resource offline once and then bring it online.

Step-By-Step: How To Configure A SQL Server Failover Cluster Instance (FCI) In Microsoft Azure IaaS

Firewall

Open TCP port 59999, 1433 and 1434 are open on the firewall of each server.

Summary

Now that the cluster is created you can connect to the SQL Failover Cluster Instance via the Internal Load Balancer, using the name sqlcluster or directly to 10.0.0.201.

Find such articles about SQL server failover cluster instance useful, do read our other blog posts.

Reproduced with permission from Clusteringformeremortal

 

Filed Under: Clustering Simplified Tagged With: cluster, SQL Server, SQL Server Failover Cluster, SQL Server Failover Cluster Instance

  • 1
  • 2
  • Next Page »

Recent Posts

  • Should I Still Use Zabbix In AWS?
  • How To Choose A Cloud When You Need High Availability
  • How To Clone Availability In The Cloud With Better Outcomes
  • New Product Release: SIOS Protection Suite for Linux 9.5.1
  • Six Reasons Your Cloud Migration Has Stalled

Most Popular Posts

Maximise replication performance for Linux Clustering with Fusion-io
Failover Clustering with VMware High Availability
create A 2-Node MySQL Cluster Without Shared Storage
create A 2-Node MySQL Cluster Without Shared Storage
SAP for High Availability Solutions For Linux
Bandwidth To Support Real-Time Replication
The Availability Equation – High Availability Solutions.jpg
Choosing Platforms To Replicate Data - Host-Based Or Storage-Based?
Guide To Connect To An iSCSI Target Using Open-iSCSI Initiator Software
Best Practices to Eliminate SPoF In Cluster Architecture
Step-By-Step How To Configure A Linux Failover Cluster In Microsoft Azure IaaS Without Shared Storage azure sanless
Take Action Before SQL Server 20082008 R2 Support Expires
How To Cluster MaxDB On Windows In The Cloud

Join Our Mailing List

Copyright © 2021 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in