Article ID: 942176 - View products that this article applies to.
This article describes the Microsoft SQL Server 2005 Integration Services (SSIS) service and the Microsoft SQL Server 2008 Integration Services (SSIS) service. We do not recommend that you cluster the SSIS service. However, this article describes alternatives to clustering the SSIS service.
The role of the SSIS serviceThe SSIS service is a Windows service that monitors the SSIS packages that are running. The SSIS service also manages the storage of SSIS packages.
The SSIS service extends the management functionalities in SQL Server Management Studio. If you are a member of the Administrators group, you can perform the following tasks in SQL Server Management Studio:
If you only want to design and to execute SSIS packages, you do not have to start the SSIS service. When the SSIS service is stopped, you can run SSIS packages by using the following utilities:
Clustering the SSIS serviceWe do not recommend that you cluster the SSIS service. If the SSIS service and SQL Server are installed in different resource groups, the SSIS service cannot delegate credentials. Therefore, you cannot use SQL Server Management Studio from a client computer to manage packages that are stored in SQL Server. Additionally, you can only install one instance of SSIS on a computer. You may also encounter other problems when you cluster the SSIS service.
If you must cluster the SSIS service, visit the following Microsoft Developer Network Web site for more information:
More information about clustersIn a cluster, the network adapter, the CPU, the memory, and the operating system are redundant. However, the shared disks do not have to be redundant. You can implement hardware solutions to make disks redundant. For example, you can use RAID devices or a System Area Network (SAN) environment to make disks redundant.
The cluster that we mention in this article is in one geographic location. For more information about geographically dispersed clusters, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/280743/ )Windows clustering and geographically separate sites
Alternatives to clustering the SSIS service
Cluster SQL Server AgentBecause SQL Server Agent jobs can run the DTExec.exe utility to execute SSIS packages, you can cluster SQL Server Agent to make sure that SSIS packages run on schedule when a failover occurs. When you cluster SQL Server Agent, SQL Server Agent jobs are available on all nodes. Therefore, the SQL Server Agent job can fail over between the nodes. The metadata of SQL Server Agent jobs is saved in the MSDB database. You can save your SSIS packages in the MSDB database or in a share disk.
Update the MsDtsSrvr.ini.xml file on each nodeInstead of clustering the SSIS service, you can update the MsDtsSrvr.ini.xml file on each node so that the file includes the information about one or more instances of virtual SQL Server in a cluster. When you do this, you save your SSIS packages in the MSDB database in SQL Server. Then, you can manage the SSIS packages from all nodes in the cluster.
Note The MsDtsSrvr.ini.xml file is located in the following folder:
%ProgramFiles%\Microsoft SQL Server\90\DTS\BinnThe following is an example of an MsDtsSrvr.ini.xml file that includes the information about two instances of virtual SQL Server in a cluster. The two instances of virtual SQL Server are named instances.
If you do not start the SSIS service, SQL Server Agent can still execute maintenance plans.
In the release version of SQL Server 2005 Integration Services, you must install the SSIS service if you want to run the Maintenance Plan Wizard. This problem is resolved in SQL Server 2005 Service Pack 1 (SP1). For more information about this problem, click the following article number to view the article in the Microsoft Knowledge Base:
909036You can use any of the following high availability options against user databases, but not for system databases such as MSDB:
(http://support.microsoft.com/kb/909036/ )Error message when you create a new maintenance plan in SQL Server 2005: "Create maintenance plan failed"
Article ID: 942176 - Last Review: March 2, 2009 - Revision: 4.0
Contact us for more help