The Art of SQL Server Database Administration, Development, and Career Skills for the Technically Minded
Any SQL Server of significant activity or size will need regular care and feeding. SSMS provides, through it’s Maintenance Plan Wizards, a smooth UI to set some of these typical tasks up in a very easy way. Today we cover these wizards and look into some of the in’s and out’s of setting plans up.
Ok, first off, I’m sorry about yesterday. Let’s get back on this horse now. The cool thing about this SSMS feature is that it gives you a really easy way to setup and schedule some fairly complex and significantly impactful scheduled jobs on your servers. The bad thing – yeah pretty much that – it gives you a simple way to really do some damage if you’re not careful.
Maintenance Tasks available in these plans include all of the following:
In the real world – most experienced DBAs have chosen many routes for carrying out regular maintenance. There are some excellent 3rd party tools on the market as well as some tried and trusted scripts from the likes of Ola Halengren. If you are responsible for doing maintenance on your server – take the time to look at all your options.
Under Object Explorer, Maintenance Plans can be found in the Management Node. The right click menu gives you two options ‘New’ and ‘Wizard’. The Wizard walks you through each option step by step, while selecting ‘New’ pretty much let’s you drive and setup the plan however you’d like.
Every Plan has one or more maintenance activities performed against one or more databases and one or more schedules to execute these activities. Quick note, you don’t HAVE to schedule it, when setting up a plan as a simple template for example. Activities are grouped into Plans and Sub Plans – and each can have it’s own schedule, logging, and connections. Having these all many to many relationships makes plans very versatile.
There is a plethora of tutorials and documentation on Maintenance Plans and the activities they perform. The focus of this series is and always has been using SSMS effectively. So, I’m not going to get into the activities themselves as much as offering a couple tips and tricks for working with the GUI.
1. When setting up a new plan, I start with the Wizard. This helps me not miss any important steps and builds out the framework. Once the plan is built – it will appear under the Maintenance Plans node and I can always go back and modify it further from there. Right click your plan under the Maint Plans node, right click, select Modify.
2. There is a fine line between making a million plans to each do a simple task – and having one plan that rules them all. They are both nearly impossible to manage for two different reasons. Group your plans into related and dependent tasks. If the tasks have nothing to do with each other – give them a separate plan.
3. Give you plans and subplans descriptive names and descriptions. Even if you set it up via the wizard you can go back in – double click the row and rename. If not for you – for the poor sap who will have to come along later and figure out what the plan does. Don’t settle for the default names.
4. Take advantage of ‘View T-SQL’. Double clicking a task block in the plan (right) brings up the modification GUI (below). Look at the bottom of the dialog. This is a great way to see the actual TSQL that will accomplish your task. Further – as you become more and more comfortable with maintenance you could use these scripts as starting points to move to your own custom solutions above and beyond what plans offer.
OK – enough of this documented, entry level stuff. We’ve spent 26 days on doing things the “right way”. Tomorrow we go all the way to the other side of the spectrum and we start hacking (ahem, I mean) building or own SSMS Add-In.