Post reply

Checklist for moving a database to another server

  • May 29, 2007 at 5:03 pm

    #75153

    I'm going to be retiring one of my servers and migrating its databases to another box.  They're both SQL 2000 and are pretty much identical in configuration, so no conversion issues.  Since the drive mapping will change, my intention is to create the DB on the new home then restore a backup.

    Anyway, I've written the following checklist to try and cover as many things as possible.  Any opinions?

     

    Procedure To Move A Database Between Servers

     

     

    1. Identify database.
    2. Identify any application software installed on server that will be needed (for example, Laserfiche needs OTG software on the server).
    3. Database maintenance:

    1. Clean DBCC run.
    2. Clean/fully verified backup.
    3. Run table row count script, save results in Excel for comparison.
    4. Script out jobs for DB.
    5. Script out users, logins, and roles for DB.
    6. Run the following: “select * from sysobjects order by type, name”.  Save the output and note the record count.
    7. Run monthly config script, print for DB. (details server/database config)
    8. Run the following stored procedures, save the output for reference:

                                                                  i.      sp_helplogins                           --use @master

                                                                ii.      sp_helprole                              --@db

                                                              iii.      sp_helprolemember                 --@db

                                                              iv.      sp_helpsrvrolemember            --@db

                                                                v.      sp_helprotect

                                                              vi.      sp_helpuser                             --@db & master

                                                            vii.      sp_server_info

    1. Install any identified application software from 2a, patch if required.
    2. Copy backup to new server.
    3. Create new DB, follow config script for options.
    4. Create backup devices for new DB.
    5. Restore DB from backup.
    6. Recreate server logins, database roles and users as required.
    7. Run table count script, compare results with 3c.
    8. Run job scripts from 3d to rebuild the jobs.  Run the jobs to test.
    9. Repeat script in 3f, compare counts, reconcile differences.
    10. Check Tivoli backup config for new location.

     

    For further info, there are Microsoft Knowledge Base papers at http://support.microsoft.com/kb/314546/ (How to move databases between computers that are running SQL Server) and http://support.microsoft.com/kb/246133/ (How to transfer logins and passwords between instances of SQL Server).

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • May 29, 2007 at 8:39 pm

    #709536

    Some components to check:

    Local and Global Security Policies (lock pages in memory, etc).

    Local windows accounts.

    Local windows security groups and group membership.

    Windows Services with start-up type and security.

    Directory structures, shares and share security.

    Windows Scheduled tasks.

    Boot.ini for any switch - /3GB

    Check the port of the old SQL Server in case it is not 1433 or named instances are used and a fixed port was configured.

    SQL Server startup trace flags such as:

    -T825 Enable NUMA support

    -T826 don’t use large pages for buffer array

    -T827 enable Super Latches

    -T888 Use locked pages for buffers

    -E increase the number of consecutive extents allocated per file to x

    All process that connect to the server/database will need to have connection specifications revised. Consider running a SQL Server Trace for a few weeks prior to the migration to identify all login/host combinations that will need to be revised. This may identity linked servers or DTS packages on other SQL Servers that reference the old server/database that wil need to be revised. Alternatevly, if the old server is being retired, consider using a DSN Alias as a temporaty work-around to reduce the risk of missing a connection revision.

    DTS Packages - a migration tool is available at http://www.sqldts.com/242.aspx

    DTS INI files

    Command files

    Linked Servers - a migration tool is available at http://www.codeproject.com/cs/database/ScriptLinkedServers.asp

    The logins should be created before the database is restored.

    When the database is restored from backup, a new database will be created containing the prior DB configuration and all objects including security, so there is no reason to create and configure a database before performing the restore.

    Before performing the backup, insure that there are no db connections and that after the backup completes, there is no possibility of any updates to the old database.

    If the database is large, consider performing the DB restore in advance with the NORECOVERY option and then later restoring with subsequent transaction log backups.

    SQL = Scarcely Qualifies as a Language

  • May 30, 2007 at 10:44 am

    #709756

    Thank you, Carl.  You caught a lot of items that I hadn't considered.  A lot of them don't apply, but a lot of them do.  Right now none of our servers have >2gig ram, but I'm planning on getting the new box upgraded to 4gig.  Fortunately none of the databases being moved are too large, the largest are a 6gig and a 2gig, all of the rest are pretty small.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

  • May 30, 2007 at 1:24 pm

    #709802

    Do your agent jobs alert operators via email? Verify whether they use Outlook mail or smtp. Make sure you have the accounts set up.



    Michelle

  • May 30, 2007 at 1:33 pm

    #709805

    The funny thing about that is that the server that I'm moving from has job results configured to mail, but the box doesn't have mail installed.  The one I'm moving to has mail installed, so I might have to change accounts, but I'm already in good shape there.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply