I am a server admin and not a dba so please ignore my ignorance. If I have
a two node SQL 2008/Windows 2008 fail over cluster, what is the benefit of
having multiple instances of SQL? In my current environment we have w
dedicated SQL servers with approximately three instances each. I would like
to move these to their own 2 node cluster but am confused on what the
benefits of splitting up the instances are?

"Go Oilers" <maxwelli (AT) nospam (DOT) postalias> wrote

Multiple instances are used for a variety of reasons. The two main reasons
are:
1. The need for different admin priviledges for different databases.
2. The need to have multiple levels of service packs and hotfixes based on
applications.

SAP is a perfect example of this situation. The version of SQL must be
approved by SAP to receive support, and SAP admins often require the ability
to administer their own databases separately.

--
Russ Kaufmann,
MVP, MCSE: Messaging and Security, MCT, MCITP, MCTS and other stuff

ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp

In addition to Russ' comments, you would want to have separate instances if
you

1) wanted different collations for each
2) wanted to set an instance-wide setting, e.g. parallelism on/off
3) wanted to resolve potential conflicts in sys.messages. For example,
you may have defined a message #60010 and a vendor app may also have one for
60010.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"Go Oilers" <maxwelli (AT) nospam (DOT) postalias> wrote

I am a server admin and not a dba so please ignore my ignorance. If I have
a two node SQL 2008/Windows 2008 fail over cluster, what is the benefit of
having multiple instances of SQL? In my current environment we have w
dedicated SQL servers with approximately three instances each. I would like
to move these to their own 2 node cluster but am confused on what the
benefits of splitting up the instances are?

I think one thing else that is missing, is that you can split the load. if
you have two instances you can run one on each node.
you need to be very careful doing this because you have to be sure that if
one node fails that the other is able to handle both loads ok.

the situations russ and tom mentioned are much better reasons, but if a
company is not willing to spend the money and understand the performance hit
if you end up on one node, then it might be a reason to do this.


"Go Oilers" <maxwelli (AT) nospam (DOT) postalias> wrote