locked
Upgrade from SQL 2000 to SQL 2016 via SQL 2008 RRS feed

  • Question

  • Hi All.    I have a database currently running on SQL 2000 SR4 and need to upgrade to the latest version.  I believe I need to goto SQL 2008 as an interim step.  Any help and suggestions would be most appreciated.  Regards, 

    Wednesday, October 12, 2016 1:10 PM

Answers

  • That is correct. You can back up the SQL 2000 database and then restore it on a SQL 2005 SQL Server or above. And then back it up there and restore it on SQL2016.

    From

    https://msdn.microsoft.com/en-us/library/ms178513.aspx

    In SQL Server 2016, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version

    Wednesday, October 12, 2016 1:31 PM
  • Hi ASh,

    What SQL 2000 bit ? 

    SQL Server 2016 is only available for 64-bit platforms. Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit using SQL Server Setup. However, you can back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to a new instance of SQL Server (64-bit) if the databases are not published in replication. You must re-create any logins and other user objects in master, msdb, and model system databases.

    The table below lists the supported upgrade scenarios from earlier versions of SQL Server to SQL Server 2016.

    Upgrade from Supported upgrade path
    SQL Server 2008 SP3 Enterprise SQL Server 2016 Enterprise 
    SQL Server 2008 SP3 Developer SQL Server 2016 Developer
    SQL Server 2008 SP3 Standard SQL Server 2016 Enterprise 

     SQL Server 2016 Standard
    SQL Server 2008 SP3 Small Business SQL Server 2016 Standard
    SQL Server 2008 SP3 Web SQL Server 2016 Enterprise 

     SQL Server 2016 Standard 
     
     SQL Server 2016 Web
    SQL Server 2008 SP3 Workgroup SQL Server 2016 Enterprise 

     SQL Server 2016 Standard
    SQL Server 2008 SP3 Express SQL Server 2016 Enterprise 

     SQL Server 2016 Standard 
     
     SQL Server 2016 Web 
     
     SQL Server 2016 Express
    SQL Server 2008 R2 SP2 Datacenter SQL Server 2016 Enterprise 
    SQL Server 2008 R2 SP2 Enterprise SQL Server 2016 Enterprise 

    Refer following link 

    https://msdn.microsoft.com/en-us/library/ms143393.aspx


    Please click Mark As Answer if my post helped.

    Wednesday, October 12, 2016 1:35 PM
  • Make note few things-

    Ensure your SQL server 2000 is in latest service pack  then  ensure  if you for sql server2005 then it should be SP4 and if its for SQL 2008(SP2) or SQL 2008 R2 (SP1) (or)May latest SP (I havent tested but it should work bcz of latest sp)


    Before you upgrade  use the Microsoft SQL Server 2008 Upgrade Advisor to see-
    follow
    >>Migration SQL Server 2000 to SQL Server 2008
    https://blogs.technet.microsoft.com/mdegre/2009/07/21/migration-sql-server-2000-to-sql-server-2008/

    understand(jut fyi..)
    Breaking Changes to Database Engine Features in SQL Server 2016
    https://technet.microsoft.com/en-us/library/ms143179(v=sql.130).aspx

    (in the same link you can go through the olderversion like 2008r2/2012/2014)
    verify with your apps/business good to go ahead,because lot of changes from one version to other version.

    Additional info

    ------------

    you may go through 

    Checklist for upgrading to a new version of SQL Server
    https://blogs.msdn.microsoft.com/cindygross/2009/10/28/checklist-for-upgrading-to-a-new-version-of-sql-server/

    How to Upgrade to SQL Server 2008 from SQL Server 2000
    http://sqlmag.com/sql-server-2008/how-upgrade-sql-server-2008-sql-server-2000


    SQL Server 2016 Upgrade Planning
    http://sqlmag.com/sql-server/sql-server-2016-upgrade-planning-0


    Hardware and Software Requirements for Installing SQL Server 2016
    https://msdn.microsoft.com/en-us/library/ms143506.aspx

    (here just change the other versions which you want to see from the same page like 2012/2014/2008R2)

    Note->this need to do carefully perform when you do one from 2000 to 2008 or 2008R2 - there are things like your jobs/packages/logins/users need to trasfer mannualy and post of the database upgrade & changed the compatibilitythen run the index/stats +perform checkdb then allow apps to validate the same then go for next version and repeat the same.

    better you go for 2008R2 instead 2008 but the steps will be quite same as 2008.




    Regards, S_NO "_"

    Wednesday, October 12, 2016 2:03 PM
  • Good to first understand all aspects, please check below

    links:

    Supported versions:

    https://msdn.microsoft.com/en-us/library/ms143393.aspx

    Ready for upgrade:

    https://msdn.microsoft.com/en-us/library/bb677622.aspx?f=255&MSPPError=-2147217396

    Considerations to upgrade:

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

    Once you reach to 2014, you can go to 2016, but above link is only for assistance on planning.

    Plan with all pros and cons of the environment, application, features, options, along with roll-back etc.

    Since there is no straight way to see another end of world, choose first good version which suits your application functionality data integrity and go with all required documentation and white board to make sure all crystal and clear.


    Santosh Singh


    Wednesday, October 12, 2016 8:56 PM
  • Hello,

    Once you upgrade to SQL Server 2008 as an interim step you need to change compatibility level of databases to 100 because compatibility level 80 and 90 are not supported on SQL 2016.

    Non-ansi joins using *= and =* that were common on SQL 2000 implementations are not longer supported.

    Features like SQL Mail are not supported and have been replaced for Database Mail.

    If DTS is used on that instance, use DTS Upgrade Wizard of SQL Server 2008
    to migrate DTS packages to SQL 2008 and from there to SQL Server 2016.

    For more discontinued features that may be blocking point in your migration project, please read the following URLs:

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.105).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.120).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.130).aspx

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

     

    Thursday, October 13, 2016 3:06 AM

All replies

  • That is correct. You can back up the SQL 2000 database and then restore it on a SQL 2005 SQL Server or above. And then back it up there and restore it on SQL2016.

    From

    https://msdn.microsoft.com/en-us/library/ms178513.aspx

    In SQL Server 2016, you can restore a user database from a database backup that was created by using SQL Server 2005 or a later version

    Wednesday, October 12, 2016 1:31 PM
  • Hi ASh,

    What SQL 2000 bit ? 

    SQL Server 2016 is only available for 64-bit platforms. Cross-platform upgrade is not supported. You cannot upgrade a 32-bit instance of SQL Server to native 64-bit using SQL Server Setup. However, you can back up or detach databases from a 32-bit instance of SQL Server, and then restore or attach them to a new instance of SQL Server (64-bit) if the databases are not published in replication. You must re-create any logins and other user objects in master, msdb, and model system databases.

    The table below lists the supported upgrade scenarios from earlier versions of SQL Server to SQL Server 2016.

    Upgrade from Supported upgrade path
    SQL Server 2008 SP3 Enterprise SQL Server 2016 Enterprise 
    SQL Server 2008 SP3 Developer SQL Server 2016 Developer
    SQL Server 2008 SP3 Standard SQL Server 2016 Enterprise 

     SQL Server 2016 Standard
    SQL Server 2008 SP3 Small Business SQL Server 2016 Standard
    SQL Server 2008 SP3 Web SQL Server 2016 Enterprise 

     SQL Server 2016 Standard 
     
     SQL Server 2016 Web
    SQL Server 2008 SP3 Workgroup SQL Server 2016 Enterprise 

     SQL Server 2016 Standard
    SQL Server 2008 SP3 Express SQL Server 2016 Enterprise 

     SQL Server 2016 Standard 
     
     SQL Server 2016 Web 
     
     SQL Server 2016 Express
    SQL Server 2008 R2 SP2 Datacenter SQL Server 2016 Enterprise 
    SQL Server 2008 R2 SP2 Enterprise SQL Server 2016 Enterprise 

    Refer following link 

    https://msdn.microsoft.com/en-us/library/ms143393.aspx


    Please click Mark As Answer if my post helped.

    Wednesday, October 12, 2016 1:35 PM
  • Make note few things-

    Ensure your SQL server 2000 is in latest service pack  then  ensure  if you for sql server2005 then it should be SP4 and if its for SQL 2008(SP2) or SQL 2008 R2 (SP1) (or)May latest SP (I havent tested but it should work bcz of latest sp)


    Before you upgrade  use the Microsoft SQL Server 2008 Upgrade Advisor to see-
    follow
    >>Migration SQL Server 2000 to SQL Server 2008
    https://blogs.technet.microsoft.com/mdegre/2009/07/21/migration-sql-server-2000-to-sql-server-2008/

    understand(jut fyi..)
    Breaking Changes to Database Engine Features in SQL Server 2016
    https://technet.microsoft.com/en-us/library/ms143179(v=sql.130).aspx

    (in the same link you can go through the olderversion like 2008r2/2012/2014)
    verify with your apps/business good to go ahead,because lot of changes from one version to other version.

    Additional info

    ------------

    you may go through 

    Checklist for upgrading to a new version of SQL Server
    https://blogs.msdn.microsoft.com/cindygross/2009/10/28/checklist-for-upgrading-to-a-new-version-of-sql-server/

    How to Upgrade to SQL Server 2008 from SQL Server 2000
    http://sqlmag.com/sql-server-2008/how-upgrade-sql-server-2008-sql-server-2000


    SQL Server 2016 Upgrade Planning
    http://sqlmag.com/sql-server/sql-server-2016-upgrade-planning-0


    Hardware and Software Requirements for Installing SQL Server 2016
    https://msdn.microsoft.com/en-us/library/ms143506.aspx

    (here just change the other versions which you want to see from the same page like 2012/2014/2008R2)

    Note->this need to do carefully perform when you do one from 2000 to 2008 or 2008R2 - there are things like your jobs/packages/logins/users need to trasfer mannualy and post of the database upgrade & changed the compatibilitythen run the index/stats +perform checkdb then allow apps to validate the same then go for next version and repeat the same.

    better you go for 2008R2 instead 2008 but the steps will be quite same as 2008.




    Regards, S_NO "_"

    Wednesday, October 12, 2016 2:03 PM
  • Good to first understand all aspects, please check below

    links:

    Supported versions:

    https://msdn.microsoft.com/en-us/library/ms143393.aspx

    Ready for upgrade:

    https://msdn.microsoft.com/en-us/library/bb677622.aspx?f=255&MSPPError=-2147217396

    Considerations to upgrade:

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

    Once you reach to 2014, you can go to 2016, but above link is only for assistance on planning.

    Plan with all pros and cons of the environment, application, features, options, along with roll-back etc.

    Since there is no straight way to see another end of world, choose first good version which suits your application functionality data integrity and go with all required documentation and white board to make sure all crystal and clear.


    Santosh Singh


    Wednesday, October 12, 2016 8:56 PM
  • Hello,

    Once you upgrade to SQL Server 2008 as an interim step you need to change compatibility level of databases to 100 because compatibility level 80 and 90 are not supported on SQL 2016.

    Non-ansi joins using *= and =* that were common on SQL 2000 implementations are not longer supported.

    Features like SQL Mail are not supported and have been replaced for Database Mail.

    If DTS is used on that instance, use DTS Upgrade Wizard of SQL Server 2008
    to migrate DTS packages to SQL 2008 and from there to SQL Server 2016.

    For more discontinued features that may be blocking point in your migration project, please read the following URLs:

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.105).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.110).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.120).aspx

    https://msdn.microsoft.com/en-us/library/ms144262(v=sql.130).aspx

    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

     

    Thursday, October 13, 2016 3:06 AM