SQL Server 2005 Clustering Questions

  • We are in the process of purchasing a SQL Server 2005 cluster. Our network admin told me that one of our vendors told him that if we are using an Active/Active cluster that there will be two copies of the data stored for each instance. I have done a lot of reading and have not read that anywhere. It seems to me that indicates database mirroring rather than clustering. It is my understanding that in an active/active cluster, one copy of the data for each SQL Server instance is stored on the shared disk array and the cluster administrator will manage the data in case of a failover.

    Also, we are trying to determine which is the best way to go, either Active/Active or Active/Passive. We need two instances of SQL Server. One will be for production and one will be beta testing (our development is on a different server altogether). I've read that it is best practice not to run two SQL Server instances on the same node, so in that case we should use active/active. I have also read that active/passive is easier to manage. The beta server will not have much traffic on it, but will have more logging enabled. I appreciate any advice/experience you can share.

    Thanks!


    Wendy Schuman

  • In general I think the terminology of "Active/Active" vs "Active/Passive" is on its way out. It's really a question of how many instances you want to install across the cluster. If you have two nodes and two instances with one instance on each node... you'd have an active/active cluster.

    I would recommend that you not store production and test on the same physical hardware if at all possible. Clustering is done to provide a high level of uptime via fast recovery in the case of hardware failures. That's not really required on most test systems in my experience (your environment may be different, I'll admit). If I could I'd put the test system in its own environment, and install a single instance on the cluster to run production. This also would avoid problems where if you screw something up on test there's less chance of it affecting production.

    And yes, the vendor is probably talking about mirroring or replication... though that's kind of vague. Clustering has a single copy of the data with multiple physical hosts connecting.

    But that's my experience, your mileage may vary.

  • I have read that Active/Active and Active/Passive is not the correct terminology anymore and people are now using the terms single instance or multiple instance clusters. I wasn't sure how many people were actually using the new terminology.

    In our case, most of the hard core testing should be done on the development server before migrating to the beta servers. We would like the beta application to be on the same hardware as our production environment for additional testing/logging. We will only have two servers available for the cluster to begin with, so I think we will have to go with the multiple instance cluster. I'm pretty sure I won't be able to talk our IT staff into purchasing two identical clusters.

    Thanks for your input and clarification about one copy of the data ... I thought that didn't sound right.


    Wendy Schuman

  • Ultimately the question of putting Test and Production come down to the value of the application to the business.  The Infrastructure guys should have very little say in what is done.

    If the application is essential to running the business, then it should have dedicated hardware in order to meet the SLA.  If the application is not business-critical, then the SLA around it will cater for downtime in Production caused by problems in Test.  You need to talk to the project sponsor to help sort out this type of issue.

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply