Post reply

SQL Server 2000 database compatibility on SQL Server 2008

  • February 13, 2012 at 12:33 am

    #264381

    Hi All,

    One of my client wants to move SQL Server 2000 to SQL Server 2008 R2. Lots of database are in SQL Server 2000. I have to suggest my client that after movement of SQL 2000 to SQL 2008 there will be no impact. So Upgrade adviser is the only option to check the compatibility of SQL Server 2000 databases on SQL Server 2008. And what kind of compatibility issues I can face while moving 2000 to 2008.

    Thanks in advance.

  • February 14, 2012 at 8:36 am

    #1446881

    There could be none or there could be quite a few. You won't know until you run the Upgrade Advisor and be sure to test, test, test. Setup a test server first before simply moving the production dbs from SQL2000 to 2008R2 and saying there will be no impact. There are a handful of reserved words in 2008 that did not exist in SQL2000 and they cannot be used.

    Before moving the databases run this on each db to make sure there are no data integrity issues

    DBCC CHECKALLOC

    DBCC CHECKDB

    After moving the databases you MUST run the UPDATE USAGE process for all tables and then do a rebuild all indexes and update stats so the SQL2008 Optimizer will have good access paths.

  • February 14, 2012 at 8:53 am

    #1446894

    In addition, the parser is stricter, there were some SQL statements that 2000 considered valid that 2005 does not. There were optimiser changes so a very small percentage of queries show a performance degradation after upgrade.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • February 15, 2012 at 4:52 am

    #1447264

    I have heard before upgrading upgrade adviser gives you so much concerns and we have to resolve those all issues that may arise. Sometimes upgrade adviser gives us blocking issues and DTS packages issues.

    How to resolve if any blocking and DTS issues arise in upgrade adviser report. Does it give compatibility issues also ?

    Thanks in advance.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply