locked
Migration from SQL Server 2000 to SQL server 2012 Standard RRS feed

  • Question

  • We would like to upgrade our SQL 2000 server to SQL 2012 Standard using new machine running Windows server 2012 R2.

    My questions are:

    1- We need to upgrade from SQL 2000 to SQL 2008, we can't upgrade directly from SQL 2000 to SQL 2012, is that correct?

    2- Do we need to purchase the SQL 2008 or there is a free version which had all the functionality to do the migration?

    3- Do we need to do any configuration for our data before we restore them from SQL 2008 into SQL 2012?

    Thanks

    Sinan A.

    Friday, October 16, 2015 6:52 PM

Answers

  • 1- We need to upgrade from SQL 2000 to SQL 2008, we can't upgrade directly from SQL 2000 to SQL 2012, is that correct?  Yes

    2- Do we need to purchase the SQL 2008 or there is a free version which had all the functionality to do the migration? No -  you can getan Evaluation Edition, which is  still being offered for download  

    Microsoft® SQL Server® 2008 Enterprise Evaluation: Trial Experience for IT Professionals

    3- Do we need to do any configuration for our data before we restore them from SQL 2008 into SQL 2012?

    There are Two option In-Place: SQL Server is upgraded where it is currently installed
    Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.

    Upgrade to SQL Server 2012

    Saturday, October 17, 2015 6:22 AM
  • Follow the upgrade steps provided but please do not upgrade live databases (in place upgrade on live leaves you at risk). Always take backups prior to attempting upgrades and also perform upgrade on copies.

    In the ideal scenario use backup restore to upgrade your user databases to up level version. Ensure that upgrade steps all succeed when it comes out of recovery and also ensure that your change compatibility level and make other version specific changes such as setting CHKSUMs instead of torn page detection.

    Follow the same procedure when going to 2012.

    Your Edition must be the same or higher -i.e. cant go from Enterprise to Standard for instance.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Saturday, October 17, 2015 12:38 PM

All replies

  • 1- We need to upgrade from SQL 2000 to SQL 2008, we can't upgrade directly from SQL 2000 to SQL 2012, is that correct?  Yes

    2- Do we need to purchase the SQL 2008 or there is a free version which had all the functionality to do the migration? No -  you can getan Evaluation Edition, which is  still being offered for download  

    Microsoft® SQL Server® 2008 Enterprise Evaluation: Trial Experience for IT Professionals

    3- Do we need to do any configuration for our data before we restore them from SQL 2008 into SQL 2012?

    There are Two option In-Place: SQL Server is upgraded where it is currently installed
    Migration: A a new environment is installed, the data is copied to it and configured with the existing data. The content from SQL server 2008 must be migrated to the 2012 supported formats.

    Upgrade to SQL Server 2012

    Saturday, October 17, 2015 6:22 AM
  • Follow the upgrade steps provided but please do not upgrade live databases (in place upgrade on live leaves you at risk). Always take backups prior to attempting upgrades and also perform upgrade on copies.

    In the ideal scenario use backup restore to upgrade your user databases to up level version. Ensure that upgrade steps all succeed when it comes out of recovery and also ensure that your change compatibility level and make other version specific changes such as setting CHKSUMs instead of torn page detection.

    Follow the same procedure when going to 2012.

    Your Edition must be the same or higher -i.e. cant go from Enterprise to Standard for instance.


    Regards,
    Mark Broadbent.
    Microsoft Certified Master
    Contact me through twitter | blog | sqlcloud


    Please click "Propose as answer" if a post solves your problem
    or/and vote the post up if it's been helpful.

    Saturday, October 17, 2015 12:38 PM
  • 1- We need to upgrade from SQL 2000 to SQL 2008, we can't upgrade directly from SQL 2000 to SQL 2012, is that correct?

    Note :- make sure your source is in SQL Server 2000 SP4

    yes, please refer the below link... please keep the sql 2008 / R2 with latest SP...

    http://blogs.technet.com/b/mdegre/archive/2012/06/15/migration-sql-server-2000-to-sql-server-2012.aspx

    http://sqlmag.com/sql-server-2014/sql-select-steps-migrate-sql-server-2000-sql-server-2014

    After restoring the databases in sql 2008 and change the compatibility level to 80..

    Migration logins between two different sql versions ?

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/20456640-1b2f-4905-a627-aa2dc79c1d99/move-logins-from-sql-server-2000-server-to-sqlserver-2012?forum=sqlsecurity

    https://support.microsoft.com/en-us/kb/918992

    2- Do we need to purchase the SQL 2008 or there is a free version which had all the functionality to do the migration?

    Use same or higher versions than your current sql 2000, you cant use any free versions.. considering that your current sql 2000 version is licensed..

    SQL support life cycle :- https://support.microsoft.com/en-us/lifecycle?c2=1044&wa=wsignin1.0

    3- Do we need to do any configuration for our data before we restore them from SQL 2008 into SQL 2012?

    Make sure to change the compatibility levels  and there are many other basic configuration and not mandatory for migrations..

    SQL Server 6.0 – 60
    SQL Server 6.5 – 65
    SQL Server 7.0 – 70
    SQL Server 2000 – 80
    SQL Server 2005 – 90
    SQL Server 2008 – 100
    SQL Server 2012 – 110
    SQL Server 2014 – 120

    SQL Server 2016 – 130

    ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 130 | 120 | 110 | 100 | 90 }

    DTS, SSIS, Any reports, sql agent jobs, linked server connections and more....


    Raju Rasagounder Sr MSSQL DBA

    Sunday, October 18, 2015 10:24 PM
  • Also please refer the below MSDN links...

    https://social.msdn.microsoft.com/Forums/en-US/49dada09-424a-4c1e-a820-141b6697cb85/migration-from-sql-server-2000-to-sql-server-2012?forum=transactsql

    https://social.msdn.microsoft.com/Forums/en-US/49a5e8a5-e802-438b-a287-1cc8b9cca25a/migrated-from-sql-server-2000-to-sql-server-2008?forum=sqlservermigration


    Raju Rasagounder Sr MSSQL DBA

    Sunday, October 18, 2015 10:27 PM