Multiple Instances; Why?

[spazman]

I did a search here for mutiple instances but most if not all seem to be about how to do it/troubleshooting.

My question is why use multiple instances?

I recently took over an IT infrastructure at a company. They are running mutliple instances on an under powered box, that also runs Exchange and BES.

Is there an advantage to doing this? Would it not be easier to just use separate DB's in the same instance?

I know about the security advantages, testing etc, etc. But maybe I am missing something? I do notice that with mutliple instances there are of course mutiple engines running all taking up 250-500 MB of RAM. Maing a sluggish machine that mush worse.

They seem to have an instance for each DB I.E. SBS Monitoring (8.0.2055), BigTime (8.0.760)(time tracking software), Sharepoint (8.0.2055), Default (9.0.4035)


Any and all input, comments are welcome.

 

[JayKusch]

quick note:

1 Application functional or load testing Load Testing - Ability to have multiple code bases loaded, coordinate stopping the needed instances to perform load testing, conduct the load testing then restart all of the instances once you are complete.
Functional Testing - Ability to have multiple code bases and be able to test independently with a pre-allocated amount of resources.

2 Server Consolidation One of the recent trends is consolidating multiple instances of SQL Server on to a single server. With multiple SQL Server instances, it is possible to perform full SQL Server backups and restorations to a single instance to maintain the same functionality as the original server.

3 Internal Hosting\Departmental Server Sharing With SQL Server originally considered a departmental solution, it is possible to have multiple instances of SQL Server on 1 physical server to equally share the server's resources (large number of CPUs and memory).
This scenario is ideal for charge backs to the departments for organizations following that paradigm.


4 Security Multiple instances are ideal for those pesky third party applications that require elevated SQL Server privileges where you do not want to commingle applications on the same instance.

5 Demos Multiple instances are ideal to support demo's where it is necessary to demonstrate a particular application and how it affects SQL Server.


Thanks

J. Kusch

 

[spazman]

Well as I described, we are a small shop.

I recognize the advatages you copied and pasted from MS Marketing material. But what I really would like to know is

In a small environment, is multiple instances better then mutiple DB in a single instance. None of the DB's have any special security requirements etc.

 

[JayKusch]

Yes the info is directly MS Marketing material and in any shop, large or small all of those points should be considered.

If you DBs have no special requirements on a resource or security level then one instance would suffice.

Thanks

J. Kusch

 

[hmckillop]

I think also that what JayKusch says was based on the original post. I read through your post thinking it was a largish company and think the response was fair for the information provided.

Based on what you have said now, by all means put your DBs onto the same server and instance. Ensure you have a strict user roles and logins implemented if you need to stop users cross-accessing DBs.
Most SMEs dont need to have more than one instance.
Managing access and installations on one server ,particularly where its your only server, is the key.
Otherwise you find you have many versions of test DBs with similiar names, etc
I have been plaqued with that in the past.

"I'm living so far beyond my income that we may almost be said to be living apart