A maintenance plan is a workflow of T-SQL tasks that can be scheduled to run or be executed on demand. Built upon the SSIS engine, maintenance plans provide an easy way to create workflow within your plans, to add custom T-SQL tasks in case the user interface doesn’t provide the specific option you need, and to tell the user at design time what the T-SQL will look like.
maintenance plan that was created to first check a database’s integrity and then issue a backup database statement. If the check database integrity task failed, it would notify an Agent Operator. Notice that two different subplans are associated with this maintenance plan: one subplan is for a test database, and the other is for a development database. Although we could have easily placed these tasks within the same subplan, having multiple subplans allows you to create separate workflows and thus have these subplans run on different schedules.
A sample maintenance plan
Using SQL Server Management Studio, you can create a maintenance plan either via the Maintenance Plan Wizard or through the Maintenance Plan Designer.
You can launch the Maintenance Plan Wizard by selecting Maintenance Plan Wizard from the context menu of the Maintenance Plans node in Object Explorer. To create a new plan using the Maintenance Plan Designer, simply select New Maintenance Plan from this same context menu.
Launching the Maintenance Plan Wizard will take you through a series of dialog boxes that allow you to pick and choose which tasks you would like to include as part of your plan. If you modify an existing plan or create a new plan, the Maintenance Plan Designer will open as a document window inside the Management Studio shell. In addition, the toolbox will become visible and it will, by default, locate itself beneath Object Explorer, as shown in Figure. The toolbox contains the list of all the available tasks in SQL Server 2008.
Maintenance Plan Tasks
If you accidentally closed the toolbox, or it doesn’t appear, you can open it from the View menu on the toolbar.
The Maintenance Plan Designer document window opens when you either create a new maintenance plan or modify an existing plan. This document window has two sections. The bottom half is the designer, where the actual workflow is defined. The top half is for metadata about the plan and resembles.
The top half of the Maintenance Plan Designer document window
The first issue you’ll notice with this section of the designer is that the name field is read-only. When you create a new plan, you’ll be asked for a plan name before you even see the designer. If you use the default name, MaintenancePlan, and later decide you want to change it, you’ll need to rename it through the Object Explorer context menu. You can’t change the name of the maintenance plan directly through the designer. However, the Description field below the Name field is editable; it can hold up to 512 characters.
Across the top of the document window are the following buttons (from left to right):
When you create a new maintenance plan, one subplan is created for you by default. This subplan has no schedule defined on it.
Scheduling Maintenance Subplans
Although you can always execute your maintenance subplans individually whenever you want, you probably will want to schedule them to be run at some particular point in the day.
To create a schedule, click the Job Schedule icon to the right of the schedule description column. This will launch the Job Schedule dialog box and allow you to define a schedule for the subplan. In the event that you want to remove a schedule and have the plan return to being an on-demand plan only, click the icon to the right of the Job Schedule icon (the one with the red X). This will delete the schedule associated with the subplan.
Managing Maintenance Plan Connections
The Manage Connections button along the top of the Maintenance Plan Designer document window allows you to add, edit, or remove SQL Server connections. Having multiple connections allows a maintenance plan to perform work on multiple servers, with the results of the tasks rolled up in one central location on the server that is running the maintenance plan.
By default, when a user creates a maintenance plan, a single server connection is generated. This connection is to the local SQL Server on which the plan is created. If you want to perform tasks on other instances of SQL Server, you’ll need to add connections.
Each subplan doesn’t need to contain tasks that all connect to the same server; you could have a subplan whereby every task uses a different connection. This design makes it easier to manage and maintain multiple servers. To manage these connections, click the Connections button. This will bring up the dialog box.
The Manage Connections dialog box
In Figure, notice that the Remove button is disabled for MyServer. This is because each maintenance plan requires you to have at lease one connection for the plan itself.
To add a connection to perform tasks on another SQL Server instance, click the Add button, which launches the dialog box in Figure. By default, the connection name will be blank. You’re free to put in any name you would like for this connection. However, we find it easier to enter the actual server name.
New Connection dialog box
Once this connection is added, you can use it in any of the existing tasks as well as any new tasks that you drop on the designer surface.
Reporting and Logging Maintenance Plans
The Logging button along the top of the Maintenance Plan Designer document window, located to the right of the Manage Connections button, brings up the Reporting and Logging dialog box.
Reporting and Logging dialog box
With SQL Server maintenance plans, you can generate text file reports of the plan, as well as send the report to an e-mail recipient. Note that you’ll need to install Database Mail in order to send the report to an Agent Operator.
In the Logging section of the Reporting and Logging dialog box, you’ll see two check boxes:
Defining Maintenance Plan Tasks
The designer surface is where you actually define what tasks are performed in your maintenance plan. To add a task, simply drag the task from the toolbox onto the designer surface.
For purposes of illustration, let’s create a maintenance plan for the AdventureWorks database that first checks the database integrity (for example, whether it will issue a DBCC CHECKDB() against SQL Server), then on success, performs a database backup. In addition, on failure of the check, the database should notify an Agent Operator.
First, drag the Check Database Integrity, Backup Database, and Notify Operator tasks onto the designer surface.
Maintenance Plan Designer designer surface
One of the first things you may notice is the red X to the right of each of these tasks. This indicates that the task isn’t configured and needs more information in order to execute—which connection to use, which database to perform the action on, and so on. Right-clicking a task brings up a context menu with the options presented in Table.
Context Menu for aMainentance Task
To edit a task, select Edit or double-clickthe task.
Check Database Integrity Task dialog box
The task dialog box may contain Connection, Databases, and Tables (or View) items. In the case of Check Database Integrity, the task needs to know only the connection and which database to check. By default, the local connection is selected; however, if you defined multiple connections, you could select one of these connections from the drop-down list or create a new connection by clicking the New button, which would bring up the New Connection dialog box.
The Databases combo box, when dropped, shows a subdialog box that allows you to pick the databases on which to perform the action.
Databases drop-down combo box
Once you select the database or databases to perform the action on, the task may enable or disable options in the dialog box, based on whether you’re performing the action on more than one database.
Once the task is configured (that is, a connection is defined and you’ve selected a database and tables or views as appropriate), you may view the T-SQL that will most likely be executed at runtime against the connection. We say “most likely” because some tasks have conditions that need to be met before they are run, For example, in the case of the Shrink Database task, you define how large the database must be before shrinking, but the View T-SQL button will still always show you the T-SQL that would be executed.
Once you configure the task, the red X will disappear, and you can continue editing the other tasks.
One of the most powerful features of a maintenance plan is its ability to create workflow among the tasks. To do this, select a task, click and hold the green arrow at the bottom of the task, and drag this to another task. By default, the arrow is green, indicating On Success. To change this, simply right-click the green line and select Error or Completion for On Error or On Completion, respectively.
Once you’ve defined your workflow and configured the rest of your tasks, you should be able to execute your plan either via Agent, by scheduling it, or on demand, by right-clicking the plan name in Object Explorer and choosing Execute.
Maintenance plans are an important part of a DBA’s daily routine. This new version of maintenance plans combines the power of the SSIS workflow engine with the usefulness of specific maintenance tasks, wrapped in an easy-to-use feature.
SQL Server 2008 Related Interview Questions
|SQL Server 2000 Interview Questions||MSBI Interview Questions|
|SQL Server 2008 Interview Questions||SQL Server 2005 Interview Questions|
|SSIS(SQL Server Integration Services) Interview Questions||SSRS(SQL Server Reporting Services) Interview Questions|
|Microsoft Entity Framework Interview Questions||LINQ Interview Questions|
|SQL Server Analysis Services (SSAS) Interview Questions||Sql Server Dba Interview Questions|
SQL Server 2008 Related Practice Tests
|SQL Server 2000 Practice Tests||MSBI Practice Tests|
|SQL Server 2008 Practice Tests||SQL Server 2005 Practice Tests|
|SSIS(SQL Server Integration Services) Practice Tests||SSRS(SQL Server Reporting Services) Practice Tests|
|Microsoft Entity Framework Practice Tests||LINQ Practice Tests|
Sql Server 2008 Tutorial
Sql Server 2008 Overview
Sql Server Installation And Configuration
Sql Server Encryption
Automation And Monitoring
Integrated Full-text Search
New Datatypes In Sql Server 2008
T-sql Enhancements For Developers
T-sql Enhancements For Dbas
Sql Server And Xml
Sql Server Xml And Xquery Support
Linq To Sql
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.