In this post I will show a procedure to configure Integration Services in a SQL Server 2016 Cluster.
Clustering Integration Services is not recommended because the Integration Services service is not a clustered or cluster-aware service, and does not support failover from one cluster node to another. Therefore, in a clustered environment, Integration Services should be installed and started as a stand-alone service on each node in the cluster.
Although the Integration Services service is not a clustered service, you can manually configure the service to operate as a cluster resource after you install Integration Services separately on each node of the cluster.
How to configure Integration Services as a cluster resource
First, open the Cluster Administrator. In the console tree, select the roles. In the results pane, select the SQL Server role to which you plan to add Integration Services:
To add Integrations Services as a cluster resource to the same resource group as SQL Server, select the group to which SQL Server belongs. Right click SQL Server role and select add resource and select “Generic Service” as the Service Type. Select SQL Server Integration Services 13.0 and Click Next. Click Finish, to add SSIS as a resource of the Cluster Role
In below screenshot, connect to the cluster role to which SSIS need to be added as resource
In below screenshot, I have selected SQL Server Integration Services 13.0.
The Integration Services service has now been added as a cluster resource in the image below.
Now right click on SQL Server Integration Services 13.0 Resource and select properties, In the Properties box go to Dependencies Tab and add SQL Server Resource and H: disk as disk resource where we have placed SSIS config.
When you install Integration Services, the setup process creates and installs the configuration file for the Integration Services service. This configuration file contains the following settings:
- The root folders to display for Integration Services in Object Explorer of SQL Server Management Studio are the MSDB and File System folders.
- The packages in the file system that the Integration Services service manages are located in %ProgramFiles%\Microsoft SQL Server\130\DTS\Packages.
Default Configuration File Example
The following example shows a default configuration file that specifies the following settings:
- Packages stop running when the Integration Services service stops.
- The root folders for package storage in Integration Services are MSDB and File System.
- The service manages packages that are stored in the msdb database of the local, default instance of SQL Server.
How to manage packages in msdb
Create a folder that is named “SSIS_Config” in a clustered disk. Go to C:\Program Files\Microsoft SQL Server\130\DTS\Binn\ copy MsDtsSrvr.ini.xml to the Clustered disk H: folder we have created, i.e. H:\ H:\SSIS_Config
Create a folder that is named “Packages” in a clustered disk H: to server as your package store. The service manages packages that are stored in the file system in the Packages folder. In below screenshot, create a folder named packages in clustered disk H:
Traverse through the path H:\SSIS_Config\MsDtsSrvr.ini.xml and open “MsDtsSrvr.ini.xml”. When SSIS is installed (regardless if the installation is local or clustered), msdb can be found in default instance.
The below screenshot shows default configuration of “servername” and “storepath”.
Open this “MsDtsSrvr.ini.xml”. File in notepad modify <ServerName>.</ServerName> to <ServerName>SQLClustername</ServerName> with the SQL Server instance name in the cluster and <StorePath>N:\Packages</StorePath> to packages folder path on the clustered drive if you store packages to file system and save the file.
In the below screenshot, the server name is replaced with SQLCS and storepath is replaced with H:\Packages.
Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS\ServiceConfigFile in the Registry to the fully-qualified path and file name of the service configuration file on the shared disk.
On the Registry Replication page, click Add to add the registry key that identifies the location of the configuration file for the Integration Services service. This file must be located on a shared disk that is in the same resource group as the Integration Services service.
In the below screenshot, we se SSIS service config file with the default path to MsDtsSrvr.ini.xml
Go to Registry and Update the value of HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130\SSIS\ServiceConfigFile to the UNC path and file name of the service configuration file on the clustered disk H:.
In below screenshot, update the value of ServiceconfigFile to H:\SSIS_Config\MsDtsSrvr.ini
Click OK and save the file.
Ganapathi varma Chekuri
Lead SQL DBA, MCP