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

Posted on 2009-04-17
Medium Priority
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
  • 2

Accepted Solution

lionel_barre earned 1000 total points
ID: 24166154
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

St3veMax earned 1000 total points
ID: 24168209
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

ID: 24168460
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

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



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've always wanted to allow a user to have a printer no matter where they login. The steps below will show you how to achieve just that. In this Article I'll show how to deploy printers automatically with group policy and then using security fil…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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