This post is part of the SQL Community Project #DBAJumpStart by John Sansom.
“If you could give a DBA just one piece of advice, what would it be?”
Last year John asked 19 successful and experienced SQL Server professionals and me (for some baffling reason) this exact question. Due to some technical problems and a number of time constraints this post is a smidgen later than some of the other collaborators, but better late than never. In the post below I share my own thoughts with you don’t forget that you can find all our collective wisdom inside DBA JumpStart, a unique collection of inspiring content just for SQL Server DBAs. Be sure to get your free copy of DBA JumpStart today!
In a previous post “Which personal traits should a #SQLPASS Director have?” I shared excerpts from my personal report created by StrengthsFinder 2.0. There is however another trait that I feel that every DBA should have. That trait is Vigilance, vigilance is key to a successful career in most professions. As a Database Administrator and guardian of the security and integrity of data it is arguably the most important trait you can have.
The key thing to take away is that a good DBA is proactive not reactive. You can never become truly proactive if you do not remain vigilant. The job of a SQL Server DBA can be quite different than that of another DBA from another RDBMS platform. Part of this is due to the width of features which see’s some DBA’s as a jack of all trades covering tasks likes monitoring as well as designing cubes and reports. Whilst others are the victim of server sprawl and the number of instances in their estate registers in the hundreds.
Saying that you should be proactive is all well and good, but how can you as a data professional achieve this.
I’ll touch briefly on each of these points to cover why I believe they are important and how being vigilant helps your organisation and your career. It is however the last point that I want to talk about today (Edit – In this series) and one aspect of your DR planning in-particular.
Automating regular tasks
I’m constantly amazed by the number of IT professionals who have a manual daily check list. It’s great that they have the checks and are doing them, but manually?!? Come on, there’s a better way. The paid for editions of SQL Server come with something called “SQL Server Agent” which is in essence a scheduler. Even if you aren’t lucky enough to be using one of the premium editions of SQL Server you can still schedule scripts through the Windows Task Scheduler.
Make sure you have the SQL Agent installed and it is set to start automatically (there’s caveats with clusters). Once you have this set up you’ll need to do the following:
- Set up Database Mail
- Create some operators, make sure that you use distribution lists here rather than hardcoded individuals. It makes things much easier as you scale out or have changes in your departments.
- Look to see which alerts are relevant for your environment.
As a proactive or vigilant DBA there were a few jobs that I set up to make sure I was aware of everything I needed to be. Why not challenge yourself to do the same:
Create a job to test Database Mail.
This may sound strange, but have you ever been on call only to find out the next day that everything was down without you knowing? Relying on your mail system can be a single point of failure! Be vigilant and build in checks. If you don’t receive alerts at certain times throughout the day and night you know there is a point of failure in your communication chain, at that point you can be proactive and dial in to find out what is going on.
Create a job to check for job failures in MSDB.
Everyday I used to have a job that read in the job history tables and checked for failures. Why did I do this? Simple, there were other teams and anyone in those teams could have created a new job which may not have had an operator associated with it. If there’s no operator, or you are not in that list then you won’t know there has been a failure.
Create a job checking your backups.
This in part goes back to the testing database mail job. I have been in situations where I didn’t receive failure alerts because jobs hadn’t run they had been disabled or the SQL Server Agent service had been stopped. Make sure you are never in this situation by creating a job that will report to you all backups that did and didn’t happen in the last 24 hours. Bonus points if you check to make sure there are transaction log backups when running in the full recovery model.
Hopefully you have found this post useful, I’d like to thank you for putting aside the time to read it and would love to hear your comments.
In tomorrows continuation of the series I shall be talking about baselining and documentation.
