February 21, 2005 at 1:34 am
Why would you have more than one instance of SQL Server on one box?
February 21, 2005 at 2:15 am
In my case,
I have a application which has hard code the db name , so to test the new upgrades with the old db i need to have two instances
My Blog:
February 22, 2005 at 12:35 am
We have a big SQL Server box with 4 instances. Three for the architectual layer infrastructure we use, and every application that belons to any of these layers wil be in the corresponding instance. The forth is for the Internet applications. That way we have a little extra wall between the Internet and other applications. All is on a 2 node cluster on a EVA SAN and on every node we have two instances.
THat is for production, and for Acceptation, Systemtest and Development we have a 2 node cluster with on one node 4 Acception instances and the other node the 2 Systemtest and Development instances.
February 22, 2005 at 8:07 am
One of our test servers has 2 instances. One is installed as a case sensitive instance to conform with a vendor's application, the other is a default install.
Francis
February 22, 2005 at 8:21 am
You need to have specific reasons to go multiple instance- because it does increase complexity and license costs if you are running Standard Edition. Not to mention some applications have issues connecting to a named instance. You also need to think about hardware- multiple instances could contend for processor, memory, etc.
So there are 'costs' involved with multiple instances- just make sure the benefits justify the costs.
Jason
Jason
February 22, 2005 at 8:25 am
If you plan any instance, if it is named or default, these items should always be considered.
February 22, 2005 at 9:00 am
Here are some of the reasons I have come across:
- App Vendor specific collation / sort order / case sensitivity
- Hard Coded Server Name
- Customer Isolation (by request)
- Service Pack Level specified
- Separate Development and Test
- Server Consolidation (Money) if the box is powerful enough and already EE on it.
HTH
* Noel
February 22, 2005 at 6:12 pm
If you have one instance on a multi CPU server then you will only be using 1 CPU for the SQL instance (each instance has a single process). Whether this outways the other overheads of an instance I have no idea!!!!
February 23, 2005 at 12:23 am
Are you sure? Can you provide me a link from the Microsoft site where that is confirmed?
February 23, 2005 at 1:32 am
I am not quoting a Microdoft site, I am speaking from observation of a single Instance machine with 2 CPUs with a high load of queries for multiple databases that will only run at 50% busy (SQLSERVR.EXE running flat chat on 1 CPU). (The second CPU will still get used by other processes such as SQLAGENT.EXE)
February 23, 2005 at 1:39 am
I think that this subject is worth an article if anyone is prepared to write it.
Scenarios, Pros, Cons, Gotchas etc.
February 23, 2005 at 2:49 am
In our case, we have a separate SQL instance for the HR database as it contains alot of sensitive information. With two instances you can have a different "sa" password for each instance and you can also remove the administrators group from one instance and not the other.
February 23, 2005 at 3:45 am
Other advantages of instances
- better security not using the deafulat TCP port
- each instance has its own tempdb
- plus you can have MSDE instances (with its limitations eg 2GB database size) and decreas licensing costs
Disadvantgaes
- harder to manage
- application connectivity
Adam
February 23, 2005 at 9:21 am
Adam check you install properties. SQL does a good job of parcing accross multiple CPUs. I can be told to use only one however.
Carl Davis
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply