Feb 2011 S3OLV Meeting

Tags: ,
Comments: No Comments
Published on: February 9, 2011

I am getting this out extremely late.  I seriously have good excuses for that.  Due to my location and Charley feeling ill, we will be doing the S3OLV UG meeting entirely virtual this month.

Here is the note on the invite:

Please join us this month for a Virtual UG meeting.  We will not be meeting at The Learning Center.

Livemeeting Details:

Follow these steps:

  1. Copy this address and paste it into your web browser: https://www.livemeeting.com/cc/UserGroups/join
  2. Copy and paste the required information: Meeting ID: 89TDFP

Randy Knight of SQLSolutions Group will be presenting.  He has some insight to help both the Oracle and SQL DBA.

Randy Knight (MCITP) is an IT Professional who has been working with Microsoft technology for over 20 years, focusing on SQL Server for the last 14. He has worked in a variety of settings, including six years as a Database Architect for Match.com, one of the largest and most successful dot coms.

We welcome anybody who is able, to attend this virtual meeting.

The meeting will be held February 10, 2011 at 6:30 PST.

I mentioned that Randy will be teaching us some useful info for both SQL and Oracle DBAs.  Here is the abstract for the presentation he will be giving.

In today’s enterprise environments, it is becoming increasingly necessary to integrate data from a variety of sources. As SQL Server continues its march into the enterprise, the days of focusing 100% on one platform are over. At a bare minimum, we need to be able to communicate with DBA’s for other platforms. Terminology as simple as Instance and Database mean very different things in Oracle than they do in SQL Server. In this session, we will compare and contrast the two platforms in terms of features and terminology. We will also discuss some of the best practices and pitfalls when integrating the two.

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.

The Lost Hero – A review

Comments: No Comments
Published on: January 25, 2011

With what looks to be five books planned in a new series by Rick Riordan, people have some good fantasy ahead of them.

I just finished the first installment in this new series and must say that I am pleased with the story line.  New characters, new aspects of mythology and a third series revolving around those principles.  Rick Riordan has got a really hot topic and something working for him with all three of his series.

In this series we are introduced to the Roman versions of the mythological gods.  With the Roman aspects of these gods, we also get a new class of “evil god.”  Book one takes off from the final prophecy in the Percy Jackson series and brings three new demigods as the central focus.  I like the powers of these new demigods.  I also like the personalities of each.  Rick has done a great job of illustrating their characteristics without making them sound too much like heroes from prior books.

I did not like the grammatical problems constantly repeated throughout this book.  The same thing happened in the Percy Jackson series and got better with each book.  This is not an issue with the dialog but more to do with editing.  An example would be “Jason sat to next to the tree.”  Stuff like that is easily skipped by many people but sticks out like a sore thumb to me.

Overall, I highly recommend the book if you enjoy mythology and fantasy.

SQLCruise Contest

Comments: 1 Comment
Published on: January 5, 2011

For those who may not have heard, there is training for SQL Professionals called SQL Cruise.  You can find more info here.

There is a bit of interesting news for an upcoming cruise.  There is a contest asking you to elaborate your mad SQL Skills and how you saved the day.  The contest is sponsored by Idera.  Here’s the problem as I see it with that contest though.  In order to participate you have to enter through Idera’s facebook page.  I’m not a fan of that site.  I don’t have an account and I won’t be signing up – not even for SQL Cruise.  For any of you who are interested – enjoy and good luck.  Believe me, I was stoked about the contest until I read that blurb about having to submit via Facebook.

SSIS Job Execution

Tags: , ,
Comments: No Comments
Published on: December 13, 2010

While working on a process to refresh the QA environment (data purge, reload data and reapply changes made over time as parts of release cycles), I ran into self-imposed requirement.  I was attempting to create a process that would perform the entire process consistently.  Amongst the items that I needed to include in this process to “refresh” the QA environment was the need to run a couple of SSIS packages.  SSIS was not installed on this particular server where I was creating this process.  I needed to have a way of executing two SSIS packages without installing SSIS on the local server.

With that need, I decided to test a remote execution of a job that had SSIS steps.  The method I decided to use was to employ OSQL.  Here is what I did for that.

[codesyntax lang=”tsql”]


I added that line of code to a SQL Agent Job step.  The job step type is Operating System (CMDExec).

This method works well for me since I had already tested the package on the remote server and knew that it worked.  There are several other methods as well, this happened to be implemented more quickly for me at this time.

SQL Confessions

Comments: 1 Comment
Published on: December 13, 2010

I had a brainstorm of an idea for a group of articles on my blog a couple of weeks ago and am finally getting around to putting it together.  The idea came about while I was struggling with a problem at work and trying to meet the requirements in the code.  As the title states, this group is going to be about things done in SQL server that I would normally avoid.

The first article came about while trying to find a solution to some business requirements.  The requirements had an average to be calculated and then a revised average if certain criteria were met.  Then it was required to move things from one group to another group depending on a series of criteria.  When I first looked at these requirements, I thought I would need several cursors.  As it turns out, I had to implement just one cursor.  I could find no way of getting away from using a cursor for this one requirement.  The essence of it was that I had two variables.  One variable was the number of items to move and the second variable was the person to whom the items needed to be moved.  Not only did it require those two variables, but there was the additional requirement that it be randomized.

First, let’s take a look at how I did the randomizing.  I took care of this requirement by dumping all of my prep data into a temp table that had a default on a column explicitly to hold  a random value.  Here is the code for that table.

[codesyntax lang=”tsql”]


Using this table, I can just run a set-based insert to populate the table and thus end up with random values in the “Randomizer” column due to the default constraint.

[codesyntax lang=”tsql”]


That piece of the puzzle was fixed rather easily.  Now for the next part – the confession part.  I had to write a cursor.  Next part of that confession – I had to look up how to write the cursor.  Without going into a lot of details, this is the code of how I used a cursor to solve this particular problem.

[codesyntax lang=”tsql”]


Note that this cursor is a little hybrid.  I am using this cursor to perform some set based actions.  I am fine with that.  I am happy with the random distribution and the fact that this performs well.  This was done for a one off requirement that will not be used again (or at least not planned to be used again) and it met all of the requirements.  I was blocked on this particular requirement by the required variables and couldn’t get past how to do this in a 100% set-based fashion.  If you have any input, let me know.

Do you have any such stories to share?

Defensive Db Programming Chapter 07

We are here again after another …um long lapse in time … and ready for another episode in this series.  Today we get to talk about chapter 7 in the book by Alex Kuznetsov (Blog).  You can find more on this series by looking here.

Advanced Use of Constraints

This chapter ties in well with chapters 4 and 6.  You may want to review those chapters as you read this chapter.  There were problems raised in those prior chapters and now they are being discussed at length in this chapter.  First order of business is to revisit the ticket tracking system that was discussed in chapter 4.

Ticket Tracking

The system was left with business rules implemented via triggers in chapter 4.  We are now going to explore using constraints in lieu of the triggers to enforce the business rules.  For sake of testing, the environment should be reset and a new environment built.  Alex provides all of the scripts to do so.  As Alex progresses through this example he lays out the assumptions and some of the problems with those assumptions.  Through a combination of Indexes and constraints.  Another item that was implemented was the use of ON UPDATE CASCADE to help ensure the business requirements could be met.  Alex talks about the implications on performance and the reasons for implementing the cascade update.  Make sure you read this chapter to find out more.

Inventory System

There is a substantial amount of discussion about a new example in this chapter.  That discussion revolves around the Inventory System and several use cases.  I recommend reading the chapter.  I am not going to discuss it beyond that here in this post.  There are a great collection of scripts to run through the example.


In this chapter Alex shows us how a combination of constraints can enforce complex business rules.  The use of constraints should be included as a viable option when trying to enforce business rules.

Once again, go get this book and read it.  It is well worth the read.

DB Maintenance

Comments: No Comments
Published on: November 2, 2010

About a month ago I read a post by Brad McGehee (Blog | Twitter) concerning a checklist for SQL Server.  It seems to be a rather comprehensive list.  If you don’t have some sort of maintenance checklist yet, I would recommend that you shoot on over and check out his list.  In addition to checking it out, I recommend that you make suggestions of other items to check when doing maintenance.  In addition to getting a maintenance routine setup, I would also suggest that a way to automate as much as possible be explored.

You can find the list here.

Voting Day is Here

Comments: 1 Comment
Published on: November 2, 2010

Tis the season for voting and SQLServerPedia has thrown out an election as well.  The SQLServerPedia awards are under way and voting will last until Nov. 9th.

Surprisingly, I have a couple of nominations.  I would ask for your vote and am delighted by every single vote I get.

I hope you vote for the best in each category.  Let’s get out and make our voices heard for something that really matters.  There is a lot of good stuff that has been nominated.

You can cast your vote here.

«page 2 of 6»

February 2020
« Jan    

Welcome , today is Wednesday, February 19, 2020