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


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 

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

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 

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

Advertisements

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

  1. Abhinav Andhare

    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
  2. Pravin

    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. Javed

        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 author

        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

  3. Pingback: A Cluster Of Fun « 36 Chambers – The Legendary Journeys: Execution to the max!

  4. Gad Frenkel

    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. RaviTeja

      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
  5. Greg

    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
  6. Deepak

    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
  7. snivas

    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
  8. ckwee

    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
  9. Pingback: 2010 in review « SQLDBPOOL.COM

  10. Abhinav Andhare

    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
    1. Jugal Shah Post author

      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
    1. Jugal Shah Post author

      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
  11. dhaval

    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
  12. venkat

    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
  13. Dave Brown

    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
  14. v2k2m

    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
  15. shankar

    hi jugal,

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

    Reply
  16. kvktandon

    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
  17. hodai

    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
  18. Anand

    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
  19. gautam

    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
  20. Gerardo J

    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
  21. chandar

    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
  22. Dhamu

    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 author

      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
  23. Sql Query

    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

      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
  24. Chetan

    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

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s