Automation for Databases–T-SQL Tuesday #110

tsqltuesdayThis is the day for T-SQL Tuesday #110, a topic picked by Garry Bargsley. Automation has been a cornerstone of most of my career. As much as I work hard, and don’t mind putting time and effort into accomplishing tedious work, I also know that automation makes my life much, much easier.

And me a far more productive employee.

Automation is king, so here’s one place I’ve used this in the past.

Complying with Standards

One of the big items auditors want to have is proof that this thing happened or didn’t happen. That an employee actually performed some action, with some proof. At one point, we were being audited and I was asked if we checked that all backups were running for all databases.

Of course, most backups worked, some failed,  and we were aware. More importantly, I could prove it. In our Exchange Public folders, we had a folder for database actions. In here, for each day, was a report from each database server. If there was a question of compliance for any of our sensitive systems, we could give them a link. More importantly, anytime we ran a remediation, we posted a note and the scripts run for the system. That way any backups that failed and were manually run, or other changes, were logged.

This was a combination of a T-SQL querying system DMVs and DBMail to send out reports to our public folder (and DBAs). Today, if I needed something similar, I might move to PoSh, but likely lots of this is still easy to accomplish in T-SQL. However, I’d really rather not build any more monitoring systems. Too many things to manage, and my time is better spent.

Now I’d use SQL Monitor and if anything, build my own reports on top of that infrastructure. That way the data gathering, archiving, etc. isn’t something I need, and I can extract out the text reports for auditors, which they like. Plus, with automation, they know that the system is running as intended.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged . Bookmark the permalink.