Tim Mitchell

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.

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.

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)

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.

Check out the photos from SQL Saturday 3 - Jacksonville:

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

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

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

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.

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!

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....

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....

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!