Should I rebuild table indexes after migrating a SQL Server 2000 database to 2005

Question

I have been instructed to migrate from SQL Server 2000 to 2005. I will be running side by side.

After restoring from a backup, I plan to do the following:

ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90; DBCC CHECKDB(<database_name>) WITH NO_INFOMSGS DBCC UPDATEUSAGE(<database_name>) WITH NO_INFOMSGS exec sp_updatestats 'resample' 

Should I rebuild table indexes before using DBCC UPDATEUSAGE and sp_updatestats?

Am I missing something obvious that needs to be done after migration?

All help will be warmly approved.

thanks

Answer 1

There are not many authoritative online materials that provide the specifics of migration (in addition to procedures aimed at simply ensuring the structural integrity of the database on a new / updated host). For this reason, and since this migration seems to be planned / planned for you, I would take the opportunity to rebuild all indexes, including cluster indexes .

For some, this may seem “redundant,” but what is even better for re-balancing and repackaging indexes / tables, providing a new fill factor that is commensurate with the expected use of CRUD, and generally approving the health database in the new host.

In practical terms, I would ...

 ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90; DBCC CHECKDB(<database_name>) -- WITH NO_INFOMSGS (I'd take the messages, I'm curious by nature ;-) 

As you suggest, but then I would rebuild all the indexes in all / most tables even (perhaps in particular ...) on very large tables. Of course, you need to evaluate the time and relative risk associated with such an operation, but in most cases, even with databases of 100+ million rows, the total time invoice is of the order of several hours, since it can delay the restructuring of future indexes. As for the risk factor, you seem to have a backup ...

Which goes without saying ... When the base table has a clustered index, and if you want to rebuild it before resetting all the other indexes so as not to spend a lot of time updating the non-clustered index (without having to seriously restore them), then, of course, recreate these nonclustered indexes.

Depending on the number of tables and indexes, it may be advantageous to write several small stored procedures to automate index dropping (and recreate, although it may also be important to individually analyze fill factors, recompute, and another parameter).

Answer 2

"Did I miss something obvious that needs to be done after the migration?"

• Make sure you are using the latest SP SS 2005.
• I am surprised that you do not mention that you tested all of your SP and UDF in SS 2005 to prove that they succeed equally and unpredictably everywhere. This may take some time, but gives you a great chance to significantly increase the reliability of your system.

Answer 3

Adding to your CheckDB list before the database leaves SQL 2000 - you want to be as confident as possible. Corruption has not stopped since 2000, if someone starts deactivating material in the system tables instead of using the correct commands, it will give you the mare once migrated.

If you rebuild indexes, then a repeated sample of exec sp_updatestats will give you worse statistics for your indexes, since they have already been updated by rebuilds. Added additional data may need to be updated, but do it individually, do not kill the index statistics for them.