Multiple Instances

  • We are installing SQL Server 2000 in a clustered environment. The client wants multiple instances (4-6) is multiple instance support just a box Microsoft has ticked to say 'look we have this'and really it has serious issues - resource contention of memory/cpu or is it viable.

  • It was intended to allow multiple applicaitons to setup their own SQL Server without worrying about issues with a database inside a server, etc. Like Access, multiple .mdb files can exist.

    I'm not sure it provides great benefits. If you search for multiple instances, you will find another thread on this with a variety of opinions.

    Why does the client want this?

    Steve Jones

    steve@dkranch.net

  • Thanks for the reply Steve.

    The client wants this because if the MSSQLService has to be taken down or fails over to the other node then it will affect all databases in that service. Whereas if we use multiple instances we can stop an instance or it can fail over without affecting the databases in the other instances. The client likes this idea but I am very concerned about how multiple instances will possibly affect performance. I have read quite a bit about multiple instances and the recommendation is not to have more than 4. What I really need to know is will having 4 instances affect performance quite a bit??

  • I'm not sure who is running instances, but this is an interesting idea.

    Couple things. By default the services share resources. So if a query in one instance runs amok, it will affect them all. No good way around this as the same thing happens with multiple databases in one instance. You can however, limit the memory per instance. Might get a little crazy if you have more than 4.

    You could separate the instances by using multiple CPUs and setting some type of affinity mask to balance the CPU load. You can also use multiple disk controllers and limit ram by instance to get as much separation as possible.

    However, does this really buy you something over haveing 8 instances on 4 servers rather than 1? I'd look at that and have 2-3 fail over to the same node since it's unlikely that more than one will fail at the same time. Or have 4 servers that fail over to each other.

    Not really sold on the use of instances, though you implementation gives the best reason I've seen so far (outside of desktop use).

    Steve Jones

    steve@dkranch.net

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

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