SQL Server 2008 Active-Passive/Active-Active Cluster Installation

61 Replies

Like SQL Server 2005, SQL Server 2008 supports clustering using both the Standard and Enterprise editions. The Standard edition supports a two-node cluster, and the Enterprise edition supports the maximum number of nodes that the OS supports

The biggest changes in SQL Server 2008 cluster is that you can now use DHCP for clustering and use IP addresses from different subnets for each side of the cluster. The ability to use IP addresses from different subnets is most useful for creating multisite clusters, called geographically distributed clusters.

SQL Server Cluster Installation

Step 1: I hope you had done with all the pre-requisites required by cluster.If you’re installing SQL Server 2008 in a cluster on Windows 2003, you’ll need to download and install the hotfix specified in the Microsoft article at http://support.microsoft.com/kb/937444 before you can install SQL Server 2008.

Note: Installation of above hotfix requires a reboot, so you have to cancel out of the installer at this point and restart the process after installing that hotfix on all your cluster nodes.

Step 2: Open SQL Server 2008 installation executable file from your CD\DVD\Shared location

Double click on installation.exe and you will find the below screen. Now select New SQL Server Failover Cluster installation option highlighted as in below image. (Note: For standalone installation we have to select standalone option)

Step1

Step 3: Once you have selected Step 2 option, installation will check for setup support rules to identify the problem. If it is failed due to any reason, you have to correct the issue. Please find the below screen shot. If it passed click on OK command button.

Step2

Step 4: Installation wizard will install the setup support files, click on Install command button

Step3

Step 5: This step will walk you through all the required information as below. Click on Next button

1. Product Key

2. Licence Terms

3. Feature Selection

4. Disk Space Reuirements

5. Error and Usage Reporting

6. Cluster instllation Rules

7. Ready to install

8. Installation Progress

Step4

Step 6: Enter Product Key or It will take automatically. See the below image for it.

Step5

Step 7: Select required features, which you want install.

Step6

Step 8: Specify the Network Name/Virtual Name for SQL Server to failover. As we are installing Active-Passive cluster we have to specify one virtual/network name. (Note: For Active-Active clustering you have specify different network names as per the number of nodes)

Step7

Step 9: SQL Server will review the disk space requirement for the features you have selected

Step8

Step 10: This step will create a new cluster resource group for your SQL server failover. Please see the note in the image.

Step9

Step 11: Select shared cluster disk resources for your SQL Server failover cluster

Step10

Step 12: Here you have to select network resources for your SQL Server failover cluster. You have to add IP address as well.

Step11

Step 13: Here you have to specify the service account for SQL Server services. It is a best practice always keeps SQL Services in cluster in manual mode

Step12

Step 14: Specify the authentication more and SA password in account provisioning tab, configure the location of the data files, log files, temp database in data directories tab and enable file stream as per the requirement

Step13

Step 15

Step13

Step14

Step 15: Select check boxes as per your requirement, I haven’t checked any option. Proceed with the next button

Step15

Step 16: Installation wizard will check the cluster installation rules, please correct if you found any failure and proceed with the next button

Step16

Step 17: Now your SQL Server cluster is ready to install. Click on Install button

Step17

Step 18: Apply the required Service Pack/Patch and reboot the server

Step 19: Once installation is done on this node, we have to proceed with the installation of SQL Server on the other cluster nodes. SQL Server 2005 was the last edition of SQL Server to automatically install SQL Server on all nodes at once. Beginning with SQL Server 2008, we must manually install and patch each cluster node individually. This process allows for less downtime as we are patching the cluster or upgrading your cluster from SQL Server 2005 to SQL Server 2008.

Step 20 Installation on the other nodes is similar to installing on the first node except that we have select the Add Node to a SQL Server failover cluster option from the initial menu. The same software requirements apply to the other nodes in the cluster, so the installer will handle the verification and installation of these components as well.

Because most of the settings are picked up from the other node(s) in the cluster that already have SQL Server installed on them, the process of adding a node to the cluster is bit shorter than installing on the first node

Step18

61 thoughts on “SQL Server 2008 Active-Passive/Active-Active Cluster Installation”

Abhinav Andhare October 7, 2009 at 11:57 am

Jugal,

This is very informative article and this has helped me a lot in installing the cluster enviornment.

Thanks for posting this article.

Abhinav

Reply ↓
Pravin October 27, 2009 at 4:39 am

hi,

I do not see any information related to active-active cluster installation on internet.
Is active-active cluster really supported by microsoft?

Please help.

Thanks

Reply ↓
1. JShah Post authorOctober 31, 2009 at 10:57 am
  
  Hi Pravin,
  
  Micrsoft Supports Active-Active and Active-Passive clustering
  
  Regards,
  Jugal
  
  Reply ↓
  1. Javed November 7, 2009 at 8:44 am
    
    Dear friend
    
    I want how to configertion 2 both node Active Active pls any one of friend doccument
    
    windows 2008 server & mssql server installion both Active ,Active installion
    
    pls send it
    
    Regard
    Javed Rohile
  2. JShah Post authorNovember 7, 2009 at 1:03 pm
    
    I have already added article for Active – Passive clustering. In active-active clustering you have to install sql server on both the instance. For SQL Server 2008 and windows server 2008 you can add 2 DTC service
Pingback: A Cluster Of Fun « 36 Chambers – The Legendary Journeys: Execution to the max!
Gad Frenkel December 25, 2009 at 4:02 pm

I understand conceptually that you’re basically insstalling two active-passive SQL clusters with Node1 failing over to Node 2 and Node 2 failing over to Node 1. What I don’t get is the underlying failover cluster. Say Node 1 is the primary for the FO cluster. Installing SQL Cluster 1 is no problem since Node1 one owns the disks. How do I install SQL Cluster 2 on Node 2, since Node 2 sees all the cluster disks as offline being that Node 1 is the FO cluster primary node?

Reply ↓
1. JShah Post authorDecember 30, 2009 at 12:43 pm
  
  Good Question. Do the failover for node2. Make sure Node2 has ownership of the required Disks and resources.
  
  Reply ↓
  1. Raja July 6, 2011 at 10:00 am
    
    can you explain in detail?
2. RaviTeja June 19, 2013 at 12:30 pm
  
  Gad, when you have windows server 2008 or later, you can check for the shared drives available in the cluster group called “Available Storage”. When you install a new instance in the cluster, you can either failover the “Available Storage” group to the node where you are installing and let SQL Server to choose the shared drives from the pool.
  
  Reply ↓
Phil April 16, 2010 at 7:26 am

Didn’t the title say active/ active aswell?? Was looking forward to reading that!

Reply ↓
1. rujinraj May 28, 2011 at 7:27 am
  
  can u give full details of active/active
  
  Reply ↓
  1. Jugal Shah Post authorJune 1, 2011 at 5:19 am
    
    What specific details you are looking for?
Greg June 4, 2010 at 5:35 pm

In the beginning of this article, you mention that SQL Server 2008 supports nodes on different IP subnets. However, I have found a mountain of contradictory evidence to this. My understanding is that this was supposed to be included in SQL 2008 R2 but is not. Have you been able to get that working or is your statement in error?

Reply ↓
1. JShah Post authorJune 29, 2010 at 12:05 pm
  
  I never tried but I think it is possible.
  
  Reply ↓
Reedb August 4, 2010 at 6:20 pm

Has the failover clustering already been installed?

Reply ↓
Deepak October 22, 2010 at 7:25 pm

Did you try to install SQL2008 R2 on Node 2 using GUI?
1.) I was not able to install it asked for SQL manager service account and it was disabled so could not eneter it and just have to quit installing but was able to install using CLI.

2.) Will you please also clear how to make Active/Passive installation to Active/Active on Windows 2008 R2 and SQL2008 R2?

3.) Hot Fixes on SQL2008 R2 what should be the procedure?

Thanks

Reply ↓
snivas October 23, 2010 at 12:18 pm

Hi Shah

Thanks for Publishing the such a good document,I have couple of question on the same.
I have SQL Server 2000 SP4 on Windows 2003 SP2 Active/Passive Cluster

1.can I follow same doc for Side by Side installation of SQL Server 2008R2
2.From which node I have to start installation active node or Passive Node.
3.Is it required to creation or modify in excisting (Quoram and MSDTC)your not mentined any thing about Quorum and MSDTC.or I can use excisting.

can you have any document which about side by side,Please help us.
4.

Reply ↓
snivas October 26, 2010 at 12:28 pm

Hello Jsha,

Did any hot fix is required for SQL Server 2008 R2 ,If Iam doing Installation On Windows 2003 SP2.

