Author Topic: DBA Automated Tasks ~ What Are Some Top Fav's  (Read 2608 times)

Offline Chris Yates

  • Sr. Member
  • ****
  • Posts: 362
  • Leadership Through Service
    • View Profile
    • The SQL Corner
DBA Automated Tasks ~ What Are Some Top Fav's
« on: February 21, 2013, 03:10:03 PM »
Every DBA has their own style, swag, and overall way they go about accomplishing things. I'm a big believer of automating what I can so it is readily available for me to review at certain times. Some of the things I like to track are:

Last Successful Backup Date
Data and Log File Location
Backup and Data File Location
Database Sizing
Job failures over the last 2 days
Replication latency reports

Some of the ones I track are via scripting, PBM, and SSIS which gets dumped to a central location or emailed to me. Be as efficient as you can be and work smarter not harder by automating some of the routine tasks. Also allows for being pro-active.

Curious what my fellow DBA partners in crime like to track and the tools utilized to track them.


Chris Yates
The SQL Corner
Blog: You are not allowed to view links. Register or Login
Twitter: @YatesSQL

Offline John Sansom (@SqlBrit)

  • Administrator
  • Hero Member
  • *****
  • Posts: 506
    • View Profile
    • John Sansom - SQL Server DBA in the UK
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #1 on: February 22, 2013, 11:03:48 AM »
We track a lot of performance related data in the current shop.

Each instance has an in house developed monitoring/tool suite database deployed to it that automates the process based on a standard default monitoring configuration. The configuration is then tweaked as necessary, for example on busier environments I might what to monitor for blocking on 1 minute intervals rather than 5 minutes.

Some of the things we automatically track/monitor include:
  • Excessive Blocking
  • Wait Types
  • Last performed backups, log and full/diff
  • Stored Procedure performance metrics etc.
This system has proved very valuable. Whenever an alarm is received or triggered, the problem may no longer be present when you go to inspect the instance but through a combination of our custom monitoring and the Management Data Warehouse(MDW) we have a complete history of performance for the environment to call upon and use for problem diagnosis.

Offline Chris Yates

  • Sr. Member
  • ****
  • Posts: 362
  • Leadership Through Service
    • View Profile
    • The SQL Corner
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #2 on: February 22, 2013, 03:45:16 PM »
You are not allowed to view links. Register or Login
Stored Procedure performance metrics etc.

I like the idea of the stored procedure performance. I know you are a big proponent of automation; I'm a firm believer in it; makes life easier.
Chris Yates
The SQL Corner
Blog: You are not allowed to view links. Register or Login
Twitter: @YatesSQL

Offline s_osborne2

  • Newbie
  • *
  • Posts: 20
    • View Profile
    • My Blog
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #3 on: February 23, 2013, 11:00:50 AM »
My favourite things to automate are pretty standard really...

Weekly DBCC check DB's
Backup and Restores for prod DB's to other environments

The most exciting thing i find myself tinkering with at the minute is an automated documentation generator. I want to use Powershell with SMO to document server information on a MediaWiki wiki. Recently i've read about documenting database related things. Nobody likes doing it, everyone thinks it sucks. I recently watched Jes Schultz Borlands youtube video on Documentation It Doesn't suck and it really got me thinking. The next thing I know @DBArgenis releases a blog via SQLBlog saying a lot of the same sort of things. His idea of automatically updating a wiki using powershell really niggled away in my brain.

So far I've got my powershell script that exports all the table names and any extended properties with MS_Description as the name and pops them out to CSV. I've got a media wiki installed and up and running and i'm at the stage of wanting to publish to the wiki. I've found a .Net library called DotNetWikiBot on sourceforge that should allow me to do it. It's just a case of linking it all together. Definitely going to post the solution on my blog once it's done. May be some weeks/months off yet tho!


Simon

Offline John Sansom (@SqlBrit)

  • Administrator
  • Hero Member
  • *****
  • Posts: 506
    • View Profile
    • John Sansom - SQL Server DBA in the UK
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #4 on: February 23, 2013, 12:03:52 PM »
Sounds very interesting indeed chap! Make sure to update this thread with the details of your post when it's up.

In the meantime, if you need any community folks to give you hand with a QA let us know.

We have a bespoke Inventory system for our database environments that was built out by our Lab DBA Team. When your working with a lot of instances, these types of inventory solutions are a must have, you'll go completely bonkers without them.

Offline SQLFunkateer

  • Jr. Member
  • **
  • Posts: 89
    • View Profile
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #5 on: February 25, 2013, 06:46:59 PM »
There comes a point in automation where the DBAs job is less to do tasks, as to handle the occasional breakdowns of the automated processes that do the tasks.  I have joined a team that is fairly developed down this path, which is nice.  It has certainly helped hone my SSIS troubleshooting, that's for certain.  But we have countless jobs and SSIS packages that take care of things.  Rough count, over 80 SQL Server instances on probably around 50 servers, with 1300 or so databases...so automation does help!

It can cut both ways.  You have to be VERY careful about changing anything because a dozen ancient automated processes may break.  As a deterrent to foolhardy folks poking around in prod, this can be seen as a feature, rather than bug, though.

Many of the processes we could homebrew automation get taken care of by 3rd party products.  Spotlight by Quest does a really good job for us, once you tweak it enough to avoid all the false positives and noise.


Offline RichardLee

  • Newbie
  • *
  • Posts: 10
    • View Profile
    • WordPress
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #6 on: March 08, 2013, 05:08:14 PM »
We use DPM as our backup routine, which is very effective and essentially shares the responsibility of backup/restore between the whole  Ops team. With regards to monitoring, we use database email to send us a daily report of file size, failed jobs, low partition counts, last full backup, last log backup etc. I like the fact that then my email then becomes like a reporting tool. Be even more handy if I could save this data to another database, then create reports from it.... like I'd ever find the time to do that.
You are not allowed to view links. Register or Login

Disclosures of a defacto DevOps

Offline Chris Yates

  • Sr. Member
  • ****
  • Posts: 362
  • Leadership Through Service
    • View Profile
    • The SQL Corner
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #7 on: March 08, 2013, 06:20:58 PM »
You are not allowed to view links. Register or Login
.......like I'd ever find the time to do that.

Keep plugging away and you will get there one day. One thing I've learned over my tenure is that progression happens over time. Appreciate you sharing.
Chris Yates
The SQL Corner
Blog: You are not allowed to view links. Register or Login
Twitter: @YatesSQL

Offline John Sansom (@SqlBrit)

  • Administrator
  • Hero Member
  • *****
  • Posts: 506
    • View Profile
    • John Sansom - SQL Server DBA in the UK
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #8 on: March 09, 2013, 11:16:24 AM »
A monitor that we've recently deployed to some of our more critical environments is to inspect the plan cache for queries that are performing Index Scans and Implicit conversions.

In an ideal world these would be being picked up/caught in the development lifecycle but well, we Data Professionals don't often work in an ideal world now do we  :D
« Last Edit: March 09, 2013, 11:22:21 AM by John Sansom (@SqlBrit) »

Offline Chris Yates

  • Sr. Member
  • ****
  • Posts: 362
  • Leadership Through Service
    • View Profile
    • The SQL Corner
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #9 on: March 11, 2013, 01:19:22 PM »
You are not allowed to view links. Register or Login
A monitor that we've recently deployed to some of our more critical environments is to inspect the plan cache for queries that are performing Index Scans and Implicit conversions.

In an ideal world these would be being picked up/caught in the development lifecycle but well, we Data Professionals don't often work in an ideal world now do we  :D

Spot on with the plan cache man!!! I like the index scan check idea.
Chris Yates
The SQL Corner
Blog: You are not allowed to view links. Register or Login
Twitter: @YatesSQL

Offline Chris Yates

  • Sr. Member
  • ****
  • Posts: 362
  • Leadership Through Service
    • View Profile
    • The SQL Corner
Re: DBA Automated Tasks ~ What Are Some Top Fav's
« Reply #10 on: May 09, 2013, 12:51:00 PM »
So, this has been in the works for a while but getting to the point of utilizing the CMS to continuously update database documentation, keeping everything up to date documentation wise down to the business owner of the database. Is tying in nicely with the server documentation. I like the granularity. Is starting to help define out separate SLA's for different business units for maintenance and such.
Chris Yates
The SQL Corner
Blog: You are not allowed to view links. Register or Login
Twitter: @YatesSQL