June 11, 2020 at 7:34 pm
so we have a side by sideupgrade planned.
on the source , to stop connections , can I change it to READ ONLY , backup the DB on source, copy onto destination and RESTORE on the destination / change to READ WRITE , after the app upgrade is done .. I hope no data will be missing by this ?
DB's are small in size and about 10 DB's need to moved from SQL Server 2008 R2 to SQL Server 2019
please give your inputs/ ideas.
June 11, 2020 at 9:58 pm
My opinion, that should work. I see no obvious reason why it wouldn't work.
The only case where I can see it being problematic is would be if you set database A to read only and application A uses database A and B to do its work and it does a successful write to B and a failed write to A but doesn't roll back the write to B.
Might not hurt ,if you have the downtime, to put all 10of the databases on the 2008 R2 in READ ONLY (or single user) mode, take the backups, restore on 2019, update the app and then let users back into the system.
The only annoying thing I can think happening is one (or more) users don't read your outage notice and are in the system while you try to do the upgrade and you get stuck waiting for their queries to finish, or you do a "WITH ROLLBACK IMMEDIATE" and it rolls back their work which may result in a phone call.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 12, 2020 at 11:45 am
Sounds like a valid plan.
Here's an addition.
Going from 2008R2 to 2019, you're hopping the 2014 Cardinality Estimation Engine upgrade. The vast majority of your queries will run the same. A few queries will run better. A very few queries, usually big, ugly queries, but for some reason, almost always, important, big, ugly queries, will run radically worse.
To deal with this, after you get the database onto 2019, before you change the compatibility level, enable Query Store. Then let people in. Keep the compatibility level at the old setting for a while. How long is on you. Your system. A day, a week, a month. I don't know. However, at the end of that period, change the compatibility mode. You'll get all the new functionality along with the new Cardinality Estimation Engine. 2-3 queries will howl. Use plan forcing in Query Store to make them go back to the old plan.
"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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply