SQL Server 2012 Integration Services – Installation and Initial Configuration

In one of our recent articles published on this forum, we provided a comprehensive listing of new and enhanced features introduced in the Integration Services incorporated into soon-to-be released SQL Server 2012. In case you are interested in exploring them in-depth, we intend to examine each of them in detail in upcoming presentations. However, for the sake of completeness and chronological order, we will start by guiding you through the steps required to install and configure this product. Note that our discussion is based on the current (as of February 2011) SQL Server 2012 Release Candidate 0, downloadable from the Microsoft Download Center for both x86 and x64 platforms (as well as available in the form of an .iso file containing both versions).

Even though it might be tempting to simply proceed with the setup program, it is prudent to first verify that your system satisfies hardware and software prerequisites (for their complete listing, refer to MSDN Library). If you are using Windows Server 2008 R2 SP1, this is likely already the case, but the situation might be different with older operating systems, where PowerShell 2.0 and the required .NET Framework 3.5 SP1 update are probably missing (setup handles the install of .NET Framework 4 along with SQL Server Native Client and necessary support files). Alternatively, if you have type A personality, with little time or patience for reading through documentation, you can launch the System Configuration Checker incorporated into SQL Server Setup wizard, which will identify missing components for you.

The wizard is invoked from the SQL Server Installation Center, where you also find sections dedicated to:

  • Planning (with hardware and software requirements, System Configuration Checker, Upgrade Advisor, and links to introductory online information)
  • Maintenance (grouping such tasks as edition upgrades, repair, or SQL Server failover cluster node removal)
  • Tools (offering installed features discovery report, Microsoft Assessment and Planning Toolkit, and PowerPivot Configuration Tool)
  • supplementary Resources (pointing to a variety of Web sites providing assistance with the product related issues or questions).

The Advanced section allows you to launch the install based on the configuration file, or guide through preparation and completion of more involved implementations (such as advanced clustering or imaging).

While it is possible to upgrade an existing instance of Integration Services to the SQL Server 2012 R2 level as well as run both of them side-by-side (both of these scenarios apply to versions 2005, 2008, and 2008 R2), we will focus on the most basic option, labeled New SQL Server stand-alone installation or add feature to an existing installation within the Installation section. Other installations, including new SQL Server failover cluster installation, adding a node to an existing SQL Server failover cluster, or an upgrade from earlier versions of SQL Server will be reviewed in future articles published on this forum.

Assuming that you successfully applied all of the prerequisites, you will be prompted to either choose a free edition (Evaluation or Express) or enter the product key. The next important decision point enforced by the SQL Server 2012 RC0 Setup wizard deals with Setup Role and entails picking one of three available options – SQL Server Feature Installation, SQL Server PowerPivot for SharePoint, or All Features with Defaults.

We will opt for the first one, which offers the highest degree of flexibility; although it forces you to manually select the desired components on the subsequent Feature Selection page. For the purpose of our demonstration, we will pick Database Engine Services with Data Quality Services from the Instance Features node. (Effectively, we will be using a dedicated SQL Server instance, allowing us to schedule and carry out executions of SSIS packages locally) as well as Integration Services, Management Tools – Complete, SQL Server Data Tools (the new SQL Server development environment, constituting a replacement for Business Intelligence Development Studio and including 32-bit version of SSIS runtime, which makes execution of legacy packages possible), and Client Tools SDK (adding managed assemblies which facilitate SSIS programming) from the Shared Features node.

As in earlier versions, you have a choice between a default and named database engine installation (which root directory you can change if desired), although this is only marginally relevant in the context of our presentation (since you are limited to a single instance of Integration Services per computer, which is accessible via its hostname). However, it is important to realize that with the database engine collocated on the same system as the SSIS, the latter automatically utilizes the default instance when storing packages in msdb database. (It is possible to alter this behavior by modifying MsDtsSrvr.ini.xml file located in %ProgramFiles%Microsoft SQL Server100DTSBinn folder, as described in MSDN Library).

On the Server Configuration page, you designate Collation (not of particular importance to us at this point) and Service Accounts (which we need to focus on for a moment). Note that when running installation on a Windows Server 2008 R2 or Windows 7 computer, the security context for SQL Server services is by default provided by employing so called Virtual accounts, also referred to as managed local accounts. Unlike standard built-in accounts (such as NetworkService, LocalService, or LocalSystem), they are not generic (and potentially shared) but assigned directly to individual services. This not only provides an increased degree of isolation, but also simplifies auditing and troubleshooting. Similar to NetworkService, virtual accounts eliminate the need for password management (precluding the possibility of account lockouts) and offers the ability to access network resources in the security context of the computer account (when operating in Active Directory domain environments). Incidentally, the name of Service SID assigned during installation to SQL Server Integration Services 11.0 service has changed (comparing with SQL Server 2008 R2) from NT ServiceMsDtsServer100 to NT ServiceMsDtsServer110 (which is hardly surprising, considering that virtual accounts have the format consisting of NT Service prefix followed by the corresponding Windows service name).

If you decide to use a Windows domain account for any of SQL Server Services (note that this is no longer required in the case of clustered instances of SQL Server, as long as you are running them on the Windows Server 2008 R2 operating system platform), you should keep in mind the implications of such a decision in regard to account lockouts, which could render your installation non-operational. In addition, it is advised to use a dedicated and non-privileged account for this purpose (relevant rights and permissions will be granted automatically during the setup process). Finally, to ensure that Kerberos (rather than NTLM) authentication is used when connecting to domain-based instances of SQL Server, you will most likely need to register the corresponding Service Principal Names, which provides a means of locating the account associated with the service in Active Directory (for instructions documenting this process, refer to Register a Service Principal Name for Kerberos Connections in the MSDN Library).

If you intend to access resources external to the SSIS instance running on a Windows Server 2008 R2 computer in an Active Directory environment, then it is recommended to switch security context of its service from Virtual to Managed Service Account due to additional benefits associated with such a change. As with Virtual accounts, this approach offers automatic password management and automatic Service Principal Name registration, as well as eliminates the possibility of account lockouts. However, each individual Managed Service Account also possesses a unique domain-wide identity (rather than operating in the security context of the computer account where the service is running), which allows you to control its permissions and privileges in a more granular manner. (For more information regarding this subject, refer to Service Accounts Step-by-Step Guide on Technet). If you decide to follow this recommendation, remember that any service account changes should be carried out using SQL Server Configuration Manager (rather than Services MMC snap-in).

After this short digression, let’s return to the remaining portion of the SQL Server 2012 Setup wizard. On the Database Engine Configuration settings page, you will be able to choose the Authentication Mode (both Windows or Mixed are available, however you should consider utilizing the former due to its security benefits), add Windows security principals to SQL Server administrators role, designate location of Data Directories and, optionally, enable FILESTREAM mechanism. Finally, you are given a chance to review your choices on the Ready to Install page and, assuming that no changes are needed, launch the actual installation.

Keep in mind that you will no longer find Business Intelligence Development Studio in the Start->All Programs menu after the setup wizard successfully completes. Instead, Microsoft Visual Studio 2010 becomes the primary package development tool. After its initial launch, you will be prompted to choose default environment settings, with four available options: Business Intelligence Settings, General Development Settings, Visual Basic Development Settings, and Visual C# Development Settings. Selecting the first of them will customize the Integrated Development Environment interface in the manner matching most closely our future objectives.

See all articles by 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