Search This Blog

MS SQL Server 2008 Fail over cluster installation on Windows Server 2008 R2

Following article describes steps for installing MS SQL Server 2008 two node failover cluster on Windows Server 2008 R2.

For instructions on setting up MS SQL Server 2008 failover cluster on Windows Server 2003 please read the articlehere. 

MS SQL 2008 instance must be installed on one (1) node of the cluster and after the installation completes successfully, other nodes have to be added to the SQL 2008 clustered installation. 

With SQL 2005, the installation program was able to install the SQL instance to all the nodes of the cluster but with the SQL 2008 installation program, it is not possible anymore i.e. it has to be installed to a node (first node) and then other nodes needs to be added to the SQL 2008 clustered instance.

Following videos illustrate steps for installing MS SQL Server 2008 failover cluster on Windows Server 2008 R2. For step by step instruction read through the information below.

Initiate MS SQL Server 2008 installation on the first node (Eg: Machine Name: WIN2K8005):
Add second node (Eg: Machine Name: WIN2K8006) to the SQL Server 2008 failover cluster
Step by step installation instructions:

Before we begin the installation make sure the following prerequisites are in place


1. Servers in domain
  • Verify that servers are part of the domain.
  • Validate virtual names and IPs are not in use (for cluster, MSDTC and SQL instance)
2. Operating System & Service Pack
  • Verify OS version and Service Pack level. All servers in failover cluster must be running same OS version and service pack level. 
3. Host name
  • Check hostname on each node.
  • At a command prompt, type: hostname
4. Drive format
  • Ensure partitions used by SQL Server are created with offset=64k and formatted using 64K block size.
5. Drive letters
  • Check drive letter assignments.
  • Correct assignments for each node.
  • Correct size for each drive.
For cluster
  • Validate all disk resources to be used by SQL instance have been added to the cluster.
  • SQL installation needs to be executed from the node owning the cluster resources.
6. Disk permissions
  • For cluster installation, use mount points to minimize a number of drive letters required. 
  • For a standalone installation, using drive letters is fine unless there will be an issue with too many drive letters to use in which case, you have to use mount points.
  • Set the right permissions on the drives to be mounted. The permissions should be (Full Control):
7. NIC configuration
  • Check NIC setup
  • Ping each node of the cluster using the Public and Private networks.
  • Repeat on each server. 
8. MSDTC Service
  • Ensure MSDTC Network access is configured.  For MSDTC configuration click here

Note: “Enable SNA LU 6.2 Transactions” (Property in blue) is valid for Windows Server 2008 R2 only.

If Standalone
  • Ensure Distributed Transaction Coordinator service is enabled and running.
  • Ensure Task Scheduler service is running.
  • Ensure both COM+ services are running.
For Cluster
  • Ensure MSDTC is clustered.
  • Ensure Task Scheduler service is running on all the nodes of the cluster.
  • Ensure both COM+ services are running on ALL nodes of the cluster.
9. AD object and local policies requirements for SQL Service Account & Group

The AD group is not required for a standalone installation but is mandatory for a cluster installation. 

For standalone installation:
  • The installation program will create a local group on the server to be used by SQL. The created local group must be added to the security policy settings that are pushed through GPO.
  • The required local policies will be granted automatically by the setup program to the AD group during the installation. 
For cluster installation:
  • Domain Security Groups for SQL Services and SQL DBA's must be created
  • Created local group must be added to the security policy settings that are pushed through GPO. 
Important: (New for SQL Server 2008) The account running cluster installation MUST HAVE “Bypass traverse checking” privilege. The GPO has to be updated to have the DBA with this privilege. To enable this, the Administrators group should be added to “Bypass traverse checking” and the 'SQLAdministrators' AD group should be a member of the OS Administrators group.

Local Policies:
  • ‘Perform volume maintenance’  policy will be set by GPO from OU level:
  • Standalone: Add the SQL Group created during the SQL installation.
  • Clustered: Add the AD ‘SQL Service’. This policy is required to enable the use of “Instant File Initialization” feature.
IMPORTANT:

1. For Cluster Installation: The computer account for the SQL Virtual Name MUST BE created in proper OU before attempting the installation and DISABLED. 
If the computer account for the SQL Virtual Name is not created or not disabled– installation will FAIL!  It will be required to uninstall all the components and perform clean install.

2. (New for 2008) The SQL 2008 installation creates the SQL Name cluster resource with Kerberos authentication enabled. If the computer account is not created in AD prior to installation, the setup will fail. 

3. The AD group for the SQL instance & AD service account(s) for the SQL instance are NOT a member of the Local administrators group

10. Windows Locale Setting
  • Confirm Windows Locale Setting – use Control Panel. 
  • Regional and Language Options, 
  • Standards and formats= English (United States)
  • Location = Canada

With the Windows Locale setting as above, a default SQL install will require SQL collation as: SQL_Latin1_General_CP1_CI_AS.

11. User Account running setup

Ensure user account running setup has following privileges:
  • Bypass Traverse Checking
  • Debug Programs
For clustered installation the privileges mentioned above should be added on Each Node of the cluster.

12. .Net Framework 3.5 SP1

.Net Framework 3.5 SP1 is mandatory prerequisite for SQL 2008 and SQL 2008 R2.

Windows Server 2008 R2 has .Net Framework 3.5.1 as a component of OS. There is no installation required, but .Net Framework 3.5.1 needs to be enabled on Windows Server 2008 R2

13. Windows Installer 4.5

SQL 2008 requires Windows Installer version 4.5.

Windows Server 2008 and Windows Server 2008 R2 already have Windows Installer 4.5 version as part of the OS. 

Installation Steps:
1. Run Setup

Use the software from the DSL or from the CD-ROM
Double-click the file: setup.exe

2. SQL Server Installation left
Click  “Installation”
Click on “New SQL Server failover cluster installation”


3. Setup Support Rules
Setup will verify prerequisites for installing “Setup Support Files”.


Click Ok

4. Setup Support Files - Install
Click Install

Setup Support Files installation will proceed.  It may take a few seconds.

5. Setup Support Rules – SQL Server 
Setup will validate readiness for SQL Server installation


Note: “Microsoft .NET Application Security” Rule might generate warning due to the fact that there is no access to the Internet.


6. Product Key
Enter product key. Click Next


7. License Agreement
Check “I accept the license terms.”.
Click Next


8. Feature Selection
Select following components/features to install:
Database Engine Services
SQL Server Replication (will be selected automatically)
Full-Text Search (will be selected automatically)
N.B.: Both options are mandatory i.e. they cannot be removed for a cluster installation. 
Client Tools Connectivity
Integration Services
Client Tools Backwards Compatibility (optional)
SQL Server Books Online
Management Tools – Basic
Management Tools – Complete

Note: Marked in Blue “Shared feature directory (x86)” will appear in 64-bit versions only.
Leave “Shared Feature Directory” and “Shared feature directory (x86) unchanged.
Click Next


9. Instance configuration
Provide instance configuration and review currently installed features, if any.
Specify Virtual SQL Server name

If named instance:
Select “Named Instance” and specify instance name  

DO NOT change “Instance ID”
Keeping the instance Name and Instance ID the same will make it easier to support 
Leave “Instance root directory” unchanged
Click Next


10. Disk Space Summary
Setup will show the amount of space required to install selected features.
Click Next


11. Cluster Resource Group
The list of the cluster resource groups present on the cluster will be displayed. You need to specify which groups can be used for SQL 2008 installation.  If group was already created in the cluster, select the cluster group you want to install SQL 2008 into. Click Next


12. Cluster Disk 
The list of cluster shared disks present on the cluster will be displayed. Select disks that can be used for SQL 2008 installation. 
Select the disks that you want to be included in the SQL Server resource cluster group. Setup will configure SQL Server resource dependency on the disks automatically.
Click Next

13. Cluster Network Configuration
List of networks, available to be configured for the Virtual SQL Server IP resource will be displayed.
Check the network to use and specify IP address of the Virtual SQL instance.
Click Next

14. SQL Service Domain Groups
Specify AD group for SQL Services. You can specify seperate group for Database Engine and SQL Server Agent or use the same AD group.
Eg: AD Group: SQL2K8CL2_SQLServices
Click Next


15. Server Configuration
Unless specified otherwise use the same account for all SQL services.
Click "Use the same account for all SQL Services"  button
Specify AD service account to be used as the SQL service account.
Eg: srvSQL2K8CL2
Click Ok


For the Full-Text search service in SQL Server 2008: the service will be using ‘Local Service’ and will be enabled & started.

Select the “Collation” tab.  
Change Collation setting if required, else Click Next
At this point Setup will validate the service account, its password and AD group membership.


16. Database Engine Configuration
On the “Account Provisioning” tab specify:
Authentication mode


Select Mixed Mode
Specify password for “sa” account

In Specify SQL Server Administrators, click Add
Specify groups/users you want to be added to “sysadmin” server role
Eg: AD Group: SQL2K8CL2_SQLAdmin

Go to “Data Directories” tab and specify:

Data root directory (it should be the root of the SQL system drive)
Directories for the database files, database log files and backups. Following logic to applied:

Go to  “FILESTREAM” tab:

You can choose to configure FILESTREAM during installation or do it post installation 
Click Next


17. Error and Usage Reporting
Click Next


18. Cluster Installation Rules
Setup will verify the readiness for the failover cluster installation.
Click Next



19. Ready to install
The list of all the features and configuration to be installed will be displayed.
Click Install


Installation in progress
You will see water rise for a while, have patience – it will take a quite sometime to complete.


Click Next


Click Close


Congratulations! This completes SQL 2008 installation on first node of the failover cluster.
We still have to install instance on other nodes.

In order to add a new node to an existing SQL Server 2008 clustered instance, the installation must be executed from the node that will be added to the SQL clustered instance i.e.  node cannot be added remotely from another node.

Access other node to be added and run setup. Double-click the file: setup.exe
From SQL Server Installation left click “Add mode to a SQL Server failover cluster” and follow the prompt. 


For step by step instructions, watch the following video:http://www.youtube.com/watch?feature=player_embedded&v=oqSP0LtSJmE