Tuesday, February 14, 2012

On the shoulders of giants

In my previous post I had said that I was going to break out my maintenance plans and roll my own scripts.
That was a dumb idea.
Sure, writing something from scratch would've given me knowledge, yadda yadda yadda, and experience. But there is also the tested and true, "don't reinvent the wheel". While wanting to the the former, I subscribed to the latter. Off to The Google, I went. The first hit was Ola Hallengren's website.

When I was perusing the site, I kept thinking "why didn't I discover this sooner?"

After setting up the nuts and bolts of the maintenance solution on my local instance, off I went to check things out. The solution out of the box, sets up agent jobs without schedules. The various jobs are full backups of the system databases, user databases, diff and log backups of the user databases. There is also a job that cleans up the solution's own logging table. That table will get full quickly as it will log every index change, stat update, log/diff/full backup, every DBCC that is run etc... It will also create DBCC jobs for system and user databases. Penultimately it will create an agent job that will run to "optimize" indexes.

For the backups, we have a different LSA for each of our user databases, so I couldn't just use the same job for all of them. I just copy and pasted the contents of the job steps in each of the log, diff, and full jobs (that were created automatically) into new agent jobs that I had created. The best part is that you can specify which databases that you don't want to be backed up. 
The DBCC and Index Optimize stored procs take the same format in regards to which databases that you want to do the work on. For our environment I have added DBCC and Index Optimize as steps in each agent job along with the desired type of backup step.

I am glad that I have gotten away from maintenance plans. It feels good to try and swim in the deep end. I know I won't drown as the shoulders of giants aren't too far below me.