Share via

Configuring Integration Services in a Clustered Environment

  • Article

New: 15 September 2007

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.

Important

Microsoft does not recommend that the Integration Services service be configured as a cluster resource.

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. For those customers who conclude that the advantages of this configuration outweigh the disadvantages, this topic contains instructions for configuring the service as a cluster resource.

However, if high availability is your goal in establishing a clustered hardware environment, you can achieve this goal without configuring the Integration Services service as a cluster resource. To manage your packages on any node in the cluster, modify the configuration file for the Integration Services service on each node in the cluster. You modify each of these configuration files to point to all available instances of SQL Server on which packages are stored. This solution provides the high availability that most customers need, without the potential problems encountered when the Integration Services service is configured as a cluster resource. For more information about how to change the configuration file, see Configuring the Integration Services Service.

Understanding the Role of the Integration Services Service

Understanding the role of the Integration Services service is critical to making an informed decision about how to configure the service in a clustered environment.

The Integration Services service supports the administrative interface in SQL Server Management Studio for listing, starting, stopping, monitoring, importing, and exporting Integration Services packages. The Integration Services service is not required to design packages. The Integration Services service is also not required to run packages, or to schedule SQL Server Agent jobs that run packages.

These are some of the tasks that you can accomplish even when the Integration Services service is not running:

  • Design and run packages in Business Intelligence Development Studio.
  • Run packages by using the dtexec command prompt utility (dtexec.exe), the Execute Package Utility (dtexecui.exe), SQL Server Agent, or the SQL Server Import and Export Wizard.
  • List packages that are stored in the msdb database by using a Transact-SQL query, and list packages that are stored in the file system by using Windows Explorer.
  • Create and run SQL Server Agent jobs and maintenance plans.

Understanding the Disadvantages of Configuring Integration Services as a Cluster Resource

Some of the potential disadvantages of configuring the Integration Services service as a cluster resource include the following:

  • When a failover occurs, running packages do not restart. You can recover from package failures by restarting packages from checkpoints. You can restart from checkpoints without configuring the service as a cluster resource. For more information, see Using Checkpoints in Packages.
  • When you configure the Integration Services service in a different resource group from SQL Server, you cannot use Management Studio from client computers to manage packages that are stored in the msdb database. The Integration Services service cannot delegate credentials in this double-hop scenario.
  • When you have multiple SQL Server resource groups that include the Integration Services service in a cluster, a failover could lead to unexpected results. Consider the following scenario. Group1, which includes the SQL Server service and the Integration Services service, is running on Node A. Group2, which also includes the SQL Server service and the Integration Services service, is running on Node B. Group 2 fails over to Node A. The attempt to start another instance of the Integration Services service on Node A fails because the Integration Services service is a single-instance service. Whether the SQL Server service that is trying to fail over to Node A also fails depends on the configuration of the Integration Services service in Group 2. If the Integration Services service was configured to affect the other services in the resource group, the SQL Server service that is failing over will fail because the Integration Services service failed. If the service was configured not to affect the other services in the resource group, the SQL Server service will be able to fail over to Node A.

Configuring the Integration Services Service as a Cluster Resource

For those customers who conclude that the advantages of configuring the Integration Services service as a cluster resource outweigh the disadvantages, this section contains the necessary configuration instructions. However, Microsoft does not recommend that the Integration Services service be configured as a cluster resource.

To configure the Integration Services service as a cluster resource, you have to follow these steps:

  • Install Integration Services on a cluster.
  • Configure Integration Services as a cluster resource.
  • Configure the Integration Services service and package store.
  • Bring the Integration Services service online as a cluster resource.

Installing Integration Services on a Cluster

To install Integration Services on a cluster, you must install Integration Services on each node in the cluster.

To install Integration Services on a cluster

  1. Install and configure a cluster with one or more nodes.

  2. (Optional) Install clustered services, such as the SQL Server Database Engine.

  3. Install Integration Services on each node of the cluster.

Configuring Integration Services as a Cluster Resource

With Integration Services installed on each node in the cluster, you have to configure Integration Services as a cluster resource. When you configure the Integration Services service as a cluster resource, you can add the service to the same resource group as the SQL Server Database Engine, or to a different group. The following table describes the possible advantages and disadvantages in selecting a resource group.

When Integration Services and SQL Server are in the same resource group When Integration Services and SQL Server are in different resource groups

Client computers can use SQL Server Management Studio to manage packages stored in the msdb database because both the SQL Server Database Engine and Integration Services service are running on the same virtual server. This configuration avoids the delegation issues of the double-hop scenario.

Client computers cannot use SQL Server Management Studio to manage packages stored in the msdb database. The client can connect to the virtual server on which the Integration Services service is running. However, that computer cannot delegate the user's credentials to the virtual server on which SQL Server is running. This is known as a double-hop scenario.

The Integration Services service competes with other SQL Server services for CPU and other computer resources.

The Integration Services service does not compete with other SQL Server services for CPU and other computer resources because the different resource groups are configured on different nodes.

The loading and saving of packages to the msdb database is faster and generates less network traffic because both services are running on the same computer.

The loading and saving of packages to the msdb database might be slower and generate more network traffic.

Both services are online or offline at the same time.

The Integration Services service might be online while the SQL Server Database Engine is offline. Thus, the packages stored in the msdb database of the SQL Server Database Engine are unavailable.

The Integration Services service cannot be moved quickly to another node if it is required.

The Integration Services service can be moved more quickly to another node if it is required.

After you have decided to which resource group you will add Integration Services, you have to configure Integration Services as a cluster resource in that group.

To configure Integration Services as a cluster resource

  1. Open the Cluster Administrator.

  2. In the console tree, select the Groups folder.

  3. In the results pane, select the group 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.
    • To add Integrations Services as a cluster resource to a different group than SQL Server, select a group other than the group to which SQL Server belongs.

  4. On the File menu, point to New, and then click Resource.

  5. On the New Resource page of the Resource Wizard, type a name and select “Generic Service” as the Service Type. Do not change the value of Group. Click Next.

  6. On the Possible Owners page, add or remove the nodes of the cluster as the possible owners of the resource. Click Next.

  7. To add dependencies, on the Dependencies page, select a resource under Available resources, and then click Add. In case of a failover, both SQL Server and the shared disk that stores Integration Services packages should come back online before Integration Services is brought online. After you have selected the dependencies, click Next.

  8. On the Generic Service Parameters page, enter MsDtsServer as the name of the service. Click Next.

  9. 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.

  10. In the Registry Key dialog box, type SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. Click OK, and then click Finish.

  11. The Integration Services service has now been added as a cluster resource.

Configuring the Integration Services Service and Package Store

Having configured Integration Services as a cluster resource, you must modify the location and the content of the configuration file for the Integration Services service on each node in the cluster. These modifications make both the configuration file and the package store available to all nodes if there is a failover. After you modify the location and content of the configuration file, you have to bring the service online.

To configure the Integration Services Service and Package Store

  1. Locate the configuration file at %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Copy it to the shared disk for the group to which you added the Integration Services service.

  2. On the shared disk, create a new folder named Packages to serve as the package store. Grant List Folders and Write permissions on the new folder to appropriate users and groups.

  3. On the shared disk, open the configuration file in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server that is in the same resource group.

  4. Change the value of the StorePath element to the fully-qualified path of the Packages folder created on the shared disk in a previous step.

  5. Update the value of **HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\**MSDTS\ServiceConfigFile in the Registry to the fully-qualified path and file name of the service configuration file on the shared disk.

To bring the Integration Services service online

  • In the Cluster Administrator, select the Integration Services service, right-click, and select Bring Online from the popup menu. The Integration Services service is now online as a cluster resource.

Connecting to Integration Services in a Clustered Environment

After configuring the Integration Services service on a cluster, or on any server, you might have to configure DCOM permissions before you can connect to the service from a client computer. For more information, see Connecting to a Remote Integration Services Server.

The Integration Services service cannot delegate credentials. Therefore, you cannot use Management Studio to manage packages stored in the msdb database when the following conditions are true:

  • The Integration Services service and SQL Server are running on separate servers or virtual servers.
  • The client that is running SQL Server Management Studio is a third computer.

The client can connect to the virtual server on which the Integration Services service is running. However, that computer cannot delegate the user's credentials to the virtual server on which SQL Server is running. This is known as a double-hop scenario.