SQL Server Multiple Instances

[Puiu]

Can anyone help me with this question:

What is the difference between having multiple instances of SQL Server, each running a different database, and having one instance of SQL Server running multiple databases?

 

What is the advantage of having multiple instances of SQL Server on the same machine?

 

Thank you

[Nerseus]

We've recently brought up this discussion at my work, but we haven't gotten past initial testing of multiple instances.

 

The main difference is that you have multiple "servers", each can be configured independentently. Each named instance can be separate from the others. For example, SQL batch jobs are stored at a server level, not database level. That means each named instance gets their own set of batch jobs.

 

We're looking into named instances for our DEV enviornments to solve an issue with names of databases. We have one SQL box for development of many implementations (some live in production, some under development). We use one DB server now with the default instance and we have to rename each DB with a code word. So for project1 we might have Project1_Customer, Project1_Admin, Project1_Common, etc. (those are DB names). For another project we'd have Project2_Customer, etc.

 

What we're looking into now is performance. It's only DEV, but we don't want performance to drop where dev's can't work. With multiple instances, you get multiple instances of... well, something - we're not sure what just yet :) Maybe multiple instances of the "engine" and "services", but to what point they duplicate and what point they share common code that serves all instances, I have no idea.

 

For us, moving to local DB development would be the best option. We're looking into the new TFS suite for DBs and it seems to fit nicely in our flow - only the cost per dev is an issue. Local DB development might alleviate some of this, although we may still need named instances locally. Luckily performance wouldn't impact us there since a local DEV would only be using one at a time.

 

On a production server - I can't come up with a good reason to use named instances right now. Production is usually something you don't want to mess with, and you wouldn't want to have two servers running on the same machine. The DB box is usually the biggest in the network, for production, and we generally spend more time optimizing the DB code, indexes, and such more than anything else. I'd be afraid to throw in named instances in that situation unless I was starting with a problem that named instances would seem to solve. I can't think of any offhand.

 

-ner

[PlausiblyDamp]

It is also worth noting that because each instance is a separate install they can be service packed individually and they also each maintain their own set of logins.