New Virtual SQL/SSAS Install on a Windows Server 2008 Cluster

These are the directions my company uses to install virtual SQL/SSAS instances on a Windows 2008 Cluster.  Hope you enjoy it.

NOTE:  Please refer to the steps outlined on the http://www.mssqltips.com/tip.asp?tip=1687 for virtual SQL clustered installs.

Pre-Requisites:

To install a new SQL/SSAS instance on a Windows Server 2008 cluster, the following must be present before the install can proceed.

  • Cluster Network Name – Requested by the WSE team, the hostname of the cluster.
    Example: HELWD1CL01P 

  • Cluster Network Object – Same as the cluster hostname but its entry in Active directory.
    Examples:  HELWD1D1CL01P$ 

  • Cluster Node(s)– Requested by the WSE team, the hostname of a nodes in the cluster.
    NOTE:  We need only 1 node active in a cluster to perform SQL installs.
    Example:  HELWD1DBCL01P 

  • SQL Service Account – Requested by the DBA team, this SQL service account will own/start all SQL/SSAS instances on a given cluster.  This login must also be added to the SQL Servers NT security group.
    NOTE:  This account needs trusted for deletion set [Active Directory User Property à Delegation à Trust this user for delegation to any service (Kerberos only)].
    Example:  INSPINC\HELTESTSQLSVC 

  • Cluster MSDTC Hostname – Requested by the DBA team, this is the DNS entry for the clustered Microsoft Distributed Transaction Coordinator (MSDTC).
    NOTE:  Each cluster needs only 1 MSDTC host.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC VCO – Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the cluster MSDTC hostname.
    NOTE:  This is the same as the cluster MSDTC hostname.
    Example:  HELWD1MSDTC01P 

  • Cluster MSDTC Storage – Requested by the DBA team, this storage is required for installing a clustered MSDTC.
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
Z:\ FC 5 1
  • Virtual SQL/SSAS Instance Hostname – Requested by the DBA team, this is the DNS entry for the virtual SQL/SSAS instances.
    NOTE:  The virtual SQL instance hostnames must be in the same IP subnet as the cluster network name/cluster node(s).
    SQL Instance Example:  DWWMAINVSQL5P
    SSAS Instance Example:  DWWVOLAP5P 

  • Virtual SQL/SSAS Instance VCO Requested by the DBA team, this is the Active Directory Virtual Computer Object [VCO] for the virtual SQL/SSAS instance hostname.
    NOTE:  This is the same as the Virtual SQL/SSAS instance hostname.
    Example:  DWWMAINVSQL5P
  • Virtual SQL/SSAS Instance Storage – Requested by the DBA team, this storage is required for installing a virtual SQL/SSAS instance.  All root drive letters are available except A-F, W [Witness] and Z [MSDTC].
    NOTE:  By default we request a root drive letter, 2 data, 1 log and 1 tempdb volume for all SQL instances.  For SSAS instance a root drive letter, 1 data and 1 tempdb volume is required.  Always start with root drive letter G.
    SQL Storage Example:
Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\Data2 FC 5 50
G:\Logs FC 1 25
G:\TempDB FC 1 25

SSAS Storage Example:

Mount Point Type [FC/FATA] RAID [1/5] Size [GB]
G:\ FC 5 1
G:\Data1 FC 5 50
G:\TempDB FC 1 30

Install of a new virtual SQL/SSAS Instance:

Here are the steps to follow in order to install a new virtual SQL/SSAS instance on a given cluster.

  1. Login to each node of the cluster with the NT credentials.  Verify you can login with any errors.
  2. Verify and add, if needed, the following users and groups to the local administration group on each node of the cluster.
    SQL Service Account
    DBA Security Group
    DBA Service Account
  3. Run the Local Group Policy Editor [gpedit.msc] and verify/add the SQL Service Account to the following policy, if needed:
    Windows Settings –> Security Settings –> Local Policies –> User Rights Assignment
    Lock pages in memory
    Perform volume maintenance tasks 

  4. Run the Registry Editor [regedit] and create, if necessary, the following registry key.
    HKLM –> SYSTEM –> CurrrentControlSet –> Control –> Lsa
    DisableLoopbackCheck [DWORD(32) Value] –> 1 [Value]
  5. Run the Failover Cluster Manager [cluadmin.msc] and install/configure, if needed, the MSDTC clustered instance.
    NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.
  6. Run the Component Services [comexp.msc] to set cluster DTC options, if needed.

NOTE:  This need to only happen once on the cluster.
Console Root –> Component Services –> My Computer –> DTC –> Clustered DTCs –> <MSDTC Hostname> –> Properties –> Security
{Checkbox Settings}
Network DTC Access
Allow Inbound
Allow Outbound
Enable XA Transactions

  1. Create the following directories under the root drive letter for each virtual SQL/SSAS instance you will be installing.
    NOTE:  You will need to know the instance name for each virtual SQL instance.
    SQL Example:
    <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup
    <ROOT DRIVE LETTER>:\Data2\MSSQL\Data
    <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
    <ROOT DRIVE LETTER>:\TempDB\MSSSQL\TempDB 

    SSAS Example:
    <ROOT DRIVE LETTER>:\Data1\MSAS\Data
    <ROOT DRIVE LETTER>:\Data1\MSAS\Backup
    <ROOT DRIVE LETTER>:\Data1\MSAS\Log
    <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB

  2. Create a dummy filename under the root drive letter to identify the virtual SQL instance which will be installed:
    <ROOT DRIVE LETTER>:\<VIRTUAL SQL/SSAS HOSTNAME>$<INSTANCE NAME>_DO_NOT_REMOVE.TXT
  3. Using nslookup, record the IP address for the virtual SQL instance you will be installing.

10.  Run the SQL installation program for the virtual SQL instance you will be installing.
NOTE:  Refer to instructions on the http://www.mssqltips.com/tip.asp?tip=1709 for detailed steps.  Accept default values except for the steps identified below.

Step 7 –
Instance Features [SQL]:
Database Engine Services
SQL Server Replication
Full-Text Search

Instance Features [SSAS]:
Analysis Services

Shared Features:
Business Intelligent Development Studio
Client Tools Connectivity
Integration Services
Client Tools Backwards Compatibility
SQL Server Books Online
Management Tools – Basic & Complete

Step 8
Specify the correct SQL/SSAS Network Name/Named Instance:
Example:
SQL Network Name = DWWMAINVSQL5P Instance = DWMAIN5
SSAS Network Name = DWWVOLAP5P Instance = DWOLAP5

Step 10
Specify the following cluster resource group name:
Example:
SQL      SQL Server (<DWWMAINVSQL5P>)
SSAS   SQL Analysis Services (<DWWVOLAP5P>)

Step 11
UNCHECK any drives specified and check the root drive letter for the SQL/SSAS instance in question.

Step 12
UNCHECK DHCP and specify the IP address of the appropriate SQL/SSAS Network Name.

Step 14 –
Service Accounts:
Click “Use the same account for all SQL Server services” and specify the SQL Service Account login and the password.  The passwords is located in the Party List.

Collation:
Specify the collation Latin1_General_CI_AS for Data Warehouse SQL Instances only.  Use default for all other installs.

Step 15 –
Add the following logins for SQL/SSAS Server administrators.
SQL Service Account
DBA Security Group
DBA Service Account

SQL Server Installs
Click Mixed Mode and specify an ‘sa’ password.

Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSSQL
User dir:            <ROOT DRIVE LETTER>:\Data1\MSSQL\Data
User log:           <ROOT DRIVE LETTER>:\Logs\MSSQL\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
TempDB log:     <ROOT DRIVE LETTER>:\TempDB\MSSQL\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data2\MSSQL\Backup

Analysis Services Installs
Specify the following values for directories:
Data root           <ROOT DRIVE LETTER>:\Data1\MSAS\Data
User dir:            <ROOT DRIVE LETTER>:\Data1\MSAS\Log
TempDB dir:      <ROOT DRIVE LETTER>:\TempDB\MSAS\TempDB
Backup:            <ROOT DRIVE LETTER>:\Data1\MSAS\Backup

11.  Repeat step above for as many SQL/SSAS instance as needed.  Once your installs are complete, upgrade to the latest SQL CU hotfix.

Advertisements

Leave a Reply

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