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

Last post 05-27-2008 12:40 PM by MichaelB. 4 replies.
Page 1 of 1 (5 items)
Active Topics My Discussions Unanswered Sort Posts: Previous Next

  • 05-23-2008 4:38 AM

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

    Reply Contact

    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.

     

  • 05-23-2008 11:23 AM In reply to

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

    Reply Contact

    Here is what I do...

    Step #

    Step

    1

    Kill any connections/processes or shut down external services

    2

    backup all DB on server to a common folder

    3

    Export DTS packages to a common folder

    4

    Script SQL jobs to a common folder

    5

    Capture Logins and save results to a common folder

    6

    Screen shot all Linked Servers

    7

    Screen shot all Maintenance Plans

    8

    Login to new server as SA

    9

    Restore DBs to new server

    10

    Change all DB's compatibility mode to 9.0 (SQL 2005)

    11

    Run restore logins script

    12

    Run logins auto_fix script

    13

    Import all DTS packages from common folder

    14

    Run SQL script to recreate jobs from common folder

    15

    Recreate all Maintenance Plans from screen shots

    16

    Recreate all linked servers from screen shots

    17

    Run Reindex and Update Stats for all databases

    18

    Run backup of all databases on new server so that tran log backups work and tlogs are truncated

    19

    Turn on any services needed by applications and make sure SQL Agent is running

    Michael
    Sr. DBA - MCDBA
    See my Reader 2 Reader articles in Jan and August 2008 issues of SQL Server Magazine:)
    View Michael Berry's profile on LinkedIn

  • 05-24-2008 8:55 AM In reply to

    • satya
    • Top 10 Contributor
    • Joined on 11-05-2002
    • United Kingdom
    • Posts 22,713
    • Microsoft MVP
      Moderator

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

    Reply Contact

    Mike's list is good one to go, here is my kip:

    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;

    As long as the SQL account used from SOurce to target has permissions between the servers the process shouldn't have any issue. By default it will use the Service account and if they are dfferent domains then make sure to use similar account between these 2 servers.

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

    Are you trying to copy the file using Operating system process or performing the backup straight away from SQL Server to the disk.

    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.

    No criteria to use copy or attach method, by experience I prefer to use BACKUP or ATTACH method. Packages and logins better to script them and apply on target server.

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

    If you need to test the new server as a pre-production until you are satisfied then you can run them parallely to see what is activity, make sure to restore the database overnight between the servers. Once the complete daily & weekly processes are completed then 1 fine day take full backup on source and restore on target then switch 2000 box.

    Also I refer to the FAQ &* GOTCHAs in Upgrade on my blog - http://sqlserver-qa.net/blogs/tools/archive/2008/01/22/upgrade-blurg.aspx link.

    HTH

    (ps I'm going to blog this post too Smile)

    -Satya S K J

    SQL Server MVP



    Knowledge is Power, you will gain by sharing it. SSQA.net - Invisible contributions to the users & visible success in SQL Community.

  • 05-27-2008 8:37 AM In reply to

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

    Reply Contact

    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

     

     

     

     

     

  • 05-27-2008 12:40 PM In reply to

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

    Reply Contact

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

    Michael
    Sr. DBA - MCDBA
    See my Reader 2 Reader articles in Jan and August 2008 issues of SQL Server Magazine:)
    View Michael Berry's profile on LinkedIn
Page 1 of 1 (5 items)
Active Topics   My Discussions    Unanswered Posts


© 2000 - 2007 vDerivatives Limited All Rights Reserved.