Reply ↓
1. JShah Post authorOctober 29, 2010 at 1:32 pm
  
  mostly not but plz check books online
  
  Reply ↓
2. JShah Post authorNovember 18, 2010 at 1:10 pm
  
  Yes. Plz check MS Support site
  
  Reply ↓
ckwee November 11, 2010 at 6:14 am

coming from oracle rac background, the underneath file system must be able to support cluster e.g. veritas CFS, or use ASM or raw devices. This article didnt mention about the type of filesystem that mssql need to have. so i have my doubts. Has anyone tried this yet?

Reply ↓
Pingback: 2010 in review « SQLDBPOOL.COM
Abhinav Andhare January 5, 2011 at 11:14 am

Jugal, You make the DBA’s life easy, configuring the clusters using your article is like rubbing butter on bread. Please write smoething on how to configure the windows cluster administrator which involves more on quorum disk and other disk layouts too.

Thanks, Abhinav

Reply ↓
Santosh January 9, 2011 at 7:02 pm

Hi Jugal , is there any document showing the process of creating active active clustering?

Reply ↓
1. Jugal Shah Post authorJanuary 11, 2011 at 5:47 am
  
  I don’t have any ready made document right now. But Active-Active cluster installation you have to follow the mentioned steps on both the nodes. Once that is done, you have to add nodes using installation menu.
  
  Reply ↓
Khan March 3, 2011 at 2:28 pm

Hi Shah,

Thanks for good article, what about A/A ?

thnx
SQLdude

Reply ↓
1. Jugal Shah Post authorMarch 11, 2011 at 4:25 am
  
  Active/Active cluster you have install cluster SQL Server on both the nodes. Once the installation done, you have use “Add Node Option” from the installation on both the nodes.
  
  Reply ↓
dhaval March 15, 2011 at 11:43 am

Im new to MSSQL.

Like RAC. Can i keep data directory in sharable mode on SAN where two physical server access same data directoy.

Reply ↓
1. Jugal Shah Post authorMarch 18, 2011 at 6:26 am
  
  In SQL Server only 1 file will be used by the instance at time.
  
  Reply ↓
Indrajeet April 6, 2011 at 7:59 am

Hi Jugal,

Could you please explain, How Quorum works in Active/Active Cluster?

Reply ↓
venkat April 6, 2011 at 12:25 pm

my question is can we configure active/active SQL2008 cluster if yes, I need to configure as follow in 1st instance we have database and 2nd instance we need to configure reporting services so that we avoid wast of resourse

Reply ↓
Dave Brown April 21, 2011 at 10:18 am

Hi Jugal,

An interesting article many thanks.

I’ve an active/passive cluster with 3 sql instances that for operational issues needs to be configured as a active/active . Do you have an article that would help me with this ?

Reply ↓
1. Jugal Shah Post authorApril 21, 2011 at 6:46 pm
  
  Dave, I don’t have any handy document for this. Let me know your queries, I will post answers.
  
  Reply ↓
Jahanzaib April 29, 2011 at 5:33 pm

Enterprise Edition support 16 nodes not OS Support,Data Center Edition support OS level support

Reply ↓
v2k2m June 14, 2011 at 5:28 pm

I just finished building 3 node cluster on Win 2k8 r2 AND sql 2k8 r2. I want to know how will one set it up so two of the the nodes are active at one point of time? Is there any way we can configure it? I have two services one for msdtc and another for SQL, running on all three nodes but only active on one node at a time. Your help will be highly appreciated.

Reply ↓
shankar June 22, 2011 at 9:05 am

hi jugal,

could you please tel me prerequirements while configure cluster (winodws and sql server)
help me this and cluster interview questions .

Reply ↓
1. Jugal Shah Post authorJuly 11, 2011 at 5:47 am
  
  Hi Shankar, I haven’t prepare any document for it, you can micorosoft site, they mentioned each and everything….
  
  Reply ↓
kvktandon July 5, 2011 at 8:48 am

Hi J-Shaw
its really good to know the installation of sql clustering and please keep on posting the other configutrations and issues

great job

Reply ↓
1. Jugal Shah Post authorJuly 11, 2011 at 5:44 am
  
  Thanks
  
  Reply ↓
Raja July 6, 2011 at 9:58 am

How to add a drive in active-passive environment if the drive is full?

