SIOS SANless clusters

SIOS SANless clusters High-availability Machine Learning monitoring

DataKeeper Cluster Edition for SQL Server High Availability In AWS Cloud

by 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/

Overcoming Performance Issues With SQL Server Alwayson Availability Groups

by Leave a Comment

Asynchronous vs synchronous replication on AlwaysOn

From attending the sessions at PASS Summit this week, it has become obvious that AlwaysOn is a hot topic with about six sessions dedicated to this solution . The one thing that I learned is that although the solution certainly has its applications, most of the successful deployments are based on using AlwaysOn in an asynchronous fashion. The reason people avoid the synchronous replication option is that the overhead is too great. During synchronous replication any write must be committed on the replica before it is committed on the source. In the testing that I have done, this overhead introduced can be as much as 68%.

For example, in a test where I have a database inserting about 1,000,000 rows per second and we measure the throughput on the log file, we see that with no mirroring in place we are writing about 400 MBps. Once we start replicating that database with AlwaysOn Availability Groups across a 10 Gbps LAN, we see about a 68% drop off in performance, with this particular database slowing down to about 250,000 inserts per second.

Asynchronous vs synchronous replication on AlwaysOn
Figure 1 – MBps written to a SQL Server database before and after AlwaysOn Synchronous Mirroring

If you are considering the solution as a replacement to your failover cluster, this drop off should be of a major concern to you. In order to achieve the automatic failover that you are accustomed to in failover clustering, you must use synchronous mirroring, which means that you must live with this performance hit. Generally this is not going to be acceptable, which is probably why you don’t hear the experts recommending such configurations on a regular basis.

So what should you do?

Should you stick with you traditional failover cluster and a SAN? What if you want to take advantage of fast, high speed storage such as Fusion-io? In that case, you can’t use a traditional cluster…or can you?

The good news is that you can build a cluster without a SAN and do it all without the expense, limitations and overhead associate with AlwaysOn Availability Groups (more on the limitations and expense in my next blog post). By using DataKeeper Cluster Edition you can build clusters without shared storage AND the overhead associated with Synchronous replication is closer to 10% vs. the close to 70% we see with AlwaysOn Availability Groups.

Come to booth 351 at #SQLPASS and I’ll be glad to demonstrate how the solution works.

Reproduced with permission from https://clusteringformeremortals.com/2012/11/09/how-to-overcome-the-performance-problems-with-sql-server-alwayson-availability-groups-sqlpass/

Achieving High Performance And High Availability For SQL Server

by 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/

SQL Server Denali Demonstration

by Leave a Comment

SQL Server Denali Hadron Multi-site Cross Subnet Failover Video Demonstration

The crowd has spoken and the SQL Server Team has answered! Denali is the first version of SQL Server to support the cross subnet failover feature which first introduced in Windows Server 2008 Failover Clustering. It seems like we have been waiting forever for the SQL team to support this feature. Now, the wait is finally drawing to an end.

The following is a link to a 5 minute video. It demonstrates a 3-node cluster Denali cluster with two nodes in the primary datacenter, and one node in the Disaster Recovery site in a separate subnet.

http://screencast.com/t/ni1zqMOIv

I will be demonstrating this solution at Tech-Ed 2011 in Atlanta this week. Stop by the SIOS Technology booth and come have a look for yourself at the SQL Server Denali multi-site cluster demonstration using SteelEye DataKeeper. We would be happy to share our technology and further explain how it could benefit your projects.

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

Step-By-Step: Configuring A 2-Node Multi-Site Cluster On Windows Server 2008 R2 – Part 3

by Leave a Comment

In Part 1 of this series, I showed you how to prepare your multi-site cluster, including setting up the Node and File Share Majority Quorum. In Part 2, we saw a video example of how to integrate SteelEye DataKeeper, one of the available cluster replication solutions, with failover clustering to configure a multi-site Hyper-V failover cluster. In Part 3, we will examine how Microsoft SQL Server 2008 can be configured in a multi-site cluster with Windows Server 2008 Failover Clustering and SteelEye DataKeeper Cluster Edition.

First, why would you want to deploy a multi-site SQL server cluster? SQL Server comes with a variety of high availability options, including log shipping, replication (transactional, merge, snapshot), database mirroring and failover clusters. There is a great article that talks about the High Availability options in SQL Server 2008, so I won’t re-invent the wheel here. If you want to learn more about all of the options, have a look at this article by Ross Mistry, High Availability Options in SQL Server 2008.

If you read the article, Ross’ summary makes the following statement…

“Clearly, failover clustering is the best choice to reduce system downtime and provide higher application availability for the whole instance of SQL Server within a site; however, a failover cluster does not protect against disk failure and requires a SAN.”

What if you could have all of the benefits that Ross outlines and also protect against disk failure and eliminate the need for a SAN? Well, the good news is that is exactly what you can do by implementing a Microsoft SQL Server 2008 multi-site cluster on Windows Server 2008. SQL Server 2008 multi-site cluster may be a little bit of a misnomer. It does not necessarily need to be deployed across geographically dispersed locations. The servers could be located in the same rack, same room, same building or across continents; it really doesn’t matter. The important thing is that you are eliminating your storage as a single point of failure and if you choose to locate your cluster nodes in different physical locations then you also have built in site resiliency.

One of the most common questions and points of confusion about SQL Server 2008 and Windows Server 2008 failover clustering is support for failing across subnets. Yes, Windows Server 2008 Failover Clustering does support failing between subnets for most applications, however, SQL Server 2008 is not one of those applications. As far as I know, SQL Server 2008 R2 will also not support failing between subnets when it is released. My understanding is that the SQL team is working on support for cross-subnet failover, but it will be supported sometime after SQL Server 2008 R2 is released. So, for the time being, you will have to span your subnet if you wish to separate your nodes geographically.

Now that you have determined to deploy a multi-node SQL server cluster, here are the steps you will need to follow.

CONFIGURE YOUR BASIC CLUSTER

Follow the steps in Part 1 of this series to configure a basic 2-node cluster with a Node and File Share Majority Quorum.

CONFIGURE YOUR REPLICATION

Deploying a multi-site cluster will require that you work very closely with your replication vendor during this part of the configuration. Each vendor will have very specific instructions on how to set up the replication and create the storage resources to be used in your cluster. For the purposes of this demonstration, I will use SteelEye DataKeeper Cluster Edition to replicate the E drive from the server named PRIMARY to the server named SECONDARY. The E drive on PRIMARY and SECONDARY are just local attached volumes and not part of any SAN. As long as the E drive shows up as a local attached disk, DataKeeper can replicate it from one server to another. You can mix and match SAS, iSCSI, SAN, VHD, or any other storage type as DataKeeper is storage agnostic.

After you have configured your basic cluster as described in Part 1 of my series, run the DataKeeper setup.exe to install SteelEye DataKeeper Cluster Edition on both nodes of the cluster. You will be prompted to enter your license key at the end of the install. Enter your license and reboot your servers when prompted. You are now ready to create your mirror. Launch the DataKeeper GUI and follow the instructions below to create your mirror.

Click on “Connect to Server”.

Figure 1 – Connect to your source and target servers
Figure 1 – Connect to your source and target servers

Enter the name of the first server. Click Connect.

Enter the name of the second server, click Connect

Now click Create Job. That will launch the Create Job wizard.

Figure 2 – Create your Job
Figure 2 – Create your Job

Give your job a name and description. These can be anything you like.

Figure 3 – Give your Job a name and description
Figure 3 – Give your Job a name and description

Choose the following:

  • Source Server – where the data will be replicated from
  • Network adapter – the network where the replication traffic will travel
  • Volume – the volume you want to replicate

Click Next to continue.

Figure 4 – Choose you source server and network to use for replication
Figure 4 – Choose you source server and network to use for replication

Choose the following:

  • Target Server – where the data will be replicated to
  • Network adapter – the network where the replication traffic will travel
  • Volume – the volume you want to replicate

Click Next to continue.

Figure 5 – Choose your target server
Figure 5 – Choose your target server

Choose the following:

  • Compression Level – If you have a 100 Mbps or faster network for replication, leave it set to none. If you have a WAN that is less that 100 Mbps, you may benefit from enabling compression. Settings somewhere in the middle tend to give you the best performance of compression vs. CPU overhead associated with enabling compression.
  • Asynchronous vs. Synchronous – Asynchronous replication is generally acceptable in most environments and is definitely required in high-latency WAN environments. I wrote a whole blog post on this topic, so if you have any questions, check out my article on Asynchronous vs. Synchronous replication.
  • Maximum bandwidth – you can think of this as a “poor man’s” QOS. If you want to ensure that replication never exceeds a certain threshold of your WAN capacity, you can put a limiter on the amount of bandwidth it can consume. Unless you have a good reason to set it, it is better off leaving it set to 0.

Click Done to create your mirror.

Figure 6 – Choose your mirror settings
Figure 6 – Choose your mirror settings

Now if you take a look at your DataKeeper GUI, it will look similar to the following.

Figure 7 – Your mirror is now created
Figure 7 – Your mirror is now created

Once you have created your mirror, you need to make your mirror available in the Microsoft Cluster “Available Storage”. There are a few ways to do this, but the most straight forward way is to use the Windows PowerShell CLI. Below is an example that shows how to take the existing mirror we just created on the E drive and add it to the cluster “Available Storage”, move it to the PRIMARY node and bring it in-service

Import-Module FailoverClusters

Add-ClusterResource -Name “DataKeeper Volume E” -ResourceType “DataKeeper Volume” -Group “Available Storage”

Get-ClusterResource “DataKeeper Volume E” | Set-ClusterParameter VolumeLetter E

Move-ClusterGroup “Available Storage” -Node primary

Start-ClusterResource “DataKeeper Volume E”

For more information on PowerShell and the available commands for use with Failover Clustering, check out this great blog post from Symon Perriman of the Microsoft Failover Clustering Team.

http://blogs.msdn.com/clustering/archive/2008/12/20/9243367.aspx

You are now going to repeat the above steps to add any additional mirrors that you will use in your cluster. In our case, we are going to create a mirror of the F drive and use it to cluster the MSDTC. After you have added your additional mirrors and added them to Available Storage, your DataKeeper GUI should look something like this.

Figure 8 – After adding the second Job for the MSDTC resource
Figure 8 – After adding the second Job for the MSDTC resource

And your Failover Cluster Manager GUI should look like this.

Figure 9 – After adding the second DataKeeper resource
Figure 9 – After adding the second DataKeeper resource

CLUSTERING MSDTC

IMPORTANT NOTE – There is a hotfix that is required in order to support DTC with 3rd party disk resources.  Please see the following KB article and apply the howfix to all cluster nodes.  http://support.microsoft.com/kb/978476

SQL 2008 is very dependent upon MSDTC, so it is highly recommended that you cluster the MSDTC resource before you cluster your SQL resource. The following articles are provided for your reference for configuration and management of your MSDTC resource.

http://technet.microsoft.com/en-us/library/cc770748(WS.10).aspx

http://technet.microsoft.com/en-us/library/cc771540(WS.10).aspx

You will start by opening the Failover Cluster Manager GUI and then choose “Configure a Service or Application”.

Figure 10 – Creating a MSDTC resource
Figure 10 – Creating a MSDTC resource

You will then choose “Distributed Transaction Coordinator” and click Next

Figure 11 – Choose your Service or Application
Figure 11 – Choose your Service or Application

Give the MSDTC resource a name and unique IP address. These should be unique to MSDTC and not the same as you will use later when you create your SQL resource.

Figure 12 – Choose a name and IP address
Figure 12 – Choose a name and IP address

Choose the volume where you will store the data for the MSDTC resource. In our case we are choosing the replicated F drive.

Figure 13 – Choose a dedicated volume for the MSDTC resource
Figure 13 – Choose a dedicated volume for the MSDTC resource

Confirm your information and click Next to continue.

Figure 14 – Click Next to configure the DTC resource
Figure 14 – Click Next to configure the DTC resource

Congratulations, you have successfully configured the DTC resource. Click Finish.

Figure 15 – A successfully configured DTC resource
Figure 15 – A successfully configured DTC resource

We are just about ready to begin the installation of the first node of the SQL Server Cluster, however, there is one thing we need to do in preparation – Slip Stream SQL 2008 SP1 onto the SQL Server 2008 RTM install media.

SLIP STREAM SQL SP1 ONTO YOUR SQL 2008 INSTALL MEDIA

What I have discovered is that SQL Server 2008 will not install on Windows Server 2008 R2 without first slipstreaming SQL Server 2008 SP1 onto your SQL 2008 install media. Here is a great article that describes how to slipstream SQL Server 2008 RTM and Service Pack 1. After I read that article and successfully slipstream SP1 onto SQL 2008 RTM, I found the following Microsoft KB article that describes the same procedure. You may get an error that looks like the following if you try to install SQL without first slipstreaming SP1 onto the media.

There was an error setting private property ‘RequireKerberos’ to value ‘1’

I followed the instructions detailed in the first article and copied my SQL 2008 with SP1 install to the C:\ drive of both nodes in my cluster. In the instructions below, I will do the installation from the local disk of each cluster node.

INSTALLING YOUR FIRST CLUSTER NODE

Now that you have your SQL Server 2008 SP1 installation media ready to go, you are ready to install your first SQL node. There is one major “gotcha” when it comes to installing SQL on a multi-node cluster. In order for you to install SQL on a multi-node cluster, you must first pass the Windows Server 2008 Failover Cluster validate process. Unfortunately, a multi-site cluster is exempt from passing the storage related test, so you never are able to actually “pass” the validation as far as SQL is concerned. It took a little investigation on my part, but what I have come to find is that there is a command line parameter that allows you to skip the validation test on the SQL 2008 installation. Here is the command line.

Setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster

To launch the SQL setup, open a Command window, browse to your SQL 2008 with SP1 install directory and type the command as shown below.

If everything goes as planned, you should see the screen below. Click OK to continue.

Enter your product key and click Next

Figure 17 – Enter your product key
Figure 17 – Enter your product key

Accept the license agreement and click Next

Click Install to install the Setup Support Files

At the end of the Setup for the Support Files you will receive a warning. Click on Show details and you will see the message below. You can click Next, ignoring this message since it is expected in a multi-site or non-shared storage cluster.

Figure 18 – The warning is expected in a multi-site or non-shared storage cluster
Figure 18 – The warning is expected in a multi-site or non-shared storage cluster

Choose the features you would like to install and click Next. Leave the “Shared Feature” directory set to the C drive as the SQL binaries should not be installed on the replicated volume.

Figure 19 – Choose your features
Figure 19 – Choose your features

On the next screen, you will choose a network name for your SQL Server. This will be the name that all of the clients will connect to. Assuming this is the default instance, leave the Instance ID and Instance root directory set to the defaults. Do not move the instance root directory to the replicated volume.

Figure 20 – Choose your SQL Network Name
Figure 20 – Choose your SQL Network Name

It is recommended that you have separate domain accounts for SQLServer and SQLAgent. You can take the time to create these accounts now if you have not already done so. These accounts require no special permissions, as this install process will give them the permissions that they require.

Confirm you have enough disk space and click Next to continue.

Choose the default settings to allow a new cluster resource group named “SQL Server (MSSQLSERVER)” to be created.

Figure 21 – Allow the wizard to create a new cluster resource group for you
Figure 21 – Allow the wizard to create a new cluster resource group for you

Choose a replicated volume that is still available, in our case the E:\ drive. Click Next to continue.

Figure 22 – Choose your replicated volume
Figure 22 – Choose your replicated volume

Now you will choose the IP address you want associated with the SQL cluster resource. You could leave it set to use DHCP if you wish.

Figure 23 – Choose your SQL cluster IP address
Figure 23 – Choose your SQL cluster IP address

Choose your security settings and click Next

Add any SQL Server administrators and choose your authentication mode and then click Next

Choose your Error and Usage Reporting options and click Next

You will once again see some warnings related to the validation process. You can ignore those messages as they are to be expected in a multi-site SQL Server cluster.

Click Install to begin the installation process

If everything installs as expected, you should see the following screens. Click Next then Close to finish the installation.

Congratulations, you have successfully installed the first node of your multi-site SQL Server Cluster. Now we will install the second node of the cluster.

INSTALL THE SECOND CLUSTER NODE

Installing the second cluster node is similar to installing the first node. We need to run SQL Setup once again from the command line so that we can skip the Validate process. So, from the 2nd node, open up your command prompt and run the following command.

  1. Install SQL 2008 SP1 (merged setup) on the 2nd node using the following command:

    Setup /SkipRules=Cluster_VerifyForErrors /Action=AddNode /INSTANCENAME=”MSSQLSERVER”

*Note: This assumes you installed the default instance on the 1st node

That will launch the install wizard as shown below. Click OK to continue.

Enter your product key and click next.

Accept the license terms and click Next

Click install to install the Setup Support files.

You can once again ignore the warning that some cluster validation tests have been skipped. This is to be expected in a multi-site cluster and non-shared storage clusters.

Verify you are adding the node to the right instance and click Next.

Add the passwords to the service accounts and click Next

Choose your Error and Usage Reporting options and click Next

Once again, ignore the warning about the skipped operations.

Click Install to begin the installation process.

If everything goes as expected, you should see the following screen.

Now that you have a fully functional two node cluster, you probably should testing things out by doing some manual switchovers. Right click on the resource and choose “Move to node SECONDARY”.

If everything is configured properly, your Failover Cluster GUI should look as follows.

CONCLUSION

I believe that SQL clusters with replicated storage make a lot of sense. Storage has always been a single point of failure of traditional clusters. You can eliminate that single point of failure by deploying a SQL Server cluster with replicated storage from SteelEye or any other Microsoft Multi-Site Cluster replication partner. I hope you found this article informative. If you have any questions or suggestions, please add your comments!

Reproduced with permission from https://clusteringformeremortals.com/2009/10/07/step-by-step-configuring-a-2-node-multi-site-cluster-on-windows-server-2008-r2-%E2%80%93-part-3/