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 AUTHORITY\NetworkService 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
2008\Configuration 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
Files\Microsoft SQL Server 2008\Configuration 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 Files\Microsoft SQL Server 2008\Configuration 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
» 
Comment and Contribute
