The Making Of A Viking Tale

Posted on April 12, 2011 by Mike

This weekend with the help of my family I put together an entry for Idera Software’s SQL Victory Contest for a chance to win a spot on SQL Cruise. While of course winning a spot on the cruise would be fantastic, I really wanted to help highlight the valuable contribution Idera and others make to the SQL community. Not only for SQL Cruise but for events like SQL Saturdays and many others. I’ve gotten so much out of these events I could never say thank you enough times (and most likely will never be asked too again). I figured if I could put together a story, have some fun with my camera, keep my kids busy, and make a tiny contribution to the community by acting like a goofball well then why they heck not. So if you haven’t entered the contest, it’s not too late. I hope to see many more great stories from a great community.

Find a Viking costume

Easy right, just pull it out of the closet.

Me: Honey, have you seen my Viking coat? I know it’s in here somewhere.

Wife: No dear, you know I think that thing smells. Check in the basement.

Me (yelling from downstairs): IT’S NOT DOWN HERE EITHER!!!!!

Wife: It’s not my job to keep track of your costumes.

Me: Now what am I going to do?

Lucky for me there is a great costume rental shop in downtown Lansing called the Lansing Civic Players Costume Shop and they were super helpful putting a costume together. They didn’t even laugh (to hard) when I told them I wanted to show more chest. And when I said I’m looking for something with more fur, they just smiled and said “of course you are.”

Blood, we need more Blood

As luck would have it. While I was at SQL Saturday #67 in Chicago, my wife took the kids to a “gross-ology” class at Impression 5 (the local science center). When I got home on Sunday the kids were running around eating fake poop made out of oatmeal and cocoa with massive bloody scabs made out of vasoline, red food color, tissue paper, and cocoa. I’m just glad we decided to use their new knowledge of making flesh wounds. Here’s my freshly mixed blood made by my 9 year old.

Photo Shoot Time

Sunday morning we pulled out our studio equipment and set up for the shoot. This last year or so we’ve been so busy I haven’t had much of a chance to play photographer. Here’s our basement converted into a studio. It’s way to cramped, but I didn’t feel like carrying everything up stairs to where we have more room.

So head on over and check out all the great entries and remember A FAMILY THAT PILLAGES TOGETHER STAYS TOGETHER!

Posted in Career Development, SQL | 1 Comment

Meme Monday – 11 Word SQL Story

Posted on April 4, 2011 by Mike

Thomas LaRock (Blog|Twitter) challenged bloggers to tell s SQL story in 11 words or less and tag 3 bloggers to do the same. Well, since Noel McKinney (Bog|Twitter) tagged me (thanks Noel) and I have a lot of 3/4 written blogs on the shelf waiting for attention. What a better way to get back rolling than an 11 word blog!

My story

If WITH NOLOCK is your answer, you didn’t understand the question.

Tag Your It!

1. Yanni Robel (Blog|Twitter)

2. Rebecca Mitchell (Blog|Twitter)

3. Matt Velic (Blog|Twitter)

Posted in Uncategorized | 4 Comments

Merry Christmas and a Happy New Career

Posted on December 17, 2010 by Mike

Time to close out the year and start another. While I didn’t set out with a lot of formal goals this year, I did have a few. But it’s really the change of my goals during the year that is the story here. This week I accepted a new job offer. After 12 1/2 years at the same place I’m leaving.

The Past

When I started I was 25 years old and engaged to be married. I didn’t have a house, kids, not even a dog. There were 6 guys who owned a company in Lansing called Melange Computer Services who had carved out a niche in the high stakes bingo industry (which to this day I can’t figure out how to explain to people). I didn’t know a thing about the industry or their software, but they were growing rapidly and needed people to do software installation and support. I was a guy with a finance degree from Michigan State and a job, but not something I wanted to be a career. They took a chance on me and for that I will always be grateful. It didn’t take very long before I was learning from the developers and doing small bug fixes and enhancements. I was presenting training to groups large and small. I continued to learn and grow and get bigger and bigger projects. I was promoted to developer and then senior developer and in the end considered a key member of the company. I measure my success not only in my technical skill and responsibilities, but also the relationships with the people with whom I have worked. Our lives have changed together. There have been weddings, the birth of children, retirements, and sadly death. This week has been a flood of emotion, remembering all the great and even the not so great stories. It’s probably only going to get worse as my final days approach. Believe me there are some tales to tell, but you’ll have to take my word for it because we all know what happens on the road stays on the road.

The Present

So now I’m leaving. That’s one big change. Even bigger, I’m going to be a DBA. I’ve done a lot of contemplating and soul searching and I realized I’ve been following a plan that was outdated. I haven’t pin pointed when the shift occurred, but I really think SQLCruise is where it started become more clear. I was asked then if I ever thought about being a DBA and I answered I didn’t think I would because I liked being a developer so much. But now looking back that was not the right answer. The last five books I have read have been on SQL Server. I’ve been reading some of the DBA interview questions on mssqltips.com and I can answer most of them. Most the people I follow closely on twitter are DBAs. My last post about having DBA skills to set me apart from other developers in the shop, was a half truth. Yes it set me apart, but I think the driving force was that I just enjoy it more.

So when a lifelong friend called me excitedly and said they were posting a DBA job at his company (Lansing Board of Water and Light) and he thought I should apply, I did. I’ve since gone back and read Brent Ozar’s post on getting a DBA job and I think I actually followed his advice without knowing it. I was honest with what I knew how to do, what I have played around with, and the things I will need to learn. After they offered me the job I had to really decide what I wanted and I decided I have to give it a shot. I have been truly humbled by the support of my wife and friends. When I struggled with my decision she reminded me how lucky I am to have the choice and that no matter what she supported my decision.

The Future

Now on to a future that is much different then I envisioned at the start of the year. I’m still putting together my goals for the year to come and I probably won’t really know most of them until I get rolling with my new gig. But there are a few I do know and need to call my self out on.

1) Blog – I think a modest and obtainable goal is 52 blog posts next year. I really need to get after this and I’m counting on my new career focus to provide some good topics. I’ve stumbled out of the blocks a bit getting this blog going. Now it is time to make real progress.

2) Get Involved – I’ve signed up to attend a few SQL Saturdays in Cleveland and Chicago, but word on the street is one is coming to west Michigan. I’d like to help out with that. There are so many great people in the SQL community. I know there is a place in it for for me, I just need to figure out where that place is.

3) Don’t blow up the data center – I think that will be key to being a DBA next year Smile

Merry Christmas.

Posted in Uncategorized | 10 Comments

TSQL Tuesday #12 – I’m not a DBA, But I Got DBA Skills

Posted on November 1, 2010 by Mike

This month brings us the 12th TSQL Tuesday, the concept started by Adam Mechanic (blog|twitter) where every second Tuesday of the month a host blogger puts forth a topic for fellow bloggers to, well, blog about. I’ve been meaning to jump into the TSQL Tuesday fray for the last couple months and just when I think ahead and look for the next topic, the darn thing is moved up a week so it doesn’t conflict with SQL PASS and I got a day to put something together. Oh well, at least it’s a topic I’m passionate about. This month the host blog is Paul Randle (blog|twitter) of sqlskills.com and the topic is DBA Skills. Which is so cool since when it comes to DBA skills, these guys are the top of the food chain (I think that’s why they have skills in their name).

So about these DBA Skills. What are they, why are they important, and what do I do about getting me some. I am not a production DBA, I’m a software developer. Most of the applications I work on are window applications and as of now the data ends up in SQL Server.

To me DBA skills are pretty basic. Knowing how to store the data, knowing how to get at the data in a meaningful and timely fashion, and knowing the data is still going to be there when I need it. Pretty simple isn’t it. Okay, maybe not. Let’s look at knowing how to store data. How is data stored on a page? How much data is stored on a page and if ever asked about it on a cruise will you know the answer? Why should I care how much data is on a page? When do pages get split? What the heck is fill factor? What about variable length data? How is it stored on the page? Blob data? XML? Does this table need a clustered index? What’s a clustered index? Man this is getting complicated. How about getting data, that’s easy right? Then why is this query so darn slow? Why is select * bad? Is that SARGable and why do I care? Should I add an index? Should I add 5 indexes? How do I figure out why this is slow? Crap this seems like a lot to think about. At least I know the data is there. What do you mean the log chain is broken? WTF does that mean? Shrinking is bad? But my drive is full because the log file keeps growing.

I could go on and on, but I think you get the point. DBA Skills are important because somebody needs to know this or eventually you are going to have a pissed off customer. The application can quickly get bogged down and end up like this car in the sand and you don’t want to be the guy pushing! As a developer, having DBA skills also works for me professionally. It helps set me apart from other developers. If a customer, tech, or fellow developer needs help on a SQL server related problem they know who they can go to for help.

So learning about all these things can be a lot of work. I do it because I enjoy it and because it’s such a huge part of what I do to build an good application. I started working with SQL Server 2000 and I think the first book I read was Inside SQL Server By Kalen Delaney (blog). I’m not sure any other SQL book will ever top that one for me, but the 2008 SQL Server internals book is right there too (see the current TSQL Tuesday tie in there). So go read the TSQL Tuesday Blogs and get yourself some DBA Skills, you’ll need them.

Posted in Career Development, SQL, Uncategorized | Leave a comment

Random Replace Sensitive Data With SQL CLR

Posted on October 11, 2010 by Mike

Much like my last post, this one comes from a little side project I played with to help make old production data suitable for use to show third parties. In this case I needed to replace the names of real people. So I decided to write a function that would take in a string and replace it with a string of random letters. My first thought was to use a good old SQL Server user defined function, but I quickly ran into problems with that since my favorite way to generate a random number in SQL is to use rand(checksum(newid())) which is not valid in a user defined function since newid() is non-deterministic. So I turned to SQL CLR for my function. Here’s how it went.

In Visual Studio I created a new Visual C# SQL CLR Database Project. If your using 2010 you’ll notice the note to write your SQL CLR code targeted for .NET 3.5. So select that up at the top.

Then I right clicked on my project and added a new user defined function.

Next I create a basic little function to randomly select some letters. To keep the random strings a little more like real words I made the function replace vowels with only vowels and consonants with only consonants. I also decided to eliminate some consonants like “X” and a few others just to make them look a little more “normal”. I decided to pass the seed value to the function. That way if I want to use the same seed value for all the records I could do so. By using the same seed value, the same sequence of letters get replaced. So “Adams” may become “ITIDT”, but it would always end up the same. This would make it not very secure, but it would keep the date distribution the same if the column was indexed. That might be helpful for performance testing where the data is still in a secure environment. In my test however, I used my trusty checksum(newid()) for the seed making every row random.

   1: using System;

   2: using System.Data;

   3: using System.Data.SqlClient;

   4: using System.Data.SqlTypes;

   5: using Microsoft.SqlServer.Server;

   6: using System.Collections.Generic;

   7: using System.Text;

   8: using System.Text.RegularExpressions;

9:

  10: public partial class UserDefinedFunctions

  11: {

  12:     [Microsoft.SqlServer.Server.SqlFunction]

  13:     public static SqlString RandomizeString(SqlString stringIn, int seed)

  14:     {

  15:         string workingString = stringIn.ToString();

16:

  17:         if (string.IsNullOrEmpty(workingString))

  18:             return string.Empty;

19:

  20:         string vowels = "AEIOU";

  21:         string consts = "BCDFGHJKLMNPRSTW";

  22:         Regex VowelExp = new Regex(@"[AEIOUaeiou]");

23:

  24:         int numberOfCharacters = workingString.Length;

25:

  26:         Random rnd = new Random(seed);

  27:         StringBuilder stringBuilder = new StringBuilder();

28:

  29:         string nextChar;

  30:         int randomNumber;

  31:         int numberCount = 0;

  32:         do

  33:         {

  34:             nextChar = workingString.Substring(numberCount, 1);

35:

  36:             if (VowelExp.Match(nextChar).Success == true)

  37:             {

  38:                 randomNumber = rnd.Next(0, vowels.Length - 1);

  39:                 nextChar = vowels.Substring(randomNumber, 1);

  40:             }

  41:             else

  42:             {

  43:                 randomNumber = rnd.Next(0, consts.Length - 1);

  44:                 nextChar = consts.Substring(randomNumber, 1);

  45:             }

  46:             stringBuilder.Append(nextChar);

  47:             numberCount += 1;

48:

  49:         } while (numberCount < numberOfCharacters);

50:

  51:         return stringBuilder.ToString();

  52:     }

  53: };

After building the dll, it’s back to SQL Server. I used the Adventureworks database and the Person.Contact table with it’s 20,000 names for testing. First the three steps to get everything wired up. Enable clr, add the reference to the dll, and create the function in SQL Server.

   1: sp_configure 'clr enabled', 1

   2: go

   3: RECONFIGURE

   4: go

5:

6:

   7: CREATE ASSEMBLY RandomizeString

   8:    FROM 'D:\Projects\RandomizeString\RandomizeString\bin\Release\RandomizeString.dll'

   9:    WITH PERMISSION_SET = SAFE

  10: GO

11:

12:

  13: CREATE FUNCTION [dbo].[RandomizeString]

  14: (@stringIn NVARCHAR (4000), @seed int)

  15: RETURNS NVARCHAR (4000)

  16: AS

  17: EXTERNAL NAME [RandomizeString].[UserDefinedFunctions].[RandomizeString]

  18: GO

Then a quick little test to exercise the function a couple of times to see how it works:

select top 50 lastname from Person.Contact order by contactID

begin tran

    update person.Contact set LastName = dbo.RandomizeString(LastName, CHECKSUM(newid()))


    select top 50 lastname from Person.Contact order by contactID
rollback tran
GO

begin tran

    update person.Contact set LastName = dbo.RandomizeString(LastName, CHECKSUM(newid()))


    select top 50 lastname from Person.Contact order by contactID
rollback tran
GO

Well there you go. Your not going to find too many of these names in baby books anytime soon!

Posted in SQL, SQL CLR | Leave a comment

Update all datetime columns in a database with new dates

Posted on September 9, 2010 by Mike

I had an interesting request today from a field tech today. At some point a few years ago somebody put together a database of good data for showing customers reports for one of our applications. If I had to guess it was probably me, but who remembers two years ago? Anyway it’s amazing how hard it is to get a good data set for reports where all the numbers actually tie together. So the techs and sales people pretty much just know what a good date range is and run the reports for dates two years ago. So the request was to update the database so the dates would be current. Then the tech could update the database prior to visiting a site and not have to remember old dates. Also the customer wouldn’t get the impression that our database was old. So I put together a quick script to update all the date fields in the database by x number of days. In my case I only wanted to update columns with “date” in the name. This particular database also has some datetime columns that don’t have “date” in the name and I didn’t want them updated. A word of caution this script was intended for a demo database on SQL Server 2005 running on a laptop, so I only spent about 10 minutes putting it together and testing.


DECLARE @Days INT
SET @Days = 365

DECLARE @ColumnList TABLE
       (
      RecordId INT IDENTITY (1,1),
      TableName NVARCHAR (100),
      columnName NVARCHAR (100)
       )

INSERT INTO @ColumnList
       SELECT OBJECT_NAME(C.object_id),
            C.name
       FROM sys.columns C
             JOIN sys.types T ON ( T.user_type_id = C.user_type_id AND T.name = 'datetime' )
       WHERE C.name LIKE '%date%'

DECLARE @Counter INT , @TotalCount INT
SET @Counter = 1
SELECT @TotalCount = COUNT(*) FROM @ColumnList C

DECLARE @TableName NVARCHAR (100), @ColumnName NVARCHAR (100), @Sql NVARCHAR (MAX)
WHILE @counter <= @TotalCount
BEGIN

       SELECT @TableName = c.TableName ,
            @ColumnName = c.ColumnName
       FROM @ColumnList c
       WHERE c.RecordID = @Counter

       SET @SQL = N'Update ' + @TableName +
                         ' SET ' + @ColumnName + ' = ' +
                         'Dateadd(dd,' + CONVERT(NVARCHAR(10), @Days ) + ',' + @ColumnName + ')'

       EXEC sp_executesql @Sql      

       SET @Counter = @Counter + 1
END

So there you go. A quick way to bump all the dates. The best part was the happy response I got from the tech. It put a big smile on my face. His email:

DECLARE @MikeIs nvarchar
SET @MikeIs = N'awesome'

Posted in Script, SQL | Leave a comment

Why I Blew My Training Budget On SQL Cruise

Posted on August 25, 2010 by Mike

About two months ago I saw a tweet about 4 contest winners for something called SQL Cruise being put on by these two guys Brent Ozar (Twitter | Web) and Tim Ford (Twitter | Web). I followed the link to the web site and then promptly forwarded the link to my boss and fellow developers. We shot jokes back and forth like “Can you imagine the bar scene with no constraints?” and really didn’t take it seriously. When I tell people now that I went on a cruise to learn about SQL Server, they have the same reaction I did. Most think it was just for the boat drinks. One of the first things Brent asked me when I met him was why I decided to attend. A pretty fair question from someone putting on an event for the first time (especially one that is non-traditional) and you would think I’d have a really good answer. I did have a good answer but it just didn’t formulate in my head fast enough. Thankfully I did my SQL Cruise homework and part of that was starting a technical blog (see Brent’s post here). So thank you Brent and Tim because my first blog post is the answer that very question.

