SQL Server Performance

Best Practice for multiple versions and instances per version

Discussion in 'SQL Server 2008 Clustering' started by billc, Dec 14, 2010.

  1. billc New Member

    I know running multiple instances, and different versions of SQL is fully supported by MS. With that said I ran across an article on this site by Brad Mcgee that stated "Running multiple instances of SQL Server on the same node is not recommended."
    This is the only place I've seen anything like this recommendation. The reason why I'm pursuing this is because we are planning on building an SQL cluster to consolidate DBs for several different applications we house. In order to stay in support for our apps we will be running SQL 2005 SP3 and SQL 2008 SP1 with at least two clustered instances for each version on our three node cluster. My intention is to run all nodes Active. Obviously we will leave a performance buffer to sustain a node failure.
    Does anyone know why running multiple instances is not recommended? I'm aware of resource concerns when running multiple SQL instances and I'm taking that into account for our setup.
    Thank you!
  2. Luis Martin Moderator

    Welcome to the forums!
  3. satya Moderator

    Welcome to the forums.
    Why do you need the cluster for mutliple instances? Why not simply take advantage of Database Mirroring for automated and quick failover techniques.
    The reason multiple instances are not recommended due to the underlying hardware is shared across and when all the instances are resource-hungry the performance is affected badly.
  4. billc New Member

    Concerning the SQL Version and number of instances:
    We have ~500 databases in our environment with ~15 distinct 'products' with different levels of support. Many of these are homegrown. We will have a 'management' cluster which will house non customer facing DBs like WSUS, SCOM, AV, etc. With a separate cluster housing customer facing DBs. On our customer facing cluster each distinct product will have two instances dedicated to it for the purpose of balancing the workload for that product across more than one node. We're running SQL 2008 alongside SQL 2005 because we have a particular product with no clustered indexes. The DBs for this product grow out of control due to heap fragmentation. SQL 2008 introduced the ability to defragment (ALTER TABLE REBUILD) heaps and remove forwarding pointers. This is such a huge problem for us that it's made 2008 almost a necessity. Currently we add and drop clustered indexes then shrink the DB. Not an ideal option by any means.
    Concerning DB Mirroring vs. Clustering in our environment:
    We will be using Mirroring to an extent. Mirroring will be used to get data to our contingency site SQL cluster.
    Given the costs involved (we needed SQL Enterprise anyway) we decided that going with a 3 node cluster with all active nodes was a better fit than running 4 systems in a mirroring setup. It really came down to SAN disk space and cost. Having to double up on SAN disk would not have been possible with the number of drawers left in our current SAN and would have put us way over budget.
    [quote user="satya"]The reason multiple instances are not recommended due to the underlying hardware is shared across and when all the instances are resource-hungry the performance is affected badly. [/quote]
    We're taking this into account. In some ways we are, almost literally, throwing hardware at the problem. In one of our new nodes we have more RAM and almost as much processing power as all of our standalone DB servers combined. Moving our DBs from local disk to SAN disk will also help a great deal.
  5. satya Moderator

    Appreciate your detailed explanation with regard to the need of multiple instance.
    Agree to the points that based on the multiple databases and multiple applications it is high time to consolidate the platform. Before I suggest anything I would like to insist about the scalability & Performance of existing databases on the platform, do you have such problems or expecting any growth in terms of next few years.
    As you say you are throwing HW to the instance means the problem still exist causing more mayhem as performance problem will become worse if you simply add HW resources without addressing the actual problem. So I would suggest you to take your time to asses the platform completely in terms of SQL SErver and databases (review of instances & databases) to address every smaller to bigger problem, which means in the longer run the scalability & performance is guranteed without any problem.
    Let me know if you need any further help as I'm experienced in this field.
  6. billc New Member

    Unfortunately we're backed into a corner when it comes to improving performance. The real problem with some of our DBs is the fundamental design itself. Almost half of our 500 DBs are from one product that, as I mentioned earlier, does not even meet 1NF. Not to say that all of our products have backends that are poorly designed, but because they are from third parties we have limited leverage in these cases. Really an issue of, "it works doesn't it?".
    From a growth perspective we're definitely taking that into account. Our goal is to put this cluster in place, and baring an addition of an entirely new product, not have to do anything hardware wise to keep up with our expected growth for about 3 years.
    But you know what they say about the best laid plans.

  7. satya Moderator

    I guessed it rightly about the performance problems, I believe it is a one-off job/;task to address the performance issue which can let you or your team a peace of mind to manage the 'n' number of instances on a single-server without having a bulge in budget.
  8. dzrihen New Member

    Hi, there is a software available for SQL version control. It shifts

    the responsibility for versioning from the users to the software.You

    can check out the following website


Share This Page