Installing and configuring SQL Server 2008 and 2008 R2 Integration Services

Marcin Policht shows you how to install and configure SQL Server 2008 and 2008 R2 Integration Services.

We have introduced the basic concepts of SQL Server 2008 and 2008 R2 (based on the November 2009 Community Technology Preview) Integration Services component, briefly describing its core features and primary characteristics that distingush it from its predecessors. That high-level overview was intended to help with a basic understanding of the technology and its intended use. Now we turn our attention to more specific implementation topics, starting with the initial setup and configuration.

While the installation of a SQL Server 2008 or 2008 R2 instance can be carried out in an unattended manner (for more information on this subject, refer to the SQL Server 2008 Books Online), it is likely that the majority of installations will be carried out interactively. Microsoft delivers this functionality via a collection of utilities and associated documentation grouped into SQL Server Installation Center. Its interface is divided in two areas, with Planning, Installation, Maintenance, Tools, Resources, Advanced, and Options categories displayed on the left and links to their respective tools and technical references appearing on the right.

Software prerequisites, whose presence can be verified using the System Configuration Checker option (located within the Planning section) include, at the minimum, Windows Installer 4.5 and .NET Framework 2.0 (although specifics depend the version of the host operating system as well as the edition of SQL Server).

If you are dealing with an existing instance of SQL Server 2000 or 2005 (rather than planning a new setup), you should take advantage of Install Upgrade Advisor, which will notify you about any existing issues that should be resolved before proceeding.

The Installation section gives you the ability to carry out a New installation or add features to an existing installation, run New SQL Server failover cluster installation, Add node to a SQL Server failover cluster, Upgrade from SQL Server 2000, SQL Server 2005, or SQL Server 2008 (in case of SQL Server 2008 R2), as well as Search for product updates.

The first of these options takes you through several preliminary steps, including verification of Setup Support Rules, selection of a desired product edition (which might require providing a relevant product key) and a setup role (in our case, SQL Server Feature Installation is most appropriate, although All Features With Defaults is also a valid choice).

On the Feature Selection page, in addition to selecting Database Engine Services, also include the Business Intelligence Development Studio, Client Tools Connectivity, and Integration Services entries (appearing in the Shared Features section). You also might want to consider installing Management Tools – Complete (which gives you access to the SQL Server Management Studio with its SSIS-related tasks, such as management of their storage and job scheduling), Client Tools SDK (which installs .NET assemblies necessary for custom package development), and Client Tools Backward Compatibility (in case you have legacy DTS packages you need to maintain).

On the Server Configuration page, specify the name of the target instance and its root directory location (note that there can be only a single installation of Integration Services on a given host operating system, regardless of a number of SQL Server Database Engine instances). On the same page, assign service accounts (by default, SQL Server Integration Services 10.0 service uses NT AUTHORITYNetworkService built-in security principal for this purpose), startup type (set it to Automatic), and collation.

The Database Engine Configuration page is divided into three tabs. The first of them, labeled Account Provisioning, determines the authentication mechanism (Windows authentication or Mixed mode) and allows you to grant SQL Administrative privileges to an arbitrary Windows user. This is required when using Windows authentication since by default these privileges are no longer granted to the local Administrator’s group. The second tab identifies Data Directories, including those hosting system and user databases, their logs, TempDB and its log, as well as the one intended for backups. The last tab allows you to enable the FILESTREAM feature (which, in essence, facilitates storing varbinary(max) binary large objects directly in NTFS file system, rather than in a database) for Transact-SQL access and for file I/O streaming access (with an option to provide the same capabilities to remote clients). Note that (as we will discuss in more detail), this functionality is required to take advantage of sample databases that we will be utilizing throughout our future articles.

The Ready to Install page summarizes your choices giving you a final chance to modify them before initiating the actual installation. Once the setup successfully completes, download and apply all up-to-date product hotfixes (if you are installing SQL Server 2008, make sure to start with Service Pack 1).

There are some additional caveats to consider when operating SQL Server Integration Services on the 64-bit operating system platform, hosted on Itanium or x64-based hardware. In particular, the former is limited to the run-time support for 64-bit packages only (this requires the choice of Management Tools – Complete during setup, resulting in installation of such tools as dtexec, dtutil, and SQL Server Import and Export Wizard, but not dtexecui or the Business Intelligence Development Studio), which also effectively prevents it from being able to execute legacy DTS code. The latter is considerably more flexible, by not only offering the native version of BIDS, but also allowing you to add its 32-bit version (by either selecting Management Tools – Complete or Business Intelligence Development Studio option).

