Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Configuring and Installing SSIS on a SQL 2008 R2 failover cluster infrastructure.

Posted by rahmanagoro on October 23, 2011


SSIS isn’t cluster aware out of the box, so there is some bit of config changes to make in order to make it work on a cluster.

C

Select just the Integration service.

On the Client Access point screen, type in the name and also the IP address as well looking at the requirement spreadsheet.

Click on Next

Since the SSIS service has been installed in its own resource group, we now need to set the dependencies.

Set the disk dependency for the SSIS service

Add the disk resource dependency

Requirement: Change SSIS configuration, custom configuration

SSIS Custom Configuration

Navigate to the path: C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn

Create a folder on the root of the SSIS cluster disk called SSIS_Config

Create a folder on the SSIS cluster disk called Packages

Copy the file from the location C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.ini.xml to S:\SSIS_Config\



Right Click the SSIS service and select properties and then go onto Registry replication and add the registry key below.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\SSIS\ServiceConfigFile\


Click on OK and then Apply.

Take the resource offline and bring it back online for the changes to take effect.



Perform the following steps for SSIS on the second node.

Change the MsDTSSrvr.ini.xml file on the location defined above and change the packages folder to point to S:\Packages

Make the registry entry changes on the second node as well to finish off the configuration.

Test the failover by failing over the SSIS resource group by NodeA to NodeB.

Copy a .dtsx file onto the S:\Packages folder and open up management studio.

In the illustration below, you can see that we have connected to the SSIS instance and we can see the packages.

102311_1839_Configuring28


Advertisements

5 Responses to “Configuring and Installing SSIS on a SQL 2008 R2 failover cluster infrastructure.”

  1. Thanks for the detailed process. Will attempt to replicate on a test environment.

  2. nikon said

    excellent step by step thank you
    we have two node sql2008r2 cluster
    last night i was trying to add new features to each node (SSIS feature)
    on the installation window called “installation type” (5th screenshot from top) i mark “new installation or add shared features” than click on next and SQL server setup failure window pop up on me saying

    “SQL Server Setup has encountered the following error :
    Element not found. (exception from HRESULT: 0x80070490).”

    any advice gladly appreciated
    thank you in advance
    nikon

    • Hello,

      I’m glad you found the guide useful, the error you mentioned below relates to the storage setup on the cluster. Can you have another double check to ensure that the disks are in the right groups etc, remove any stale disks or deleted disks from the cluster and re-validate.

      Thanks.

      • nikon said

        hi there,

        yes the problem was with “old temporary disk Array” storage disks.
        the thing is when we first time install the system and SQL Cluster with datacore storage everything worked perfectly but then (after a year or so) we needed to upgrade the datacore (i think from melodie version to symphony or something funny as that) so we move the whole thing to temporary storage (not so fancy as the datacore one..;-))…) after the upgrade we move all the data back to datacore and the not so fancy storage put to offline in case we need to move the data from the datacore again …. months later we need to install the SSIS for some reasons and here i am ;-)))

        anyway this guide is honestly the best i find
        thank you again
        nikon

  3. Chandler Forrest said

    Thanks for the detailed steps on configuring SSIS in a clustered environment. How would the steps change if we already have SSIS setup as a stand alone service (not clustered) and have packages stored in the default repository?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: