How to: Configure Integration Services on a Cluster

SQL Server Setup

previous page next page

New: 5 December 2005

SQL Server 2005 Integration Services does not provide special installation options for a failover cluster. However, after installing Integration Services separately on each node of a cluster, you can manually configure it to operate as a clustered service.

Note:
After configuring the Integration Services service on a cluster, or on any server, you may need 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.

To prepare to install Integration Services on a cluster

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

  2. Optionally, install clustered services such as the SQL Server 2005 SQL Server Database Engine.

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

  4. Decide whether you will configure Integration Services as a clustered service:

    1. in the same group as the SQL Server 2005 SQL Server Database Engine.
    2. in a different group than the SQL Server Database Engine.

Configuring Integration Services in the Same Group as SQL Server

In this configuration:

  • The loading and saving of packages to the MSDB database is faster.
  • The MSDB node is available in Management Studio.

To configure Integration Services as a clustered service in the same group as SQL Server

  1. Open the Cluster Administrator.

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

  3. In the details pane, select the group to which you plan to add Integration Services.

  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 choose “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 configuration file for the Integration Services service. This file needs to be located on a shared disk that is in the same group for the Integration Services service, and fails over to the next node along with for the Integration Services service. In the Registry Key dialog box, type SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. Click OK, then click Finish. The Integration Services service has now been added as a clustered service.

  10. Now you must modify the location and the content of the service configuration file so that both the configuration file and the package store are available to all nodes in the case of a failover. Locate the configuration file at %ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.ini.xml. Copy it to the shared disk.

  11. Create a new folder named Packages on the shared disk. Grant List Folders and Write permissions on the new folder to the built-in Users group.

  12. Open the configuration file from the shared disk in a text or XML editor. Change the value of the ServerName element to the name of the virtual SQL Server which is in the same group.

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

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

  15. Now bring the clustered 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 clustered service.

Configuring Integration Services in a Different Group than SQL Server

In this configuration:

  • The Integration Services Service may be online while the SQL Server 2005 SQL Server Database Engine is offline and the packages stored in its msdb database are unavailable.
  • The Integration Services Service can be moved more quickly to another node if necessary.
  • The Integration Services Service does not compete with other SQL Server services for CPU resources (when other SQL Server services are not installed on the same computer).

To configure Integration Services as a clustered service in a different group than SQL Server

  1. In the Cluster Administrator, select a group other than the group to which SQL Server belongs.

  2. Add and configure IP Address, Network Name, and Generic Services resources for the Integration Services service. For the Generic Service, follow steps 1 through 9 of the previous procedure.

  3. Place the service configuration file and the Packages folder on the shared disk for this group. Follow steps 10 through 14 of the previous procedure to update the location and content of the configuration file.

  4. Bring the group online.

See Also

previous page start next page