Database Server Upgrades – The Plan, The Template, and The Task List

  • Comments posted to this topic are about the item Database Server Upgrades – The Plan, The Template, and The Task List

  • Thanks Bill,

    For sharing such a good Upgrade plan Specially the Excel sheet you have uploaded.

  • Thanks for the good article! we are planning a server upgrade in the near future and it will become very handy 😀

  • Great article Bill. As with most good articles you read it and say "Yeah, that makes sense", but then you realize how many times you leave out some of the steps.

  • I can't agree more with all of the above, good article. I posted a reply to an how do I move to a cluster once, let me find it....

    Here is the answer I gave. It is relevant whether you are moving to newer, faster tin or a cluster.

    Moving Production Databases from a stand alone server to a cluster is not much different in moving dbs to a new stand alone server. (because you are upgrading the tin for instance). I've got a check list some where, let me dig it out...

    found it. This covers most (I can't guarantee all) things to think about.

    Task_Name

    Cluster Config

    Check current Active-Active config ok

    Convert to Active-Passive

    Test Active-Passive Fail-over

    Cluster Available

    Install Oracle Driver for Linked Server (If required)

    Install JDBC Exteneded Stored Procs (If required)

    anything else specific to your site?

    Create mail profile

    Install Outlook

    Configure SQL mail

    Successful SQL Mail Test

    Cluster Config Complete

    Migration Prep

    Identify all Dependent DBs to migrate

    Check for Original Server references in views, SPs etc.

      Create script to update relevant objects found above, (if any)

      DTS Packages

      Identify all packages to migrate

      Manually "Save As" packages to NEW server

      Update any affected pointers

      Scheduled Jobs

      Identify all Jobs to be migrated

      Copy jobs to New Server

      DO NOT COPY JOBS THAT RUN DTS PACKAGES. Reshedule packages instead

      Update any affected pointers

      Linked Servers

      Copy linked servers

      Test linked server

      Security & Errs

      Copy Server Level logins

      Copy DB Logins

      Test accounts

      Copy User defined err messages (I always forget this)

      New Backup Structure

      Create new backup structure on your backup server

      Terminal Servers

      Identify all reg settings pointing to old server (If your app stores settings in the registary)

      Create reg update pointing to new server

      New Server DBs Preperation

      Copy Migrating DBs to New Server (For test purposes)

        Maintanence Plans

        Recreate System DBs backup plan

        Recreate Sys DB optimisation plan

        Recreate User DB backup plans

        Recreate User DB Integrity & optimisation plans

        Test plans via Scheduled Job

        Copy Old Server alerts - Check for Old Server references

        Migration

        Terminal Servers

        Apply reg update to all TS's

        Update ODBC setting from OLD Server to to New

        Run your app from ***EVERY*** TS

        Disbale old server Migrated Scheduled Jobs

        Isolate Migrating DBs

          - place in Read-Only mode

          Backup migrating DBs

            Restore Migrating DBs to New Server

              Check in FULL recovery mode

              Run any UPDATE scripts

              Enable New Server Scheduled Jobs

              Update any DTS connections

              Post Migration Checks

              Terminal Servers

              Run your app .exe from EVERY TS

              Isolate dbs on old server, or better, turn it off.

              Sunday Morning

              Check maintenance task completed successfully

              Check Scheduled Jobs completed successfully

              Check backups copied to SQLBackups share

              MIGRATION COMPLETE - CLUSTER GO LIVE

              I'm sure this is not complete, but hopefully it is a good starter for 10 🙂

              Any one else want to add to it?

              HTH

              Dave J


              http://glossopian.co.uk/
              "I don't know what I don't know."

            1. Excellent Job, Bill!

            2. Outstanding article. This is an essential task that all DBAs do eventually have to do.

            3. Very detailed. I am going through a server consolidation this Sunday with some 70 databases and two machines. (We need a stressed icon.)

              Also we have web site monitoring, so that will have to be turned off so our phones aren't going nuts during the outage.

              Just want to add that I will have profiler running to proactively look for problems and fix them.

            4. Thanks to everyone who read and commented on my article. I hope that it is useful for other DBAs who are developing an upgrade process. Also, after searching the internet, I did not find a database server upgrade template for SQL Server. My desire was to place a standard template on the internet for the SQL Server community to use in their upgrades.

              Bill Richards

            5. Thanks Bill. Excellent list. Will be very handy when upgrading to 2008 version.

              SQL DBA.

            6. Very useful information.

              Thanks.

              ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
              Sometimes, winning is not an issue but trying.
              You can check my BLOG
              [font="Arial Black"]here[/font][/url][/right]

            7. I am not sure if I can use the same plan. But, I am looking for upgrade plan that occured twice a year. Once in April and the other in October. I am looking for a template to have high level milestones and the same template will be used for future upgrade.... Please HELP

            8. DTS Packages

              Identify all packages to migrate

              Manually "Save As" packages to NEW server

              Update any affected pointers

              Scheduled Jobs

              Identify all Jobs to be migrated

              Copy jobs to New Server

              DO NOT COPY JOBS THAT RUN DTS PACKAGES. Reshedule packages instead

              Update any affected pointers

              Sorry for digging up an old thread but can I ask why it is necessary to reschedule DTS package jobs rather than run the scripted job on new migration instance?

              Thanks

              qh

              [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]
            9. when you upload a DTS package, it gets given a GUID. The jobs refer to this GUID, which will be different on the new server, as it is a 'new' package. one workaround is to edit the job to refer to the dts name instead of Id, but I think it is easier to re-schedule

              HTH

              Dave Jackson


              http://glossopian.co.uk/
              "I don't know what I don't know."

            10. Cheers David!

              Rgds,

              qh

              [font="Tahoma"]Who looks outside, dreams; who looks inside, awakes. – Carl Jung.[/font]

            Viewing 15 posts - 1 through 15 (of 15 total)

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