October 7, 2004 at 10:32 am
What situations necessitate multiple instances of SQL Server on one server? I can see a few cases, such as different collation schemes (possibly) or other server-wide configuration differences but what else?
Which is better, many databases on one instance of a SQL Server or multiple instances of SQL Server with the databases divided among them?
Just curious.
Thanks!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
October 7, 2004 at 11:29 am
Different collation schemes can typically be handled at the database level so it's usually not an issue. Some reasons why you could want multiple instances:
- You have a cluster and you want to utilize all your hardware. Active\passive means at least one node is not utilized by SQL Server.
- You want to create different environments all together. For instance, different developers/development groups who need segmentation.
- You have a requirement for cross-database ownership chaining but have limited hardware (# of servers) so you deploy that to one instance and put everything else on the other.
K. Brian Kelley
@kbriankelley
October 8, 2004 at 2:59 am
active / active clusters are ok in as much as they provide a failover route without redundent hardware, but you do need redundent resource, and this isn't multiple instances anyway.
Beware the resource conflicts when installing multiple instances, you need quite beefy servers. With 2003 you can now use a 8 way zeon 4 with 16Gb ram so you could manage a server consolidation project to use multiple instances.
For web services where you might want to provide hosting with sql server the multiple instances may prove useful.
My experience with using multiple instances for development is that you need to manage the memory very carefully!!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 8, 2004 at 6:59 am
Agreed on managing resources carefully on a cluster. It's a given you must plan for the case where all the instances are on one node with respect to processors and memory. However, in a situation like this most organizations trade-off some performance for the high-availability with the idea of an active/active cluster.
With that said, the second part of the active is a new instance that's actually installed to all supporting nodes of the cluster. Hence the reason you can only have one default instance on a cluster. The cluster just handles the name resolution and IP for the virtual server and allows the SQL Server for a given instance to only run on one node at a time.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply