Solved

SQL 2008 R2 Multiples Instances

Posted on 2014-03-29
2
530 Views
Last Modified: 2014-03-31
Regards,


I want to know which configuration is best in a SQL Failover Cluster 2008 R2:

multiple instances or a single instance that unifies 5 servers?

Would greatly appreciate your views on this topic.
0
Comment
Question by:JnavarroMc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 37

Accepted Solution

by:
Mahesh earned 250 total points
ID: 39964634
Multiple instances will reduce dependencies on single SQL Service, (You will get separate service for each instance)
Multiple instances will help you for maintenance windows etc
If you are planning multiple mission critical databases on single server, then I'd prefer to have multiple instances provided that your server sizing is perfectly calculated for all database workloads
Otherwise one server \ per instances will be perfect in case of critical databases
 
For the new instance you need additional 2 disk  data /log files and the disks should be in the cluster as available. when you do the cluster setup the first time to setup instance 1 it took 2 disk. for installing the second instance it is exactly the same as setting up the first instance.

With Failover Cluster 2008 and above, you are now allowed to have multiple instances of DTC resources all active at the same time referred to as Active-Active DTC. This same principal can be applied to services or applications such as SQL Server, MSMQ or any application that needs to make a distributed transaction API call

Check below articles for more info
http://social.technet.microsoft.com/Forums/windowsserver/en-US/87fbf175-465c-4ea9-a58b-4d552f358100/2008r2-2node-cluster-how-to-configure-for-multiple-instances-of-sql-2008r2?forum=winserverClustering
http://blogs.technet.com/b/askcore/archive/2009/02/18/how-to-configure-multiple-instances-of-distributed-transaction-coordinator-dtc-on-a-windows-server-failover-cluster-2008.aspx

Mahesh.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 39964970
*laughing* thought we are not meant to use "active-active" terminology over "multi-instance failover cluster"

It is hard to answer your question. There are many factors to consider. Most of which are to do with your HA / DR aspirations and plans. Then complications like shared storage...

The problem with active/active is each node (server) is running various instances, and if one node does fail, then the remaining is failed over to and must be capable of running the whole lot. In an active / passive cluster, the "passive" is sitting for all intents and purposes, waiting for the active node to fail.

There is a good series of links etc over at : http://www.brentozar.com/sql/sql-server-failover-cluster/

And a good whitepaper to download : http://download.microsoft.com/download/5/B/D/5BD13FFA-5E34-4AE1-9AA0-C6E6951B8FC8/SQL%20Server%202008%20R2%20High%20Availability%20Architecture%20White%20Paper.docx

And of course, Books OnLine : http://msdn.microsoft.com/en-us/library/ms189134(v=sql.105).aspx (and note the topics on the LHS).

If you can express what you ideally want to accomplish, along with your business processes (in terms of DB's) then it might be easier to provide more guidance.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VMware PVSCSI SQL Server 2016 AlwaysOn 2 37
SQL - Load records to temp table through CTE 6 38
SQL: Transformation or Pivot 3 37
relocating SQL 2000 18 39
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article explains how to install and use the NTBackup utility that comes with Windows Server.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question