TSQLTuesday #138: Managing Technology Changes

This is my 9th TSQLTuesday, and this month Andy Leonard has given the prompt “How Do You Respond When Technology Changes Underneath You?”

I’ve largely worked at places where we’ve used pretty stable products. Despite my best efforts, most of the teams I’ve been part of have been reluctant to adopt much in the way of cutting edge technologies. There are things I could write about like the change in SSIS from 2008 to 2012, and the associated relearning I had to do to keep myself current, but I think I want to talk about something a little different:

SQL Server version upgrades

I’m not 100% sure this is what Andy had in mind with his prompt, but it feels on topic enough that I’m going to go with it anyway.

SQL Server version upgrades are something most developers and DBAs will have to confront at one point or another, and I’ve found myself dealing with a few significant ones in the last few years. All have been from 2008R2, including the one my company is currently in the middle of (I wasn’t kidding when I said they don’t tend to adopt the most cutting edge technologies), and they’ve all gone a similar way. I’m not going into all of the details (one blog post is way too short to write a complete how to on this), but there are a few things I want to share about my experiences.

Data Migration Assistant

The SQL Server Data Migration Assistant is an amazing tool. You download it, point it at a SQL instance, tell it what database(s) you want to upgrade, and tell it the target version. It then tells you a lot of what you need to know in terms of incompatible code, code that might behave differently, code that might perform differently, features marked for deprecation etc. Some of these things are easy fixes, for instance using the no longer valid syntax for calling RAISERROR. Others require a bit more work, like any old style outer joins using the *= syntax, in this case you will need to re-write the statement but it should be pretty obvious how you need to do that. Still others may need some more involved work, for instance one of our upgrades was using the old version of database mail. The fix for that meant setting up and configuring the target to use the up to date version, and changing every proc to call the new system procedures instead of the old.

As you might be gathering, there can be quite a bit of work involved even with the Data Migration Assistant’s help. But that’s only half the story.

Things the Assistant can’t help with

There are some design patterns you might adopt that make these upgrades harder, because they put code in places the migration assistant can’t see. The obvious example here is dynamic SQL, the migration assistant can’t see the code you will eventually generate because it’s not held directly in any object, and therefore can’t check what obsolete syntax might be lurking.

Similar principals apply to any code held in tables, or held in SSIS packages, or generated in scripts in SSIS packages, or run from the application side, and so on. Basically, any T-SQL code you put anywhere other than directly in a database object will cause you some issues here.

Obviously the first bit of advice I’m going to give you is “don’t do that”. If you keep your code inside your database, limit calls from external sources like SSIS or applications to just executing stored procedures, and resist the urge to write any dynamic SQL, you should be fine. But these things do exist for a reason, and can be useful, and as I’ve written about before, I am actually a fan of dynamic SQL when it’s used properly.

If you do choose to use some of these design patterns, you will do yourself a big favour by keeping the numbers to a minimum, and heavily documenting them somewhere. That way, when you come to upgrade you should have a ready list of trouble spots in your code base, and if you’ve kept them to a manageable number you should be able to check their upgrade compatibility fairly simply.

One way to perform those checks, that we are currently experimenting with, is to just generate stored procedures from those dynamic scripts. Run through the different possible paths (which you should have documented), generate a script for each of them, and wrap them in CREATE PROCEDURE statements. Once you’ve done that, the migration assistant should pick them up the same as anything else. Another option is just to test them thoroughly in your migration test environment. You should ideally have some re-runnable unit and end-to-end tests for this functionality, so do your test migration and run them.

As I said at the start, there’s a lot more to write about SQL Server version upgrades but this post is probably long enough already so I’m leaving it there. Thanks for the prompt, I hope I wasn’t too far off topic.