Administration of the Integration Services Service


Now that you have a better understanding of the parts of Integration Services, we'll look at the various administrative aspects of Integration Services and discuss the details needed to feel comfortable working with the components. We will start with a review of the Integration Services service and then look at various configuration elements of the service. Next, you'll look at how you can adjust properties of the SSIS service using either the Windows Services Snap-in or the SQL Server Configuration manager. Understanding how you can modify the Windows Firewall follows, and then you'll look at the management and configuration of event logs.

Last, we will look at performance monitoring.

Overview

The Integration Services service is a Windows service used to manage SSIS packages and is accessed through SQL Server Management Studio. The following summarizes the management capabilities provided by this service:

  • Starting and stopping local and remote packages

  • Monitoring local and remote packages

  • Importing and exporting packages from different sources

  • Managing the package store

  • Customizing storage folders

  • Stopping running packages when service is stopped

  • Viewing the Windows Event Log

  • Connecting to multiple SSIS server instances

To be very clear, you don't need this service for designing or executing packages. The primary purpose of this service is to manage packages within Management Studio. One side benefit to having the service running is that the SSIS Designer in Business Intelligence Developer Studio can use the service to cache the objects used in the designer, thus enhancing the performance of the designer.

Configuration

The configuration of the Integration Services service includes viewing and possibly modifying the XML file responsible for the runtime configuration of the service, setting service properties using either the Windows Services Snap-in or using SQL Server Configuration Manager and, potentially, configuring the Windows Firewall to permit access by Integration Services.

XML Configuration File

The MsDtsSrvr.ini.xml file responsible for the configuration of the Integration Services service is located in C:\Program Files\Microsoft SQL Server\90\DTS\Binn by default. This file includes settings for determining whether or not running packages are stopped when the service is stopped, a listing of root folders to display in the Object Explorer of Management Studio, and settings for specifying the folders in the filesystem that are managed by the service.

The configuration filename and location can be changed. This information is obtained by Management Studio from the Windows registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTS\ServiceConfigFile. As with most registry key changes, you should back up the registry before making any changes, and you will need to restart the service after making changes in order for them to take effect.

One example of a configuration change that must be made is when you connect to a named instance of SQL Server. The following example shows the modification for handling a named instance:

 <?xml version="1.0" encoding=" utf-8"?> <DtsServiceConfiguration xmlns:xsd=" http://www.w3.org/2001/XMLSchema" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance">   <StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>   <TopLevelFolders>       <Folder xsi:type=" SqlServerFolder">         <Name>MSDB</Name>         <ServerName>MyServerName\MyInstanceName</ServerName>       </Folder>       <Folder xsi:type=" FileSystemFolder">         <Name>File System</Name>         <StorePath>..\Packages</StorePath>       </Folder>   </TopLevelFolders> </DtsServiceConfiguration> 

Other common configuration file change scenarios include adding additional paths from which to display packages other than the default SSIS package store path of C:\Program Files\SQL Server\90\Packages and creating a centralized folder structure for multiple servers by storing the service configuration file in a central fileshare.

Now that you have seen how to configure the MsDtsSrvr.ini.xml file responsible for the configuraion of the Integration Services service, you'll next need to get an understanding of how to set the service's properties.

Setting Service Properties Using the Windows Services Snap-in

As with any other Windows service, the Integration Services service has properties that dictate how it is to be started. Specifically, you can manage the following from the Windows Services Snap-in:

  • Configure the startup type as Manual, Automatic, or Disabled.

  • Request that the service is started, stopped, or restarted.

  • Establish how the computer reacts to service failures.

  • View or modify a listing of dependant services (none set up by default).

To view and modify SSIS Services properties using the Windows Services Snap-in, follow these steps:

  1. Open the Services Snap-in from Control PanelAdministrative Tools (or using the Category view from Performance and MaintenanceAdministrative Tools).

  2. Locate and right-click SQL Server Integration Service in the list of services.

  3. Select Properties to view the settings currently applied to the service.

  4. On the General tab, you can view or change the Startup type (Automatic, Manual, or Disabled). When set to either Manual or Automatic, you can change the Service status to Start, Stop, or Resume (see Figure 6-2).

  5. On the Log On tab, you can view or alter the account used to start up and run the service. By default, this runs under the NT AUTHORITY\NetworkService account.

  6. On the Recovery tab, you can configure how the server will respond to failures of the service by setting the First, Second, and Subsequent failures options to either Take No Action (the default), Restart the Service, Run a Program, or Restart the Computer (see Figure 6-3). Additionally, you can instruct the service to reset the failure count after a certain number of days.

  7. Last, you can modify the list of services that the SSIS service depends on (none by default) and view the list of services dependent on the SSIS service (none by default) on the Dependencies tab.

image from book
Figure 6-2

image from book
Figure 6-3

You have just learned how to configure the Integration Services service using the Windows Services Snap-in. Now we'll describe how you can perform some of this configuration using SQL Server Configuration Manger.

Setting Service Properties Using SQL Server Configuration Manager

As with using the Windows Services Snap-in, you can also configure a limited set of Integration Services service properties using SQL Server Configuration Manager. Specifically, you can both configure the logon information used by the service and establish the startup mode of the service.

Here's how to view and modify SSIS Services properties using the SQL Server Configuration Manager.

  1. Open the SQL Server Configuration Manager from All ProgramsMicrosoft SQL Server 2005Configuration Tools.

  2. On the list of services on the right side, right-click SQL Server Integration Services and select Properties.

  3. On the Log On tab, you can view or alter the account used to startup and run the service. By default, this runs under the NT AUTHORITY\NetworkService account.

  4. On the Service tab, you can view or change the Startup type (Automatic, Manual, or Disabled).

Now that you are comfortable setting up the service properties for the Integration Services service using either the Windows Services Snap-in or using SQL Server Configuration Manger, you'll next see how you may need to modify Windows Firewall to permit accessing Integration Services.

Configuring Windows Firewall for Access

You'll probably find that your service requires modifications to be made to the Windows Firewall system in order to provide consistent access to Integration Services. The Windows Firewall system controls access to specific computer resources primarily by limiting access to preconfigured ports. We have no ability to modify the port number used by Integration Services, as it only works using port 135.

Here's how to configure the Windows Firewall to permit Integration Services access:

  1. Open the Windows Firewall from Control Panel.

  2. Select the Exceptions tab and click Add Program.

  3. In the Add Program dialog, click Browse and select C:\Program Files\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.exe. You should also, of course, use the Change Scope option in order to detail the computers that have access to the program by specifying a custom list of IP addresses, subnets, or both. The resulting exception is shown in the Windows Firewall dialog (see Figure 6-4).

  4. Click Add Port.

  5. In the Add Port dialog, type a meaningful description like RPC(TCP/135) Integration Services, type 135 in the Port Number box, and select TCP as the protocol. You should also, of course, use the Change Scope option in order to detail the computers that have access to the port by specifying a custom list of IP addresses, subnets, or both. The resulting exception is shown in the Windows Firewall dialog in Figure 6-4.

image from book
Figure 6-4

Administrators often prefer to rely on commands and scripts rather than dialog boxes to configure various features and properties. Rather than using the dialog to configure the Windows Firewall, you can run the following commands at the command prompt.

 netsh firewall add portopenint protocol=TCP port=135 name=" RPC(TCP/135) Integration Services" mode=ENABLE scope=SUBNET netsh firewall add allowedprogram program="%ProgramFiles%\Microsoft SQL Server\90\DTS\Binn\MsDtsSrvr.exe" name=" SSIS Service" scope=SUBNET 

You should use the scope argument of both commands to detail the computers that have access to the program and/or port by specifying a custom list of IP addresses, subnets, or both.

We have covered a substantial amount of the configuration required for the Integration Services service. Our next focus for understanding the Integration Services service is to understand event logging.

Event Logs

Integration Services records events raised by packages during their execution in logs. The SSIS log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files. In order to perform logging, SSIS packages and tasks must have logging enabled. Logging can occur at the package, the container, and the task level, and you can specify different logs for packages, containers, and tasks. Additionally, to record the events raised, a log provider must be selected and a log added for the package. These logs can be created only at the package level, and a task or container must use one of the logs created for the package. Once you've configured the logs within packages, you can view them using the Windows Event Viewer or within SQL Server Management Studio.

Here's how to view SSIS event logs using the Windows Event Viewer:

  1. Open the Event Viewer from Control PanelAdministrative Tools (or using Category view from Performance and MaintenanceAdministrative Tools).

  2. Within the Event Viewer dialog, click Application.

  3. After the Application snap-in is displayed, locate an entry in the Source column valued as SQLISService.

  4. Right-click the entry and select Properties to display descriptive information about the entry (see Figure 6-5).

image from book
Figure 6-5

Here's how to view these events in SQL Server Management Studio:

  1. Open Management Studio and connect to the target Integration Services server.

  2. In Object Explorer, right-click Integration Services (topmost node) and click View Logs.

  3. Select SQL Server Integration Services option from the Select Logs section.

  4. You can see the details for an event displayed in the lower pane by clicking an event in the upper pane (see Figure 6-6).

image from book
Figure 6-6

Now that you've learned about the event logs and seen how to view them, you're ready to monitor the activity of your service.

Monitoring Activity

Part of the performance monitoring of the Integration Services service includes configuring the logging of performance counters. These counters allow you to view and understand the use of resources consumed during the execution of SSIS packages. More specifically, the logging encompasses event-resource usage, while packages perform the dataflow tasks.

We'll begin by focusing on some of the more insightful counters, including Rows read, Buffers in use, and Buffers spooled. The Rows read counter provides us with the number of rows read from all data sources during package execution. The Buffers in use counter details the number of pipeline buffers (memory pools) in use throughout the package pipeline. Lastly, the Buffers spooled counter specifies the number of buffers used to handle the dataflow processes. The buffers spooled counter is specifically important because it is a good indicator of when your machine is running out of physical memory or is running out of virtual memory during dataflow processing. The importance of using buffers rather than disk cannot be understated, as SSIS has been tuned to effectively use memory rather than disk to provide a fast and efficient dataflow processing capability. Whenever SSIS has to use disk files rather than memory, performance of the data operations will degrade, and this will show up in the Buffers spooled counter.

Here's how to set up resource counters in order to monitor dataflow performance:

  1. Open Performance (also referred to or known as PerfMon or Performance Monitor) from Control PanelAdministrative Tools (or using Category view from Performance and MaintenanceAdministrative Tools).

  2. Within Performance Monitor, expand Performance Logs and Alerts.

  3. Add a new log by right-clicking Counter Logs, and select New Log Settings.

  4. On the New Log Settings dialog, give your log a meaningful name such as SSISDataFlowPerfLog.

  5. On the SSISDataFlowPerfLog (or whatever you named the log) dialog box, click Add Counters.

  6. On the Add Counters dialog, either select the local computer or a computer from the list to specify a remote computer you wish to monitor. Also, select SQL Server: SSIS Pipeline in the Performance object list and select individual counters you wish to include in the log from the list of counters supplied. Your log configuration should now resemble Figure 6-7.

image from book
Figure 6-7

Now that you've added dataflow performance counters to Performance Monitor, you can monitor how critical resources are used by our Integration Services dataflows during package execution. One example of how these performance counters can be used includes ensuring that your server running the SSIS packages has enough memory. One of the bottlenecks in any transformation process includes input/output operations, whereby data is staged to disk during the transformations. Integration Services was designed to optimize system resources when transforming data between a source and destination including attempting to perform these transformations in memory rather than having to stage data to disk and incur I/O performance penalties. You should expect to see the value of the Buffers spooled counter remain at zero (0) when only memory is being used during the transformation processes being performed by the SSIS packages. When you observe that the Buffers spooled counter is normally valued higher than zero (0), you have a good indication that more memory is needed on the server processing the SSIS packages.

You can use SQL Server Profiler to analyze the data operations and query plans generated for various dataflow pipeline activities. You can use this information to refine indexes or apply other optimization techniques to the data sources your SSIS solution is using. The SQL Server Profiler is covered in more detail in Chapter 13.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net