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

Posted on 2009-04-17
Last Modified: 2012-05-06
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.


Question by:YaHozna
    LVL 4

    Accepted Solution

    There is a licencing cost to having multpile instances of SQL Server.

    On the other hand running Active/active Clusters, each node is running a different SQL Server instance makes "best usage" of your hardware resources. If one server fails however both instances are running on the single server alive thus reducing the performance... This is why it is usually recommended to deploy Active/Passive Clusters.

    A disadavantage yields in Memory usage, as each instance running on a single server would use more memory than a single instance. Advantage though, environments are separate, you can manage maintenance on the distinct instances separately

    These are a few thoughts on the subject... A lot more could be said
    LVL 13

    Assisted Solution

    Another disadvantage as I see it is that depending how you setup CPU affinity; you could have issues where Instance 1 gobbles up all the CPU and leaves Instance 2 struggling.

    On Clusters I'm a firm believer of a single instance; as if one fails over for a hardware/software issue then the other picks up; if you're running more instances on one then you increase that risk of affecting more systems/applications IMO.

    Author Comment

    Hi Lionel. In this particular case there is no additional licensing cost - licensing is per CPU and we can have
    up to 25 instances at no additional cost. Also is there any reason for multiple instances taking up more memory since you can allocate memory according to what databases are running on each instance? On the other hand I however I guess that each instance of the SQL Server engine has a specific memory requirement. Something to bear in mind.

    Many thanks for the input.



    Author Comment

    Not that clued up on CPU Affinity, St3ve. I'll do some reading . Many thanks for the pointer.



    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based developeā€¦
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filledā€¦

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now