At this point, we’ve been through each of the specific tasks to create a maintenance plan for each task. We’ve followed practical examples to lead us to the culmination of this book, which is to have you, the user, create your very own maintenance plan. From here on, we move from theory to application; we’re going to apply what we know.
Checking Your Environment
If you’ve followed along, you’ve probably gotten quite a few test e-mails in your inbox. Go ahead and delete those since you don’t need them anymore.
Figure 13-1 shows the jobs listed in SQL Server Agent .
Figure 13-1. SQL Server Agent Jobs
Additionally, we should have the following maintenance plans available, as shown in Figure 13-2.
Figure 13-2. Maintenance Plans
Now, the important thing here is that, for the most part, we are done. That’s right! You could conceivably leave the plans as they are and call it a day. However, there are important considerations that must be taken into account with regards to maintenance plans, and the most important one to remember is that some tasks need to be run before other tasks, and some maybe don’t need to be run every hour or even every day. This next section is going to focus on what tasks actually need to be run, when they should be run, and in what order they should run.
Ordering of the Maintenance Tasks
That’s right; there is a preferred order to the maintenance tasks. Why? It doesn’t make much sense to back up the database, and then rebuild the indexes, now does it? There has to be an order to the tasks, so let’s look at them in regards to the main task to be run—backing up the database.
Think about it like this. You don’t want to back up bad data, but you also want to provide for a longstanding backup mechanism. To achieve this, you want to take care of the legwork up front, meaning the tasks that deal with the data integrity. After the backup, you want the cleanup.
Check out Figure 13-3. Ideally, this is how I would structure my new maintenance plan.
Figure 13-3. Maintenance Plan Structure
To summarize this even further, think of it like this.
The Pre-Backup phase is made up of all the things that need to happen before the database is backed up.
The Backup phase is what runs to actually back up the data.
The Post-Backup phase is made up of what needs to happen after the backup is complete.
See how that works? We run tasks before, during, and after. The result is a fresh, clean backup of the database, and properly maintained tasks and logs.
So now we know the order that we need to run the tasks in. Next, we need to decide how robust we want this to be. We can decide to do as much or as little as needed for our security plan. Remember that, as a DBA, you have to follow the guidelines set forth for data retention and availability. You may be subject to different requirements than I am, so it is best to remember to use this as a guideline to creating your own database maintenance plan within the confines of your security principles.
Determining Complexity of the Maintenance Plan
Like I just said, this can be as simple or as complex as you want. I tend to try and keep things as simple as possible, so in this case, I’m not going to stray too far from that logic.
There are four main areas of database maturity , as shown in Figure 13-4.
Figure 13-4. Database Maturity Phases
Tip
Database maturity is the developmental process from when a new database is created, until it is to be implemented into a production environment for transaction processing to fulfill a business need.
Whether you are creating a database to simply store user and session information, or logging billions of transactions per second, you will always start with a fresh, clean database. That database must therefore mature before being ready to be used as it is intended. The only way that it matures is by going through the processes to ensure that it will meet the needs of the application and, ultimately, the end user. Part of those processes is going to be what maintenance needs to be done to the database and at what interval.
The interesting part here is that the planning for the database, ideally, should only be done at Phase 1. Now, obviously, this isn’t always realistic because the requirements of the application or the database may change. In this event, Phase 1 begins again. See how that works? Anytime new development happens, it must go from 1 to 4 and not skip any steps. This is the only way to ensure absolute integrity and cohesion of the systems. In other words, when new functionality is being developed, it needs to be planned first in Phase 1, shown Figure 13-4, and then progress through the workflow until it hits production.
This may be a bit too complex for your needs. This is how I do things at my job, so that’s how I’m used to doing it. You may find another way of doing things that fits your needs, and that’s fine. For now though, we are going to work using this method.
This doesn’t mean that we are going to create four different databases or anything like that. Instead, we are going to create one maintenance plan and then I will show you how to port the logic behind that maintenance plan, that is, what you learn from this book, into other databases.
Planning the Maintenance Plan
Plan the plan ? Sounds like a plan. Always plan ahead, whenever you can. Seriously. Yes, there’s a lot of alliteration in this paragraph, but it’s a concept that will save you more times than not. Always err on the side of caution, especially when dealing with databases. And here is the cardinal rule of dealing with databases: when in doubt, do a backup first. All it takes is that one time when you blow something up on accident, but you didn’t take a backup. That is probably all you need to happen to become a firm believer in backing up as often as possible.
Tip
When in doubt, always do a backup first!
So how do you plan a plan? You determine which steps you need, and then you plan how to implement them. Let’s look at two different scenarios and determine the best way to implement a maintenance plan from there.
For the first scenario, let’s say that you have a small database that doesn’t handle a lot of data at once. This might be in instances like a home database that is used to record kids’ chores or a CD collection.
For the second scenario, let’s say that you are the DBA for a medium or large company running their database that handles a fairly large number of transactions per hour.
Scenario 1
The first scenario is going to be for a small database that doesn’t handle a lot of data. Since this isn’t a very large database, and it probably isn’t mission critical (meaning it can be recovered quickly and easily and will not affect any portion of a mission or objective in its absence), then we can leave some of the tasks to the database engine to run. What tasks does SQL Server run automatically when those three magic DML statements are encountered? Rebuild Indexes, Reorganize Indexes, and Update Statistics. We can leave those out for this scenario, since we would effectively just be repeating what SQL Server has already done. For this reason, Scenario 1 might have a maintenance plan that looks like Figure 13-5.
Figure 13-5. Scenario 1 Structure
That’s a bit more streamlined, isn’t it? Remember, we don’t necessarily need to run all the tasks just because they’re available to us. We can leave some of the internal processing alone that SQL Server does anyway, and just worry about the integrity of the data. I would call this the “bare-bones minimum, absolutely necessary tasks only” maintenance plan. I don’t really think that this scenario would work for a whole lot of businesses out there, and if it does, it is probably because the DBA either doesn’t understand maintenance plans or doesn’t care to take the time to learn. Good thing you aren’t that DBA; you are taking positive steps to enhance your own knowledge just by reading this book. When put into practice, the tools you have picked up from this book with definitely help you in your quest to better data availability and integrity.
Like I said earlier, this is probably the best scenario for a home database, or one that is not critically important. If you have a dependency on the data to operate your business, then I don’t think that this is going to be the scenario you are going to want to go with.
Instead, you are going to want something much more robust; one that will perform the tasks that SQL Server does automatically, but when done as part of a maintenance plan, they are done completely and not just as a passing thought. Remember how SQL Server performs index restructuring and updates the database statistics after most queries; this factors in here because we are manually telling SQL Server to go ahead and make sure that those are ready to go for the next batch of data interactions after the maintenance plan has run successfully.
Scenario 2
This one is a bit more complex, but it’s closer to what I am used to. For this scenario, you are going to want to remember that we are not going to go for the quick and dirty path like in Scenario 1. This process is actually much closer to the complete maintenance plan that we will build shortly; it looks like Figure 13-6.
Figure 13-6. Scenario 2 Structure
Look familiar? It should. It’s the same graphic as earlier. This clearly implies that we need to do all of these steps. Remember that, although we have which tasks defined that we want to run, we need to know when to run them.
Notice that, in both instances, I have left the complete backup schedule intact. This is because, even though you may have a smaller database, the data within the database is no less important to your business or your end users. Without that data, you would have no job. So the concept of database backups will remain in place, regardless of the size of the database.
The rule that I’ve always heard is that maintenance needs to be done when your customers are off the database. If you have a database that is in use 24/7, then you just have to determine when your database is used least, or set the schedule to fire when the CPU is idle. Remember that SQL Server gives you lots of options for scheduling, and there is going to be a solution to just about any scheduling issue you can think of. For this plan, we are going to fire this at midnight every night. We are going to have different tasks run at different intervals also, all of which I will demonstrate shortly.
Another important part to scheduling the maintenance plan is that you can always set precedence constraints on the tasks, which we will get into later. Those are lifesavers! What those do is say that, when you have a chain of tasks in a task, you want to do them in a certain order. You can also set what happens if the task fails, succeeds, or completes. You can imagine that we are going to be setting reporting and logging at these steps, so that we are aware of any issues that may potentially affect our end users.
Let’s take a look at something interesting really quick. Double-click any of the jobs in SQL Server Agent. I chose bak Files, but you can choose whichever one you want. You should see Figure 13-7 now.
Figure 13-7. Job Properties
Same as before, what’s the big deal? Click the Steps option on the left and expand the columns in the job step list field, as shown in Figure 13-8. Notice anything special here?
Figure 13-8. Job Properties, Steps tab
That’s right; this is a SQL Server Integration Services package . I briefly mentioned this in the beginning chapter. That’s how it was saved in the database and we didn’t even know it. This opens up a whole new level of interaction with the database, because now that we have an SSIS package, we can put them all together to make one giant package.
Before we go on, I want to add the following caveat: you should have thorough and complete jobs at this point. What do I mean by complete? Double-check that all descriptions and names are filled in on all objects within the jobs, specifically:
General tab: Description box.
Steps tab, General tab: Name box (click Edit and change the “Step name” value).
Steps tab, Advanced tab: “Include step output in history” should be selected.
Schedules tab: We’re going to remove these in a few minutes, so they can stay for now.
Alerts tab: Should be blank.
Notifications tab: Write to the Windows Application event log should be selected with “When the job completes” selected.
Targets tab: Should be left blank.
Notifications should not be selected for individual tasks; instead, we are going to have the subplans take care of reporting and logging. E-mailing is a part of this function, which you will see shortly.
Guess what we’re gonna do next?
Creating the Maintenance Plan
Finally! This is what we’ve been working towards for this entire book. We are going to create one package that will be the implementation of the maintenance plan that we will use to maintain the database from now on.
Show of hands for who thinks we are going to use Maintenance Plan Wizard to create this maintenance plan…? Guess what? We can’t use the Maintenance Plan Wizard for this task, because the wizard is specifically for creating new tasks, not for combining them into a single package. We can make as many individual jobs as we want, but unless we put them all in one package together and run them on separate schedules, they will never work together. Also, leaving them to run individually, and not as part of an integrated solution, does not allow us to enforce the precedence constraints we looked at before; instead, we will simply run a task on a schedule, not knowing or caring if it actually worked or not.
Instead of that approach, we are going to work on the definition of robust maintenance plans; a one-stop-shop for all of our maintenance needs in one area. This is going to require a bit more tweaking of the individual plans, which we will look at now.
Editing the Jobs
The first thing that we need to do, in light of the knowledge that these are SSIS packages , is plan for the deployment of the packages. What does that mean? It means that each package is going to be run as a portion of a larger package , with precedence constraints and reporting/logging built in, and we need to make sure all of that is 100% set before unleashing it onto our development environment. We will do a lot of testing before we ever unleash anything to the production environment, right? Let’s look at the individual jobs now, and tweak them to our needs.
Recall that our jobs list looks like what’s shown in Figure 13-9.
Figure 13-9. SQL Server Agent Jobs
Again, quite a few jobs! Each one of those jobs is going to be a part of the package later. For now, we are going to edit these, though; so start from the top and make the recommended changes.
Finalizing the Jobs
Double-click a job to show the General page. Then go through each of the steps described in the following sections, making sure that the changes are made as explained.
General Page Settings
The General page holds all of the “high level” settings of the job. What I mean by this is that the further abstractions of properties are further divided in subsequent pages, but the generic settings for the entire job are all here.
Owner field should be set to the owner of the database.
Category should be set to Database Maintenance.
Enter a short description.
Make sure that the Enabled check box is selected.
Steps Page Settings
Click the Edit button at the bottom of the screen and change the Step name on the General page to the task name (Cleanup History, Text Files, T-SQL Task—the job name, not the plan name). Notice that this screen is something new and different, since it pertains strictly to the settings used to run the SSIS package. There isn’t a need to change anything in here yet, so leave this area alone.
Click Advanced on the left. This is where we defined the on success and on failure actions of the package. The individual package doesn’t need an output file, since we will be doing that at the maintenance plan package level and not this level. Ensure that the “Include step output in history” check box is selected.
Click OK when you are ready to move on.
Schedules Page Settings
Remove the schedule by clicking the Remove button at the bottom of the screen.
You will see error shown in Figure 13-10 when doing this step.
Figure 13-10. Schedule error
This is to be expected. Here’s how we’re going to take care of that for future schedule removals. Click OK on the error message and then click OK to close the job interface.
First of all, let’s look at the available schedules by running the following query.
SELECT * FROM msdb.dbo.sysschedules;The error in Figure 13-10 referenced the sysmaintplan_subplans.schedule_id column, so the short answer is to set these values to NULL using the following query.
UPDATE msdb.dbo.sysmaintplan_subplans SET schedule_id = NULL;And that’s about it for that. Now, when you go in to remove a schedule, you won’t get an error because all of the schedules have been removed. They are still resident in the interface though. How annoying.
The sysjobschedules Table
The msdb database holds a lot of information about our jobs, including the schedules assigned to those jobs. In particular, the table we are going to reference is named sysjobschedules . We are going to write a query that will show us the information in this table, so we can see the schedules and how they work in the context of the jobs.
Here’s the query we’re going to use to see what’s in that table; go ahead and run this in a new query window.
SELECT * FROM msdb.dbo.sysjobschedules ORDER BY schedule_id;That query will return the information shown in Figure 13-11.
Figure 13-11. Query Results
Let’s put a little INNER JOIN magic on the sysjobschedules and sysjobs tables to show us what is going on. For clarification, sysjobschedules holds the scheduling information for the jobs referenced from the sysjobs table. What we want to do is show the job_id and name columns from the sysjobs table, and the schedule_id column from the sysjobschedules table. The following is the completed query.
SELECT msdb.dbo.sysjobs.job_id, msdb.dbo.sysjobs.name, sysjobschedules.schedule_id
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.sysjobschedules ON
sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
ORDER BY msdb.dbo.sysjobs.name;Run this query. You will see the results shown in Figure 13-12.
Figure 13-12. Query Results
That’s better! That shows us the schedules left for us to delete. Let’s go ahead and manually delete the schedule for the top one, bak Files, and then rerun the query. Figure 13-13 shows the result of this query.
Figure 13-13. Query Results
Just as we thought; it’s gone! What does that tell us? It means that this table specifically stores the schedules for the jobs, hence the name sysjobschedules. It also tells us that we can now delete the contents of the sysjobschedules table, and that will remove all of our schedules.
Let’s take a quick look at the structure of the sysjobschedules table in Figure 13-14 for a second though.
Figure 13-14. sysjobsschedules Structure
None of these columns are IDENTITY columns, but we can see those foreign constraints that stopped us from deleting the schedules earlier (the error generated by SSMS). Since there isn’t an IDENTITY column to worry about, I’m going to recommend that we DELETE FROM this table.
This actually brings up a good point. What is the difference between TRUNCATE and DELETE FROM when clearing data from a table? Basically, when you TRUNCATE, the database engine doesn’t bother with that whole transaction log thing, it just wipes the table and the pages. The TRUNCATE operation can be rolled back if it is part of a transaction. If it’s not, you aren’t getting that data back without restoring from a backup. TRUNCATE also locks the table, so don’t use it on a table that is used a lot. DELETE FROM, on the other hand, simply deletes the specified information and logs it in the transaction log. DELETE FROM does not “restart” a table either; if you have a table with an IDENTITY column and delete everything in the table using the DELETE FROM command, the next IDENTITY value entered into the table is going to be the next value—even though there is no direct reference in the table. That means if you have 15 rows of data in a table; then run a DELETE FROM on that table; then insert a new row of data; the IDENTITY column value is going to be 16 and not 1. TRUNCATE “restarts” that table, so that when you have 15 rows of data and then TRUNCATE that table, the IDENTITY column starts back at 1 when inserting a new row of data. Big difference there!
That’s not to say that you can’t run a DELETE FROM command and not restart the numbering at 1 when the next row comes in. To do this, run the following query after running your DELETE FROM statement.
DBCC CHECKIDENT('[table_name].[column_name]', RESEED, 0);This query forces the table to reseed the column with the value defined as the IDENTITY qualifier, which in this case is 0.
So, in both theory and in practice, you could run a DELETE FROM statement and then the preceding DBCC statement, and achieve the same results as a TRUNCATE statement (wipe the table and reset the numbering for the IDENTITY column in a table), except that we are able to have the DELETE FROM and DBCC statements as part of the transaction log.
Okay, let’s go ahead and delete the contents of the sysjobschedules table, except for the syspolicy_purge_history record, by running the following query. Grab the job ID from the previous INNER JOIN query we ran before and insert that into the following query to run it.
DELETE FROM msdb.dbo.sysjobschedules WHERE job_id <> 'F74BC679-83F7-4F20-B816-9E12A630EAF1';That’s all you need to do. Remember, there is no IDENTITY column to worry about reseeding, so that command will do all we need for this table; clear out the schedules. If you open up the next job in the list, Cleanup History, notice the distinct lack of a schedule in the Schedules tab. Open any of them and check; they’re all gone. Good job!
Alerts Page Settings
This page should be blank. That’s okay, because we will be handling the logging and reporting from the package and not the individual tasks.
Notifications Page Settings
This is where we set the notifications earlier. This should be cleared out now though, much like the Schedules part above. We don’t need to send an e-mail, but I recommend keeping the Write to the Windows Application event log option selected and with the “When the job completes” value. That way, whenever the job completes (either On Success or On Failure), it is entered into the event log.
Targets Page Settings
This page is blank by default. Leave it blank.
Saving the Changes
Click OK to save any changes you have made and you are done with finalizing the jobs. Repeat the process of finalizing for each job except for the one called E-mail User Information. Leave that job alone.
We aren’t going to worry about the job titled E-mail User Information, since that one was specifically to show how the SQL Server Agent job task works. To keep this job from firing, just disable the task by right-clicking it and choosing Disable. You should see the interface shown in in Figure 13-15 when this happens.
Figure 13-15. Disable Jobs
Also note that the job now has a little red down arrow in the Jobs folder, as shown in Figure 13-16.
Figure 13-16. SQL Server Agent showing a disabled job
This icon shows at a glance that the job is disabled.
Reviewing Your Schedule Needs
We have configured the tasks to add to the maintenance plan. Let’s review what the schedule needs to look like for the maintenance plan (see Figure 13-17). Remember that we’re following Scenario 2 also, where we will be using as many maintenance tasks as available for proper maintenance of our database.
Figure 13-17. Scenario 2 Structure
I kept both of the Index tasks in there, as you can see. Remember how I said earlier that you didn’t need both in one plan? I will be running those at different times; that’s why they’re both in there like that.
Adding the Tasks to the Plan
Are you ready to begin putting your plan together? Here we go!
Expand the Maintenance Plan folder and look for Backup Maintenance Plan . Double-click this and you should see the interface shown in Figure 13-18.
Figure 13-18. Backup Maintenance Plan stage
Unfortunately, the Subplan names don’t carry over, and since we deleted our schedules, we need to update those really quick. Double-click the Subplan names to update the information, and notice how the stage changes when you click a different Subplan. This helps you to name them appropriately; Subplan_1 is Backup Database (Full), Subplan_2 is Backup Database (Differential), and Subplan_3 is Backup Database (Transaction Log). Update them using the interface and then enter the schedules.
Remember the schedule we wanted for the backups? Full backups are at midnight, differentials are every 6 hours, and transaction logs are every hour. Set your schedules for the three tasks to these values. You should end up with the interface shown in Figure 13-19.
Figure 13-19. Backup Maintenance Plan Schedules
Notice that the schedules are now set, the Subplan names are updated, and they are all set to run as SQL Server Agent service account.
What we want to do now is add the rest of the tasks to this maintenance plan. It’s pretty safe to say that we are going to stick to the three backup plans shown in Figure 13-19 as our standard for backing up, and all of our maintenance activities are going to be done in those three windows. Not all of the maintenance tasks, but some at different times and for different reasons. Because of this, we are going to add the needed tasks to each of the three backup tasks, and enforce precedence constraints.
Also, when you save the Backup Maintenance Plan, the job names are updated. How very inconvenient! We can update these again later.
Full Backup Maintenance Activities
What tasks do we want to run along with the midnight full backups ? Most of them, to be honest. For most installations, midnight is probably a good time to run maintenance activities since the chances of having users on is slim to none.
In particular, we want to run the following jobs, in this order:
Check Integrity
Rebuild Index
Shrink Database
Update Statistics
Backup Maintenance Plan.Backup Database (Full)
Cleanup History
bak Files
txt Files
trn Files
These tasks will be run at midnight every night. Adding them to the existing backup plan is trivial, at this point, since they’re already created. How? SQL Server gives us a lot of different ways to do things in different environments. In this case, we are going to add the jobs we already created to the existing Backup Maintenance Plan and go from there. This implies a couple of things, so let’s go over that really quick before we push forward.
Don’t delete the “old” maintenance plans. The original jobs, which are going to be added to the Backup Maintenance Plan, are still linked to those original maintenance plans.
Don’t delete the jobs.
In other words, just because we are adding the jobs to the maintenance plan, you shouldn’t assume that there isn’t a reference remaining to the original plans. As long as it’s structured how we have it, the reference will remain. Later, we will export the completed plans and review how to restore them. You will then see exactly how these references work together to create the maintenance plans as you now know them.
Adding the Check Integrity Task
Click the Backup Database (Full) subplan as shown in Figure 13-19. This will show the Back Up Database (Full) task on the stage, as also shown in Figure 13-18. Now, click and drag Execute SQL Server Agent job from the toolbar to the stage. You should see the interface shown in Figure 13-20 at this point.
Figure 13-20. Backup Maintenance Plan Stage
All that says is that we have a generic SQL Server Agent job task there, but we haven’t defined a task yet. To do this, double-click the task on the stage. Guess what shows up? Figure 13-21 is a big hint.
Figure 13-21. Execute SQL Server Agent Job Task
Look at that! All the jobs that we already created are lined up and looking nice and presentable (once I sorted the job name column). How cool is that? The implication of this is that we don’t have to go back in and re-create what we have already done; we can just add them here and be done with it. Aren’t you glad you put in all that work now? Since I already sorted the jobs, go ahead and click the check box next to Check Integrity, and click OK. You should now see what is shown in Figure 13-22.
Figure 13-22. Execute SQL Server Agent Job Task on Design Surface Stage
That shows us that the task has been updated to the existing Check Integrity task. If you long-click the task name on the top, you can change it to Check Integrity, as shown in Figure 13-23.
Figure 13-23. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Now I can easily see what this task is. At this point, you need to reorganize your stage a little bit. See how the backup task is way up on the top left? Just drag it more to the middle, and the drag the Check Integrity task above it. You should end up looking similar to Figure 13-24.
Figure 13-24. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the Rebuild Index Task
Just like before, click and drag Execute SQL Server Agent job from the toolbar to the stage, but this time, place it in between the Check Integrity task and the backup task. You should end up looking similar to Figure 13-25.
Figure 13-25. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Rebuild Index task from the list, and click OK. Long-click and change the name of the task. You should see Figure 13-26 when you are done.
Figure 13-26. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the Shrink Database Task
Click and drag Execute SQL Server Agent job from the toolbar to the stage, but this time, place it in between the Rebuild Index task and the backup task. You should end up looking similar to Figure 13-27.
Figure 13-27. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Shrink Database task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-28 when you are done.
Figure 13-28. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the Update Statistics Task
Click and drag Execute SQL Server Agent job from the toolbar to the stage, but this time, place it in between the Shrink Database task and the backup task. You should end up looking similar to Figure 13-29.
Figure 13-29. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Update Statistics task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-30 when you are done.
Figure 13-30. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the Cleanup History Task
Click and drag Execute SQL Server Agent job from the toolbar to the stage, but this time, place it after the Back Up Database (Full) task at the bottom. You should end up looking similar to Figure 13-31.
Figure 13-31. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Cleanup History task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-32 when you are done.
Figure 13-32. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the bak Files Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it at the bottom. You should end up looking similar to Figure 13-33.
Figure 13-33. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the bak Files task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-34 when you are done.
Figure 13-34. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the txt Files Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it at the bottom. You should end up looking similar to Figure 13-35.
Figure 13-35. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the txt Files task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-36 when you are done.
Figure 13-36. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the trn Files Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it at the bottom. You should end up looking similar to Figure 13-37.
Figure 13-37. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the trn Files task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-38 when you are done.
Figure 13-38. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
We will reorganize that shortly, don’t worry.
Differential Backup Maintenance Activities
What tasks do we want to run along with the differential backups run every 6 hours? We want this to be sort of a refresh period; a quick water break, so to speak.
In particular, we want to run the following jobs, in this order:
Check Integrity
Reorganize Index
Shrink Database
Update Statistics
Backup Maintenance Plan.Differential Backup
Wait. That’s mostly the same list! Yes, because most of the same operations need to be performed whenever you back up the database. The main difference is that we are doing the Reorganize task instead of the Rebuild task, and we aren’t cleaning the maintenance files or history. That is done only at midnight, so we are sure to stay in sync with the backup schedule. Remember the difference between reorganizing and rebuilding the indexes? Reorganize keeps the hotel built, and Rebuild tears it down and starts brand new. We don’t need to take the time to rebuild when we can just run a quick reorganize task and continue on. This isn’t sacrificing anything, since the nightly rebuild takes care of this extra step.
Adding these is going to be very similar to the previous part, so feel free to add them and skip ahead, if necessary.
Adding the Check Integrity Task
Click the Backup Database (Differential) subplan as shown in Figure 13-39. This will show the Back Up Database (Differential) task on the stage, as also shown in Figure 13-39.
Figure 13-39. Backup Maintenance Plan Stage
Now, click and drag Execute SQL Server Agent Job from the toolbar to the stage. You should see Figure 13-40 at this point.
Figure 13-40. Execute SQL Server Agent Job Task
Double-click the task on the stage and click the check box next to Database Integrity Plan.Check Integrity, and click OK. Update the name by long-clicking the task. If you long-click the task name on the top, you can change it to Check Integrity, as shown in Figure 13-41.
Figure 13-41. Execute SQL Server Agent Job Task on Design Surface Stage
At this point, you need to reorganize your stage a little bit. See how the backup task is way up on the top left? Just drag it more to the middle, and the drag the Check Integrity task above it. You should end up looking similar to Figure 13-42.
Figure 13-42. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Adding the Reorganize Index Task
Just like before, click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Check Integrity task and the backup task. You should end up looking similar to Figure 13-43.
Figure 13-43. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Reorganize Index task from the list, and click OK. Long-click and change the name of the task. You should see Figure 13-44 when you are done.
Figure 13-44. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Adding the Shrink Database Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Rebuild Index task and the backup task. You should end up looking similar to what’s shown in Figure 13-45.
Figure 13-45. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Shrink Database task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-46 when you are done.
Figure 13-46. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Adding the Update Statistics Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Shrink Database task and the backup task. You should end up looking similar to Figure 13-47.
Figure 13-47. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Update Statistics task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-48 when you are done.
Figure 13-48. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Transaction Log Backup Maintenance Activities
What tasks do we want to run along with the transaction log backups run every hour? This is sort of the most difficult one, because we don’t want to put too much burden on our server, but we want to be able to perform maintenance as necessary.
In particular, we want to run the following tasks, in this order:
Database Integrity Plan.Check Integrity
Reorganize Index Plan.Reorganize Index
Shrink Database Plan.Shrink Database
Update Statistics Plan.Update Statistics
Backup Maintenance Plan.Transaction Log Backup
Adding these is going to be very similar to the previous part, so feel free to add them and skip ahead, if necessary.
Adding the Check Integrity Task
Click the Backup Database (Transaction Log) subplan as shown in Figure 13-49. This will show the Back Up Database (Transaction Log) task on the stage, as also shown in Figure 13-49.
Figure 13-49. Backup Maintenance Plan Stage
Now, click and drag Execute SQL Server Agent Job from the toolbar to the stage. You should see Figure 13-50 at this point.
Figure 13-50. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task on the stage and click the check box next to Database Integrity Plan.Check Integrity, and click OK. Update the name by long-clicking the task. If you long-click the task name on the top, you can change it to Check Integrity, as shown in Figure 13-51.
Figure 13-51. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
At this point, you need to reorganize your stage a little bit. See how the backup task is way up on the top left? Just drag it more to the middle, and the drag the Check Integrity task above it. You should end up looking similar to Figure 13-52.
Figure 13-52. Execute SQL Server Agent Job Task on Design Surface Stage (completed)
Adding the Reorganize Index Task
Just like before, click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Check Integrity task and the backup task. You should end up looking similar to Figure 13-53.
Figure 13-53. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Reorganize Index task from the list, and click OK. Long-click and change the name of the task. You should see Figure 13-54 when you are done.
Figure 13-54. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Adding the Shrink Database Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Rebuild Index task and the backup task. You should end up looking similar to Figure 13-55.
Figure 13-55. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Shrink Database task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see Figure 13-56 when you are done.
Figure 13-56. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
Adding the Update Statistics Task
Click and drag Execute SQL Server Agent Job from the toolbar to the stage, but this time, place it in between the Shrink Database task and the backup task. You should end up looking similar to what’s shown in Figure 13-57.
Figure 13-57. Execute SQL Server Agent Job Task on Design Surface Stage
Double-click the task that we just added, select the Update Statistics task from the list, and click OK. Update the name of the task by long-clicking and updating it. You should see what’s shown in Figure 13-58 when you are done.
Figure 13-58. Execute SQL Server Agent Job Task on Design Surface Stage (updated)
So there are the three parts of the maintenance plan in place. But we don’t have schedules for the jobs we just added. How is the maintenance plan going to know when to execute the packages?
By using the power of…
Precedence Constraints
What is a precedence constraint ? Basically, a precedence constraint is what determines the path of execution for a task based on the outcome of an expression or constraint. Using precedence constraints allows us to assign pass, fail, or success values based on the state of execution of the task.
For example, let’s assume that we have a Success constraint and a Failure constraint for a task. We could define a Notify Operator task as a result of the Failure constraint, and either define another task after the Success constraint, or let it cease execution normally. Enforcing precedence constraints in a maintenance plan workflow will greatly enhance the visibility into the tasks being run and the conditions they are executing under.
Let’s start with the full backup tasks. First off, separate your tasks a little bit, as shown in Figure 13-59. Give yourself some room to work. There are nine tasks in here, so it’s going to take up a fair bit of room. Note the yellow highlighter to show you how to structure these tasks.
Figure 13-59. Full Backups Precedence Constraints Map
Click Check Integrity and a little green arrow will appear on the bottom of the task. Click that arrow and drag it to the Rebuild Index task on the stage. You should see what’s shown in Figure 13-60 when you are done.
Figure 13-60. Full Backups Precedence Constraints (updated)
It kind of snaps to the task when it connects, doesn’t it? That’s good though, so you know where it is heading.
Now, if you double-click that green line, you see Figure 13-61.
Figure 13-61. Precedence Constraint Editor
This is called the Precedence Constraint Editor. Let’s take a look at these constraints for a minute.
Constraint Options
Constraint options allow us to define what operation to evaluate, and what action to take when an evaluated constraint is discovered. We need to choose a constraint and/or an expression to evaluate, and then define the return value based on the value of the constraint or expression so we can catch the condition and plan accordingly. The following list explains this in a bit more detail.
Evaluation Operation
Constraint
Expression
Constraint and Expression
Constraint or Expression
Value
Success: the job executes successfully, without error
Failure: the job fails and causes an error
Completion: the job completes, whether success or failure
Multiple Constraints
Yes, there can be multiple constraints on the task. This gives us greater flexibility in the interpretation of the task, as shown in the following list.
Logical AND: All statements must evaluate to TRUE
Logical OR: One constraint must evaluate to TRUE
LOGICAL AND means that we can define a lot of constraints in our task, and ALL of these constraints must evaluate to TRUE.
LOGICAL OR means that we can define a lot of constraints in our task, and ANY ONE constraint must evaluate to TRUE. This means that, in an n-based list of constraints, 1-to-n constraints must be TRUE, but always at least one, or the task fails.
Honestly, most of this can stay right where it is. We want a Success constraint, and the default is exactly that. That’s not to say that you can’t update this area to do what you want, but for this purpose, we don’t need to update anything, so click OK.
It looks like you can add multiple constraints as well. Click the Check Integrity task, and another little green arrow appears. Drag it to the Rebuild Index task. You should see what is shown in Figure 13-62.
Figure 13-62. Precedence Constraints error
Okay, looks like a no. Which kind of presents a question as to why SSMS gave us the option in the first place, but that’s a different subject. That’s okay though, because we’ve got our constraint set up.
Now, do this for each of the tasks in the full backup plan as shown in Figure 13-63. Remember, the only constraints you want are Success constraints.
Figure 13-63. Full Backups Precedence Constraints
Figure 13-64 shows what your differential backups plan should look like.
Figure 13-64. Differential Backups Precedence Constraints
And Figure 13-65 shows what your transaction log backup plan should look like.
Figure 13-65. Transaction Log Backups Precedence Constraints
We can see that each of the tasks now have a literal workflow. Each task leads to the next task, and if it fails, it is logged.
Speaking of logging, what options do we have for reporting and logging of this maintenance plan? I’m glad you asked! Click the Reporting and Logging button in the taskbar, as shown in Figure 13-66.
Figure 13-66. Reporting and Logging button
Clicking that opens the interface shown in Figure 13-67.
Figure 13-67. Reporting and Logging
This is the default screen. Look at that; we can generate a new text file report of the maintenance plan operations when they are run, AND we can send an e-mail to an operator, as shown in Figure 13-68.
Figure 13-68. Reporting and Logging (updated)
That’s very convenient! Once your interface is updated, click OK to continue. Save your work now, and bask in the glory of your nearly completed maintenance plan.
Note that, since we have set this, these reporting and logging settings are enforced for all of the subplans in the Backup Maintenance Plan. This is a good thing, so we don’t have to keep setting them. Alternately, this also implies that if you need reporting or logging outside of these restrictions, you must create a new maintenance plan with different reporting and logging settings.
Let’s review what we’ve done really quickly.
We added the relevant tasks to the existing Backup Maintenance Plan.
We added precedence constraints on the tasks, in the order we decided earlier.
We added reporting and logging to the maintenance plan.
What this simply means is that the subplans of the Backup Maintenance Plan run at their scheduled time, starting with the first task and ending with the last task. When it is complete, an e-mail is sent with the maintenance text report attached. The SQL Server Agent keeps a record of this in its History logs as well. Whether it fails or succeeds, the operator is alerted.
Testing the Maintenance Plan
Now that it’s all set up, let’s run some tests on it. We set our operator to be informed, so we should just be able to run the maintenance plans, and then wait for notification.
Keep in mind that this will run everything, all of the tasks, in the order they are in. We want to start with full and then run the differential, and finally run the transaction log. You can’t just right-click the Maintenance Plan though, because there are the three subplans in there. Instead, right-click the Backup Maintenance Plan.Backup Database (Full) job in the Jobs folder in SQL Server Agent, choose Start Job at Step… and observe what is shown in Figure 13-69.
Figure 13-69. Start Jobs
Looks good so far.
Take it back a step and run the differential job now, and see what is shown in Figure 13-70.
Figure 13-70. Start Jobs
Let’s run the transaction log job to see what is shown in Figure 13-71.
Figure 13-71. Start Jobs
Nicely done. If you’ve followed along, then you have probably received quite a few e-mails detailing the maintenance activities. Take a few minutes to familiarize yourself with these e-mails, and know what you’re looking for. That way, in the future, you could conceivably scan an e-mail quickly for the information you need. You can also decide whether to leverage the usefulness of having e-mails sent when the maintenance activities complete, or to bypass this notification all together. Ultimately, it is your choice as the database administrator. Luckily, you’ve just learned how to adjust the settings of the maintenance plan to include or remove steps and notifications very quickly!
This proves that we have now accomplished our goal of creating a complete maintenance plan in our database that will take care of almost all of our day-to-day maintenance activities. Keep in mind that these jobs can be run at any time by running them from SQL Server Agent. Alternatively, you can also run a job with a script, which might be more up your alley. To do this, let’s first look at the script and then understand what it does.
Starting a Job from a T-SQL Script
The script to start a job is sort of simple, and looks like this.
EXEC msdb.dbo.sp_start_job @job_name=N'Backup Maintenance Plan.Backup Database (Transaction Log)'; In this case, I wanted to start the transaction log job. Entering this query into a New Query window and pressing F5 to execute it will show you the following status.
A little note about this script; it is a stored procedure that is running a start command against the sysjobs table in msdb. To take a look at this table, just enter the following query in a New Query window and press F5.
SELECT * FROM msdb.dbo.sysjobs;Figure 13-72 shows what is returned from that query.
Figure 13-72. Query Results
Look familiar? That’s all of the jobs we created, and some detail information too. Pretty cool!
Now, open up the Job Activity Monitor inside of SQL Server Agent. Figure 13-73 shows you what you should see at this point.
Figure 13-73. Job Activity Monitor
Well, there are all of our jobs, lined up for us and telling us all sorts of useless stuff. You can see that they all are enabled, except for E-mail User Information. They are all Idle, meaning they aren’t currently running. They have all succeeded when they last ran. If you scroll to the right, you will see the columns Last Run and Next Run. Guess what those tell you? This is a great way to get a glimpse of whether or not your jobs are running correctly. It is right here if they aren’t. You can right-click any of the jobs here and do some damage, so be aware of what you’re doing.
Tip
Is it important to note that you should not delete the extra maintenance plans. They are tied to the jobs they contain, and if you delete the maintenance plan, you will delete the job. Guess what happens at that point? Dunce cap. You guessed it.
One more thing before we close this out. In SSMS Object Explorer, click Connect and connect to Integration Services. If you can’t connect to Integration Services, then just start SSMS as an Administrator and you should be good to go.
Expand the Stored Packages folder, followed by MSDB, and then Maintenance Plans. You should see what’s shown in Figure 13-74 at this point.
Figure 13-74. Integration Services Maintenance Plans
Right-click our Backup Maintenance Plan and select Export Package… Figure 13-75 will then appear.
Figure 13-75. Export Package
We want to change the package path to a new folder in E:SQL Server, so let’s call it DTSX. That means our maintenance plan backup location is going to be E:SQL ServerDTSXBackup Maintenance Plan.dtsx, so enter that and then click OK. It’s okay to leave all the other settings alone. If you check that directory, you will see that file now in there. You can even double-click these .dtsx files to execute them.
Do this for all the other maintenance plans, just in case. Now, we have a backup of all of the work that we’ve done, just in case something happens to our original files.
Let’s test it! Oh, look, a nefarious individual has broken into the database and deleted the Update Statistics Plan maintenance plan. Look at Figure 13-76—it’s disappeared!
Figure 13-76. Maintenance Plans
Doggonit, Figure 13-77 shows that the job is gone too!
Figure 13-77. SQL Server Agent Jobs
Whatever shall we do? Oh yeah, we took a backup. Can we add that back easily? Yep.
Connect to Integration Services and check the Maintenance Plans folder. It’s gone also. No worries though; just right-click this folder and choose Import Package… to show Figure 13-78.
Figure 13-78. Import Package
Click the ellipse next to “Package path” and navigate to our DTSX folder location, E:SQL ServerDTSX. Select Update Statistics Plan.dtsx to continue. Your interface should now look like Figure 13-79.
Figure 13-79. Import Package (updated)
Click OK and refresh this folder, and there is our plan again, as shown in Figure 13-80.
Figure 13-80. Integration Services Maintenance Plans
Go look at your Maintenance Plans and Jobs folders in the database engine. See anything interesting in Figure 13-81 or Figure 13-82?
Figure 13-81. Maintenance Plans
Figure 13-82. SQL Server Agent Jobs
So it added the Maintenance Plan, but not the job? Yes. But we’re not done yet.
Go back to the Integration Services part again, and right-click the Update Statistics Plan and choose Export Package…, which brings up the interface shown in Figure 13-83.
Figure 13-83. Export Package
We aren’t going to export it to the file system; that wouldn’t make any sense for what we’re trying to accomplish. Instead, update your interface to show the values shown in Figure 13-84. For the Package path value, click the ellipse and expand MSDB, click Maintenance Plans, and then click OK.
Figure 13-84. Export Package (updated)
Click OK when you’re done. You’re going to get a pop-up window that looks like Figure 13-85 next.
Figure 13-85. Export Package alert
Click Yes here, so that we’re replacing the current package.
Skip back over to the database engine instance and refresh your Maintenance Plans folders again. You can see in Figure 13-86 that the plan is back.
Figure 13-86. Maintenance Plans
Still no job though. Weird, huh? One last step, young Jedi.
Even if you refresh the Jobs folder, it won’t be there. You need to open up the plan first, and go from there. Double-click Update Statistics Plan in Maintenance Plans to show the interface shown in Figure 13-87.
Figure 13-87. Update Statistics Plan
Double-click the Subplan name and update it to show what is in Figure 13-88.
Figure 13-88. Subplan Properties
Save the plan now, and go back to look at your Jobs folder again, as shown in Figure 13-89.
Figure 13-89. SQL Server Agent Jobs
There it is! You’re going to need to change the name from Update Statistics Plan.Update Statistics to just Update Statistics though. After that’s done, you should see the updated interface shown in Figure 13-90.
Figure 13-90. SQL Server Agent Jobs, updated
Let’s verify that we have it back to how it was before though.
Double-click Backup Maintenance Plan and then double-click the Update Statistics task in the stage. You should then see the interface shown in Figure 13-91.
Figure 13-91. Execute SQL Server Agent Job Task
Right down at the bottom there… excellent! We have successfully restored our saved package.
Summary
Wow! We took all that we learned in the course of this book and put it all together into one cohesive plan. Let’s take a quick look at exactly what this chapter showed us.
We created individual maintenance plans for each time slice on the backup schedule, with each schedule having different tasks running at different times.
We enforced precedence constraints on the tasks within each maintenance plan.
We set reporting and logging on the tasks, with e-mails on completion of each task.
We learned how to start a job with a script.
We learned briefly about the Job Activity Monitor and its importance.
Let’s examine a couple of things before we close:
We could have done the exercises in this whole book without setting the Schedule in the individual chapters, but that wouldn’t have led you to the knowledge of how the schedules work together with the queries in this chapter. I wanted you, the reader, to understand that those schedules aren’t just arbitrary values, but are part of the overall maintenance strategy and should be treated as such.
We also could have done the large majority of this book working with just the Backup Maintenance Plan in the Design Surface, but again, this wouldn’t have given you the knowledge you now have on the why and not just the how. You can certainly do that if you would like—go back and do it from the Design Surface instead—as that would be a good exercise in your spare time. But eventually, you need to get back into the jobs portion of SQL Server Agent to configure the actual jobs, which is why I had you go through it like we did.
Congratulations! You are now finished with the meat and potatoes (tofu and kale) of this book. I hope that you have a fully functional maintenance plan now in place and that you are already dreaming of ways to expand upon this newfound knowledge.