I have finally been given the green light to upgrade my SQL instance (starting with DEV environment). I have a stand alone instance, no replication. I'm thinking the best way to upgrade is in place rather than a new instance and migrate. I have found documentation for each method, but little documentation on why one would proceed with one or the other. Please share your thoughts on which would be best. Steve
April 13, 2021 at 6:06 pm
One big reason to do a migration method is that you have a quick and easy rollback method.
One big reason to do an in-place upgrade is no duplication of data.
There are pros and cons to each method. In place upgrade is a 1-way upgrade, you don't need to migrate data, you don't need to re-create logins or map their permissions to users, or set any instance level configuration options (unless you want to change them after upgrading). BUT if problems arise, it is a 1-way upgrade and you are "stuck" on the newer version. Technically, you could downgrade from backups taken prior to upgrading, but it is a slower and more painful process to revert.
With a migration upgrade, in the event things break horribly or (worst case scenario) the instance doesn't start after the upgrade, you still have the old one to fall back on. But the user database files are going to get upgraded and won't work on the old system, which is why I said you get duplication of data - you have 2 copies of the user databases - one at 2017, one at 2019. You also get the headache of informing applicable end users of the instance name change which can be painful. You also have to re-setup your instance level configuration.
In place upgrade is more transparent but longer to restore to current state if things don't go smoothly. Migration upgrade is easier to roll back but harder to coordinate with all affected parties.
My preference is ALWAYS a migration approach because you get the "fresh" feeling of the instance and it forces you to review the instance level configuration to make sure it still makes sense. Is MAXDOP set appropriately? what about Cost threshold for parallelism? Are your databases in the proper compat levels? Do you REALLY need all of those linked severs? Should we really migrate ALL of these jobs over?
Plus, the migration approach gives you a good chance to test applications after the migration prior to having the enterprise hammer the server.
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.
April 13, 2021 at 7:02 pm
Thanks for the insight Brian. Some more information about the rollback is that it is a VM. I can stop the SQL service, disable it, and take a snapshot. I think the snapshot would be a valid rollback option with SQL Services all disabled. Also, I have a Test machine for Development, and a QA instance that are mirrors of PROD that will be guinea pigs and completed weeks before production. Our environment is very straight forward and we use all 20 of our jobs, and in my case, I will not need a cleanup. As a sys admin for the first years of my career, I do appreciate a "clean" install for sure though (like you are saying). Registry gets old on an old server. Also, I ran the MS Migration assistance and set it from 2017 to 2019, and all databases came up green without issues. With these additional details, I'm leaning towards the in place upgrade. Do you agree with this new information? This is my first time upgrading, so I appreciate a sound board!
Personally, this sounds like a good fit for an in-place upgrade then. You get to skip over the overhead needed for a migration install and you sound like you have the risk of rollback covered too.
Only thing I am not 100% certain on with that VM snapshot would be if SAN disks or attached storage would be included in the snapshot. Why this MAY matter - if you store the MDF and LDF files on a different disk, when you upgrade the instance, it will upgrade the databases and you may have some snags rolling those back.
If you have the time and resources, I would test the upgrade and then test a rollback so you can be aware of any problems that MAY arise and how to correct them. Plus it gives you a good chance to document the whole process as you go.
The above being said, if your 2017 version was originally a 2000 instance that was upgraded to 2005 then 2008 then 2008 R2 and so on, I would recommend doing a fresh install as there is likely a lot of fluff in there that is no longer needed. But if 2017 was the most recent "fresh" install and you want to go to 2019, I think that should be pretty safe.
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.
April 13, 2021 at 8:32 pm
Thanks again for the great points. I will be able to test the snapshot and I believe the SAN disk being on a different SAN device could be a problem - I will look into that to ensure the snapshot will be able to rollback the other disks as well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply