What are the advantages and disadvantages of multiple instances on a SQL Server 2005 cluster?

  • Looking for advice rather than the solution to a specific question here.

    I'm trying to make the case for creating a number of additional instances on my company's existing 2-node active/passive SQL Server 2005 cluster. Currently there is a single instance overloaded (to my mind anyway) with 92 databases (excluding the systems). (I'm sure I read somewhere that the recommended limit was 25).

    So I figured that I would recommend the creation of a number of additional instances to host separately in-house databases, third-party databases, SharePoint databases (we're adopting SP big-time at the moment) and a test instance (currently databases are developed on a test server then moved to the live cluster - would be useful to have a test instance on the live cluster). Naturally, however, I'm being asked to justify that recommendation in terms of increased efficiency both of my time as DBA and of the cluster itself. Apologies for the somewhat lengthy preamble by the way.

    So what then are the pros and cons? I've mentioned having fewer databases in any one instance, I guess thereby making Admin easier, at least in terms of managing scheduled jobs, etc. Are there technical advantages as well (or indeed disadvantages)? I figure memory management could be more effective - i.e.the individual instances could be apportioned the optimum amounts of memory. Security might also be improved. By the way, the cluster nodes are quad-core Intels running Win 2003 Server. Does this mean that the multi-threading capabilities of such cores will mean better management of, for example, locking, blocking or even more efficient running of scheduled jobs?

    And what, if any, advantages/disadvantages are there in terms of I/O? Databases are all held off-server on FAS storage in this case. Will having several instances instead of one mean better performance, poorer performance, no change?

    I'm sure there are lots of other things I haven't mentioned or indeed thought of. I would certainly welcome any and all advice, points of view, comments, etc.

    Regards,

    YaHozna.

  • Another advantage worth considering are licensing costs. Once you've paid for SQL Server and have it running on a given machine, you can create instances to your hearts content. If you wanted to add another machine to your system, you need to purchase more licenses.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • I'm not a big fan of multiple instances because to me there is an administration overhead, to me its one of those cases of just because you can do it doesn't mean you have to. So there has to be a good reason to split the databases out into multiple instances, such as security, the need to run at different service packs, business requirements (divide databases up by department say) or contention on tempdb

    In your case you are on a cluster so you could go active\active and make better use of memory and CPU resources and actually use the second server, there will be license costs though.

    IO wise it does not really help unless you can split the instances on to different drives, but you don't need a new instance to move databases about. If you have a heavily used tempdb it can help because you get multiple tempdbs, but again they should be on sperate drives.

    memory can be a pain because you get into having to allocate memory on a per instance basis and can't let SQL server decide what it wants. Multiple instances don't use memory as well as a single instance. This goes away if you have loads of memory and are on 64 bit.

    Do you really want your test instance on the same server, a badly formatted query hogging CPU could still slow the whole server. This would be a case for active\active, with the test instance on the other node.

    ---------------------------------------------------------------------

  • I completely glossed over the test instance thing on the first read. Yeah, that's a bad idea. You can seriously mess up your environment that way. Testing and development should be seperated from production, completely. Remember, it's not paranoia when they're actually out to get you. Protecting the production system is job #1.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • One of the things you will have to consider is the additional LUN's that will need to be presented to the cluster. Remember, each instance is going to have to have it's own disk resources.

    Depending on how you carve that up and how many instances you have, you'll probably end up using mount points. Which is another level of maintenance altogether...

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'm with George, I think multiple instances are a pain. The only load issue I see is that you can separate out tempdb issues with separate instances.

    Memory isn't optimized. The instances don't like to give up memory, so you essentially have to set the amount for each server. So you won't necessarily save there. You could perhaps limit one db/app from filling up the data cache, but if that's the case with one or two, you might be better off moving those to a new server anyway.

    Personally I like the idea of VMs, with more I/O separation rather than multiple instances. I think the hypervisors balance memory better than instances do.

  • Many thanks to those who have taken the time to reply with some very useful input. And apologies for having taken so long to reply - am on a course this week.

    Well I'd pretty much decided I'd be going ahead with multiple instances until I read the replies. However it seems there are as many disadvantages as there are advantages. I'd rather assumed it would be useful to allocate memory according to each instances needs however both George and Steve suggest SQL Server isn't that great here. On the other hand it is x64 and I do have lots of memory.

    Additional LUNs aren't a problem - the disk space is available although I suspect the Network guys would prefer to use it for other things. I would be unlikely to end up with more than 3 or 4 instances so mount points probably aren't an issue.

    Certainly security is mentioned as one advantage. Additionally, due to all the scheduled jobs associated with the 90+ databases hosted on the default instance I'm having to purge frequently running jobs from the job history. On the other hand that's no big deal. I don't really need to see several hundred versions of the same job step.

    I certainly take the point about having a test instance on the production infrastructure. My thinking here was that it would be for databases that had already been developed and tested in a development environment but would ideally need to be further tested on the production infrastructure. However maybe that could be better achieved through virtualisation. And Steve's mention of virtualisation might be something to consider. Already using VMWare extensively and there's a new EXS farm due to come on-stream so will definitely re-visit that.

    A couple of replies mentioned tempdb. Would I be better served (no pun intended) forgetting about additional instances and carving out additional LUNS for tempdb's files instead (there are 4)?

    In short lots of food for that. Once again many thanks indeed for the input.

    Regards,

    Gordon.

Viewing 7 posts - 1 through 7 (of 7 total)

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