SQL Server Migration: 2000 32bit TO 2005 64 bit (Part 1)

Overview
This is the first post in a 3 part series on how to migrate from SQL 2000 32bit TO SQL 2005 64bit.

The first part will be a migration from SQL 2000 32bit to SQL 2005 32bit.

Notes

• Depending on what the Upgrade Adviser Tool tells you ... your steps may vary from those below.
• We also have aliased our DB using the Network Client Utility. This is not necessarily best practice, but it allows for easy migration and DB name changes.
  

Steps
1. Backup your SharePoint farm completely
2. Run the MS SQL 2005 Upgrade Adviser Tool
3. Delete excess backup history in the (Master>backupset table) that goes over 10,000 rows. (speeds up migration)
4. Quiesce the Farm.
5. Stop the WWW publishing service and all SharePoint specific services.
6. Check to make sure all sessions are killed.
7. Backup the SQL 2000 DATABASES.
8. Add the accounts for the application pools to the main Administrators group on the SQL 2005 box.
9. Move all SharePoint related logins to the new SQL 2005 instance.
10. Restore the SQL 2000 backup databases to the new SQL 2005 box.
11. Modify queries that use column aliases prefixed by table aliases in the ORDER BY clause. (what the Adviser told us)
12. Run the following stored procedure: sp_fulltext_service ‘load_os_resources’, 1 (what the Advisor told us)
13. On the web server go to Start > Programs > Microsoft SQL Server > Network Client Utility
14. In the Alias tab change the IP from the old server to the new server
14. Rebuild the indexes on the 2005 databases (what the Advisor told us).
15. Rebuild the statistics on the 2005 databases (what the Advisor told us).
16. Reboot the web server.
17. Restart all services.
18. Unquiesce the Farm.

Part 2 of this series will be changing the compatibility of the DB from 80 to 90. Part 3 will be moving from SQL Server 2005 32 bit to 64bit.