TechOnTip Weblog

Run book for Technocrats

SQL 2008 Failover Clustering Guide

Posted by Brajesh Panda on April 21, 2010

Before you start building SQL Cluster; You may like to know what are the things I need to build a cluster. I will advise you read below posts before you start..
  1. Application Clustering – Config Build
  2. Windows Clustering – Reference Architecture & Build
  3. SQL Server prerequisites for installation build a cluster
Let’s Build a SQL 2008 Cluster.  Same steps will also applicable for SQL 2008 R2.

  • SQL 2008 Clustering has been changed a lot from SQL 2000 or SQL 2005 Clustering.
  • Earlier whenever you build SQL clusters all nodes get installed the same time.
  • However incase of SQL 2008 Clustering there are two ways you can complete installation,
    • Standard
      • Create a New SQL Failover cluster node by clicking New SQL Server failover installation
      • Then add other nodes using SQL 2008 Installation Wizard by clicking Add node to SQL Server failover Cluster

  • Advanced
    • Prepare all nodes for failover clustering using Advanced Cluster Preparation one by one. You can use config file (.ini) for quick turnaround.
    • Install complete failover cluster functionality on any node, preferably on the node which is holding the shared disks by clicking Advanced Cluster Completion

Install Microsoft Distributed Transaction Coordinator Cluster Resources

Before installing SQL Server on a failover cluster, determine whether the Microsoft Distributed Transaction Coordinator (MSDTC) cluster resource must be created. If you are installing only the Database Engine, the MSDTC cluster resource is not required. If you are installing the Database Engine and SSIS, Workstation Components, or if you will use distributed transactions, you must install MSDTC. Note that MSDTC is not required for Analysis Services-only instances. You can use a smaller Disk or Quorum Disk for MSDTC itself

  • If you like to create a MSDTC cluster resource, better to make available a Small Disk (Storage) in the system like Quorum. Assign a drive letter to this

  • Select Distributed Transaction Coordinator (DTC) & Click Next

  • Type a name of DTC resource & specify IP Address on Client Access Point window

  • Select Storage Drive for DTC
  • And Finish the wizard

Install 1st Node of SQL 2008 Cluster

  • On SQL Installation wizard, select Installation & then New SQL Failover Cluster Installation
  • It will run through Setup Support Rules testing

  • Verify testing results & press okay to move forward
  • Click Install to Install Support Files
  • It will run Detailed Setup Support Rule Testing for Failover Cluster. Verify the results & move forward
  • Provide License Key & accept End User License Agreement.
  • On features selection page select required features. Note: Reporting Services can’t be clustered. To make reporting service high available we have to use Windows NLB having multiple reporting services.
  • Next Configuration pages depend on the number of features you have selected.

  • On Instance Configuration page type Clustered SQL Server Network Name & Path for SQL Binary installation as Instance root directory. It is the path to local directory. Next window will show you result regarding Disk Space Requirements i.e. if enough space is available for installation.

  • In next Cluster Resource Group window provide SQL Cluster Group name. I used prefer SQL Cluster Network Name – Instance Name

  • Select Storage Drives which will be used for SQL Cluster Resource Group you have created. Not to worry you can change those drives later on. As the 1st drive in the selection will be used for default drive for all databases, I would like to select the bigger drive over here.

  • In Cluster Network Configuration window provide Network IP Address details for SQL Cluster Server

  • On Cluster Security Policy window, you can define security groups. However use service SIDs are recommended

  • On Server Configuration window select Service Accounts. For demonstration purpose I have selected my Domain Admin account over here. But it is absolutely not recommended. I expect you have knowledge on service accounts. This service account don’t need any administrative permission on nodes, minimizing any security risk.

  • On Collation Tab, I would like to retain default config. If you need any specific config please change
  • Database Engine Configuration Page – Account Provisioning page I would like to select Mixed Mode for authentication with a complex password for my SQL SA account.

  • On Data Directories Tab I would like to verify if all paths are pointed my Shared Storage Volume

  • On FILESTREAM tab enable streaming configuration as required

  • On Analysis Services Configuration Window configure account provisioning & data directories. If you have extra shared storage drives you can select those drives over here.
  • On Reporting Services Configuration page select “Install but don’t configure”
  • Configure error reporting as per organization standard. I always ignore them 😉
  • Installation wizard will check Cluster Installation Rules
  • Verify all Summary details & click next for Installation

  • Complete Installation

This installation will install SQL Failover Cluster on the node from where you are running the installation. So after installation it will Create a single node sql failover cluster. So later on we have to join new SQL nodes to make it a high available architecture.

Follow next steps on how to add another node to this Single Node SQL Failover Cluster!

Add Another Node to Existing SQL 2008 Cluster

In my lab I have created a Single Node SQL Cluster & adding additional node later on.

  • Start SQL Installation from Media & select Add node

  • Click Okay
  • Provide Product Key in other window
  • Accept License Agreement
  • Install Support Files
  • Verify Errors & fix it if required.  Even if set the binding order correctly, sometime you may see this error. I have mentioned the steps how to fix it in another post, find it here

  • Next window will show up all cluster config details. Verify everything is okay. Installation wizard detects all config from other nodes which are part of same windows cluster

  • Provide Service Account & Password Details. Installation Wizard picks up all details from other SQL Node in the Fail Over Cluster

  • Configure Error reporting as per your organizations standard
  • Next window will verify all Clustering parameters before it start the Node Joining to the SQL cluster

  • Verify detailed report about the installation & move ahead

Remove a Node from Existing SQL 2008 Cluster

  • You can remove nodes from cluster from Maintenance tab at left hand side of installation wizard & then clicking Remove Node from a SQL Failover Cluster. Start from the Passive Node who don’t won anything in the failover cluster.

After wards it pretty straight forward so you can click next->next-> finish


5 Responses to “SQL 2008 Failover Clustering Guide”

  1. great post as usual . Thanks.
    Al Riyadh

  2. You are a very thoughtful speaker. This is a good website. Thanks.
    Al Riyadh

  3. sql wildcard,sql wildcards,sql rollback,rollback sql,sql copy table,sql sum,sql mirroring,sum sql,sql cluster,sql server performance,truncate in sql,backup sql,backup sql database,backup sql server,sql performance,date functions in sql,sql over,trunc…

    […]SQL 2008 Failover Clustering Guide « TechOnTip Weblog[…]…

  4. Arrange a tour through the Shey-Phoksundo National Park in the mountains of Western Nepal.
    In the next month, we’ll be talking to several
    professional photographers who specialize in specific areas of photography.
    From there, you can head south into China or Mongolia.

  5. siddhartha said

    This will allow students to learn about many of the less well known holiday practices.
    1Whenyou are referring to the related data for traditional Thai wedding ceremony,you may feel quite incredible.
    In June of 2008, I took my bodhisattva vows with an acharya (senior teacher) in our Shambhala lineage.

Leave a Reply

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

You are commenting using your 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

%d bloggers like this: