My First Maintenance Plan–Part 1

I know that it’s been a while since I’ve posted anything to my blog but I’ve had an incredibly busy past couple of weeks.  I managed to pass MB3-700 so now I’m certified in Dynamics GP 2013 so that’s fun and next up is 70-461 which is querying Microsoft SQL Server 2012.  So I’ve been reading GP community forums and trying to answer questions regarding SQL server as best I can and I’ve come to a conclusion.  I believe that most users  / admins of GP know how to write very complicated SQL queries but not many have working knowledge on the database administration side of the fence at least in my own little world.  So I got to thinking back on when I was placed in charge of my companies GP install and also being new to SQL server.  I found that first couple of weeks to be very rough and we ended up calling in some outside help for GP because our maintenance plans stopped working and I had no clue on what to do.  Well in this series I’d like to walk you through the process of creating a basic SQL maintenance plan using SSMS.  Now I’ll admit that generally most DBA’s really don’t like maintenance plans that are created out of the box using SSMS because your ability to customize them and the default values are really off in some aspects.  We’ll cover some very basic information and I’ll link you to the detailed information from Microsoft at the end of the post.  Please note this is very basic introduction so you can get up and running in a very short amount of time. 

 

Stuff:

Before we begin it would be a good idea to ensure that you have SQL server setup with operators and the ability to send emails.  Those subjects are outside the scope of this post but I’ll link you two really great resources for getting these setup.  While it’s not an absolute requirement it helps to have SQL email you when a job fails or completes, if not then you’ll need to manually check it and that can get tiresome after a while. 

 

SQL Agent Operators – http://www.ideaexcursion.com/2009/01/05/configuring-sql-server-agent-operators/

Database Mail – http://www.mssqltips.com/sqlservertip/1100/setting-up-database-mail-for-sql-2005/

 

Basics:

Maintenance is an ongoing part of managing a Microsoft SQL environment.  How simple or complex the maintenance is can vary from one server to another.  So where does one start when they are looking to build a maintenance plan?  Well that question is pretty broad in terms of SQL but I’ll give you my spin on it and take it from there. 

The first thing you are going to want to do is figure out what’s going on now.  There are several methods that you can use but for the sake of simplicity we’ll stick to using the GUI inside of SSMS. 

  1. Open SSMS and connect to your GP database server.
  2. Select Management > Maintenance Plans. 
    1. If nothing is listed here continue on to the end of this list. 
  3. Examine the items listed and double click on each one.  This will open the current maintenance plans.  Take time to examine each object by double clicking it. 

If nothing was listed that doesn’t mean that maintenance isn’t being done.  It could mean that the previous DBA scripted out the maintenance into Agent Jobs.  To identify those expand out SQL Agent inside of SSMS and then click on JOBS as show in the image below.

image

If you click here and there are no jobs then the next thing is to figure out if the maintenance is being done via a 3rd party application.  If no maintenance is being done then you might want to at the very least take backups of all the databases on the server w/ the exception of the TempDB.  I would urge you to expedite the backups as quickly as you can because having no backups could be absolutely devastating to a company.  To learn how to take a backup simply click here and follow these instructions. If you did find a maintenance plan then good deal.  Take a moment to open it up and take a look under the hood and when your done close it out.  If you are asked to save changes simply say no.

 

What’s In a Maintenance Plan?

The basic outline for a maintenance plan in my head is as follows.

  1. Database Integrity Check
  2. Index’s
    1. Reorganize
    2. Rebuild
  3. Update Statistics
  4. Backups
  5. Cleanup

 

Wrapping Up:

So at this point I think we’ve reached a rather decent stopping point.  We’ve identified methods to determine if a maintenance plan is in place and if not then you need to at the very least take a backup of the database.  In the next part of the this series I’ll outline the details of the 5 items above and what each of them do.  Then in the 3rd part I’ll tie it all up and I’ll create a maintenance plan wit detailed instructions. 

One Comment

Add a Comment

Your email address will not be published. Required fields are marked *