SQL Server Performance

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

Discussion in 'SQL Server 2005 General DBA Questions' started by jaybee, May 23, 2008.

  1. jaybee New Member

    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.
  2. MichaelB Member

    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
  3. satya Moderator

    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 [:)])
  4. colinr New Member

    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
  5. MichaelB Member

Share This Page