SQL Server 2005 Integration Services - Maintenance Plan Tasks - Part 46
April 13, 2007
Continuing our coverage of Control Flow tasks in SQL Server 2005 Integration Services, we will now focus on several tasks, whose shared purpose is to provide the ability to perform standard database maintenance procedures. You can find these tasks grouped together within Business Intelligence Development Studio, in a section of the Toolbox labeled "Maintenance Plan Tasks." It is also possible to access them (and use them to create less complex packages) via the "New Maintenance Plan..." option, from the context sensitive menu of the Maintenance Plan subfolder in the Management folder of Object Explorer in SQL Server Management Studio. (The number of options available from this interface is limited, preventing you from taking advantage of many other SSIS features, such as, Data Flow or tasks that belong to other than Maintenance Plan categories). The third approach to creating new packages that contain maintenance tasks (which, however, precludes editing existing ones) relies on Maintenance Plan Wizard, which is invoked from the context sensitive menu of the Maintenance Plans subfolder (right below the "New Maintenance Plan..." option).
You might recall that the wizard served as the primary GUI-based method of handling database maintenance activities in the previous version of SQL Server. Its outcome, in the form of SQL Server Agent jobs that invoked the xp_sqlmaint extended stored procedure (which, in turn, leveraged SQLMAINT command-line utility) with a lengthy parameters list, can be viewed and modified through the SQL Server Enterprise Manager Management Console. With the advent of SQL Server 2005, xp_sqlmaint as well as the SQLMAINT utility, which is associated with it, have been deprecated. (Although they still function, allowing the reuse of existing maintenance T-SQL based scripts) and have been replaced by SSIS packages that, just like their predecessors, can be executed on demand or on schedule (as individual steps of SQL Server Agent jobs).
While the three methods for scheduling and executing maintenance tasks we listed above are in many aspects equivalent (and result in the creation of SSIS packages), they are far from identical. The most obvious difference is the interface that provides their configuration features and determines their operating environment. The first one exists within the boundaries of Business Intelligence Development Studio that offers rich programming functionality, which might be necessary when automating more complex administrative scenarios. The remaining two, implemented as part of SQL Server Management Studio are considerably more straightforward to use, but suffer from limited flexibility. On the other hand, there are some visual similarities between the Maintenance Plan option in SQL Server Management Studio and its SSIS counterpart, since both of them are based on a similar workspace layout, consisting of the Toolbox window offering access to Maintenance Plan tasks (the former consists of the Maintenance Plan Tasks subsection of the full listing displayed by the latter) and a designer area with the ability to define connections, predicates, and containers (referred to as Subplans according to Management Studio's Maintenance Plan terminology). At the same rate, both allow for creation of new and modification of existing packages. These characteristics contrast with Maintenance Plan Wizard, which does not have editing capabilities and whose interface consists of a number of consecutive pages. Through these pages, you have a chance to select and configure one or more of the following types of actions:
As expected, the same (although in some cases, slightly differently named) options are available via Business Intelligence Development Studio Control Flow Designer and Maintenance Plan Toolbox windows. In addition, both of them also include Notify Operator and Execute T-SQL Statement items within the same task category (which do not appear in the Maintenance Plan Wizard). Note that this does not necessarily mean that you cannot implement their functionality when running the wizard (access to some of their features is provided through options displayed on wizard pages). For example, e-mail, pager, or NET SEND operator notifications (controlled by Notify Operator Task) can be configured from the Select Report Options page of the Maintenance Plan Wizard.
Another important factor that differentiates the three methods is their package storage mechanism. The two hosted by SQL Server Management Studio use msdb database for this purpose (for more information on this subject, refer to our earlier article of this series), while the Business Intelligence Development Studio relies on the file system (by storing packages in *.DTSX and associated files). It is not possible to open packages residing in msdb database from SSIS designer (and packages created with Business Intelligence Development Studio do not appear automatically within Integration Services node of SQL Server Management Studio), but you can make them available in either interface using import and export commands, readily accessible from the context sensitive menu of relevant subnodes within the Integration Services node in Object Explorer of SQL Server Management Studio (although, as we will explain later, there are important implications of transferring Maintenance Plan packages in this manner that you need to consider).
In addition, there is also a significant difference between the task scheduling mechanism employed by the SQL Server Management Studio Maintenance Plan designer (especially following Service Pack 2 installations) and the one that needs to be applied to Business Integration Development Studio packages containing Maintenance Plan tasks (which is reflected by a unique Subplan schedule option in the Management Studio Maintenance Plan designer interface). We will postpone further discussion on this topic until our next article of this series.
In order to help you gain better familiarity with the methods we have been describing, we will review their implementation, starting with the most straightforward one that involves the Maintenance Plan Wizard. Before proceeding further, however, you need to take some preliminary steps. First, ensure that SQL Server Agent on the target server is running, which you can establish by reviewing the list of active services (this can be accomplished by examining the output of NET START command or checking the content of Services MMC snap-in). You might also need to enable 'Agent XPs' advanced SQL Server option (by running sp_configure stored procedure after turning on 'show advanced option' setting), which controls visibility of the SQL Server Agent node in SQL Server Management Server Object Explorer and the availability of its extended stored procedures. If you desire such functionality as the ability to run maintenance plans without having SSIS installed (using SQL Server Database Services instead), support for multiserver environments or multiple schedules for individual tasks, then you must install SQL Server 2005 Service Pack 2. (In case this already has been done, review and apply, if appropriate, instructions from Knowledge Base article 933508 in order to prevent scheduled cleanup tasks from deleting data at intervals different from those intended).
Once these steps are completed, drill down to the Management folder in the Object Explorer and select the Maintenance Plan Wizard entry from the context sensitive menu of its Maintenance Plans subfolder. Following the initial page with a brief overview of the tasks that the wizard allows you to perform, you are prompted for a unique identifier for the plan you are creating (that will become the name of the resulting SSIS package), its description, and (when running SQL Server 2005 SP2) the intended execution method (allowing you to define separate schedules for each individual task or a single schedule for the entire plan, as well as to launch the package on an as-needed basis). Next, you can choose which of the standard maintenance tasks we listed earlier that you want to use. In case you selected multiple entries, you are also given an opportunity to adjust their order. Subsequent pages that are being displayed depend largely on the choices you have made so far. For example, the Shrink Database task requires that you specify whether it applies to all databases, all system databases (master, msdb, model), all user databases, or arbitrarily selected ones, size threshold below which no action should be taken, amount of free space to retain following its completion, and the intended use of reclaimed space (which can either be retained within database files or be returned to the operating system).
Regardless of task type, you are also given an opportunity to specify whether the report summarizing outcome should be written to an arbitrary text file or e-mailed to one of earlier defined operators (listed in the Operators folder of the SQL Server Agent node in Object Explorer of SQL Server Management Studio), which, as mentioned earlier, serves a similar role to that of the Notify Operator Task available in SSIS and Maintenance Plan Designer Toolbox. In the final step of the wizard, you can review all of the choices you have made up to this point. After clicking on the Finish button, you are presented with the progress of creating the maintenance plan, with status displayed for each of intermediate stages. Assuming successful completion, a new entry with the label matching unique name you assigned earlier should appear under the Maintenance Plans subfolder of the Management folder in Object Explorer of SQL Server Management Studio.
At this point, if you intend to modify the newly created package, simply select the Modify option in its context sensitive menu. This will display its content in the Design interface, providing some unique features, such as adding and scheduling Subplans (which resemble SSIS Sequence Containers) as well as already familiar ones, including the ability to manage server connections, configure precedence constraints, and add individual Maintenance Plan tasks from the Toolbox. We will examine this functionality in more detail in our next article.