High Availability Archives - Page 47 of 52

Windows Azure High Availability Options For SQL Server

February 18, 2018 by Jason Aw Leave a Comment

Downtime! Who Should Bear The Responsibility?

Ensuring High Availability Options For SQL Server all the time is probably a main reason we make use of Cloud Services. However, protecting against downtime associated with cloud outages is something that anyone deploying on ANY cloud service needs to address. It is easy to simply deploy your app in “the cloud” and assume that it is someone else’s problem to manage now. Cloud providers probably have more resources and expertise to ensure your servers stay up. But the ultimate responsibility to ensure that your critical application is available rests squarely on your shoulders.

High Availability Options For SQL Server Isn’t As Easy As ABC

Believe it or not, simply deploying your SQL Server in Windows Azure does not make it “highly available”. To make it highly available, you must use traditional tools and techniques that you might use in your own datacenter. While there is some varying of opinion on this topic, I believe that High Availability Options For SQL Server 2012/2014 are as follows:

Regardless of which option you choose, you are going to want to become familiar with the Windows Azure Fault Domain as described below:

“Nonetheless, in Windows Azure a rack of computers is indeed identified as a fault domain. And the allocation of a fault domain is determined by Windows Azure at deployment time. A service owner cannot control the allocation of a fault domain, however can programmatically find out which fault domain a service is running within. Windows Azure Compute service SLA guarantees the level of connectivity uptime for a deployed service only if two or more instances of each role of a service are deployed”

Have your SQL Server reside in different Fault Domains

As you start deploying Windows Azure VMs, ensure each SQL Server and any “witness” servers reside in different Fault Domains. You do this by putting all of the VMs in the same “Availability Set”. Essentially, each server in the same Availability Set resides in a different Fault Domain, hopefully eliminating failures.

Windows Azure High Availability Options For SQL Server — VMs in the same Availability Set provisioned in different Fault Domains

Put all VMs in different Fault Domains and configure a SQL Server Failover Cluster or Availability Group to protect against the usual types of outages that might be localized to a single rack of servers, AKA, Fault Domain. I’ve written a step-by-step article entitled Creating a SQL Server 2014 AlwaysOn Failover Cluster (FCI) Instance in Windows Azure IaaS with DataKeeper which should help in your endeavor to build resiliency within the Azure cloud for your SQL Server.

But What Happens If Windows Azure Has A Major Outage That Takes Out A Whole Region?

Natural disaster or human error would likely be the cause of such an outage. Unfortunately, at this point there is no way to stretch an Azure Virtual Private Network between two different Azure Regions. This includes Southeast Asia. However, the Azure Virtual Private Network can support a site-to-site VPN connection with a limited number of VPN devices. These devices are from Cisco, Juniper and even Microsoft RRAS.

How About Somewhere Outside Of Azure?

That leads us to thinking about alternate locations outside of Azure, even our own private data center. I recently wrote a step-by-step article that explains how to extend your on premise datacenter to the Azure Cloud. Connect datacenter to Windows Azure, configure AlwaysOn Availability Groups or AlwaysOn Failover Clustering (multisite) for protection from catastrophic Azure failures. I’ve written previously about the Advantages of Multisite Clustering vs. Availability Groups. So in my lab I decided to create a 2-node SQL Failover Cluster Instance up in Azure and then add a 3^rd node in my primary data center. I’ve written the detailed configuration steps in my blog post entitled Creating a Multisite Cluster in Windows Azure for Disaster Recovery.

If you rather use AlwaysOn Availability Groups, you probably want to visit the tutorials called AlwaysOn Availability Groups in Windows Azure (GUI) and Listener Configuration for AlwaysOn Availability Groups in Windows Azure. If you are using SQL 2008 R2 or earlier I’m sure you could configure database mirroring. At this point if your are moving to Azure, I’m assuming you are probably deploying SQL Server 2012 or 2014. Other technology like Log Shipping and Replication are options for moving data, but I don’t consider them high availability solutions.

Reproduced with permission from https://clusteringformeremortals.com/2014/01/15/windows-azure-high-availability-options-for-sql-server-azure-cloud-iaas/

Create Multi-Site Cluster In Windows Azure For DR With DataKeeper

February 15, 2018 by Jason Aw Leave a Comment

Create Multi-Site Cluster In Windows Azure For DR With DataKeeper

This is the 4^th post in my series on High Availability and Disaster Recovery for Windows Azure with SIOS DataKeeper Cluster Edition. This is a step-by-step post on the processes to create multi-site cluster. It builds upon the Azure configuration that we built during my first three articles…

We are now going to extend the existing cluster (SQL1 and SQL2) to your local data center, SQL3. This configuration will give you both high availability for your application within the Azure Cloud, as well as a disaster recovery solution should Azure suffer a major outage. You could configure this in reverse as well with your on premise datacenter as your primary site and use Windows Azure as your disaster recovery site. And of course this solution illustrates SQL Server as the application, but any cluster aware application can be protected in the same fashion.

At this point, if you have been following along your network should look like the illustration below.

Add SQL3 To The Cluster

To add SQL3 to the cluster, the first thing we need to do is make sure SQL3 is up and running, fully patched and added to the domain. We also need to make sure that it has an F:\ drive attached that is of the same size as the F:\ drives in use in Azure. And finally, if you relocated tempdb on the SQL cluster, make sure you have the directory structure where tempdb is located pre-configured on SQL1 as well.

Next we will add the Failover Cluster feature to SQL3.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

With failover clustering installed on SQL3, we will open Failover Cluster Manager on SQL1 and click Add Node

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Select SQL3 and click Next

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Run all the validation tests on SQL3

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Let’s take a look at some of the warnings in the validation report

The RegisterAllProvidersIP property is set to 1, which can be good when you intend to Create Multi-Site Cluster. You can read more about this setting here: http://technet.microsoft.com/en-us/library/ca35febe-9f0b-48a0-aa9b-a83725feb4ae

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

This next warning talks about only having a single network between the cluster nodes. At this time Azure only supports a single network interface between VMs. Do note there is nothing you can do about this warning. However, this network interface is fully redundant behind the scenes. Safely ignore this message.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Of course you are going to see a lot of warnings around storage. That’s because this cluster has no shared storage. Instead it relies on replicated storage by SIOS DataKeeper Cluster Edition. As stated below, this is perfectly fine as the database will be kept in sync with the replication software.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

We are now ready to add SQL3 to the cluster.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Once you click Finish, SQL3 will be added to the cluster as shown below.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

However, there are a few things we need to do to complete this installation. Next we will work of the following steps:

Add an additional IP address to the Cluster Name Object
Tune the heartbeat settings
Extend the DataKeeper mirror to SQL3
Install SQL 2014 on SQL3

Add An Additional IP Address To The Cluster Name Object

To Create Multi-Site Cluster, we added SQL3 to the cluster. So from a single site cluster, it becomes a multi-subnet cluster. If the cluster was originally created as a single site cluster and you later add a node that resides in a different subnet, you have to manually add a second IP address to the Cluster Name Object and create an OR dependency. For more information on this topic, view the following article. http://blogs.msdn.com/b/clustering/archive/2011/08/31/10204142.aspx

To add a second IP address to the Cluster Name Object (CNO), we must use the PowerShell commands described in the article mentioned above.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Now if you are following along with the MSDN article I referenced, you would expect to see these “NewIP” somewhere in the GUI. However, at least with Windows 2012 R2 I am not currently seeing this resource in the GUI.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

However, if I right click on the SQLCLUSTER name and choose properties and try to add NewIP as a dependency, I see it is listed as a possible resource.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Choose “NewIP” and also make the dependency type “OR” as shown below.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Once you click OK, it now appears in the GUI as an IP Address that needs to be configured.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

We can now choose the properties of this IP Address and configure the address to use an IP address that is not currently in use in the 10.10.10.0/24 subnet, which is the same subnet where SQL3 resides.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Tune The Heartbeat Settings

We now are ready to tune the heartbeat settings. Essentially, we are going to be a little more tolerant with network communication. Furthermore, since SQL3 is located across a VPN connection with some latency on the line and we only have the single network interface on the cluster nodes. I highly recommend you read this article by Elden Christensen to help you decide what the right settings for your requirements are:http://blogs.msdn.com/b/clustering/archive/2012/11/21/10370765.aspx

For our environment, we are going go to what he is calling the “Relaxed” setting by setting the SameSubnetThreshold to 10 heartbeats and the CrossSubnetThreshold to 20 heartbeats.

The commands are:

(get-cluster).SameSubnetThreshold = 10

(get-cluster).CrossSubnetThreshold = 20

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

What this means is that heartbeats will continue to be sent every 1 second, but a SQL1 and SQL2 will only be considered dead after 10 missed heartbeats. SQL3 will be dead after 20 missed heartbeats. This will increase your Recovery Time Objective slightly (5-10 seconds), but it will also eliminate potential false failovers.

Extend The DataKeeper Mirror To SQL3

Before we can install SQL 2014 on SQL3 we must extend the DataKeeper mirror so that it includes SQL3 as a replication target. Of course you must install DataKeeper Cluster Edition on SQL3 first, and make sure that is has a F:\ drive at least as big as the source of the mirror. Once DataKeeper is installed

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Install SQL 2014 On SQL3

Now it is time to install SQL 2014 onto the 3^rd node. The process is exactly the same as it was to install in on SQL2. Start by launching SQL Setup on SQL3.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Run through all the steps…

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

At this point in the installation you have to pick an IP address that is valid for SQL3’s subnet. The cluster will add this IP address with an “OR” dependency to the client access point.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

Enter the passwords for your service accounts

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

After you complete the installation let the fun begin. You now have a multisite SQL Server cluster that should look something like this.

Create A Multi-Site Cluster In Windows Azure For DR With DataKeeper

For information to Create Multi-Site Cluster or any clustering questions, read here to see how SIOS could help you achieve a better solution

Reproduced with permission from https://clusteringformeremortals.com/2014/01/14/creating-a-multi-site-cluster-in-windows-azure-for-disaster-recovery-azure-cloud/

SQL Server – Massive Speed And High Availability

February 11, 2018 by Jason Aw Leave a Comment

Check out this great article by SQL Server guru @MrDenny

Title: Massive Speed and HA

Subtitle: Two Things That Usually Don’t Go Together

First Paragraph: Question: William asks “I have a SQL Server which needs both high availability and high-speed storage. Even SAN storage with SSD based hard drives isn’t providing the performance levels that we are looking for, while still getting the failover cluster HA solution that we need for our SQL Server 2008 R2 database?”

DataKeeper Cluster Edition for SQL Server High Availability In AWS Cloud

February 9, 2018 by Jason Aw Leave a Comment

The potential cost savings of moving to the cloud is nearly impossible not to consider. However, after you stop counting the money you are going to save, you start thinking about things like security and availability and wonder whether the cloud is for you. But fear not, we’ve got just the right solution – SIOS Datakeeper Cluster Edition.

In a traditional data center, you have the control and can deploy whatever security and high availability solution you like. However, once you decide to move your servers to the cloud your choices can become much more limited. It doesn’t matter whether you’re with Amazon, Google or Microsoft, outages in the cloud can and do occur and you need to do whatever you can to mitigate such risks.

Amazon Web Services

Let’s take a closer look at Amazon Web Services (AWS) for instance. What are the options you have to ensure that your SQL Server database can survive an unexpected outage? While some applications can be deployed in a load balanced configuration across multiple availability zones, SQL Server is generally not deployed in a load balanced configuration. What this means is that SQL Server itself resides in a single availability zone and if that zone should become unavailable, your whole application stack can come to a grinding halt.

SQL Server 2008 R2 and its limitations

If you read this article by Miles Ward, you will see that with SQL Server 2008 R2, your availability options are pretty limited. In that article on page 11, there is a nice chart that lays out your HA options. As you will see, the options are severely limited and mostly fall outside of the category which would be described as HA. Log shipping, mirroring and transactional replication are pretty much the only options you have, and they are more of a data protection options rather than HA options. If you want Microsoft failover clustering, you will find yourself out of luck due to some network limitations (clients can’t connect to a clustered IP address) in AWS and the lack of a shared disk resource required for traditional SQL clusters.

AWS

If you are looking to deploy SQL Server 2012, your options get a little better. As described by Jeremy Peschka, with a little manual intervention you can deploy AlwaysOn Availability Groups in AWS to do asynchronous replication from your data center to AWS, or even between AWS availability groups. Of course this assumes you have the SQL 2012 Enterprise license required for AlwaysOn Availability Groups. The only “issue” is that AWS really doesn’t support moving cluster IP address from one server to another, so client redirection has to be done manually using the ec2-unassign-private-ip-addresses and ec2-assign-private-ip-addresses commands after switchover that Peschka describes in his article. All-in-all this is a very manual process, which again does not really fit the description of a highly available system.

A Solution To The Limitations

If you can live without automated recovery and with the limitations of AlwaysOn Availability Groups that I described in a previous blog post, then you might just want to go ahead and try the AlwaysOn Availability Group deployment in AWS. However, if you are looking for an easier, more affordable, more robust HA solution, I have some really good news. SIOS Technology Corp has been looking at this problem and has developed a solution that overcomes all of the limitations previously described and will be available as an AMI for easy deployment. This solution is currently in private beta, but will be widely available later this year.

SIOS DataKeeper Cluster Edition

The SIOS solution is based on SQL server in a Microsoft Failover Clustering using DataKeeper Cluster Edition host based replication. By using hosted based replication they have overcome the first obstacle of clustering in EC2 – lack of shared storage. The second obstacle that SIOS had to overcome was the issue of client redirection described by Peschka; the client access point needs to be manipulated from within EC2, not failover clustering. SIOS has built intelligence into their AMI solution such that the reassigning of the IP address is automated as part of the cluster failover process, effectively simulating the behavior you would normally expect from a cluster.

And because all of this is built on top of failover clustering, this can be deployed using SQL 2008/2008 R2 or 2012. Even the Standard Edition of SQL Server will support a 2-node cluster so the cost savings vs. deploying SQL 2012 AlwaysOn Availability groups could be substantial.

Let me know what you think. Does SIOS Datakeeper Cluster Edition sound interesting? What are you doing today to ensure the availability of your SQL Server EC2 instances?

Reproduced with permission from https://clusteringformeremortals.com/2013/01/11/sql-server-high-availability-in-aws-cloud/