none
SQL server 2000 upgrade to SQL server 2012 RRS feed

  • Question

  • While migrating from SQL server 2000 databases to SQL server 2012 databases , we need to follow one of the below approach .

    SQL server 2000>>SQL Server 2005 SP4 >> SQL server 2012

    • Clean approach to migrate Remediation of Non ANSI SQL 2000 code to ANSI complained code
    • TOC of SQL server 2005 is less than the TOC for SQL server 2008
    • Amount of remediation  for data type deprecation, syntax changes is high from SQL 2000 to SQL 2008 migration

    SQL server 2000>>SQL Server 2008 R2 >> SQL server 2012

    • Remediation for SQL 2000 and SQL 2005 will be handled in on go
    • TOC of SQL server 2008  is higher than the TOC of SQL server 2005
    • Most of the remediation issue will be address at the intermediate migration

    Kindly let me know your views about the best  approach ?

    • Moved by Kalman Toth Thursday, March 21, 2013 9:02 AM Not db design
    Thursday, March 21, 2013 5:49 AM

Answers

  • Hello,

    I would migrate first to SQL Server 2005 SP4, verify all databases work fine with compatibility level 90, and then upgrade to SQL Server 2012.

    Upgrading from SQL Server 2000 to SQL Server 2005 is a big step.

    Use SQL Server 2005 Upgrade Advisor on the first migration process. Use SQL Server 2012 Upgrade Advisor on the last migration

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, March 21, 2013 1:12 PM
    Moderator

All replies

  • I have been done both approaches. You can run Upgrade Advisor tool before upgrading to remove deprecated features.

    http://msdn.microsoft.com/en-us/library/ms144256.aspx


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, March 21, 2013 6:34 AM
  • Uri thanks for the quick reply ...but i searching answer for the SQL 2000 to SQL server 2012 upgrade .Please let me know is it advisable to 1st migrate to SQL server 2005 SP4 and  2005 SP4 to SQL server 2012 or else we should migrate sql 2000 to SQL 2008R2 and then to 2012.

    Thursday, March 21, 2013 7:52 AM
  • Hello,

    I would migrate first to SQL Server 2005 SP4, verify all databases work fine with compatibility level 90, and then upgrade to SQL Server 2012.

    Upgrading from SQL Server 2000 to SQL Server 2005 is a big step.

    Use SQL Server 2005 Upgrade Advisor on the first migration process. Use SQL Server 2012 Upgrade Advisor on the last migration

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com

    Thursday, March 21, 2013 1:12 PM
    Moderator
  • Hi Rushikesh,

    Total effort of remediation to reach from SQL 2000 to SQL 2012 is same.. any of the path you choose.

    Couple of more points -

    You may need considerable tweaking of same DB object TWICE while going via 2000-2005-2008 path.

    You need to tweak one object just once if you are bringing it close to 2012 via path 2000-2008-2012.

    Main stream support for SQL 2005 is ending on 11-Apr-2013.. you may not get this software now..

    Considering above points i would recommend second path - 2000-2008-2012.

    Friday, March 22, 2013 5:46 AM