SQL Server Performance Forum – Threads Archive

Multiple Instance

I have heard through word of mouth that running multiple instances of SQL Server is either not recommended or discouraged. I have not been able to find any documentation explaining the downside of running multiple instances. Please help.

---

May be if you tell us what is the situation we can help more, because there is a lot of reason to have more than one instances Just and example: One instance to producction other to development when you don´t have other server. (and someone can say: why not in one instance?) In few words, each instance compite to get resources. Duplicate services, etc. One good reason could be: One to SQL 7 and other to SQL 2k, in migration case.
Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.

---

you can create multiple databases and evrything in one instance..then why we need to create another instance on same server..BUT their may be some geniune requirments. refer below links for more informations. http://www.sql-server-performance.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=1892 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_9i5u.asp http://www.certtutor.net/Articles/Index.cfm?ArticleID=20315 Deepak Kumar –An eye for an eye and everyone shall be blind

---

I had a situation to compare two databases with one pc. when that databases need to have same database name I used Multiple instances.
Even BOL also provides information regading this.

---

http://www.windowsitpro.com/SQLServer/Article/ArticleID/8686/8686.html
from BOL
Before installing multiple instances of Microsoft® SQL Server™ 2000 on the computer, you should be aware of the resources each instance will be using. Each instance acts like an individual server and yields resources only to the operating system and not to other instances. For example, if instance1 needs more memory to run a query, it will not ask instance2 to yield but will request a memory grant from the operating system. If you have multiple instances installed on a single-CPU computer, with both instances actively processing queries, expect a slowdown in the queries because both instances will compete for CPU resources. In that environment, a query that is resource intensive, such as one containing JOIN with GROUP BY or ORDER BY clauses, may take twice as much time to run as the same query on a single instance installed on a single-CPU computer. This information is based on comparing the query execution on a single-CPU computer with one instance to two instances on the same computer, with both instances running the same CPU intensive operation simultaneously. Installing multiple instances on a computer with low RAM leads to slower query execution. For example, installing three instances on a server with 64MB of RAM will slow your queries significantly. You can expect that about 15 percent more time will be required to run the same query.

---

On our test environment we are running 8 multiple instances from one server and it is used extensively by the Developers all the times, with no issues. On production we have 6 instances running on one machines which is a 24/7 application system, this one too with no issues. As it depends on the hardware and resources you allocate and optimize the databases to take care of performance before hand. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>