SQL Server 2005 Integration Services - Maintenance Plan Tasks - Part 47
April 23, 2007
In the previous installment of our series dedicated to SQL Server 2005 Integration Services, we introduced the concept of Maintenance Plans and presented a brief overview of three methods that can be used for their implementation. We also described in more detail the most straightforward of them, which is initiated from Object Explorer of SQL Server Management Studio and configured through a series of consecutive, wizard-driven pages. In addition, we mentioned that a variety of features (along with representing them graphical interface options) have changed with the advent of SQL Server 2005 Service Pack 2. We will point out these changes as we continue our presentation, in order to bring your attention to some of the more relevant improvements (for a complete list of new features in SQL Server 2005 SP2, refer to the "What's New in SQL Server 2005 SP2" article of SQL Server 2005 Books Online posted on the MSDN Web site). On the other hand, remember that the initial release of the service pack contained a number of software bugs, which had a negative impact on maintenance plan functionality. To ensure that these issues will not impact your environment, you should verify that you have installed the corrected version (which was made available by Microsoft on March 5 2007) as well as apply the instructions outlined in Microsoft Knowledge Base Article 933508.
As we have demonstrated in the previous article, successful completion of the Maintenance Plan Wizard generates a new SSIS package that is saved in the msdb database. This event coincides with the creation of one or more SQL Server Agent jobs of Database Maintenance type (appearing in the Jobs subfolder of the SQL Server Agent node of Object Explorer in SQL Server Management Studio). Their actual count depends on the number of unique execution options you selected when running the wizard. This number also determines how many subplans within the Maintenance Plan package you have (subplans serve a role similar to SSIS Sequence Containers, controlling the sequence of task execution within a package; however, unlike containers, they can be individually scheduled). If you decided to apply the same schedule to all tasks or to use on-demand invocation, you will end up with a single job entry (and a single subplan). Choosing a separate schedule for all tasks will result in an individual job for each of them (each corresponding to a distinct subplan). Subplans auto generated by the wizard are named simply as Subplan_1, Subplan_2, etc. Names of jobs are formed by concatenating identifiers of the maintenance plan and their matching subplans, with a single period between them (e.g. MaintenancePlan_1.Subplan_1).
In order to edit (or simply view) the content of a maintenance plan, use the Modify option from its context sensitive menu (all plans are listed in the Maintenance Plans subfolder of the Management folder in Object Explorer of SQL Server Management Studio). The appearance of the resulting designer window depends on the service pack level. With SQL Server 2005 SP2, the design area is divided into three, horizontally arranged sections. The top one contains the name and description of the currently viewed maintenance plan. Immediately below, there is a listing of all of its subplans, with their description and schedule (two buttons on the right hand side allow you to, respectively, configure and delete scheduling information). Selecting an individual subplan from this list displays its graphical representation in the third section, in the lower portion of the designer window. From here, you can manipulate properties of existing maintenance plan tasks, add new ones by dragging them from the Toolbox, or set precedence constraints between them. The toolbar at the top of the designer window gives you the ability to add new subplans (in case you want to introduce tasks with their own, independent schedules), modify or delete existing ones, configure reporting and logging options (that apply to all subplans within the maintenance plan), as well as manage server connections (including their authentication methods). Using Servers... toolbar button, you can also designate servers where the maintenance plan subtasks will execute (providing you operate in a multiserver environment with target servers running jobs defined on a master).
All of the tasks that are listed in the Toolbox of Maintenance Plan designer in SQL Server Management Studio have their own specific editors, which are identical to the ones available in the SSIS Designer window of Business Intelligence Development Studio. One of quite useful features common to all editors is the View T-SQL command button, which allows you to display T-SQL statements that produce the same outcome as applying properties you set through the graphical interface. As we will demonstrate later in our series, this helps address some of the shortcomings of current Maintenance Plan implementation. Furthermore, you can use these statements to implement maintenance procedures in SQL Server 2005 Express edition, which does not support directly SSIS or Maintenance Plans. (Although, keep in mind that automating them would also require some type of scheduling software, such as Windows Task Scheduler, in order to compensate for the lack of SQL Server Agent Job Scheduling service functionality).
Interestingly, some of subplan settings can be configured from either the Maintenance Plan designer window (displayed after you select the Modify entry from the context sensitive menu of the plan you want to modify that appears in the Maintenance Plans subfolder of Management folder in Object Explorer of SQL Server Management Studio) or from the Properties dialog box of a job that correspond to this subplan. (To activate this dialog box, select the Properties entry from the context sensitive menu of this job, which is listed under the Jobs subfolder of the SQL Server Agent node in Object Explorer of SQL Server Management Studio). For example, this is the case with schedules of individual subplans, which are identical to corresponding job schedules and can be accessed either via the Schedules section of SQL Server Agent Job Properties dialog box or via the Subplan schedule command button in Maintenance Plan designer (either approach triggers a display of the same Job Schedule Properties dialog box). On the other hand, there are also some seemingly partially redundant settings, which serve a similar role but deliver them at different stages of the maintenance plan (and corresponding job) execution. For example, within Maintenance Plan designer window, you can configure notifications by introducing Notify Operator Task into the workflow area (which provides status information about intermediate stages of execution via an e-mail to designated operators), or through the Reporting and Logging dialog box (from which you can specify recipients of e-mail reports on the outcome of the maintenance plan). Note that both mechanisms require Database Mail functionality. Another similar option, which appears in the Notifications section in the Job Properties window for a SQL Server Agent job, is intended for defining actions that are performed once the job completes. These actions include sending pages and e-mails (which also rely on Database Mail being properly set up), NET SEND messages (which require Messenger service to be running on the source and target systems), recording relevant information in the Windows Application log, as well as automatically deleting jobs that have completed. You can turn on any of them conditionally, depending on the job success, failure, or simply completion.
There are also several SSIS specific parameters that are configurable from the SQL Server Management Studio interface. In order to review them, double-click on a job you want to edit listed under the Jobs subfolder of SQL Server Agent node in Object Explorer of SQL Server Management Studio. In the resulting Job Properties dialog box, switch to the Steps section (listed on the left side) and click on Edit after selecting a relevant Subplan entry. This will display the Job Step Properties dialog box. From here, you should be able to view and edit such settings as package source, target server and authentication method, configurations, Command Files, Data sources, Execution options, Verification, or Logging - just to mention a few.
Once you have adjusted configuration settings according to your requirements, you might attempt to launch your maintenance plans. Unfortunately, even this seemingly straightforward activity has a significant number of caveats you need to be aware of. If you are planning to schedule execution of individual subplans within a single maintenance plan (which is equivalent to scheduling a SQL Server Agent job), then you should take into consideration items outlined in Microsoft KB Article 918760 titled "An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step". On the other hand, if your intention is to launch subplans interactively, you can accomplish this with the Start Job as Step... option in the context sensitive menu of jobs listed under the Jobs subfolder of the SQL Server Agent node. In order to run all subplans within a maintenance plan, you need to repeat this step for each of them, since it appears that executing multi-subplan maintenance plans by invoking the Execute option from their context sensitive menu (plans are listed under the Maintenance Plans subfolder of Management folder in Object Explorer) is not possible (although you can use this option when dealing with maintenance plans that contain only a single subplan). Note that this surprising limitation does not surface if you schedule maintenance tasks (rather than using them in on-demand fashion), since subplans are launched separately as distinct SQL Server Agent jobs.
Results of the execution can be either communicated directly to designated individuals (through the notification mechanism, which we described earlier) or analyzed by reviewing the content of various logs. It is possible to enable logging by leveraging SSIS providers (through the Logging tab on the General page of the Job Step Properties dialog box), Windows Application log (from the Notifications page of the Job Properties dialog box), or Log File Viewer, which contains a comprehensive view of Database Mail, Job History, Maintenance Plans, and SQL Agent logs (to display it, select the View History entry from the context sensitive menu of the maintenance plan you are interested in, listed under the Maintenance Plans subfolder of the Management folder in Object Explorer of SQL Server Management Studio). As mentioned before, you can also enable logging via the Reporting and Logging dialog box invoked with the toolbar button in the Maintenance Plan designer window.
We will take a closer look at the remaining options available from this window (including all of the maintenance tasks listed in its Toolbox) in the next article of our series.