Reply ↓
1. Jugal Shah Post authorJuly 11, 2011 at 5:44 am
  
  Do you want to add one more drive or you want to increase the storage? What kind of disk you are using in clustering???
  
  Reply ↓
Srinivas Ch August 31, 2011 at 9:45 am

good

Reply ↓
Srinivas Ch August 31, 2011 at 9:46 am

nice article

Reply ↓
hodai September 6, 2011 at 3:39 pm

hi
have a nice day
can you explain in detail of sql server 2008 cluter
can you give blog the most sgl server 2008 cluter

Reply ↓
hodai September 7, 2011 at 10:07 am

hi have nice a day
are you give document of sql server 2008 cluter?
are you sent gmail me ?

Reply ↓
Ashish December 21, 2011 at 5:11 pm

Can someboday forward me basic stuff of clustering?

Reply ↓
Anand January 17, 2012 at 3:37 am

Hi jugal,

I’m working as a storage engineer for fujitsu SAN.we are implementing sql clustering also,can you please send me the stuff related to clustering need from basics….Please help me to do better…..mi id anand.9rk@gmail.com

Reply ↓
rani January 27, 2012 at 2:39 pm

Thanks alot,Nice documentation.

Reply ↓
gautam February 8, 2012 at 1:22 pm

hi
i have 2 windows server 2008 r2 standard sp1 multilig…
& 2 sql server 2008 r2 enterprise ….
how to configure Sql failover clustering…
its posible on win2k8 server 2008 r2 standard
plz gys help me ….

Reply ↓
1. Jugal Shah Post authorFebruary 10, 2012 at 9:16 pm
  
  Gautam, you can setup clustering with Win Enterprise edition, plz check for the minimum requirement for the SQL cluster.
  
  Reply ↓
Gerardo J March 16, 2012 at 3:22 pm

Hi, I understand Active/Active as a Cluster where the DB instance uses all Hardware resources contained in all the cluster servers, i.e if you have cluster with 2 nodes, each with 4 CPU´s then the Active/Active Cluster will enable to use the 8 CPU for the same Database instance. Is this supported by SQL Server?

Thanx in Advanced!
Kind Regasrds.

Reply ↓
1. Jugal Shah Post authorMarch 22, 2012 at 12:24 am
  
  No, it will only use the resources of the server where sql instance is running.
  
  Reply ↓
chandar April 3, 2012 at 1:39 pm

i want setup clustering environment :

Please let me know the steps for windows cluster as you explained above sql cluster

Thnaks in advance
Chandar

Reply ↓
1. Jugal Shah Post authorApril 11, 2012 at 8:33 pm
  
  You can download the virtual machine and install the clustering on local box.
  
  Reply ↓
Dhamu April 4, 2012 at 4:53 am

it’s mentioned that in active/active sql instances will be running on both the nodes.so without utilising the reource on both the node how sql instances will be running on both the nodes?

Reply ↓
1. Jugal Shah Post authorApril 11, 2012 at 8:32 pm
  
  in activie-active clustering SQL instance will run on different servers/nodes and they will utilize the resource of the nodes on which SQL Server is running. For example if you are planning for the hardware for cluster.. and the both the nodes requires 32GB memory, you have to allocate 64GB memory to both nodes, in case of failover SQL Server has enough resources.
  
  Reply ↓
Sql Query April 21, 2012 at 2:30 pm

I have a existing cluster active /passive.I would like to install Active /Active .
is that i can use same storage groups and DTC ? or do i have to use different Cluster group?
do you have any article posted for that?

Thank you

Reply ↓
1. RaviTeja June 19, 2013 at 12:42 pm
  
  You need to have a different Cluster group and should have a separate shared disk for a new instance. The MSDTC which is already there can be used by the new instance. How ever, starting from Windows server 2008, we can have multiple MSDTCs for each cluster groups.
  
  Reply ↓
karthik February 1, 2013 at 8:34 am

Jugal sab ,

i’m looking for job in sql server dba can u guide me please

Reply ↓
Chetan February 3, 2013 at 3:00 am

How many IP Addresses(Private or Public) are required for 5 Node (4 Active, 1 Passive) Cluster ??

How many IP Addresses(Private or Public) are required for 3 Node (2 Active, 1 Passive) Cluster ??

Thanks In Advance..!!!!

Reply ↓