T-SQL Tuesday #15 DBA Automaton

Comments: 3 Comments
Published on: February 8, 2011

I have been incognito for the last couple of weeks and nearly missed TSQL Tuesday this month.  If it weren’t for somebody pinging me requesting the link to the list of upcoming hosts, I would have missed it entirely.  The topic merges well with the work I have been doing for the past few weeks.  In fact, there are so many things that DBA’s do on a regular basis to automate things – there should be plenty of items to cherry pick for a topic.  Unfortunately, that doesn’t make this topic any easier for me.  My decision process for this is partially based on thinking through what might be unique without having read what others may have written.  (While thinking about reading blogs, it would be nice if there was an automated method to ingest all of the blogs into my head without having to iterate through them one at a time – manually.)

This month the party is hosted by Pat Wright (blogtwitter).  Pat has asked us to describe some of the things we automate – or some of our automation that we have implemented.  Often, we hear about DBA’s automating everything under the sun.  Why?  It simplifies the job and creates time to work on other projects.  With all of the automation, I wonder if DBA’s are related to Hephaestus in any fashion.  If we had our way, it seems that our databases would be…Automatons.

A Lesser Automation

Now that I have rambled for a good bit, I guess it is time to get to the meat of the topic.  First, we need to understand automation.  So, what is automation?

  1. The automatic operation or control of equipment, a process, or a system.
  2. The techniques and equipment used to achieve automatic operation or control.

A closely related word to automation is:

  1. Computerization – the control of processes by computer

In other words, for a DBA, automation is the implementation of a process or control for the computer to operate without the DBA doing the work.  This is typically something that is repetitive or menial or tedious or frequently done.  But that is not always the case.  This can also be something that is fun and/or only done once or twice a year.

I have one of those cases where I may use the automation even less often than once a year, or maybe it could come about more frequently.  It all depends on the needs of designing and testing new databases.

This process is to help in properly sizing the database before the database is finally released to production.  With good project requirements, you may have a good idea of what the fields and sizes of those fields should be.  In some cases, you will be getting data from an external source in some fashion or another.  This data does not always come with storage requirements or data size parameters.  You can make guesses at it by looking at the data – but sometimes, something more is required.

It is when more is required that this script comes in a bit handy for me.  The script is ugly, but it does the deed.  The base idea is to retrieve the data length for a sample of data from each of the tables (after import into a staging database).  This is done so I can run statistical analysis on the data later.  And cringe now because I use a nested cursor to get at what I want.

So Here is the script that will load a sample of data from every table in every database for every column and give you the length of the pertinent columns (I have excluded obvious columns such as numeric types and certain LOB types).

And yeah, I rushed through this to get it finished up quickly.  I hope to have more time to delve into it later.  At least with this script, I can load a table with adequate data to be able to generate histograms on the data length/size distribution and then make appropriate sizing decisions based on the statistics.  Be warned – though automated it is slow and should not be run on a production server.

Immersions Training

Comments: 4 Comments
Published on: February 8, 2011

The last week of January 2011, I wrote a blog post entering a contest for free training at the hands of SQLSkills.  Later that week an announcement was made as to the winner(s) of that contest.  Lo and behold I found out that I was one of the winners.  Totally awesome.

What did I win?  I won a week of training at half price.  The training is in Dallas the week of February 21st.  Brent Ozar even came up with some info to show the ROI on this training.  (Yeah, I’ll be bookmarking that page.)

Even with all of the upside, I will be unable to attend this time.  I have other things at this juncture that take precedence for me.  I evaluated the pros and cons and had people encouraging me to attend the training regardless of the cons.  Some of the conflicts at this point in time are:

  1. Moving my family and trying to get settled in
  2. Transitioning new work

Those are just too big right now to try to interrupt for a week.  Again, many thanks to Paul, Kim, Brent, and crew for the great opportunity.

Anybody who has the opportunity to attend one of these events really should do it.  My wife and I agree that it is something that is worth me doing.  I will be diving in the immersions training some time in the future.

page 1 of 1

February 2011
« Jan   Mar »

Welcome , today is Friday, May 29, 2020