Best practices/Gotcha's when migrating 2k DB's to 2K5 box? (also 2K->2K)

Hi all,

I've got a bunch of 2K databases ranging from 10Mb to100Gb in size, with corresponding complexity of DTS packages, numbersof logins/user etc, that will have to come off a 2K box that is to bedecommissioned, and migrated/upgraded to a recently configured 2K5 box.

I'm trying to build an overall methodology, here's what I've got so far, it's by no means exhaustive or correctly ordered;

1. Audit datafile sizes, datafile placements, DB options (Autogrow/logfile placement etc);

2. Audit scheduled processes/DTS jobs;

3. Place above info into Excel S/S;

4. Run DBCC’s updateusage and checkdb before the transfer:

5. Set database(s) to Single-User mode

6. If above ok, Backup DB's and attach at target, or use Copy/Transfer DB wizard;

7. Run script at target to check that filesizes/rowcounts etc tally with those before backup, if not review for errors;

8. Runscript to check that DB options identical to those at source, if minordifferences found (ie torn page detection etc) change manually, ifmajor (disk file placement) back out;

9. Run scriptto check that expected users/logins survived migration, if not recreatethese and/or orphans programmatically/graphically (depending of courseon how many exceptions found);

10. If DTS used,check for existence and functionality of packages; useDTSMigrationWizard.exe if packages non-existent at target, and if (aslikely) non-functional, determine whether to:

- Replace failing non-upgradeable DTS package elements with their SSIS equivalents, or;
- Encapsulate the package into an Execute DTS 2000 Package, or;
- Create a new SSIS package from scratch

11. (Windows Team) re-point all users/groups to new server:


What I'm not sure about yet:

1)Whether the target server(s) will be on the same domain(s) as thecurrent production boxes, and how this will affect the transfer oflogins/DTS;

2) Whether the network can handle a .bak file of 100Gb (last time I tried just 10Gb, the backup failed!)

3)Whether to use the Copy/Transfer database utilities (if same domain) orbackup/Attach (or is that Detach/Restore?!?!) with the consequentpossibilities of having to recreate logins/users/packages.

4) Handling cutover, as the size of the OLTP data will doubtless change if the systems are currently live.

Any and all advice appreciated!!

Jaybee.

Here is what I do...
Step #Step
1Kill any connections/processes or shut down external services
2backup all DB on server to a common folder
3Export DTS packages to a common folder
4Script SQL jobs to a common folder
5Capture Logins and save results to a common folder
6Screen shot all Linked Servers
7Screen shot all Maintenance Plans
8Login to new server as SA
9Restore DBs to new server
10Change all DB's compatibility mode to 9.0 (SQL 2005)
11Run restore logins script
12Run logins auto_fix script
13Import all DTS packages from common folder
14Run SQL script to recreate jobs from common folder
15Recreate all Maintenance Plans from screen shots
16Recreate all linked servers from screen shots
17Run Reindex and Update Stats for all databases
18Run backup of all databases on new server so that tran log backups work and tlogs are truncated
19Turn on any services needed by applications and make sure SQL Agent is running

There are a few gotchas to be aware of
if you are going to 64bit 2005 at the same time the legacy providers are not supported under 64bit. not sure of everywhere this causes an issue but its definatly an issue when running/modifying Legacy DTS packages. in the same vain you need to be aware of any 32 BIT third party Driver issues
Be aware that the Reporting services reports RDL may contain code that hasnt been put through the migration wizard if it doesnt call stored procs.
Ive also found that the migration wizard doesnt identify code that does a "select distinct Order by (a field that not in the select fieldlist)" ie:

select distinct field1,field2 from table1 order by Date

this is very common code in sql2000 but not permitted any longer in 2005 for some reason yet to be explained !
I also find it helps performance of the final conversion to script all indexes with a drop and recreate
Cheers

some gotchas are included here: http://msdn2.microsoft.com/en-us/library/ms143179.aspx