This project has moved and is read-only. For the latest updates, please go here.

Previous Install Visual Studio 2010 SP1 Manual Configuration Setup Cluster Bindings Next

Setup SSIS Cluster

FineBuild can configure a SSIS Cluster. This processing is only relevant if SQL Server is being installed as a cluster.

In most situations SSIS should not be set up as a cluster. SSIS Cluster Guidelines describes when it is safe to set up SSIS as a cluster. If you do decide to cluster SSIS, then FineBuild can do this for you.

FineBuild SSIS Cluster configuration

The SSIS Cluster configuration relates to Process Id 2CG and is controlled by the parameters below:

Parameter Build SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014
SetupSSISCluster FULL No No No No No
SetupSSISCluster WORKSTATION No No No No No
SetupSSISCluster CLIENT N/A N/A N/A N/A N/A

FineBuild also uses the following parameters to help configure SSIS Cluster:
Parameter Default Description
ClusISSuffix IS Suffix appended to Windows Cluster name for SSIS Clusters

The SQL FineBuild processing for Setup SSIS Cluster consists of the following items:
Configure SSIS Cluster Resource
Configure SSIS Configuration File
Add Node to SSIS Cluster
Top

Manual SSIS Cluster configuration

The following steps show what you would have to do for manual SSIS Cluster configuration. FineBuild does all of this work for you automatically.

The example below uses the disk, server and IP addresses used in Install First SQL Server Cluster Node.

Configure SSIS Cluster

1) Create the SSIS cluster using the following commands. The SSIS Service Name should be taken from the following table:
SQL Version SQL2005 SQL2008 SQL2008 R2 SQL2012 SQL2014
Service Name MSDtsServer MSDtsServer110 MSDtsServer110 MSDtsServer120 MSDtsServer120
SetupSSISCluster FULL No No No No No

CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
  /CREATE /GROUP:"PDGB01SQLC01DB" /TYPE:"Generic Service" ^
  /PROP DESCRIPTION="SQL Server Integration Services"
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
  /PRIV ServiceName="MSDtsServerxxx"
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" ^
  /ADDDEP:"SQL Network Name (PDGB01SQLC01DBA)"

Configure SSIS Configuration File

The SSIS Configuration File must be move from its location on a single node to a cshared disk location.

1) Ensure the SSIS cluster resource is offline by using the following command:
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF

2) Get the location of the SSIS Configuration File by reading the value of the following Registry Key:
SQL Version Registry Key
SQL 2005 32-bit HKLM\SOFTWARE\Wow6432Node\Microsoft\MSDTS\ServiceConfigFile\
SQL 2005 64-bit HKLM\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile\
SQL 2008 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\
SQL 2008 R2 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\
SQL 2012 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\110\SSIS\ServiceConfigFile\
SQL 2014 HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\120\SSIS\ServiceConfigFile\

3) Copy the SSIS Configuration File to the shared disk location
VolData\SQLFiles\MSIS.InstanceName.Data

4) Update the registry key from 2) above to hold the new location of the SSIS Configuration File

5) Change the ServerName value to specifically name the current server
Look in the SSIS XML configuration file for a Node called ServerName. Set the value for this Node to match the current server name.

6) Change the StorePath value to show the shared location for SSIS Packages
Look in the SSIS XML configuration file for a Node called StorePath. Set the value of this Node to VolData\SQLFiles\MSIS.InstanceName.Data\Packages.

7) Put the SSIS cluster resource online by using the following command:
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ON

Add Node to SSIS Cluster

Each node in the SQL Server Cluster must be added as a possible owner of the SSIS Cluster Resource
1) Ensure the SSIS cluster resource is offline by using the following command:
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /OFF

2) Ensure the current server name as a possible owner for the SSIS Cluster Resource:
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ADDOWNER:"PDGB01SQLC01N01"

3) Put the SSIS cluster resource online by using the following command:
CLUSTER "PDGB01SQLC01" RESOURCE "PDGB01SQLC01IS" /ON

Copyright FineBuild Team © 2015 - 2016. License and Acknowledgements
Previous Install Visual Studio 2010 SP1 Top Setup Cluster Bindings Next

Last edited Oct 17, 2016 at 10:26 AM by EdVassie, version 2