Microsoft SQL Server

SQL Server Core Engineer Tips

Migration SQL Server 2000 to SQL Server 2008

Migration SQL Server 2000 to SQL Server 2008

  • Comments 4
  • Likes


The purpose of this post is to explain the minimum required for the migration of Microsoft SQL Server 2000 to Microsoft SQL Server 2008. You can use the same principles to migrate a Microsoft SQL Server 2005 to Microsoft SQL Server 2008.


1. SQL server 2008 upgrade advisor

1.1. Before migrating
Microsoft provides a tool called "Microsoft SQL Server 2008 Upgrade Advisor" to alert you of any changes in design between Microsoft SQL Server 2000/2005 and SQL Server 2008. It is strongly recommended to run this software before migration.

1.2. Download
You can download this tool from the link below:

“Download the Microsoft SQL Server 2008 Upgrade Advisor. Upgrade Advisor analyzes instances of SQL Server 2000 and SQL Server 2005 to help you prepare for upgrades to SQL Server 2008.”
http://www.microsoft.com/downloads/details.aspx?familyid=F5A6C5E9-4CD9-4E42-A21C-7291E7F0F852&displaylang=en 

After installation, a new tab appears in: Start>> All Programs >> Microsoft SQL server 2008 >> SQL Server 2008 Upgrade Advisor

1.3. Report before migration
Run “SQL Server 2008 Upgrade Advisor”.
Then click on “Launch Upgrade Advisor Analysis Wizard”.
Then click on "Detect". The tool will automatically select the components installed on your platform.
It is also interesting to give a trace profiler tool containing a representative of your business so that it detects all the elements that would longer supported or recommended in Microsoft SQL Server 2008.
migration SQL Server 2005 to SQL Server 2008

Then configure the connection to your SQL server 2000 instance. After a few minutes a report will be generated with warning or points on which you must bring your attention. These items may include Full Text Search, replication, objects that no longer exist or have been modified in the new version, plans to maintain ...
The tool will provide two other types of information:
1. Objects affected
2. Advice you can find a workaround or fix the problem.

Sample report provided by the tool:
migration SQL Server 2005 to SQL Server 2008

3. Migration with the database restore method

3.1. Restoring a database SQL server 2000
On your new instance Microsoft SQL Server 2008, connect to Management Studio 2008. Then click on the "Restore Database". Then follow the instructions.

RESTORE (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186858.aspx

How to: Restore a Database Backup (SQL Server Management Studio)
http://msdn.microsoft.com/en-us/library/ms177429.aspx
 
3.2. Compatibility Level SQL 2000/ SQL 2008
If you restore your database in SQL Server 2000 SQL Server 2008, the level of compatibility will default mode "SQL Server 2000 (80).
To know the level of compatibility, Make a right click on the name of the database>> "Property"
Then in the dialog "Database Properties", click "Options"

migration SQL Server 2005 to SQL Server 2008

To enjoy all the new features in the new engine SQL server 2008, you must change the compatibility level to 100.

To know the differences between compatibility 80, 90 or 100, I invite you to read the following article
http://msdn.microsoft.com/en-us/library/bb510680.aspx

sp_dbcmptlevel (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178653.aspx 
 

3.3. Transferring SQL Server logins and Windows
There are different ways to migrate your users
- SQL Server Intégration Services, with component « "transfer Login task ».
- SQL Server Management Studio, with “Copy Database Wizard”
- You can also draw on examples of script between SQL Server 2000 and 2005 KB Article http://support.microsoft.com/kb/246133

3.4. SQL Server Agent jobs
You can migrate your SQL Server Agent jobs using Enterprise Manager 2000. You can find more detail in the documentation below:

How to script jobs using Transact-SQL (Enterprise Manager)
http://msdn.microsoft.com/en-us/library/aa177024(SQL.80).aspx

3.5. Other components
You must also reconfigure the components such as SQL database Mail extended stored procedures, linked servers...

3.6. Update statistics
It is recommended that, after having committed or changed the compatibility mode to 100, execute the stored procedure: sp_updatestats

The procedure allows sp_updatestats system to recalculate the statistics and make an update for all the statistics on each table in your base data. To avoid errors related to the statistics of the previous version.

sp_updatestats (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms173804.aspx





Related Resources

Resources for Upgrading to SQL Server 2008
http://msdn.microsoft.com/en-us/library/cc936623.aspx
 
SQL Server 2008 Upgrade Technical Reference Guide
http://www.microsoft.com/downloads/details.aspx?FamilyID=66d3e6f5-6902-4fdd-af75-9975aea5bea7&displaylang=en

Michel Degremont | Microsoft EMEA
Product Support Services Developer - SQL Server Core Engineer |
Comments
  • Gracías

  • Hi There

    Thank for this piece of information as we are also planning to move some of our SQL Server 2000 database to 2008 R2.

    Is there any difference if we move to SQL Server 2012 from SQL Server 2000 data bases Please update

    Many thanks

    Syed Qazafi Anjum

  • Hi Syed,

    You will be not able to migrate from SQL 200 to 2012 directly.

    SQL Server 2012 supports upgrade from only the following versions: SQL 2005 SP4 or SQL 2008 SP2 or SQL 2008 R2 SP1.

    You will have to make the migration in two steps:

    - Step 1 : Make a first migration from SQL 2000 to SQL 2008 for instance

    - Step 2 : Make a second migration from SQL Server 2008 to 2012.

    I invite you to read the following bol : Supported Version and Edition Upgrades (msdn.microsoft.com/.../ms143393.aspx )

    Michel.

  • Migration SQL Server 2000 to SQL Server 2012

    blogs.technet.com/.../migration-sql-server-2000-to-sql-server-2012.aspx

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment