Multiple SQL Server Instances?

[cpatters]

Why are multiple SQL Server instances on a large server more optimal for testing and development than multiple databases with different names in one instance?

 

[tlbroadbent]

I don't have an answer based on experience. We run standard edition so running multiple instances is not an option. I have some opinions.

1) It should be easier to implement a security model with multiple instances. If you have several enties (companies, divisions) that need SQL Server but don't share data, multiple instances of SQL could be used to segregate them.

2) It would be easy to implement a test environment that duplicates the production environment.

3) I don't know how there could be any performance improvement with multiple instances. I would think performance would be hurt with multiple instances on the same server. Perhaps someone with experience could enlighten me about this issue. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[fluteplr]

The only reason I have found so far is so I can run 7 and 2000 on the same machine. I also have a Case Sensitive 2000 instance that we use once in a while to test stuff. I would be running this stuff on different boxes if I had more boxes.

There seems to be a performance hit, if for no other reason that two instances take up more memory.

BTW - We usually only have on of the instances running at any one time.