Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers 2

up vote 5 down vote accepted

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.

share|improve this answer
    
Good real-world synopsis. Thanks. –  BoxerBucks Mar 18 '11 at 16:29
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.