May 22, 2008 at 1:19 pm
Hi all,
I've got a bunch of 2K databases ranging from 10Mb to 100Gb in size, with corresponding complexity of DTS packages, numbers of logins/user etc, that will have to come off a 2K box that is to be decommissioned, 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.Run script to check that DB options identical to those at source, if minor differences found (ie torn page detection etc) change manually, if major (disk file placement) back out;
9.Run script to check that expected users/logins survived migration, if not recreate these and/or orphans programmatically/graphically (depending of course on how many exceptions found);
10.If DTS used, check for existence and functionality of packages; use DTSMigrationWizard.exe if packages non-existent at target, and if (as likely) 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 the current production boxes, and how this will affect the transfer of logins/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) or backup/Attach (or is that Detach/Restore?!?!) with the consequent possibilities 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.
May 22, 2008 at 1:46 pm
first off all dont forget to script the loggins, and note ur linked server if any.
second:
about the DTS u have to chagne the source and make it right.
about the Database size if u can get the files on an external hard disk, then plugge it on the new server to attach the new database files. this way u want use the network at all.
this one i couldnt get:
"4) Handling cutover, as the size of the OLTP data will doubtless change if the systems are currently live.
"
..>>..
MobashA
May 23, 2008 at 3:45 am
Thanks for that!
As for the 2K to 2K migration, if I use the wizards or script out the database completely, will my DTS packages also be migrated across to the 2K boxes? Or should I use a separate tool for these?
Thanks,
Jaybee.
May 23, 2008 at 4:09 am
u cant get DTS this way u have to get them some how.
..>>..
MobashA
May 23, 2008 at 9:33 am
You can add few more items to your list
1 check the compitibility of db on 2005 server after db restore from 2000 to make sure you have 90.
2. Update statistics on 2005 after db restore. As 2005 has new system views and you need all to be updated for 2005.
3. There are scripts available on this site which can help you scripting linked server of 2000.
Kindest Regards,
Sameer Raval [Sql Server DBA]
Geico Insurance
RavalSameer@hotmail.com
May 23, 2008 at 9:57 am
Jaybee,
Regarding moving DTS packages between SQL 2000 instances, I prefer to open the packages in the old instance and save them to the new instance. They're stored in msdb, so if you plan to backup and restore that in the new instance, the packages will go with it. You can also use DTSBackup 2000 from http://www.sqldts.com. It's free and it will copy all the packages at once.
For both migrations, you'll need to migrate SQL Agent jobs. Script them in Enterprise Manager and recreate them in the new instances.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply