October 24, 2018 at 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?
"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]
October 24, 2018 at 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
October 24, 2018 at 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.
October 24, 2018 at 2:07 pm
b4 u begin, make sure apps are not using depreciated features.
October 24, 2018 at 6:51 pm
Admintering Databases - Wednesday, October 24, 2018 12:48 PMI 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
Change is inevitable... Change for the better is not.
October 24, 2018 at 7:30 pm
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.
October 25, 2018 at 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).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 25, 2018 at 5:06 am
chuck.forbes - Wednesday, October 24, 2018 1:08 PMDetermine 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]
October 25, 2018 at 5:10 am
Chris Harshman - Wednesday, October 24, 2018 2:02 PMAre 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]
October 25, 2018 at 5:11 am
goher2000 - Wednesday, October 24, 2018 2:07 PMb4 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]
October 25, 2018 at 5:22 am
Grant Fritchey - Thursday, October 25, 2018 1:43 AMStrong 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]
October 25, 2018 at 7:41 am
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
Change is inevitable... Change for the better is not.
October 25, 2018 at 12:51 pm
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 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply