Author: daveberm

Clustering SQL Server 2012 on Windows Server 2012 Step-by-Step

daveberm6 Comments

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 configuring the cluster resources. If you have completed those steps you are ready to move on and actually create your clustered application. 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!

I’ll break 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.

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 is to 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 3rd 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 DataKeeper Cluster Edition to replicate the local disks for use in a SQL cluster.

  1. 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).
  2. In Step 2 we are going to begin the installation of SQL Server 2012 on the first cluster node.
    1. Before we begin, make sure your storage appears in Failover Cluster Manager and is assigned to the Available Storage group as shown below
    2. 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
    3. 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.aspx

      For 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 1st cluster node has been installed.

  3. We are now ready to install SQL on the second node of the cluster.
    1. 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.









  4. 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 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!

Clustering SQL Server 2012 on Windows Server 2012 Step-by-Step

Windows Server 2012 Clustering Step-by-Step

daveberm29 Comments

This article is the first in a series of articles on Clustering Windows Server 2012. This first article covers the basics first steps of any cluster, regardless of whether you are clustering Hyper-V, SQL Server Failover Clusters, File Servers, iSCSI Target Server or others. Future articles will cover more detailed instructions for each cluster resource type, but the following information is applicable to ALL clusters.

I’m assuming you know a little bit about clusters and why you would want to build one, so I won’t go into those details in this particular post. I also assume you are familiar with Windows Server 2012 and basic things like DNS, AD, etc. It is also worth noting that in Windows Server 2012 failover clustering comes with every edition, unlike Windows Server 2008 R2 and earlier where failover clustering was only included in Enterprise Edition and above.

This particular series will focus on a basic 2-node cluster, where we have two servers (named PRIMARY and SECONDARY) running Windows Server 2012 in a Windows Server 2012 Domain (domain controller named DC). It also assumes that PRIMARY and SECONDARY can communicate with each other over two network connections I have labeled PUBLIC and PRIVATE. In production scenarios these network connections should run through entirely different network gear (switches, routers, etc) to eliminate any single point of failure.

