November 11, 2020 at 1:33 pm
Hi
I guess this question has been asked before - but I failed to find best practice recommendation. 🙁
We have a SQL Server 2017 and need to upgrade to 2019.
The plan is to just install the 2019 instance on same physical SQL Server as a second instance.
Then move databases from the 2017 instance one-by-one to run on the 2019 instance.
Downtime is accepted per database. The server has less than 100 databases, ranging from 100gb to 100 mb.
The actual database and logfiles should preferably be kept on same drives as they are now.
The most simple approach I can come up with is:
Alternatively set database in single user mode - take backup and restore to SQL 2019.
But as the largest is about 100GB - I found the backup/restore unnecessary complicated and requires disk
Are there any increased risk in the process of just detach and reattach that I've missed?
Best regards
/Anders
November 11, 2020 at 4:16 pm
The actual database and logfiles should preferably be kept on same drives as they are now.
The most simple approach I can come up with is:
- Detach the database in SQL 2017
- Attach the database in SQL 2019
Alternatively set database in single user mode - take backup and restore to SQL 2019.
But as the largest is about 100GB - I found the backup/restore unnecessary complicated and requires disk
Are there any increased risk in the process of just detach and reattach that I've missed?
I see what you're trying for in general, and it makes sense. Here's some things for you to consider.
Detach has a mild risk in that if there are certain types on errors in a db, you will get an error when you try to attach it.
Attach can be a real problem if it fails, because the attach attempt updates the primary file and then you can't try the attach again with the same file. Be sure you have a copy of the primary file and/or a backup to fall back to.
Do the actual attach with a CREATE DATABASE ... FOR ATTACH, and specify only the primary file. SQL can find the rest of the files from the primary. If you try to specify all of them, it's easier to goof up one which naturally causes the attach to fail.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
November 12, 2020 at 7:53 am
Ok, what I hear is, and correct me if i'm wrong.
I can use the attach/detach process. In most cases it would work smoothly, but if it doesn't then I want a backup in place.
So the procedure would be more fail safe if I add a "full backup" step.
We have a regular backup routine with daily full backups and 10 minute log backups. But I think I would prefer to have a "just now" backup in place.
The step above will obviously add increased downtime for the larger databases. For the smaller databases I guess it will not matter much.
What I am most interested in, is a smooth transition without too much downtime (and no risk), but primarily that I don't have to double disk requirements during the operation.
November 12, 2020 at 10:25 am
Do your applications have to change their connection strings to connect to the new 2019 instance?
November 12, 2020 at 11:26 am
Don't detach attach the system DB's
You need to migrate the objects individually, logins, linked servers, jobs etc anything thats server side and not DB side.
Putting a 2017 master on a 2019 server is just asking for trouble as its going to get is knickers in a right royal twist and not know what the hell it thinks it is.
Make sure you put the full backup you do somewhere safe, remember upgrading is a one way process, if you don't like the results of 2019 then you can't go back to 2017, you would have to script everything out from 2019 and recreate the DB and play everything back in to a new 2017 database.
November 12, 2020 at 11:46 am
William, yes, if I create a new instance I would have to change connectionstrings, but that is ok. We control the clients accessing the databases, and are only a few places per database.
Anthony, yes good point. Was only thinking on moving the actual databases, but you are right - users will have to be recreated, as well as jobs and anything else defined on the server.
Obviously, the easiest solutions seems to just upgrade the existing 2017 to 2019 - and then no external changes would be needed - but nobody seems to suggest that as a good path to go. I would also expect "risk" to score high on that, if the upgrade fails on something.
November 12, 2020 at 2:27 pm
I think there is one way you can try the upgrade path.
IF your san/disk/software allows you to snapshot a given point-in-time on disk, and accurately restore everything on disk (including local drive(s)) back to that time, then you could theoretically:
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply