Next: Home Network Upgraded. Previously: Name Change.

SQL Server 2005 Migration

369 words.

We recently completed a migration from SQL Server 2000 to SQL Server 2005 at work. Here are some of the issues I’ve had to deal with from an application perspective.

Apparently the locking scheme is different in 2005. We have had to pepper our SQL select and update queries with the locking hints “WITH(NOLOCK)” and “WITH(ROWLOCK)” to achieve any level of concurrent activity. As a programmer who likes cross-platform standards, it’s kind of annoying to have to Microsoftize what should be pretty standard SQL queries. It’s also annoying because SQL is supposed to be a declarative language, and adding locking hints specifies not what data to retrieve but how to retrieve it. I feel like the “how” should be more in the territory of the database administrator than the application programmer. (Although in my case, my team has limited database administration experience, so I’m happy to tackle locking issues myself.)

In SSIS packages, Fast Loading into a view no longer worked using the OLE DB data destination component. I’m not sure if it’s a driver issue or a database issue or what. SSIS returned an inexplicable “fatal error 606” and SQL Server reported some kind of metadata error. Disabling Fast Load took care of the problem. (It also loaded slower.)

Again in SSIS, the IsolationLevel seems to be much more important with SQL Server 2005 than it was with 2000. All my packages had been built with the default Serializable level, which now turns out to be the most aggressive locking and also happens to be the slowest and least concurrency-friendly in 2005. With 2000, it didn’t seem to be a hindrance.

I think the bottom line is that you could get away with a lot more laziness in 2000 than you can in 2005, especially in the areas of concurrency and blocking. For example, a transformation task that used to take 5 minutes in SQL Server 2000 without any special locking hints or indexes suddenly took about 5+ hours after migrating to SQL Server 2005, during which time it bogged down everyone else using the database. (This was an SSIS transformation.) Adding locking hints and table indexes sped up the process to a much more pleasing 30 seconds.

Related

Next: Home Network Upgraded. Previously: Name Change.

This page is a static archival copy of what was originally a WordPress post. It was converted from HTML to Markdown format before being built by Hugo. There may be formatting problems that I haven't addressed yet. There may be problems with missing or mangled images that I haven't fixed yet. There may have been comments on the original post, which I have archived, but I haven't quite worked out how to show them on the new site.

Sorry, new comments are disabled on older posts. This helps reduce spam. Active commenting almost always occurs within a day or two of new posts.