SQL 2008 Failover Clustering Guide
Posted by Brajesh Panda on April 21, 2010
- Application Clustering – Config Build
- Windows Clustering – Reference Architecture & Build
- SQL Server prerequisites for installation build a cluster
- 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,
- 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
- 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
- Before you start the installation meet all pre-requisites as defined at http://technet.microsoft.com/en-us/library/ms189910.aspx
- Over here in my lab I will be concentrating on Standard Installation. May in a later day I will publish the advanced installation steps.
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