What are the advantages and disadvantages of multiple instances on a SQL Server 2005 cluster?
Posted on 2009-04-17
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 (Standard) 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. It's quite a big ask this one so 500 points. Would welcome any and all advice, points of view, comments, etc.