Critical Status - Database-Oriented Issues Plus

It seems that everything has a critical status, especially when it comes to databases and software development! Here's a place for listing issues and ideas relating to database and development issues. I'll list problems and ideas mainly related to SQL Server 2005. Post your thoughts, advice or complaints! Later this year will be the 25th anniversary of being in the trenches of the IT industry. I specialize in database applications development. I'd like to share my experiences with everyone and learn of those of others. You never stop learning.

Thursday, May 1, 2008

determine the compatibility level prior to migration

Ahhhh here's one from the past couple of weeks!

When moving a database to 2005 from 2000. select properties of the database, go to options and see what the compatibility level is. DSNs and apps can be affected by this. For example, I was working on a database issue with linked tables in Access. Users lost connectivity. i.e. they couldn't edit. The database compatibility level was set to the default 2005 when created yet ODBC connections and ADO.NET connection strings were using the SQL Server 2000 driver not the SQL Native Client driver. In reducing compatibility of the database to 2000 level, users were able to edit data with their current 2000 settings. issue resolved without user interaction. As I had used copies of the 2005 database, I had to delete non-2000 versions and rename. The users simply had to select and delete their linked tables and check the "Always prompt for a new location" checkbox. Basically, you're going to refresh your DSN connection and table linkage. Also, developers of a database that migrated a 2000 database after our team did, kept the default compatibility level yet, the team here set it to 2000. Connectivity issues in code will generate lost profit for the developers team due to code rewrite, process changes, etc.

The moral of the story is to determine the compatibility level prior to migration to 2005 AND communicate this to all stakeholderes!!

No comments: