In the previous level of this Stairway, we looked at using proxy accounts to have job steps impersonate a windows security context to accomplish the work of the job step. Most subsystems support having proxy accounts, and proxy accounts are restricted by subsystem, as well as requiring users to be explicitly authorized to be able to use the proxy credentials in job steps owned by those users. In this step, you will examine maintenance plans. Maintenance plans are used to perform various tasks to optimize your SQL Server installation, including backup, rebuild and/or reorganize indexes, update statistics, etc. Behind the scenes, maintenance plans are creating jobs. This step will provide a high level overview of maintenance plans and a look at the types of jobs that maintenance plans create.
An Overview of Maintenance Plans
A maintenance plan is a very nice graphical interface to help you perform the most common tasks for maintaining databases and their structures in SQL Server. Using maintenance plans is a nice shortcut rather than manually creating the correct T-SQL or PowerShell scripts to perform these routine maintenance tasks. These tasks include:
- Back Up Database Task
- Check Database Integrity Task
- Execute SQL Server Agent Job Task
- Execute T-SQL Statement Task
- History Cleanup Task
- Maintenance Cleanup Task
- Notify Operator Task
- Rebuild Index Task
- Reorganize Index Task
- Shrink Database Task
- Update Statistics Task
Intuitively you can understand what these tasks would do, but you can also review them further at http://msdn.microsoft.com/en-us/library/ms140255.aspx. If you follow the link you will notice that you are looking at SQL Server Integration Services (SSIS) tasks. That’s because Maintenance plans are in fact SSIS packages that have been customized to be easy to create and maintain for Database Administrators.
Each maintenance plan is made up of one or more subplans. Each subplan can run on a different schedule (because, as you might have guessed, they are separate jobs in SQL Server Agent). You should only group logically related tasks together as subplans into a single maintenance plan. You are allowed to have multiple maintenance plans for your server, and maintenance plans can apply to one or more of your databases on your server.
Creating a Maintenance Plan
The first thing you will notice is that Maintenance plans are not listed under SQL Server Agent. To find maintenance plans in SSMS, you need to navigate to the “Management” folder, then to the “Maintenance Plans” folder. There are no maintenance plans created by default in SQL Server. If you right-click on the folder, you will see that you can launch either “New Maintenance Plan…” or the “Maintenance Plan Wizard.” The vast majority of people will find that using the wizard is the fastest and easiest way to create a maintenance plan. Launch it, and you should see something like Figure 1.
Figure 1: Launching the Maintenance Plan Wizard
Click Next, and give your maintenance plan a name. For this example, you will perform maintenance on the AdventureWorks database, so you can name your plan “AdventureWorks Maintenance.” Enter a description for the plan, and then choose the option for “Separate schedules for each task” to see a more complex SQL Server Agent job schedule for this maintenance plan. Your screen should now look like Figure 2.
Figure 2: Selecting Plan Properties for your Maintenance Plan
Click Next, and you can then select which maintenance tasks will be performed. Select “Check Database Integrity”, “Update Statistics”, and “Back Up Database (Full)”. You should see something like Figure 3.
Figure 3: Selecting Maintenance Tasks for your Maintenance Plan
Click Next again, and then you will select the order to perform the tasks. Click Next again, as no modifications are necessary, and you should see the “Define Database Check Integrity Task” panel. Select the AdventureWorks database from the list of databases, as shown in Figure 4. Note that if you wanted to, you could select multiple databases, all databases, all system databases, or all “user” databases (those that are not system databases). You can also choose to check the option to Ignore databases where the state is not online to avoid errors at runtime if you choose to maintain all databases.
Figure 4: Selecting Database(s) to use in your Maintenance Plan
Click OK to accept AdventureWorks, then click the “Change” button at the bottom to adjust the schedule. This will bring up the standard scheduling dialog, as shown if Figure 5. In this case, accept the default naming and schedule.
Figure 5: Creating the Shared Schedule
Click OK, and you will then see the “Define Database Check Integrity Tasks” pane. You will notice that the AdventureWorks database has been selected for you in the dropdown list under “Specific Databases”, and the weekly schedule has been accepted as the default. Click Next again, and you will see the “Define Update Statistics Task.” Select AdventureWorks from the list of databases, accept the rest of the defaults, and create the default job schedule again.
Click Next, and you will be at the “Define Back Up Database (Full) Task” pane. Select AdventureWorks as the database, Accept the defaults (you might also check the “Verify backup integrity” option, always a good idea), and set the same weekly schedule (shown in Figure 6).
Figure 6: Creating the Database Backup task
Click Next again, and you will be given the option to create a report of the maintenance plan’s execution, by default in the same folder as your SQL Server error logs. You can also specify to email a report copy to someone if Database Mail is configured. Accept the defaults and click Next, and then Finish to complete the Wizard. The wizard will run, creating the maintenance plan, and finish with a success message as shown in Figure 7.
Figure 7: The Maintenance Plan Wizard completes
If you want to look at the results of the wizard in the Maintenance plan GUI, you can refresh the Maintenance Plans folder, and double-click on the newly created Maintenance plan, as shown in Figure 8.
Figure 8: Examining the Maintenance Plan
Close the maintenance plan, and navigate to the Jobs folder in SQL Server Agent. You will now see three new jobs created by the wizard, one for each subplan within the maintenance plan (shown in Figure 9). You have three separate subplans because you selected to have three separate schedules.
Figure 9: Maintenance Plan Jobs created by the Wizard
Open the AdventureWorks Maintenance.Subplan_1 job, and then click on Steps, and then open step Subplan_1. You will see that you have a “SQL Server Integration Services Package” for your job subsystem (shown in Figure 10). If you are skilled in working with SSIS, you can manually edit the properties here, but it’s not recommended as you could break the maintenance plan in a way that would prevent the graphical interface from working; however, you could potentially specify proxy accounts and configure connection information to SQL Server here.
Figure 10: Maintenance Plan Job Step
One change you might consider is to go back to the job, and change the Schedules to all use a single job schedule. As you’ve probably figured out by now, this contrived example wouldn’t work too well because you’d want these things to happen in sequence, not all at the same time. So, choosing a common schedule would make more sense.
Viewing Maintenance Plan Jobs in Job Activity Monitor
Each job created by maintenance plans is put into a special job category, “Database Maintenance.” You can take advantage of this in the Job Activity Monitor using the Filter capabilities. Open the Job Activity Monitor, and click on the Filter… button. For “Category”, type “Database Maintenance” (unfortunately you have to type it, there’s no drop-down to select existing categories). Check the Apply filter button, and click OK. Now you should see just your maintenance plan jobs in the window, and can much more easily check on the execution and results of your maintenance plan jobs (as shown in Figure 11).
Figure 11: Filtering for Just Maintenance Plan Jobs
What’s Next
Maintenance Plan jobs are an easy way to create sophisticated SSIS jobs for advanced workflow while still getting a simple user interface and creating the basic infrastructure for routine maintenance for your SQL Server databases. Underneath the covers, jobs, job steps, and schedules are created to support the plans. You need to understand the nature of these jobs to ensure you don’t accidentally break or delete them.
In our last step, we will look at using the MSX/TSX feature of SQL Server Agent to scale out job management to multiple servers.