SQL Server Integration Service is not a cluster-aware service ( at least till the SQL Server 2012 version! ) . But we can tweak some settings and ensure that DTS packages can run on a cluster. There are two ways in which you can run SSIS on a cluster - one, as a cluster resource in the same or a different resource group from the database service resource group, or, secondly, as services running individually on each node, as if on two different servers.
The first approach is well documented by Microsoft, and can be applied following the procedures given in the MSDN document ( http://msdn.microsoft.com/en-us/library/hh231005.aspx ) . This involves adding SSIS as a clustered resource and thereby making changes in the registry. For those who do not want to tamper the Windows Registry, the second approach is the way out. In this approach, which we are going to discuss in details, SSIS has to be installed on each node separately and as a fresh installation, instead of adding features to an existing installation. Obviously, while installing SSIS on any cluster, you should understand that if the cluster fails over while the package is running, the package run will come to a halt. You can either re-run the package, or restart the failed package using Checkpoints ( http://msdn.microsoft.com/en-us/library/ms140226%28v=sql.105%29.aspx ). Furthermore, while deploying a package, if you are selecting 'File System' as the package location, make sure that the disk you place it on is a shared disk which is a cluster storage, and NOT on a local drive.
Below are the steps for the second approach.
The first approach is well documented by Microsoft, and can be applied following the procedures given in the MSDN document ( http://msdn.microsoft.com/en-us/library/hh231005.aspx ) . This involves adding SSIS as a clustered resource and thereby making changes in the registry. For those who do not want to tamper the Windows Registry, the second approach is the way out. In this approach, which we are going to discuss in details, SSIS has to be installed on each node separately and as a fresh installation, instead of adding features to an existing installation. Obviously, while installing SSIS on any cluster, you should understand that if the cluster fails over while the package is running, the package run will come to a halt. You can either re-run the package, or restart the failed package using Checkpoints ( http://msdn.microsoft.com/en-us/library/ms140226%28v=sql.105%29.aspx ). Furthermore, while deploying a package, if you are selecting 'File System' as the package location, make sure that the disk you place it on is a shared disk which is a cluster storage, and NOT on a local drive.
Below are the steps for the second approach.
- Install SSIS on each node of the cluster separately. Select fresh installation, instead of 'Add Features to Existing Installation'.
- Locate the configuration file at %ProgramFiles%\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml on each node. Open the file using notepad, or any other XML editor. Locate a XML node <ServerName>. You will see a "." , which indicates the server name, or in this case, the node name. Replace it with the virtual cluster name. This section is for saving package in MSDB. For saving package on File sSstem, change the value of the <StorePath> element to the fully-qualified path of the Packages folder which has to be created on the shared disk . You can make both the changes, but it is advisable to follow either MSDB or File System for all packages, and not both.
- Save the file. DTS packages are now ready to be deployed on the nodes. Do a smoke test by failing over the cluster and running a deployed package on the secondary node as well.
