Geeks With Blogs
Caffeinated Coder A Grande, Triple Shot, Non-Fat Core Dump by Russell Ball

There was some discussion about SQL Server 2008 at the KC .NET user group meeting last night and I involuntarily winced at the thought of another upgrade because we just finished an intensive migration process a few months ago. In hindsight, the migration went fairly smoothly but it was definitely not a trivial process, nor was it something that I would want to repeat any time soon. It definitely requires some advanced planning and organized regression testing. I thought I would jot down a few lessons that we learned before I completely block this experience out of my memory.


Here are some quick background stats (mid-sized bank):

1.       Apps Tested: 40

2.       Technologies supported: .NET 2.0, 1.1, 1.0, Classic ASP, VB6, PowerBuilder, Access 97

3.       Databases: 25

4.       Total Tables: 2400

5.       Total DB Objects (sprocs & udf’s): 3500

6.       DTS packages: 100

7.       Server implementation: Clustered Quadzilla running on 32 bit Standard Edition, separate Reporting Server

8.       Total required object changes: 40-50


Lessons Learned

  1. The upgrade wizard was helpful but not full-proof.  It caught about 60% of the changes we had to make. If you have separate reports or application logic that runs on specific days (i.e. end of month, start of month, end of quarter, etc), make sure you run profile traces on those days and then use the trace files as input for the upgrade adviser.
  2. Script out all the sprocs and udf’s and then run the ddl scripts against an upgraded test server. We caught another 20% of the problems through the syntax errors we got when trying to recreate these objects.
  3. Set up an organized regression test of your critical apps, especially if your business has a low tolerance to risk. The last 20% of our changes were only caught through testing because of dynamically generated SQL or SQL that was embedded within the applications rather than stored as objects within SQL Server.
  4. Carefully review all proposed sproc changes and when possible compare before and after result sets with a tool like RedGate BeyondCompare. Most of the changes we had were syntax related and easily fixed, however we had to redo a few changed sprocs because of some misunderstandings of how the old *= join syntax worked.
  5. Be on guard for a few hard to spot errors that might show up later in production. For example, this excerpt from one of our udf’s actually concatenated multiple rows together in SQL 2000, but only returned the first row in 2005. The only way we figured this out was that someone reported that a few of our app generated pdf documents were missing several paragraphs.

select @para = @para + cast(dgp.Paragraph as varchar(7000)) + '

            from DocumentParagraphs dgp

            where dgp.ReportID = @ReportID

                        and dgp.ActiveFlag = 1

            order by dgp.ParagraphOrder

  1. Do not rely on 8.0 compatibility mode as a safety net. Dropping back into 8.0 compatibility mode did not make the problems we found magically work.
  2. If you have to move many DTS packages to a new machine, be prepared for some mind-numbing, tedious work. Each package had to be moved manually with lots of GUI clicks and all inner packages had to be re-selected due to GUID issues. Despite this headache, the DTS process was much smoother than expected and we didn’t have to make any code changes within the packages. We did experience some additional flakiness with SQLMail, but we’re not sure if that is related to SQL 2005 or moving from a single server to a cluster.
  3. If you have to support an old version of a defunct language like PowerBuilder, make sure that you are nice to the only guy who can support it. It turns out that we really didn’t have one of those guys anymore so a couple of us were forced to stumble around in the IDE for the first time ever in order to search for the offending embedded SQL while under deadline pressure. NOT FUN!
  4. Don’t call undocumented functions! An old C++ module used by a couple of our legacy apps uses pwdcompare(). We finally got it to work but only by seriously elevating the applications account permissions.
  5. Contrary to all expectations, we really didn’t have any problems with linked tables within our Access databases even though we were stuck on a really old version (Access 97). I expected this to be a nightmare, but it wasn’t.
Despite these hassles, the migration was definitely worth the effort for us. We were running up against a memory limit imposed by SQL Server Standard edition with our old server and were starting to experience some painful performance problems. SQL 2005 allowed us to upgrade to a much more powerful machine without having to pay the additional $180,000 in licensing fees that it would have cost us to upgrade all of our servers to the SQL 2000 Enterprise Edition which had support for additional memory.
Posted on Wednesday, June 27, 2007 5:21 PM Technical How-To's | Back to top

Comments on this post: I survived a SQL 2005 upgrade and all I got was this lousy blog post...

No comments posted yet.
Your comment:
 (will show your gravatar)

Copyright © Russell Ball | Powered by: