AlwaysOn Availability Groups Archives

Clustering SQL Server 2012 On Windows Server 2012 with DataKeeper

February 9, 2018 by Jason Aw Leave a Comment

A Little Recap

In my previous post I walked through the process of building a 2-node cluster up to the point where we are ready to start clustering SQL Server 2012 on Windows Server 2012. If you have completed those steps, you are ready to move on and actually create your clustered application with my suggested SIOS Datakeeper Cluster Edition.

Next step, clustering SQL Server 2012

First up, we have SQL Server 2012. SQL Server 2012 cluster installation is pretty much identical to SQL 2008/2008 R2 cluster installations, so most of this will apply even if you are using SQL 2008/2008 R2. The terminology around SQL Server 2012 Clustering gets a little convoluted. You will hear mention of SQL Server AlwaysOn, which essentially could mean one of two different things: AlwaysOn Availability Groups or AlwaysOn Failover Cluster Instance. The confusion arises because both solutions require some level of integration with Windows Server Failover Clustering and it is even further confused by the fact that you can deploy a combination of AlwaysOn Availability Groups and AlwaysOn Failover Clustering, but that is a topic for another day!

Breaking It Down In Easy-To-Understand Terms

Essentially AlwaysOn Availability Groups is what used to be called Database Mirroring in SQL 2008 R2 and earlier. It has some new bells and whistles that overcome some of the limitations of earlier versions of database mirroring, so it is certainly worth checking it out. AlwaysOn Failover Cluster Instance is simply what used to be called a SQL Server Failover Cluster. This is the latest edition of the same clustering technology that has been available since early versions of SQL Server. One of the best new features of SQL Server 2012 AlwaysOn Failover Cluster Instance is the ability to have nodes in different subnets. This was a major limitation in earlier versions of SQL Server. In a previous blog entry I discussed some of the limitations of AlwaysOn Availability Groups, you should check that out before you make any decisions on which technology to deploy.

Let’s Start

With that said, this article is going to focus on the Step-by-Step instructions on deploying a SQL Server 2012 AlwaysOn Failover Cluster Instance.

Step 1

Make sure your cluster storage is ready. If you followed the instructions in my previous post, you will know that instead of a shared disk resource, we are going to use a replicated disk resource using the 3^rd party software DataKeeper Cluster Edition. If you are using shared storage and have added the storage than you can skip right to Step 2 where we begin the SQL install. Otherwise, follow the steps below to configure the software to replicate the local disks for use in a SQL cluster.

Configuring DataKeeper Cluster Edition

Install and configure DataKeeper Cluster Edition
1. Run DK Setup
2. Go through the entire installation process selecting all of the default values.
3. Restart the computer after the installation completes as prompted and repeat the process on the SECONDARY server
4. Launch the DataKeeper UI on PRIMARY and click Connect to Server. Connect to PRIMARY and then connect to SECONDARY
5. Click on Create Job and walk through the Create Job wizard to create a mirror of the E drive
  
  Choose the source volume of the mirror and the IP address of the NIC that will carry the replication traffic.
  
  Choose the target of the mirror and click Next
  
  Here you will choose your mirror options:
  Compression – only enable for replication across a WAN
  Asynchronous – choose this for all WAN replication
  Synchronous – this is ideal for LAN replication
  Maximum bandwidth – used in WAN replication as a way to put a cap on the amount of bandwidth replication is allowed to use. Generally it should be left on 0, however for initial mirror creation you may want to limit the bandwidth so replication does not use all available bandwidth to do the initial synchronization
  
  Once you click Done the mirror will be created.
  
  Once the mirror is created you will be prompted to register the volume in Windows Server Failover Clustering (WSFC). Click Yes and a new DataKeeper Volume Resource will be registered in Available Storage (see picture in Step 2).

Step 2

We are going to begin the installation of SQL Server 2012 on the first cluster node.

Before we begin, make sure your storage appears in Failover Cluster Manager and is assigned to the Available Storage group as shown below
At this point we are going to launch the SQL Server 2012 setup and go to the Installation Tab and click New SQL Server failover cluster installation
Step through the installation as shown in the following screen shots.

The following error is expected if your servers are not connected to the internet. If you are connected to the internet you should go ahead and accept the updates it finds.

For Service Account best practices read the following: http://msdn.microsoft.com/en-us/library/ms143504.aspxFor our lab purposes I am just using the Administrator account

Before you click next, click on the Data Directories tab and change the location of tempdb. With Windows Server 2012 tempdb no longer has to reside on the cluster storage. In our example we are moving tempdb to the C drive to avoid replicating unnecessary data.

At this point you will need to make sure to create the same tempdb directory on the SECONDARY server as advised by the warning.

Congratulations, the 1^st cluster node has been installed.

We are now ready to install SQL on the second node of the cluster.

Go to the SECONDARY server and launch the SQL Server 2012 Setup and follow the wizard as shown in the following screen shots, starting with clicking on Add node to a SQL Server failover cluster.

The following error is expected if your servers are not connected to the internet. If you are connected to the internet you should go ahead and accept the updates it finds.

Congratulations – you have built a 2-node SQL Server 2012 AlwaysOn Failover Cluster Instance. Open up Failover Cluster Manager and you should see something that looks like this.This article “clustering SQL Server 2012 in a Windows Server 2012 cluster” was meant to be just a quick run through on how to install SQL 2012 in a Windows Server 2012 cluster. For additional reading start here and let Google be your friend!

Reproduced with permission from https://clusteringformeremortals.com/2013/01/05/clustering-sql-server-2012-on-windows-server-2012-step-by-step/

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

Overcome Alwayson Availabilty Groups limitations with SIOS Datakeeper

February 5, 2018 by Jason Aw Leave a Comment

Overcome Alwayson Availabilty Groups limitations with SIOS Datakeeper

After hearing all of the great sessions at SQL PASS Summit on Availability Groups, are you thinking about biting the bullet to upgrade to SQL Server 2012 Enterprise Edition? Before you get your checkbook out, stop and ask yourself these questions and also, allow me to introduce SteelEye DataKeeper Cluster Edition.

Do I use/need

to lower my SQL Server cost?
replication or log shipping?
to minimize the impact that replication has on the performance of my application?
Lync Server, Dynamics CRM or other applications that use distributed transactions?
to ensure that SQL Agent jobs such as database backups, optimizations, DTS and others continue to run regardless of the node in service?
to ensure that SQL login accounts are kept in sync between cluster nodes?
to minimize my administrative burden?

Alwayson Availabilty Groups Limitations

If you answered yes to any of these questions, you may want to reconsider your options when it comes to your SQL Server HA/DR deployment. While AlwaysOn Availability Groups certainly have their place, you may want to consider the overhead associate with them. Read my previous article. Also, you really need to consider what applications will be utilizing the SQL Server database as not all applications support AlwaysOn Availability Groups. This includes many of Microsoft’s applications such as Lync Server and others (check your application documentation).

Introducing SteelEye DataKeeper Cluster Edition

What I would propose instead is to consider building a traditional active/passive cluster which overcomes all of the Alwayson Availabilty Groups Limitations listed above. But instead of using shared storage, use the cluster integrated block level replication solution from SIOS Technology called SteelEye DataKeeper Cluster Edition. Using this replication solution, you are able to eliminate the SAN as a single point of failure as well as eliminate all of the limitations associated with AlwaysOn Availability Groups listed above. When you consider the possibility of using high speed local storage solutions such as @Fusionio in conjunction with DataKeeper, you can have a high speed, highly available SQL Server cluster with a minimal investment in hardware and software. And best of all, this solution works with SQL 2005/2008/2008R2/2012 Standard Edition as well as Enterprise Edition. The cost saving alone on SQL Server licensing can more than pay for the solution. More on costs saving in my next post.

How Should I Start?

You can buy a 2 server pre-package solution that includes HP, Dell or Supermicro servers, Fusion-io ioDrives. DataKeeper software to help you deploy you first high speed, highly available SQL cluster. For more information see the press release here.

http://www.sqlpass.org/summit/2012/About/News/PressReleases/PartnerPressRelease9.aspx

Like how Datakeeper help overcome Alwayson Availabilty Groups limitations? Read here for more success stories

Reproduced with permission from Clusteringformeremortals.com

Achieving High Performance And High Availability For SQL Server

February 4, 2018 by Jason Aw Leave a Comment

Register here to learn how high performance and high availability for Microsoft SQL Server can be achieved with solutions from Fusion-io and SIOS. This powerful combination delivers high-speed storage and high availability for SQL Server without the limitations of single copy clusters or AlwaysOn Availability Groups.

Make the switch and enjoy:

Cost savings
Higher database performance
Smaller floor space requirements
Lower cooling and power demands

… all without sacrificing availability.

The end result: faster performance at a fraction of the cost of a typical SAN with 100% data availability.

Ready to learn about how Fusion-io and SIOS could achieve better results with Microsoft SQL Server. Hear from our featured Speakers:

Dave Bermingham is recognized within the technology community as a high availability expert and has been a Microsoft MVP in Clustering for the past three years. David’s work as Sr. Technical Evangelist at SIOS has him focused on delivering high availability and disaster recovery solutions. David holds numerous technical certifications and has over twenty years of technology experience focused on application availability.

Jamila Gunawardena is a Senior OEM Sales Engineer for Fusion-io. Jamila has over 15 years experience in Software Engineering and System Design with expertise in Internet, Bioinformatics, and Semiconductor DFM solutions. Currently, Jamila is focused on ISV and appliance partnerships at Fusion-io.

Geoff Hiten is a Microsoft SQL MVP and is currently an Infrastructure Architect for Intellinet. Geoff began working with SQL Server in 1992 with version 4.2 and has used every version since. He specializes in high-availability and high-performance SQL systems. Geoff is heavily involved in the Microsoft SQL Server Community and was initially awarded MVP status in 2004. Geoff was appointed in 2011 to fill an interim Director position at the National PASS organization.

Reproduced with permission from https://clusteringformeremortals.com/2012/09/12/achieving-high-performance-and-high-availability-for-sql-server/