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

Solution 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.

Solution 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.