SQL Server 2005 Integration Services – Maintenance Plan Tasks – Part 46

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:

  • Check Database Integrity

  • Shrink Database

  • Reorganize Index

  • Rebuild Index

  • Update Statistics

  • Clean Up History

  • Execute SQL Server Agent Job

  • Back Up Database (Full)

  • Back Up Database (Differential)

  • Back Up Database (Transaction Log)

  • Maintenance Cleanup Task (with SQL Server 2005 SP2 installed)

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.

»


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.

Latest Articles