With both sets of tools installed, you should ensure that you run the intended version (one way to accomplish this is to specify their full path during invocation). While you will not be able to include existing 32-bit DTS code as part of native 64-bit SSIS packages (via Execute DTS 2000 Package task), it is possible to do so when running in 32-bit mode on x64 systems, as long as you have DTS run-time components present (this requires applying the procedure described in msdn’s How to: Install Support for Data Transformation Services Packages article in the SQL Server Books Online). Similarly, you will need to resort to 32-bit mode when using packages utilizing the Jet and SQL Server Compact providers, as well as those that employ logging to SQL Server Profiler (we will explore these topics in more details in our future articles.).

When operating in a distributed Windows environment, you also need to take into consideration the firewall settings of computers running SQL Server components and management tools. (Starting with Windows Server 2008, all remote connections are blocked by default). Since an interaction with Integration Services utilizes the DCOM mechanism, which relies on the availability of port 135 (via TCP protocol), you might need to define an appropriate exception to allow relevant network traffic to pass through. The procedure applicable to Windows XP and Windows Server 2003 is documented in msdn’s How to: Configure a Windows Firewall for Integration Services Books Online article. If you are using Windows Server 2008 or 2008 R2, you can accomplish the same result by modifying rules via the Windows Firewall with Advanced Security interface (accessible via Administrative Tools menu) or netsh command line utility (as described in msdn’s Configuring a Windows Firewall for Integration Services Access Books Online article). You will likely have to follow an equivalent procedure (but referencing a different set of protocol and port pairs) in order to allow Database Engine access. By default, such access requires TCP port 1433 to be opened (this setting is customizable). Similarly, the ability to utilize SQL Server Browser relies on the availability of UDP port 1434. (For more information regarding relevant configuration steps, refer to msdn’s How to: Configure a Windows Firewall for Database Engine Access article of SQL Server 2008 Books Online). Note that in each of these cases, you should not only designate appropriate ports as open to communication via specific protocol, but also decide on the most secure network scope (which might include all systems, local subnet, or a specific list of IP addresses). More importantly, though, in order to allow remote access to SQL Server 2008 and 2008 R2, make sure to modify the default protocol settings by changing status of TCP/IP to Enabled in the SQL Server Network Configuration node of SQL Server Configuration Manager (accessible via Microsoft SQL Server 2008Configuration Tools menu).

There is an additional configuration change (mentioned earlier) that is required in our case in order to facilitate the use of AdventureWorks Data Warehouse database throughout our upcoming discussions on SQL Server 2008 Integration Services-related topics. Since sample databases are no longer incorporated in the source installation files, it is necessary to download them from the CodePlex Open Source Community Web site.

Their successful installation is contingent on a number of prerequisites. More specifically, they depend on having installed the Full-Text Search component (which you can add by employing the SQL Server Installation Center, accessible via Program FilesMicrosoft SQL Server 2008Configuration Tools menu), started SQL Full-text Filter Daemon Launcher service (managed from the SQL Server Services node of SQL Server Configuration Manager also located in the Program FilesMicrosoft SQL Server 2008Configuration Tools menu), and enabled FILESTREAM option (this can be accomplished either during installation, as described above, or afterwards, by turning on Enable FILESTREAM for Transact-SQL access option on the FILESTREAM tab of the target SQL Server instance Properties dialog box in SQL Server Configuration Manager and by running sp_configure filestream_access_level, 1 followed by RECONFIGURE statement).

Launching the self-extracting executable (AdventureWorks2008_SR4.exe) you downloaded will invoke the SQL Server 2008 Database Installer wizard. Once the wizard is running, on its Database Selection page, designate databases you want to become available. (For the purpose of our presentations, we will be using AdventureWorks Data Warehouse 2008).

In our next article, we will take a closer look at the package creation process, relying on the Import and Export Wizard for this purpose, as well as analyze its structure using the Designer interface in Business Intelligence Development Studio.

» See All Articles by Columnist Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles