When to use an MS SQL instance vs. different database on same instance

Question

We have some MS SQL servers that are setup with different instances on the same server to separate applciation DB's as well as some servers that are setup with all DB's on the same instance, just separated with security settings.

When is it advisable to create a new instance for SQL server and install your DB's in that instance as opposed to just creating a new DB on the same instance and putting security around the database itself? Is there more to the decision that just a security aspect?

Answer 1

Here's some of the reasons to use multiple instances:

Using different instances (named instances) allows you to run different application databases at different SQL Server Service Pack / fix levels, or indeed on different versions of SQL Server.

If an application (particularly from third party vendors) requires elevated privileges, then it makes sense to separate it by putting it on its own instance.

Using different instances is a primitive way of resource allocation - allocating only so much memory to one instance, and offering a different amount of memory to another instance.

In a clustered environment, using instance-stacking is a good way of getting a better ROI - you've paid for all that hardware, and the licencing costs.

Answer 2

Reasons for separate instances:

• If you have different SQL Server version requirements then you will have to use named instances
• If for some reason you have a separate set of people you want to be sysadmins then you would need separate instances. I would expect that to be quite rare, though.
• If you need to dedicate X amount of system memory to one or more clients then you will need a separate instance for that as well.

Otherwise I prefer to create multiple databases rather than multiple server instances.