Take the 2-minute tour ×

I'm aware of the possibility to create instances but i don't know a real good reason to do it. This way one has per definition at least two sqlserver services running, but what for should this be good?

The two instances have to share all the resouces, mainly the RAM. If you have to rename the server you will end up with an access like NEWSQLServer\OldInstanceName.

So what is the case for instances?

share|improve this question
add comment

1 Answer

up vote 6 down vote accepted

Here are a few advantages to multiple instances:

  1. security: each instance has its own separate security configuration so someone with full access to one instance doesn't necessarily have access at all to another instance. If you host a big SQL server and host databases on it for different teams within your company, for instance, this can be good.

  2. versions: each instance can run a different version/service pack/patch level of SQL. This allows you to meet the needs of different apps with one SQL server.

  3. stability: if developer X runs some code on a db in instance 1 that somehow messes up the master or model databases it can cause problems for other databases in that instance but won't hurt other instances.

  4. resource control: you can to some degree control how much resources (CPU and memory and disk) an instance can use. This can be used to throttle how much of the server a specific application uses.

  5. application requirements: some applications simply require that they run in their own instance. This is less common these days but I ran into such a requirement with a recent deployment of Microsoft's OCS and I've seen it before too.

I'm sure there are more advantages (and I hope others will chime in to flesh this out) and I know for sure that we could generate another list of the disadvantages of multiple instances. Whether you need multiple instances or not is a balance of all of these factors.

share|improve this answer
Good list. I believe if you enforce TLS encryption on the server side, this also can be done per-instance. –  Greg Askew Mar 27 '10 at 23:17
I Agree allthought for some examples i would recommend a dedicated server. –  Ice Mar 28 '10 at 19:29
I'd also be more inclined to look into virtualized SQL servers over multiple instances if you had some need for them to be on the same hardware. –  Sean Howat Mar 29 '10 at 14:55
add comment

Your Answer


