April 6, 2010 at 5:24 am
Hi List,
My organisation has been running several applications on MS SQL Server 2005 SP3 single instance cluster. Recently we had performance problems, and I was asked to work out for a new solution.
My first thought was to install MS SQL 2008 multiple instance cluster in a two node Windows 2008 cluster. That means I would like to install two instances of MS SQL Server 2008 on each node of wondows cluster.
My questions are:
Is it possible to install multiple instances in a two node environment?
Which edition of MS SQL Server 2008 do I need?
What are the hardware prerquistes to run multiple intances in a two node environment?
I would very much appreciate if there are examples for this task and if there is literature on this topic.
Thanks for your help in advance.
Regards
Niyala
April 6, 2010 at 9:01 am
Please refer to below link about sql server 2008 cluster.
http://msdn.microsoft.com/en-us/library/ms189134.aspx
I doubt why you want to install sql server 2008. SQL Server 2005 also support multi-instance. Have you identified the root cause of performance problem? Can you make sure the problem can be resolved by upgrading db?
Welcome to my website: DB Expert
April 7, 2010 at 12:16 am
if you do this you have to make sure you have enough memory on each individual server to handle all 4 instances and any OS commands in case of a failover.
That being said yes it is possible and once the active\active cluster is setup running the install is fairly straight forward using default values.
However I also agree I would not jump to 2008 since that is just adding additional potential issues. I would suggest you run trace (use profiler if needs be) and see what is taking up cpu and taking the most time and by far the most important typically is how much IO is going on on the disk. I would also check you page life expectancy and see if there are any memory grants pending.
I am guessing with the write tuning of the scripts and stored procedures and some reindexing and other general maintenance you could get it working where it is. But upgrading to new hardware is always good too 🙂
Hope that helps get you in the right direction
April 7, 2010 at 3:17 am
Thanks a lot for your information.
I will consider your suggestions in a test environment. I still wonder if there is information on hard requirements to install multiple instance cluster.
Suppose I have two instances running on the Active node. Two instances on the passive node.
My question is, how can I configure the shared disk? How many shared disks do I have to buy?
Thanks indeed for your clues.
Regards
Niyala
April 7, 2010 at 7:08 am
Niyala,
Are you planning to run 1 cluster with multiple sql instance (not sure if this is possible)? or are you planning to run 1 pair of cluster with multiple cluster and 1 instance of sql per virtal cluster name.
In any case, each cluster resource would require it's own disk resource. Before jumping to sql 2008, you should determine what are your bottleneck first. If you are going to reuse the same hardware, writing to the same SAN, using the same network card, I am not sure how much you would benefit after doing all of these works.
Good luck.
--sopheap
April 7, 2010 at 7:21 am
Hi sopheap,
I am planning to run multiple instances of MS SQL Server 2008 in a two node windows 2008 cluster. Our previous system was running on Windows 2003 and MS SQL 2005. We are planning to purchase new hardware on which we can run Windows 2008 cluster and run multiple instances of MS SQL Server 2008.
I have read bits and pices of information and found that it is possible to run multiple instances of MS SQL 2008 in a two node windows 2008 cluster. I just could not find clear examples of implementing this.
Thanks for your information regarding the shared disk. I understand that I have to plan budget for two shared disks if I would like to run two instances. Three disks for three instances.
I would appreciate any hint and good examples.
Thanks in advance.
Regards
Niyala
April 7, 2010 at 8:37 am
I would recommend that you follow this link resource:
http://www.mssqltips.com/category.asp?catid=11
I have read through most of these documents and I have to say that they are pretty good.
--sopheap
April 7, 2010 at 5:00 pm
if you do it you would want one set of disk per instance. each server would only need the binaries installed once
April 8, 2010 at 12:26 pm
Niyala (4/7/2010)
Suppose I have two instances running on the Active node. Two instances on the passive node.
if the passive node has 2 instances then it is not passive is it??????
Take the time to learn more detail about Windows clustering and sql server on clustered platforms,
this will help you going forward
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 8, 2010 at 8:09 pm
also typically active\active clusters do not provide any real benefit unless you have a lot of resources avaliable for it. It would be a better idea to go Active\active\Passive in a 3 node cluster.
Also unless your need is high avaliablity the cluster typically provide no benefit. And if you need high avalibility an active\active cluster would only provide more headache during a failover than it is worth. Look in to what the actual requirements are and what would be need to make everything work and make the investment now while you have the option or possibility
🙂
April 10, 2010 at 5:48 am
xinyu.wang1 (4/10/2010)
active/passive, active/active are obsolete terms. Now the term is multi-instance.
It's not obsolete as active\passive refers to the Windows cluster itself and not SQL Server!
Example: A 2 node Windows cluster will be active\passive if one node has active software instances and the other does not. This software could be SQL, Exchange, SSO, Backup, etc the list goes on.
But yes, multi instance is now the preferred terminology to describe clustered SQL Server instances 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply