SQL Server 2012 Archives - SIOS SANless clusters

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/

SQL Server 2012 Alwayson Multisite Failover Cluster Instance White Paper

February 10, 2018 by Jason Aw Leave a Comment

Building SQL Server Multisite Clusters

Here is an excellent white paper on SQL Server Multisite Clusters, however they forget to mention that you can also do this with host based replication. Instead, they assume you have “two EMC Symmetrix VMAX enterprise storage arrays, one at each site. The arrays were both configured with two VMAX storage engines and 240 disk drives”.

How About A More Cost Effective Solution?

If you have a million dollar budget for storage, go ahead and knock yourself out. If not, look into some Fusion-io PCIe Flash storage and host based replication with DataKeeper cluster edition. It is faster than a SAN at a fraction of the cost with all the availability. Check out how Polaris Industries did just this http://www.fusionio.com/blog/polaris-sios/

Reproduced with permission from https://clusteringformeremortals.com/2013/02/05/sql-server-2012-alwayson-multisite-failover-cluster-instance-white-paper/

Alwayson Availability Groups A Viable Alternative To Failover Clusters?

February 7, 2018 by Jason Aw Leave a Comment

Is Alwayson Availability Groups A Viable Alternative To Alwayson Failover Clusters In The Public Cloud?

I recently read an article entitled SQL Server 2012 AlwaysOn: High Availability database for cloud data centers where the author John Joyner makes a case for using AlwaysOn Availability Groups for SQL Server high availability in the cloud. I have been investigating AlwaysOn Availability Groups since it was available in pre-release versions of SQL Server 2012. While it certainly has some valid uses (mostly in disaster recovery configurations), saying that it is a “new way to achieve HA SQL” glosses over many of the issues that make deploying AlwaysOn Availability Groups as a replacement for failover clusters simply not a viable option in many cases.

My Response

In a response I wrote to the article I proposed that an AlwaysOn Multisite Clustering using the host based replication solution DataKeeper Cluster Edition is a much better alternative and I went ahead and explained why..

My original response to the article seems to have been deleted, so I decided to repost my response to the original article below:

There are a few things to consider with AlwaysOn Availability Groups. As you mention, “Microsoft announced support for some System Center 2012 SP1 applications to work with SQL AlwaysOn”, meaning that there are still applications that do not support AlwaysOn. In fact, there are a LOT of applications that do not support AlwaysOn Availability Groups, including any applications that use distributed transactions. And what about the other limitations, like not being able to keep MSDB, Master and other databases in sync? I blog about these limitations here.

https://clusteringformeremortals.com/2012/11/09/how-to-overcome-the-limitations-of-sql-server-alwayson-availability-groups-sqlpass/

I agree that SQL HA is important, however, the only way to get “High Availability” (meaning automatic recovery in the event of a failure) with AlwaysOn Availability Groups is by using synchronous mirroring. At PASS Summitt in Seattle earlier this month I sat in many different presentations on AlwaysOn and almost without fail the presenters talked about AlwaysOn in an asynchronous configuration. The reason being is that AlwaysOn synchronous replication has a SIGNIFICANT impact on the performance of your application. I have personally measured up to a 68% performance penalty with AlwaysOn Synchronous mirroring, and that was across a dedicated 10 Gbps LAN! I blog about this result here

Unfortunately, in an asynchronous configuration you give up automatic failover, so you really are not getting HA, you are getting data protection, but certainly not the same RTO as you can expect from a traditional SQL failover cluster.

And then finally there is the cost to consider. SQL Server 2012 Enterprise is nothing to sneeze at. If you want to build a 2-node cluster and take advantage of readable secondaries and you are using a 2-socket, 16-core servers you are looking at shelling out close to $220k for SQL Server 2012 Enterprise licenses. I broke down the associated cost in my blog article here.

Don’t get me wrong, SQL Server 2012 AlwaysOn Availability Groups can solve many problems, but I would not categorize the asynchronous configuration required in most cloud deployments as an HA alternative. Many people are overlooking the other “AlwaysOn”, AlwaysOn Failover Clusters. New features of SQL Server AlwaysOn Failover Clusters, including enhanced support for cross subnet multisite clusters, will give you a true HA solution and overcomes all of the limitations I describe above. Of course in a pure cloud solution you may not be able to integrate with array based replication to support multisite clusters, but you can always use host based replication solutions such as SteelEye DataKeeper Cluster Edition to build multisite clusters in public or private clouds and in your own physical data center and you can do this with SQL Server 2008 through 2012 AND it works on SQL Server Standard edition as well as Enterprise.

Have you done any testing with AlwaysOn Availability Groups in a HA configuration? If so I’d be curious to know if you measured the overhead associated with synchronous replication in your environment.

Reproduced with permission from https://clusteringformeremortals.com/2012/11/27/is-alwayson-availability-groups-a-viable-alternative-to-alwayson-failover-clusters-in-the-public-cloud/

SQL Server Alwayson Features Without The Hefty Price Tag? Try DataKeeper

February 6, 2018 by Jason Aw Leave a Comment

Want SQL Server Alwayson Features But Can’t Afford SQL 2012 Enterprise Edition?

No doubt AlwaysOn Availability Groups is a hot topic here at SQL PASS Summit. As I mentioned in my previous posts, you need to consider the overhead associated with AlwaysOn as well as other limitations as you deploy SQL Server 2012 Enterprise Edition. Read on as I introduce SIOS DataKeeper Cluster Edition.

Price Comparison

I priced out (list price) a 2-node solution using SQL Server 2012 AlwaysOn Availability Groups with a read-only target with a typical 2-socket, 16-core server configuration. I also added a comparable configuration. Running DataKeeper Cluster Edition on SQL 2012 Standard Edition and was as SQL 2008 R2 Enterprise Edition.

DataKeeper-Get SQL Server Alwayson Features without SQL 2012 Enterprise Edition

As you can see, deploying SQL Server 2012 Enterprise Edition (required for Availability Groups) your expense is much greater than if you deploy a similar replicated cluster solution using DataKeeper Cluster Edition.

Reproduced with permission from Clusteringformeremortals.com

Need Alternatives to AlwaysOn Availability Groups, Try SIOS DataKeeper

February 4, 2018 by Jason Aw Leave a Comment

Looking For Alternatives to AlwaysOn Availability Groups?

Microsoft made announcements about its most widely anticipated availability options that is being introduced with SQL Server 2012. This changes include AlwaysOn Availability Groups which will only be available with the Enterprise Edition of SQL. The cost of SQL Server Enterprise is $27,496 for any server (that has up to 4 physical processors) vs. $7,172 for Standard Edition. To take advantage of “Read-Only” replica, the cost of the solution is doubled ($54,992) since you have to license both the source and the target server. With such a high price point, you begin to wonder if there are Alternatives to AlwaysOn Availability Groups. Keep reading. Because I’m going to introduce SteelEye DataKeeper Cluster Edition which probably gives you the best bang for your buck.

The good news is that Microsoft still allows you to build 2-node clusters using SQL Server Standard Edition. Since this is generally deployed in an active-passive configuration, you do not have to license the standby server. So for $7,172 you can build a pretty robust 2-node SQL cluster. That’s assuming you have an enterprise class SAN that you can use to store your cluster data.

What’s that you say, you don’t have a SAN? Or you’d rather build a solution that eliminates the SAN as a single point of failure. And instead allows you to use data replication to keep the data in sync between cluster nodes the way that AlwaysOn Availability Groups allows you to? Or perhaps you want to use take advantage of the speed offered by local attached SSD drives such as those offered by Fusion-IO, but yet don’t want to give up on availability?

The Greatness of SteelEye DataKeeper Cluster Edition

For the cost of a single copy of SQL Server 2012 Standard Edition and the very affordable addition of SteelEye DataKeeper Cluster Edition, you’ll be able to deploy 2-node SQL Server 2012 Standard Edition clusters with data replication. Best part? It’s about half the cost of a 2-node SQL Server Enterprise Edition AlwaysOn Availability Group. Furthermore, it’s about ¼ of the price of a AlwaysOn Availability Group with read-only targets.

If you find yourself answering yes to any of the following questions. Then perhaps, it’s a sign to seek Alternatives to AlwaysOn Availability Groups. Windows Server Failover Clustering and DataKeeper Cluster Edition may be a wiser choice.

Am I concerned about the cost of SQL Server Enterprise Edition?
Do I use replication or log shipping?
Do I need to support Lync Server or other applications that use distributed transactions?
Do I need to ensure that SQL Agent jobs such as database backups, optimizations, DTS and others continue to run regardless of the node in service?
Do I need to ensure that SQL login accounts are kept in sync between cluster nodes?
Do I want to minimize my administrative burden?

We’ve Done The Comparison

The following chart summarizes your SQL Server 2012 availability options. We’ve included the 3^rd option which is to build a traditional SQL cluster using Windows Server Failover Clustering with DataKeeper Cluster Edition.

SQL Server 2012 availability options including Datakeeper

Failover Clustering with DataKeeper Cluster Edition is going to save you plenty of money. And, it also is going to help you overcome some of the inherent limitations of AlwaysOn Availability Groups.

About the only thing you can’t do with the DataKeeper solution is to have read-only targets. As I mentioned earlier, read-only targets requires a second SQL license, so to have that feature will cost you minimally $54,938. If you really must have read-only targets, you’ll be glad to know that you can mix AlwaysOn Failover Clusters with DataKeeper and AlwaysOn Availability Groups. Basically you would wind up with a 2-node SQL failover cluster with DataKeeper and a single standalone SQL Server acting as a read-only target for an AlwaysOn Availability Group. In that case, you would still need two copies of SQL Server Enterprise Edition – one for the cluster and one for the read-only target.

Essentially, do check out possible Alternatives to AlwaysOn Availability Groups to save costs and improve work processes. I demonstrated this solution at Tech-Ed 2011 in Atlanta last year and got a lot of really positive feedback. This particular demonstration shows a 2-node multisite cluster. However, fret not the same concept can be applied to single site clusters.

https://clusteringformeremortals.com/2011/05/15/sql-server-denali-hadron-multisite-cross-subnet-failover-video-demonstration/

Reproduced with permission from https://clusteringformeremortals.com/2012/04/05/sql-server-2012-standard-edition-availability-options/