Multiple SQL Server Integration Services (SSIS) database instances on one machine
On a recent assignment they requested me to implement two Integration Services instances on the same machine. Their serverpark isn't that big, but they want to simulate a separated test en development environment. With DTS this wasn't a real problem, but how do you configure SSIS to use multiple instances, since SSIS can only be installed once on a single machine and totally works different then DTS. Before I explain how you can configure SSIS you first need to know the differences between DTS and SSIS.
Traditional (DTS) warehouse loading
- Integration process simply conforms data and loads the database server
- The database performs aggregations, sorting and other operations
- Database competes for resources from user queries
- This solution does not scale very well
Warehouse loading with SSIS
- SQL Server Integration Services conforms the data
- But also aggregates and sorts, and loads the database
- This frees-up the database server for user queries
SSIS includes a configuration file for configuring the Integration Services service. By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.
The default configuration file contains the following settings:
- The root folders to display for Integration Services in Object Explorer of SQL Server Management Studio are the MSDB and File System folders.
- The packages in the file system that the Integration Services service manages are located in %Program Files%\Microsoft SQL Server\90\DTS\Packages.
You can modify the configuration file to display additional root folders in Object Explorer, or to specify a different folder or additional folders in the file system to be managed by Integration Services service. The example below shows how I configured the configuration file to use more than one MSDB database, which are stored in separated database instances.
As you can see in the Management Studio I can store my packages in different databases as well a multiple File Systems.
The registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile specifies the location and name for the configuration file that Integration Services service uses. The default value of the registry key is C:\Program Files\Microsoft SQL Server\90\DTS\Binn\ MsDtsSrvr.ini.xml. You can update the value of the registry key to use a different name and location for the configuration file.
The SQL Team did a great job for letting you choose how many instances you want to use on one machine. They're fully configurable and administrable and you're able to create a real test and development environment on one machine. Great job guys.