A Strategy for Dealing with SSIS in Clusters

Using Microsoft Server Clustering to run multiple instances of SQL Server is common. For example, suppose you have an online instance, an extraction, transformation, and loading (ETL) instance, and a reporting instance. You can serve these SQL Server instances as clustered virtual machines (CVMs) on four physical machines in an active/active/active/passive configuration. (See Figure 1.) The passive machine observes the status of the other three machines, ready to assume their functions if necessary. (Note that CVMs use clustering technology that has been around since SQL Server 7.0. They aren't related to virtualization platforms, such as VMware ESXi or Microsoft Hyper-V.)

Each SQL Server instance takes care of different needs, so each instance should run SQL Server Integration Services (SSIS). However, complications arise when you use SSIS in a cluster because it is machine based rather than instance based like its predecessor DTS. In other words, in a cluster, SSIS runs on a physical machine, when most everything else runs on a CVM, as Figure 1 shows.

As a result, using SSIS in a cluster presents several challenges to a solution architect:

Because SSIS isn't a cluster-aware service and doesn't support failover, Microsoft doesn't recommend configuring SSIS as a cluster resource (although they provide instructions on doing so in the "Configuring Integration Services in a Cluster" web page. However, I've come up with a two-part strategy that addresses the challenges, making it a viable option.

The Strategy, Part One

The first part of my strategy is to store production packages in the file system rather than in msdb. Although this is a major deviation from past SQL Server 2000 practice, it's an important one. Here's why.

SSIS determines the location of msdb by looking in C:Program FilesMicrosoft SQL Server100DTSBinnMsDtsSrvr.ini.xml. When SSIS is installed (regardless if the installation is local or clustered), Microsoft places the tag

.

in this file. This tag indicates that msdb can be found in the default instance of this physical machine. (Remember, SSIS is machine based.) However, there is no default instance on the physical machine. The instance runs on the CVM.

You could change the tag on the physical machine that prodcvm01online typically runs on to

prodcvm01online

You could even make the same type of change on the physical machines that your ETL and reporting instances typically run on. Everything will be fine as long as the CVM/physical machine relationship remains in its typical configuration. But what if there are serious hardware problems and the CVM with the online instance ends up running on the physical machine that typically hosts the reporting instance? Further, what value do you put into the MsDtsSrvr.ini.xml file on the passive physical machine?

To avoid these issues, here's what you can do:

The Strategy, Part Two

I'm a big believer in using a formalized, consistent directory structure in which I store all my database files (e.g., data files, transaction logs, scripts, dumps). I copy this structure to every disk (or LUN) on a database server. The root of the structure is simply SqlData, which is followed by the name of the instance being supported (e.g., D:SqlDataOnline).

In that structure, I introduce an SSIS directory, which includes a subdirectory for every logical SSIS application supported on that instance. For example, D:SqlDataETLSSISPartnerETL would be the directory for an SSIS application known as PartnerETL located on the ETL instance. In the application directories, I store SSIS packages as .dtsx files.

In the root of the SSIS directory, I maintain and centrally manage a number of configuration files that are commonly used by the SSIS applications. I maintain one configuration file for every database in our architecture. Applications reference these configuration files by simply "reaching back" to them. For example, the common connection to the Online database can be used by referencing

..Online.dtsconfig

within an SSIS package.

I use SQL Server Agent jobs to schedule the execution of SSIS packages. SQL Server Agent provides a job step type of SQL Server Integration Services Package. There are two critical tabs when using this type of job step. In the General tab, which Figure 2 shows, you need to identify the package to be run.

In the Configurations tab, which Figure 3 shows, you need to identify the configurations needed by the package.

You can use the GUI to create the job in a development environment. It then can be scripted. If everything is done properly, you can use the script as is in your nonclustered environment. To run the script in a clustered production environment, you'll only need to change the drive letters. Because the package is stored on a LUN that's part of the cluster group and because the package is scheduled by SQL Server Agent (which is also part of the cluster group), the package will run regardless of what physical machine your CVM is located on.

The Strategy's Benefits

Besides effectively dealing with the challenges associated with implementing SSIS in a cluster, I've found that this strategy also has other advantages: