SQLServerCentral Article

Configuring Integration Services in a SQL Server 2016 Cluster

,

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:

  1. The root folders to display for Integration Services in Object Explorer of SQL Server Management Studio are the MSDB and File System folders.
  2. 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:

  1. Packages stop running when the Integration Services service stops.
  2. The root folders for package storage in Integration Services are MSDB and File System.
  3. 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.

That's it.

Ganapathi varma Chekuri

Lead SQL DBA, MCP

Email: gana20m@gmail.com

Linkedin

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Share

Categories

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (1)

You rated this post out of 5. Change rating

Related content

Technical Article

Reproduced with kind permission from the blog of Ashvini Sharma (MSFT)

InfoPath forms can be saved to XML, these XML Files can later be used in SSIS XMLSource adapter to pull out the data in tables and columns. However, there are some common problems you may meet in these scenarios. This article describes how to work around these potential problems. The issues mentioned in this article is not only specific to InfoPath files, it can also be referenced in other similar situations as well.

2005-06-20

1,328 reads

External Article

How to Asynchronously Execute a DTS package from ASP or ASP.NET

The Data Trasformation Services are a powerful tool, and sometime its features are so useful that you’d like to invoke a DTS package not only from SQL Server but from an external program.

To do this you have several choices: you can use the DTSRun.exe tool or you can do it leveraging the SQL-DMO features.

Unfortunately if you’re developing a web application (ASP, ASP.Net or whatever you use) none of them seems to be the right choice: too much problems, too much effort and a very modest results. In addition none of these solutions can be called asynchronously: if you just need to implement a “fire-and-forget” technique, you just cannot do that!

2005-05-24

2,663 reads

Technical Article

Easy Package Configuration

One of the age old problems in DTS is moving packages between your development, test and production environments. Typically a series of manual edits needs to be done to all the packages to make sure that all the connection objects are pointing to the correct physical servers. This is time consuming and gives rise to the possibility of human error, particularly if the solution incorporates many DTS packages. Many companies have provided their own custom solutions for managing this problem but these are still workarounds for a problem that is inherently DTS's.

2004-12-14

1,761 reads

Technical Article

For Loop Container Samples

One of the new tasks in SQL Server 2005 is the For Loop Container. In this article we will demonstrate a few simple examples of how this works. Firstly it is worth mentioning that the For Loop Container follows the same logic as most other loop mechanism you may have come across, in that it will continue to iterate whilst the loop test (EvalExpression) is true. There is a known issue with the EvalExpression description in the task UI being wrong at present. (SQL Server 2005 Beta 2).

2004-11-18

3,221 reads