Move old DBs to new existing servers.

  • I have bunch of DBs I am trying to migrate from old DB serves (2005, 2008, 2008R2) to 2016. We have enough resources on this 2016 server to host all DBs from old servers. This is the 1st time I am doing the migration so I have to be sure. This is what the plan is:
    1. Take the backup of old DBs
    2. Restore all DBs on new servers
    3.Copy all logins and create them on the new server if those logins don't already exist
    4. Change the connection string of applications to point to this new 2016 servers.  
    and that's it.
    What else am I missing?

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Determine if you want to alter the compatibility level to match with your 2016 install.
    If you aren't recreating the folder structure where you store your data and log files, then you'll need to specify the storage location in your restore statement.
    Double-check the databases to see if they're the expected recovery model. In many of the older databases I inherited, they were set to SIMPLE.

    --=Chuck

  • Are you doing these steps during off hours when no one is in the system?  If so that would be fine, if there's the possibility of data INSERTs/UPDATEs/DELETEs occurring during your migration window, then maybe you could RESTORE WITH NORECOVERY, then restore transaction log backups once you're ready for final cutover.

    Also, something to check is if there are any SQL Agent jobs related to the databases.  This is a query I use to find those:
    SELECT j.job_id, j.name, js.step_id, js.step_name,
      p.name as proxy_name, js.command, js.output_file_name,
      CASE js.on_success_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_success_step_id AS varchar(3)) END AS on_success_action,
      CASE js.on_fail_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_fail_step_id AS varchar(3)) END AS on_fail_action,
      js.retry_attempts, js.retry_interval
    FROM msdb.dbo.sysjobs j
      INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
      LEFT OUTER JOIN msdb.dbo.sysproxies p ON js.proxy_id = p.proxy_id
    WHERE js.command LIKE '%YourDBname%' OR js.database_name = 'YourDBname'
    ORDER BY j.name, js.step_id;

    Make sure you setup your database backups, DBCC checks, and any index or statistics maintenance on your new server as well.

  • b4 u begin, make sure apps are not using depreciated features.

  • Admintering Databases - Wednesday, October 24, 2018 12:48 PM

    I have bunch of DBs I am trying to migrate from old DB serves (2005, 2008, 2008R2) to 2016. We have enough resources on this 2016 server to host all DBs from old servers. This is the 1st time I am doing the migration so I have to be sure. This is what the plan is:
    1. Take the backup of old DBs
    2. Restore all DBs on new servers
    3.Copy all logins and create them on the new server if those logins don't already exist
    4. Change the connection string of applications to point to this new 2016 servers.  
    and that's it.
    What else am I missing?

    Don't forget linked servers and special providers such as "ACE Drivers", IBM drivers (if you have linked servers that point to IBM boxes), and jobs, etc.  Make sure the new box can see the old backup area, as well.  If you have SSIS, SSRS, SSAS, SSSB, or any other 4 letter word packages/jobs, good luck. 😉  If you have anything going on where the server login itself had access to certain drives/folders on other systems, you'll also need to make sure the new server login can see the same.  You may also have to ensure that you have other services installed or activated that were on the old box but not the new.  And don't forget to check who the owner of the migrated databases and jobs are because they'll change to whomever is doing the work.  If you have any "Trustworthy" or database chaining going on, you'll need to re-instantiate that because restores break that stuff quite intentionally.

    Last but not least, if you have any encryption or other types of certificates that are associated with the old boxes/databases, make sure you get all that in place and have tested the bejeezus out all of it before kill the old box.  If you also have encryption (like encrypted columns, etc) on the new box, make sure that your efforts in fixing the old stuff don't kill the existing stuff on the new box.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Once the app team confirms that they are ready, as a first step, we DISABLE app login in SQL Server.
    Then trigger a final log backup and get it restored on new servers.

  • Strong recommendation, turn on query store on the databases you migrate from pre-2014 servers to post-2014 servers (2016 or greater). Leave the database in the old compatibility mode for a while (you'll have to decide how long, a week or a month, whatever will best allow you to capture normal behavior). Then switch compatibility mode to the new version. If you experience regressions on query performance, you can use Query Store to force the plans to the old behavior without having to abandon the appropriate compatibility mode or rewrite the queries (although, you may want to do that too).

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • chuck.forbes - Wednesday, October 24, 2018 1:08 PM

    Determine if you want to alter the compatibility level to match with your 2016 install.
    If you aren't recreating the folder structure where you store your data and log files, then you'll need to specify the storage location in your restore statement.
    Double-check the databases to see if they're the expected recovery model. In many of the older databases I inherited, they were set to SIMPLE.

    --=Chuck

    I am changing the compatibility level to match my 2016.
    Data and log files are going on their own drives which we have plenty of space for. 
    All DBs will be part of AG so no simple recovery mode.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Chris Harshman - Wednesday, October 24, 2018 2:02 PM

    Are you doing these steps during off hours when no one is in the system?  If so that would be fine, if there's the possibility of data INSERTs/UPDATEs/DELETEs occurring during your migration window, then maybe you could RESTORE WITH NORECOVERY, then restore transaction log backups once you're ready for final cutover.

    Also, something to check is if there are any SQL Agent jobs related to the databases.  This is a query I use to find those:
    SELECT j.job_id, j.name, js.step_id, js.step_name,
      p.name as proxy_name, js.command, js.output_file_name,
      CASE js.on_success_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_success_step_id AS varchar(3)) END AS on_success_action,
      CASE js.on_fail_action WHEN 1 THEN 'Quit with success' WHEN 2 THEN 'Quit with failure' WHEN 3 THEN 'Go to next step' WHEN 4 THEN 'Go to step ' + CAST(js.on_fail_step_id AS varchar(3)) END AS on_fail_action,
      js.retry_attempts, js.retry_interval
    FROM msdb.dbo.sysjobs j
      INNER JOIN msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
      LEFT OUTER JOIN msdb.dbo.sysproxies p ON js.proxy_id = p.proxy_id
    WHERE js.command LIKE '%YourDBname%' OR js.database_name = 'YourDBname'
    ORDER BY j.name, js.step_id;

    Make sure you setup your database backups, DBCC checks, and any index or statistics maintenance on your new server as well.

    We are disabling all Agent jobs and might bring DBs offline, take the backup and then restore. And yes we are doing it off hours.

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • goher2000 - Wednesday, October 24, 2018 2:07 PM

    b4 u begin, make sure apps are not using depreciated features.

    Ah ha, didn't think about that. Good to know. Thanks!

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Grant Fritchey - Thursday, October 25, 2018 1:43 AM

    Strong recommendation, turn on query store on the databases you migrate from pre-2014 servers to post-2014 servers (2016 or greater). Leave the database in the old compatibility mode for a while (you'll have to decide how long, a week or a month, whatever will best allow you to capture normal behavior). Then switch compatibility mode to the new version. If you experience regressions on query performance, you can use Query Store to force the plans to the old behavior without having to abandon the appropriate compatibility mode or rewrite the queries (although, you may want to do that too).

    Didn't think about that either. Point noted. Thanks

    "He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]

  • Shifting gears a bit to the actual migration... I saved on a huge amount of down time during such a migration by restoring the full backups from the night before to the new box and then continued to do restores of the transaction logs as the day went on.  When it was time to take the apps down and do the migrations, I did "Tail Log" backups, which also takes the databases offline to ensure that no new data is being introduced and it took just several minutes to restore those.  We were able to test overnight and, if something had gone seriously wrong, all we would have had to do would be to bring the old databases back online and we'd have been back in business.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • One consideration if you had SSIS jobs running on the old servers to import data:
    The folder structure on the new server may not match. That would require some adjustment of the SSIS package(s) to adapt to the new system. For example, if in the new system all files get imported to a F: drive that did not exist in other servers. Here is where using variables in SSIS to denote drive paths comes in handy. You only update one string value (D:\myfolder to F:\myfolder for instance) and done.

    ----------------------------------------------------

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

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