The migration from 32-Bit SQL Server to 64-Bit has completed after ~6 months of red tape (government contracts, arg), cryptic SMTP errors, and a hardware failure that caused the entire server to be rebuilt and reapportioned. Now for the real work – to see what weird differences the data warehouse can illuminate!
The most apparent difference is a positive impact on ETL due to more efficient handling of the larger, ~2Gb debug logs as well as the massive index building that occurs in the temp and look up tables for all the logs we process. That equates to 2 hour gains in the ETL+reporting processes, in turn allowing expansion of the data warehouse for future log types.
One peculiar benefit of 64-bit is the proper(?) handling of wide columns defined in BCP format files when doing a bulk import. We occasionally have log entries from attempted buffer overflow attacks which can exceed various ETL parameters. Over time we have found a good balance between efficient data type lengths and leaving enough room for such attacks, but exceptions do arise. In this particular case I noticed a data source causing SQL Error 4864, “bulk load data conversion error” on both 64-bit and 32-bit servers. After some research I decided to check the BCP format file used in this import and noticed a SQLCHAR with 8000 length. Usually 8000 length is a default large value in our settings, so I decided to increase it to 999,999,999 on both servers. Curiously this alleviated the error on the 64-bit server but not on the 32-bit server! I presume this may have to do with the rampant memory mishandling issues reported in 32-bit SQL 2005, but I am surprised 32-bit wasn’t able to handle longer strings on import. Please enlighten me if you know the specifics, it is appreciated!
I plan to update this post with any other oddities that manifest while subjecting SQL 2005 to the “extreme” conditions our data sources generate. Feel free to share your findings in comments below.
bowdenc 