October 26, 2020 at 9:47 pm
We are going to migrate all our SQL Servers from 2014 to 2019. Where I can find step-by-step guide before, during, and after migration? Besides Database Migration Assistant, I could not find any. We have 80+ databases, BLOB images, using AlwaysOn, replication, SSIS packages, SSRS reports, some 3rd-party tools. We will use backup/restore methodology, but what else should I take into account? What hidden pitfalls should I be aware of?
Thanks
October 27, 2020 at 7:19 am
Jobs, logins, linked servers, audits, server triggers anything at the server level which is not done as part of backup restore of the user dbs.
Take a look at dbatools and the migration options they have.
https://www.youtube.com/watch?v=Fraig15pwxE&feature=emb_title, is a good starting point
October 27, 2020 at 10:18 am
The big tasks are the SSIS packages and SSRS reports. Often they require quite a bit of rework. I'd start checking those out as soon as you can.
The other stuff should move reasonably well, almost transparently.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
October 27, 2020 at 12:28 pm
Testing, testing, testing, then test some more.
One thing many people don't know about when upgrading SQL Server using the modern versions (2016+), is Query Store. This is a major tool for the upgrade process. You move a given database from your old server to your new server. The compatibility level is at the older version (or even an older version if it wasn't updated on your server). Leave it alone for the moment. Turn on Query Store. Run the system for a bit (you decide how long is best for a given system, a day, a week, a month, it depends on the system). Now change the compatibility mode. Query Store will help you identify queries that have gone off the rails due to changes in the optimizer, cardinality estimation engine, or just SQL Server itself, through the Regressed Queries report. You can then decide what to do about the query, including using Query Store to force the old plan until you resolve whatever problems were reported.
All this is in addition to the other things reported by everyone else.
----------------------------------------------------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
October 27, 2020 at 2:00 pm
Thank you Grant, and all, for your advises.
What I also heard is that we need to collect a database workload from production and apply it on our testing environment to find out SP's and queries that are running slower than in old one. Is this still the case? If yes, how to collect the whole workload?
Thanks
October 27, 2020 at 3:04 pm
Thank you Grant, and all, for your advises.
What I also heard is that we need to collect a database workload from production and apply it on our testing environment to find out SP's and queries that are running slower than in old one. Is this still the case? If yes, how to collect the whole workload?
Thanks
Absolutely a possibility. I wouldn't say it's a requirement, but if possible, sure, I'd do this.
Now, there is no easy way to do it. Distributed Replay is how I've done it in the past. It's how I would do it right now. You can use Trace or Extended Events to capture the load. Then, replay that using Distributed Replay. Do a search for docs & blog posts. They're out there. It's a bear to get it up and running the first time. Then, it's easy to use. However, you have to get over that install and config hump, which ain't easy.
Good luck on that.
----------------------------------------------------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
October 28, 2020 at 11:55 am
This was removed by the editor as SPAM
November 10, 2020 at 3:26 am
This was removed by the editor as SPAM
November 18, 2020 at 6:22 am
This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available: Supported Version and Edition Upgrades for SQL Server 2019
Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential blocking issues such as deprecated features that you may need to resolve before you can migrate.
SQL Database Recovery Expert 🙂
February 4, 2022 at 3:52 pm
hello ,
i have sql 2017 installed in my machine i want install 2019 , should I delete 2017 before ?
thank u
February 4, 2022 at 4:16 pm
hello ,
i have sql 2017 installed in my machine i want install 2019 , should I delete 2017 before ?
thank u
It's generally a good idea to post new questions, not post to old threads. The only people who are likely to see this are the ones who posted here already.
If we're talking about your development machine, just install 2019 right over the top of 2017. If it's a production system, I strongly advocate for a side-by-side approach. Install 2019 to a new machine, then migrate your stuff over from the 2017 machine. It's much safer.
----------------------------------------------------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
February 4, 2022 at 8:36 pm
Thank you for your feedback and sorry, next time I will post a new question
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply