Tim Mitchell

Delayed gratification

2008-07-21T03:37:00Z

Call me odd, but I enjoy taking certification tests. It may be that I've always tested well, or perhaps it's that I enjoy a challenge, but for whatever reason I have always looked forward to sitting for exams. So when the opportunity arose to take a beta exam for the SQL Server 2008 business intelligence components, I signed up as soon as I could.

I admit that I took the exam cold, only having worked with SQL Server 2008 beta a dozen times or so. I've been studying for the SQL Server 2005 BI exams, and my thought was that a free pass at the 2008 beta test would be a good study tool for me. I spent almost 2 hours in front of the terminal, using what information I knew from experience (mostly on SQL Server 2005) and making educated guesses for the rest. All went well until I reached the end of the exam - you know, the point at which your pulse races just a bit as you wait for the "You Passed" or "You Failed" message. Imagine my surprise when neither of these was shown, but rather I received a "Thanks For Playing, We'll Get Back To You" message. Specifically, the message indicated that the results of the beta exam would be delivered to me via mail in 90 days or so. Yes, that's mail as in paper and envelopes and stamps, right here in the midst of the digital age [Suggestion to Microsoft: Save some cash - post the results on my MCP record and send me an e-mail when it's ready].

I've never taken a Microsoft beta exam before, so I don't know if this is status quo for these types of tests. Even though certifications have gotten a bad rap lately, I still believe that, when properly administered, certification programs can still offer some measure of a person's willingness and ability to succeed on a particular software component or career track. And as one who intends to continue to pursue certifications pertinent to my skillset, I have a vested interest in helping to make the SQL Server certification track as relevant as possible, so I am glad that I participated in this trial. However, I have to admit - and yes, it's probably a little selfish - that I'm not sure I would have been so eager to participate if I'd known that I would have to wait months for any kind of hint as to how I performed on the exam.

So here are my questions. First of all, is the delayed response common to all Microsoft exams, and second, is this a deterrent to other potential beta exam test-takers?

Slacking and data quality (in that order)

2008-07-01T03:10:00Z

Well, I almost missed blogging for the entire month of June. I'm sure that this fact didn't go unnoticed by both of the people who read my blog... I'm working on a major data conversion and am in a mad dash to finish converting and validating years of healthcare and financial data, and unfortunately my free time (including the time allocated for blogging) has been scarce. The good news is that the project - at least the data conversion piece - will be over in late September and perhaps life will return to some semblance of normalcy.

The aforementioned project has been an interesting exercise in data quality. The system from which I am extracting data is quite old, in technology years anyway, and the application design lacks some of the keystones of modern systems - not the least of which is relational integrity. The de facto standard for data entry was free text, which made for many (in some cases, tens of thousands) of duplicates. Fortunately, the system to which I am converting has a well designed SQL Server backend, and in spite of a few disagreements, the vendor has been open to modifying the system to suite or needs. As to the quality of our data, I've had lots of opportunities to expand my SSIS skills to gently (most of the time) massage the data into the target system. I've even been able to write some code, which I don't do that much any more, for some advanced text parsing and manipulation.

Once this project is complete, I'll write a more comprehensive - and coherent - post to discuss in more detail my travels through this conversion and some of the data quality lessons I've learned.

Using IN with possible NULL values

2008-05-30T04:01:00Z

There is a little quirk with NULLs when using the NOT IN qualifier. I use the term "quirk" loosely here because the behavior is exactly as intended, though it may not be obvious. The following query shows a trivial example:

At first glance, one might think that we'll see returned the row for the Chevy MINIVAN. However, when you run the above query (with the default option of ANSI nulls set to ON), you'll always receive zero rows returned. Why? Because the NOT IN is still a set-based comparison, and with ANSI_NULLS ON, comparing any value to NULL yields False (technically it yields NULL, but you see what I mean).

A quick solution can be found, of course, by simply adding a NOT NULL to the lookup column in your subquery. This isn't rocket science, and really isn't an advanced T-SQL topic, but it's one of those thing that can sneak up on you if you're not expecting it, particularly if you have one of these deeply nested in a complex query.

A little fun with SSIS Expression Language

2008-05-08T21:21:00Z

The SSIS expression language is a powerful yet enigmatic entity. Once you get used to its syntax - which is part C#, part T-SQL and part *WTH?!?* - it's actually somewhat fun to use. However, one thing it is lacking (as far as I can tell - correct me if I've missed something) is the ability to use an IF...ELSE IF...ELSE statement.

So I ran into a situation earlier in which I needed to do an advanced conditional expression. Here's the situation: My input has a column named provider. This column comes to me as an integer but has to be padded with zeros where necessary to make it a total length of 3 ("2" becomes "002", "13" becomes "013", etc.). A special case exception is the value "1" which is to be left as is.

I knew that the *proper* way to do this was to either create a script component and drop down into VB.NET to map the value appropriately, or use a Conditional Split transform and then merge the values together. However, a colleague had just asked an unrelated question about the ternary operator in the expression language (officially called the Conditional Operator in Redmond), and I decided to push it a bit and force this processing into the Derived Column transform using the Conditional Operator.

So just for fun, I've posted the expression below. It's actually three ~~ternary~~ Conditional Operators working together to provide an advance IF...ELSE IF...ELSE statement. [For the record and in case it's not clear from the code below, the syntax for the Conditional Operator is (value_to_test) ? (value_to_return_if_true) : (value_to_return_if_false).]

(LEN(provider) < 3 && LEN(provider) > 0 && provider != "1") ? (LEN(provider) == 1 ? "00" + provider: "0" + provider) : (LEN(provider) == 0 ? "000" : provider)

SQL Backup 5 (Red Gate)

2008-05-06T20:37:00Z

So after many months of trudging through native SQL Server backups for new mission-critical application I'm deploying, I have decided to do some evaluations and cost/benefit analyses on aftermarket SQL Server backup products. I've started off with SQL Backup 5 from Red Gate. No disclaimers here - I have no affiliation with Red Gate except that they are kind enough to allow me to blog on SqlServerCentral.com (which is owned by Red Gate).

The download and installation of this product was very straightforward - the download is just under 50mb and includes the entire suite of applications on the SQL Toolbelt. I installed only the Backup tool, which was ready for use in under 5 minutes. The interface is very clean in intuitive, and allowed me to register my test server and view its backup history.

To actually backup and restore on my test server (to which I am connecting remotely), I have to install a server-side component. This process happened quickly (less than a minute), and resulted in the addition of a new service (SQL Backup Agent) on this test machine. This service is required to schedule backup jobs from within this application, as it appears that SQL Backup 5 does not rely on the SQL Server Agent service to run scheduled backups. Note that the server-side piece must be installed on the server even if you are not running scheduled jobs.

I ran a baseline backup using native SQL backup tools on my database. The database, somewhere north of 26gb in size, generated a 17gb backup file in just under 1/2 hour. Restoring this backup file to a new database took about 10 minutes. By comparison, the same backup using Red Gate SQL Backup 5 took just over 16 minutes and created a backup file a little less than 3gb in size; restoring this file took about 6 minutes. These operations were done using the default settings in SQL Backup 5, including a compression level of 2 (the middle-of-the-road) setting, and bypassing the Multiple Threads option. The total time for a restore + backup operation (which will be required for our datawarehousing app) would be cut down to just over half the original time required, which was not as good as what I had hoped. However, the size of the resulting file was less than 20% of the original size, which is right in line with my expectations.

I was interested to find that SQL Server reports the last backup date as the date and time at which I ran the backup from SQL Backup 5, which surprised me since this is a third party app and not truly integrated with SQL Server.

Licensing seems to be straightforward - the SQL Toolbelt, an all-inclusive suite of SQL Server administration tools, includes one server license for SQL Backup 5. Additional server licenses are available as well, and licenses for test/demo installations are available at 30% of list price. One important item of note is that the gentleman I spoke to by telephone at Red Gate indicated that we will need a license for both nodes of our SQL cluster, though the second node qualifies for the same price break as a test/demo machine.

My eval version of this product expires in a few weeks, and in the meantime I plan on pushing it further by testing some of the advanced features (changing compression levels, using multiple threads, etc.) to see how it responds. I'm also interested in hearing from others who have used this product - please ping me or just comment here if you've had experiences, good or bad, with Red Gate SQL Backup 5. I plan to eval at least two more products in this category as well in the coming weeks and months, and I'd appreciate any feedback.

Photos from SQL Saturday

2008-05-06T15:48:00Z

Check out the photos from SQL Saturday 3 - Jacksonville:

http://tim-mitchell.spaces.live.com/

SQL Saturday Jacksonville

2008-05-04T16:01:00Z

I just wrapped up a high-octane day of SQL Server community education at SQL Saturday in Jacksonville, Florida. This event, the third thus far in a series of training opportunities for SQL Server professionals, is loosely based on the Code Camp model and is billed as a community event. I enjoy these kind of events, having attended several Code Camps and other similar training opportunities. They are community-based, meaning that most of the trainers are peer professionals and not professional technical educators. Events are held on the weekends, and by the way, they're free to attendees.

I stepped in and did something I had never done before: I volunteered to be a presenter at this event. I've been working toward expanding my capabilities by writing and speaking more, and this was a great opportunity to do the latter (and hopefully will open some doors for the former). The session I presented addressed the SMO (SQL Server Management Objects) framework and how DBAs and developers can use these objects to automate the administration of SQL Server instances throughout an organization. As I shared with my attendees, SMO is the coolest technology that nobody is using. I've posted my notes from this session on my website, so if you're interested in learning more about SMO feel free to download the slide deck and samples and use them as you will (these will likely be posted on the SQLSaturday website as well).

I got to spend some time visiting with Andy Warren and Brian Knight about the framework of the SQL Saturday event, and I am convinced that something like this would be a big hit back home (the Dallas/Ft. Worth area). There is a large user base, and there are enough people who have demonstrated a willingness to volunteer their time to make such an event a success in north Texas. I plan to seriously pursue this so if you are interested in seeing a SQL Saturday event in the Dallas area, please let me know.

If you are a north Floridian or will be in that area next month, there is actually another SQL Saturday event coming up next month at the Orange County Convention Center. If you can make it, I encourage you to stop by.

Gotta love the Information Age

2008-04-27T18:07:00Z

First it was CNN 24x7. Next the Internet brought us live information from around the globe.

Now this... real-time pizza tracking...

Free SQL Server 2008 online courseware from Microsoft

2008-04-14T17:53:00Z

Just received word that Microsoft has posted 3 new courseware titles under the "What's New in SQL Server 2008" umbrella. Available titles are Business Intelligence, Database Development, and Enterprise Data Platform:

https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=139087

On a related note, I have downloaded and installed SQL Server 2008. I'll post more information later once I've had a chance to completely review it, but two things immediately stand out as positive improvements: Intellisense in the query window, and C# scripting in the SSIS script task/component.

SQL Saturday

2008-03-31T01:34:00Z

I've just received notice that I will be a presenter at the upcoming SQL Saturday event in Jacksonville, Florida. I'll be speaking about the SQL SMO object namespace and how this powerful set of classes can be used to administer SQL Server instances programmatically.

I'm looking forward to this opportunity. I've not had much experience delivering presentations to those outside my own places of employment, so this should be a learning experience for me. I'm also the last presentation of the day, so I'll get to attend many other sessions as well.

So if you can get to Jacksonville the first weekend in May, I encourage you to come out to SQL Saturday. Hope to see you there!

Give us SP3!

2008-03-13T01:03:00Z

There has been a great deal of wailing and gnashing of teeth regarding the scuttlebutt that SP2 will be the last service pack for SQL Server 2005. I, too, am excited about the release of the next version of SQL Server later this year, but SQL Server 2005 is likely to be around for a long while. C'mon, it's 2008 and I still have SQL Server 2000 boxes running in production (not by choice, but I digress...). I believe it was Steve Jones who suggested that there be a new SP every six months as long as the product is fully supported; I don't necessarily think we need service packs that often, but I concur that there should be a commitment to continue with full support - which includes regular service pack releases - as long as the product is mainstream.

There is a feedback poll on Microsoft's website to allow us to weigh in on the release (or nonrelease) of Service Pack 3. I've never voted on one of these polls, so I don't know how seriously the Blue Badges take these things, but at least it's worth voicing my opinion....

We Are Microsoft Charity Challenge Weekend

2008-01-21T17:20:00Z

This weekend, I had the unique opportunity to donate some time to a worthwhile charity organization. Through the efforts of the Dallas-area .NET user group community (and specifically, Toi Wright), Microsoft, BravoTech and a number of other vendors, the first annual We Are Microsoft Charity Challenge was born in a flurry of activity over the last three days. This event was held in Dallas and matched up 100 or so developers with 18 charities in need of development services.

I was placed on a team assisting SER Child Development Center with developing a new website . This organization aids low-income families by providing low-cost child care and education, as well as adult education and career development services. From the outset, the organization staff were prepared and readily available; they arrived at the kickoff with design ideas, a packet of information as well as a USB drive full of electronic content and photos. Juan Torres, the CEO and President, and Dr. Carol Johnson-Gerendas, director of the center, were very enthusiastic about this project, volunteering to stay with us for as long as necessary to bring us up to speed on their needs. These two were gracious and appreciative, and made us feel like our efforts really will make a difference.

My team initially consisted of four people, but we lost one member to the flu on Friday. The other remaining members were Ryan Magnusson, a developer working for Wal-Mart in Arkansas (yes, he actually drove in for the weekend) and Raymond Sanchez, a web developer local to the Dallas area. After reviewing the charity's business model and website requirements, we opted to use a SubSonic starter kit for our project. This allowed us to quickly roll out the base application (essentially a CMS) and gave us a framework on which we could develop a couple of requested custom components. I took on the role of project lead as well as writing the custom components, the latter of which was very gratifying since I don't get to write as much code as I used to.

Like all software projects, we had a few glitches. The most frustrating issue was the web space provided for the event had a number of issues which were not resolved until the last day of the event. This left us with the unfortunate choice to leave their existing site in place (having run the demo from my laptop, where the code resides) until we resolve the issues with their web host. We also had a phantom error in SubSonic that slowed us down for a few hours. Since we only had 48 hours in which to work, sleep was simply an afterthought (in fact, one of the guys actually pitched a tent in the break room and slept on site). But the food was good and plentiful, they kept us filled up with caffiene, and the facilities were spacious.

We also had the opportunity to be interviewed by some of the guys from GeeksWithBlogs.net, which was published as a podcast [listen here] . This was my first - and hopefully not the last - podcast interview.

All things considered, the project was a success; when we met with the charity staff at the wrap-up meeting on Sunday, they were highly impressed with the product. Though we didn't win any awards, I'm confident that we have created a solid application on which they can promote their charity for many years to come.

The We Are Microsoft event was billed as a "first annual", suggesting that this will be an ongoing gig. It was suggested that perhaps other user groups will follow suit and host their own WAM event. As for me, I'll be first in line to participate again next year. And maybe I'll bring my tent....

On the Importance of Documentation...

2008-01-10T05:03:00Z

It seems that I keep inheriting old systems that provide a singular, albeit mission critical, function to their owners. In the majority of these cases, I have encountered numerous small applications that were designed to run in a standalone environment to solve a very narrow problem or set of problems. The person who supports the application - which is very often the same person that wrote the code behind it - eventually departs the company without documenting his/her work. Somehow these database apps keep chugging along for months or years until one day a tragedy occurs - like a folder being renamed or a mapped drive being deleted. My phone rings, and I step out of my database analyst role and into my Sherlock Holmes coat.

I'm sure you've all been there. A problem that should take you 30 minutes to fix actually costs you two days because you have to reverse engineer the environment to figure out what the programmer or database designer was thinking. These kind of things get me more irritated than a cowboy without a saddle on a hot summer day.

It's not just for the sake of others that you document. I can't tell you how many times I've looked over some of my own handiwork and wondered what I had been thinking when I designed a feature or configured a particular database object. With few exceptions, I almost always fall back on my documentation at some point, even for the most trivial of things.

So I put out the call to all of my fellow IT folks, database analysts/programmers/sysadmins/[insert your job role here]. Document, document, document. Even if you're sure no one will ever need it, document. If you are creating an ETL database that you plan to delete in 90 days, document. If you write a function that's more than three lines long, document. Your successors will thank you for it. You might even save your own job some day!

Passed 70-431

2007-11-26T02:46:00Z

After putting it off for six months, I finally took - and passed - 70-431 yesterday. I know, I'm probably one of the last ones on board, but I'm kind of a geek and I don't want to sit for an exam until I'm really ready. I've failed two MS exams and after each failure I was bummed out for a while. In fact, when I failed 70-228 (the SQL 2000 admin/maint exam), I waited for an entire year before I retook it. But enough about my hangups...

I can't disclose much for fear of violating the NDA, but I can say that 70-431 was unlike any other cert exam I've taken. There was a section of standard Q&A format, and there were some really tough questions - two of which I answered with what can only be called an educated guess. The second part was a simulation of several tasks in a GUI simulator. This to me was the toughest part, because I didn't (and still don't) know how exactly these are scored. The simulator is intimidating in that the possible answers aren't laid out for you; it's up to you to work through the problems presented and find the solution yourself.

For those who would consider this exam, I can tell you what worked for me. The SQL Server 2005 Implementation and Maintenace book by Microsoft Press was the primary text I used to prepare for this exam. It takes you on a broad (but not deep) trip through SQL Server 2005 and its core features, and for me this was very useful in preparing for the Q&A portion of this test. As good as the book was, the electronic test simulator included was of little help; the questions and answers were badly worded, often misspelled, and even more ambiguous than what you would expect from standardized tests. As far as the simulations, the best thing to do to prepare is to get to know the GUI of the product. Don't just learn the T-SQL to accomplish all of your tasks - although this is helpful and necessary, you should also be prepared to demonstrate your knowledge about using the interface. For the simulations, a recent certification prep course from Chris Ford, a local (Dallas area) SQL Server consultant and trainer, helped to expose me to some of the functions of SQL Server with which I had little experience.

Professional certifications have taken a hit lately as a relevant measure of experience and knowledge, and with good reason. Old fashioned Q&A tests simply measure one's ability to answer questions when all of the possible answers are presented to you; these tests do not measure one's problem solving skills, and they allow for unscrupulous test takers to simply memorize questions and answers ahead of time. I like what Microsoft in adding simulations to the exams, which require the test taker to have at least some knowledge and problem solving ability with the product. I think this change increases the difficulty - and therefore the value - of the certification.

PASS and new SSC design

2007-09-24T03:35:00Z

I'm rarely a "needy" guy, but I have to admit that I'm feeling a little left out. Due to project commitments and budget constraints, I didn't get to make it to PASS for the second year in a row. The PASS conference is (or at least, has been) my favorite conference of the year, not to mention that I love Denver and all things Colorado. I'm planning to make PASS next year, even if I bankroll the trip myself.

On a lighter note, it appears that the new SQLServerCentral.com site went live sometime this weekend. There appear to be at least a few minor bugs - my forum profile declares me to be a "Supreme Being", but also reports that I've only visited the site 5 times. All in all, it looks like a positive upgrade.