In his book The Passionate Programmer: Creating a Remarkable Career in Software Development, Chad Fowler says to “treat your career like a business” and that is my guiding theme when I choose what to do for training. This book is fantastic for anyone in a technical profession, not just developers. I pay for my own training, so I’ve set up a training budget to which I allocate a set amount each month. I attend conferences that maximize my ROI. Devlink in Nashville, TN is a great 3 day conference with sessions on both .NET and SQL Server and it ends on a Saturday so you can miss less work. Codemash in Sandusky, OH is a 2 day conference which focuses on bring together developers using a different languages and development practices. There are numerous other free one day conferences like SQLSaturday and Day of .NET out there too. Why do I go to conferences even though nobody is helping me pay? It makes me a more valuable employee. It increase my stock price so to speak. It creates wealth for the shareholders and the shareholders of my career are the most important people to me, my wife and kids.

Now, lets get back to SQL Cruise. A month later and just a month away from the sail date chatter about it was picking up on Twitter. The price of the cruise had dropped a bit. Out of curiosity I landed back on the web site. This time I found a post on mssqltips.com by Tim. Tim’s post talked about why a cruise was economically a good way to go so I ran some quick numbers to compare it to other conferences I might attend.

Training Option	SQL Cruise	PASS	Devlink
Conf. Fee	$295	$1595	$100
Lodging	$920	$480	$480
Dining	$0	$250	$250
Transportation	$0	$0	$100
Total	$1215	$2325	$930

This is my pre-cruise projection, not actual cost. I just wanted to come up with ballpark figures. I ended up spending more on boat drinks and snorkeling on the cruise, but hey it’s a cruise and it was so worth it. I’m sure I’d spend about the same on entertainment if I went to Seattle or Nashville for a conference because there’s lots to do there too. My transportation expense in real dollars was zero for SQL Cruise and PASS since I planned to use frequent flier miles, but I would probably drive to Devlink so I put in a hundred bucks. I ended up spending a bit on transfers to and from the port. I assumed $120 per room night in a hotel and $50 a day for food. The price of the cruise was about $400 a person, but that assumes you have 2 people in the cabin. If you try and book a single person in the cabin then it’s $800 so either way I was going to be out the $800 but at least I could pitch it to my wife that if she went, her cruise was free. I also had to book 1 night hotel to arrive the day before the cruise.

Then I looked at the return side of the equation. What would I get out of SQL Cruise that I wouldn’t get from another venue? First, there is Tim and Brent. I had never met either of them before, but thanks to Twitter and their blogs I really respect what they have to say (and not just about bacon). It also speaks to why Brent’s advice on starting a blog hit home. Four days to pick their brains in such a small group setting was something I didn’t want to pass up. Second, THE CRUISERS. The part of Tim’s post that really stuck to me was “Both Brent and I are believers that you learn just as much, if not more, from those who you spend time with during conferences as you do in the conferences themselves.” Where else would I find 14 people with varying backgrounds that are passionate enough about SQL Server that they want to spend all day in class while sailing across the ocean? I mean three of these people put together some pretty awesome videos and one wrote a poem to get on this thing. Last, there was this quote from the SQL Cruise web site “You don’t need to be a senior DBA – these courses are designed to help your transition from junior DBA into a senior DBA, or from a developer into “the SQL guru” in your office.” Well that’s me, “the SQL guru” in the office. So I little SQL Cruise marketing paid off and going on the initial voyage of SQL Cruise could only enhance my “guru” status in the office.

Decision time. There was only 1 spot left on the cruise. The investment was reasonable, the return off the chart. Just looking at ROI, it’s a no brainer. But looking at my budget told a different story. I’d already been to Codemash and SQL Saturday #42 in Columbus this year and I’d made small family vacations out of both and while I probably shouldn’t count all that toward my budget, that fact is we spent the money. Going on SQL Cruise would break that budget wide open. There is a saying in business, “timing is everything.” You may have a better product or a better service and still loose out because somebody else got to market first. Decision made, I’m going.

Was it worth it? Hell yes. Tim and Brent delivered an exceptional experience. We really did start class at 7 AM on two of the days and not one person missed class. The Cruiser were incredible. I was so impressed with what they brought to the party (I mean table). DBA’s, developers, and consultants listening to bad karaoke in perfect harmony. Who could ask for more? Weeks later I’m still processing everything. The week I got back one of my fellow cruisers pointed me in the right direction on twitter to solve a problem, yesterday I helped a fellow developer in the office tune a query using some of the techniques from class, and now I just finished my first technical blog post. Three weeks later and I can already point to specific instances where SQL Cruise has helped my career.

Well that’s a wrap. My first post is in the books. Thanks for reading, leave a comment and let me know how I did.

Posted in Career Development, SQL, Training | 7 Comments

/* SQL Comments */