This series will be written in a very basic, step-by-step style that walks you through the process in an ordered list with basic instructions and plenty of screen shots to help illustrate the procedure where needed. So let’s begin at the beginning…

  1. Add the Failover Clustering Feature on all of the servers you want to add to the cluster
    1. Open the Server Manager Dashboard (this 1st step will need to be completed on both PRIMARY and SECONDARY)
    2. Click on Add roles and features

    3. Choose Role-based or feature-based installation

    4. Choose the server on which you wish to enable the failover cluster feature

    5. Skip over the Server Roles page
    6. On the Features page select Failover Clustering and click Next and then confirm the installation

  2. Before we start configuring the cluster, we need to consider what kind of storage the cluster will use. Traditionally clusters will use some sort of SAN, but with Windows 2012 not all clusters will use a SAN. For instance, if you are building a cluster to support SQL Server AlwaysOn Availability Groups your storage will be replicated by SQL Server, eliminating the need for a SAN. Also, with SMB 3.0 being support as cluster storage for Hyper-V and SQL Server you may not have a traditional SAN for storage. And let’s not forget clustered Storage Spaces with shared SAS drives is also a possibility in Windows Server 2012. In addition to the options mentioned above, you also can use local disks and 3rd party host based replication solutions like DataKeeper Cluster Edition which is an excellent alternative which I blog about pretty frequently.

    For the purposes of this post, I am going to assume you have no shared storage. However, if you do have shared storage at this point you should configure you storage such that you have LUN(s) carved out and shared with each of the cluster nodes with one LUN being used as a disk witness and the remaining LUNs can be used for the application which you want to cluster. In lieu of a disk witness for our quorum, I am going to use a node and file share witness quorum type which I will explain later.

  3. Now that Failover Clustering is enabled on each server, you can open the Failover Cluster Manager on your PRIMARY server. The first thing we will want to do is to run “Validate Configuration” so we can identify any potential issues before we begin. Click on Validate Cluster

  4. Step through the Validate a Configuration Wizard as shown in the following steps.
    1. Select the servers you want to cluster
    2. Run all tests (depending on what roles you have installed on the servers you may get more or less tests. For instance, if Hyper-V is enabled there are new Hyper-V specific tests for clusters)
    3. Assuming you cluster “passed” validation you should have a report that looks similar to mine. You will notice that my report contains “warnings” but no errors. It is important for you to view the report and understand what warnings might be present, but you as long as you understand the warnings and they make sense for your particular environment you can move on. If you validation “failed”, you MUST fix the failures before moving on. Click View Report to view the report
    4. You will see all of my warnings are related to storage, so I am not concerned since I have not configured any shared storage, so I would expect some of these thests to produce warnings.

 

  1. Once Validation completes without any errors, you will automatically be thrown into the Create Cluster Wizard. Walk through this wizard as shown below to create your basic cluster.
    1. In this first screen you will choose a name for your cluster and pick an IP address that will be associated with this name in DNS. This name is just the name used to manage your cluster – this is NOT the name that your clients will use to connect to the clustered resource(s) you will eventually create. Once you create this access point a new computer object will be created in AD with this name and a DNS A record will be created with this name and IP address.
    2. On the confirmation screen you will see the name and IP address you selected. You will also see an option which is new with Windows Server 2012 failover clustering…”Add all eligible storage to the cluster”. Personally I’m not sure why this is selected by default, as this option can really confuse things. By default, this selection will add all shared storage (if you have it configured) to the cluster, but I have also seen it add just local, non-shared disks, to the cluster as well. I suppose they want to make it easy to support symmetric storage, but generally any host based or array based replication solutions are going to have some pretty specific instructions on how to add symmetric storage to the cluster and generally this option to add all disks to the cluster is more of a hindrance than a help when it comes to asymmetric storage. For our case, since I have no shared storage configured and I don’t want the cluster adding any local disks to the cluster for me automatically I have unchecked the Add all eligible storage to the cluster option.

    3. After you click next you will see that the cluster has finished the creation process, but there may be some warnings. In our case the warnings are probably related to the quorum configuration which we will take care of in the next step. Click View Report to check out any warnings.

      You see that the warning is telling use to change the quorum type.
  2. Because we have no shared storage, we will not be using a Node and Disk Majority quorum as suggested. Instead, we will use and Node and File Share Majority quorum. The following steps will help us configure the Node and File Majority Quorum
    1. A File Share Witness needs to be configured on a server that is not part of the cluster. A file share witness is a basic file share that the cluster computer name (MYCLUSTER in our case) has read/write access. The first step involves creating this file share. In our example, we are going to create a file share on our DC and give MYCLUSTER read/write access to it.
    2. The file share does not need to reside on a Windows 2012 server, but it does need to be on a Windows Server in the same domain as the cluster. The important thing to remember is that the cluster computer name that we created needs read/write access at both the share level and NTFS level. The following are some screen shots that walk you through this process on the DC server which is running Windows Server 2012 in my lab.




    3. Now that we have the file share created on DC, we will go back to PRIMARY and use the Failover Cluster Manager to change the quorum type as shown in the following steps.






      If by chance this wizard fails, it is most likely related to the permissions on the file share. Make sure you give the cluster computer name read/write permissions at BOTH the file share and security (NTFS) level and try again.
  3. You now have a basic 2-node cluster and are ready to move on to the next step…creating your cluster resources. I will be publishing a series of articles on how to cluster different resources, starting with SQL 2012 in my next post.

     

Windows Server 2012 Clustering Step-by-Step

DataKeeper Cluster Edition 7.5 now support Windows Server 2012

daveberm1 Comment

Great news for you Windows Server 2012 early adopters – you can now use DataKeeper Cluster Edition with Windows Server 2012. I’ll be posting some Windows Server 2012 Step-by-Step articles before the end of the year and will be sure to include some multisite cluster examples as well. In the meantime, if you want to build clusters based on Windows Server 2012 and want to eliminate shared storage as a single point of failure or you want to stretch your cluster across geographic locations you can do that with DataKeeper Cluster Edition v7.5.

DataKeeper Cluster Edition 7.5 now support Windows Server 2012

No More Free Google Apps #googleapps #azure

davebermLeave a comment

http://googleenterprise.blogspot.com/2012/12/changes-to-google-apps-for-businesses.html

I’m glad I got Boy Scout Troop 20 registered under the deadline. Free Google Apps was a great intro to the product for me and I looked at it as a gateway “drug” that gave me enough of a look to consider the premium version for any “serious” cloud based business operation, i.e., not a Boy Scout Troop. While $50/year per user is not expensive, it certainly can be a show stopper for many small non-profit organizations who may have otherwise been potential customers. So the question is will Microsoft take advantage of this opening to gain market share? If so, I know where I will be deploying my next cloud based small business site.

No More Free Google Apps #googleapps #azure

Is AlwaysOn Availability Groups a viable alternative to AlwaysOn Failover Clusters in the public cloud?

davebermLeave a comment

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 and 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 which make deploying AlwaysOn Availability Groups as a replacement for failover clusters simply not a viable option in many cases and not a good idea in the rest of the cases. 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.

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

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.

https://clusteringformeremortals.com/2012/11/09/want-sql-server-alwayson-features-but-cant-afford-sql-2012-enterprise-edition-sqlpass/

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.

Is AlwaysOn Availability Groups a viable alternative to AlwaysOn Failover Clusters in the public cloud?

Want SQL Server AlwaysOn Features But Can’t Afford SQL 2012 Enterprise Edition? #SQLPASS

daveberm2 Comments

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, If however you can deal with the overhead and the limitations do not apply to you and you still want to deploy AlwaysOn Availability Groups you may want to have a seat when you go open your checkbook.

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.

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.

Stop by both 351 at PASS Summit to see a demo and get more information.

Want SQL Server AlwaysOn Features But Can’t Afford SQL 2012 Enterprise Edition? #SQLPASS

How to Overcome the Limitations of SQL Server AlwaysOn Availability Groups #SQLPASS

daveberm6 Comments

After hearing all of the great sessions at SQL PASS Summit on Availability Groups are you thinking about biting the bullet and writing the check to upgrade to SQL Server 2012 Enterprise Edition to take advantage of this great feature? Before you get your checkbook out, stop and ask yourself these questions.

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?

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 as I discussed in 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, including many of Microsoft’s applications such as Lync Server and others (check your application documentation).

What I would propose instead is to consider building a traditional active/passive cluster which overcomes all of the 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, so the cost saving alone on SQL Server licensing can more than pay for the solution (more on costs saving in my next post).

You can by a 2 server pre-package solution that includes HP, Dell or Supermicro servers, Fusion-io ioDrives and 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

 

How to Overcome the Limitations of SQL Server AlwaysOn Availability Groups #SQLPASS

How to Overcome the Performance Problems with SQL Server AlwaysOn Availability Groups #SQLPASS

daveberm8 Comments

Attending the sessions at PASS Summit this week it has become obvious that AlwaysOn is a hot topic with about six sessions dedicated to the topic. The one thing that I learned is that although AlwaysOn 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.

Figure 1 – MBps written to a SQL Server database before and after AlwaysOn Synchronous Mirroring

If you are considering AlwaysOn 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.

How to Overcome the Performance Problems with SQL Server AlwaysOn Availability Groups #SQLPASS

Hurricane Sandy Disaster Recovery for Business

davebermLeave a comment

My thoughts and prayers go out to those affect by this massive storm. Although I live in NJ, my neighborhood remained relatively unscathed other than some downed trees and power lines. The pictures coming in from the coastal communities up and down the eastern seaboard show that many people did not fare as well. I’m hopeful that most of the damage is property that can be rebuilt, but I am sorry to hear that some people lost their lives and I can only imagine the pain of their friends and family – I am truly sorry for their loss.

As an employee of a company that specializes in disaster recovery software, I am also privy to many stories of companies that lost data that cannot be replaced. Many of these companies never recover from such catastrophes, but those that do are usually the ones who immediately look to put into place a plan that includes some sort of real-time data protection that includes replicating their critical data offsite or to some cloud repository so they are never caught in such a predicament again. If that is your story or even if you were lucky enough to avoid disaster this time but want to prepare ahead, please contact me immediately so I can help you assess your risks and recommend some data protection and disaster recovery solutions to help mitigate the risks.

Hurricane Sandy Disaster Recovery for Business