As a Systems Admin in a smallish shop with my fingers in a lot of pies, I don't have the luxury of focusing on one tech and getting uber-awesome at it. I don't know every nook and cranny of SQL Server, IIS, or even Windows Server. However I can follow directions and read blogs like a pro. Also, as with everything else... Google is my friend!
With this in mind, it is no surprise that I was a bit late to the game when it comes to SQL Server Maintenance Plans. A SQL Server can run without them and databases work. So, when you get an app (like Rock RMS) installed and running it's easy to move on to the next thing. But, getting a maintenance plan in place is a great 'next step' to help keep your Rock install running smoothly.
After doing some research, we here at Central have come up with what we believe to be a maintenance plan that works pretty well. As stated, I am certainly no SQL Server expert and welcome any feedback and thoughts on this post. With that said, let's have a look at what we are doing with our SQL Server maintenance plan for our Rock RMS database.
To create our maintenance plan, we started in SQL Server Management Studio:
- In Object Explorer, navigate from the server, to Management, to Maintenance Plans.
- Right-click and select 'New Maintenance Plan...'
- Name the plan and click OK.
This will take you to the Plan Designer for your Maintenance Plan.
Our maintenance plan has two sub-plans. So, first thing, click the Add Subplan button to add a second subplan.
(One subplan runs every Friday and the second subplan runs every other day of the week (Sat-Thu))
You can double-click on each subplan to change its properties (name, description, schedule, etc.) We ended up with the following
- Now with a subplan selected, you can begin building it out by dragging Tasks from the Toolbox.
- The “Weekly on Friday" subplan only has two Tasks, as shown below.
- Drag the two tasks from the Toolbox. Connect then by selecting the 'Check Database Integrity Task' and then dragging the arrow to the 'Rebuild Index Task'. This creates the progression from task to task within the subplan. You can right-click on the connecting arrow to modify the criteria for the progression from task to task.
- Edit the tasks as needed, pointing them to your Rock database.
- The 'Daily Except Friday' subplan has a few more tasks
- Again, drag the tasks from the Toolbox, link the tasks (editing the links as needed), and configure each task.
- Save your changes.
Once your plan has run at least once (say, the next day), you can right-click on it and select 'View History' to see how things went. Green check-marks are good, from what I can tell!
I hope this helps you create a SQL Server maintenance plan for your Rock RMS database and keeps your system running smoothly.