Avg updating every day
The challenge with this approach is, you are expecting a software written and tested on servers available more than a decade ago to run and scale out of the box on modern high-end servers with large memory, high core density socket configuration.
Further, when you upgrade, we recommend keeping the database compatibility level to the older version with query store turned ON.As explained above, it makes more sense to upgrade your SQL Server first before upgrading your hardware since upgrading SQL Server and OS itself may provide better scale out of the box even if the hardware remains the same.In addition, with latest SQL Server version, you can leverage latest features and innovations our engineering team has built on top of some of the latest hardware technology to make the best use of modern hardware to scale up your workload.We recommended them to run DMA and assess their database schema and TSQL code to see if there are any upgrade blockers.We found that there were few stored procedures using TSQL syntax which were deprecated but it was never being used so we could ignore them.In most scenarios, customers choose to add new hardware as opposed to upgrading their SQL Server since the general perception is that cost of upgrade is high.
While before you go with this anecdotal assumption, it is important to assess and estimate the cost of upgrade.
SQL Server Upgrade Advisor gave multiple reasons to not upgrade while the team has done great job with DMA to help identify blockers for upgrade and also provide guidance on how to overcome them.
In addition, DMA assesses the databases against multiple compatibility levels in single run to provide a recommendation on which DBCompat would be a right fit for your database application.
This is where a tool like Data Migration Assistant (DMA) can be very valuable.
DMA is a complete rewrite and makeover of the SQL Upgrade Advisor tool.
Later, you can test to change the DBCompat level to the latest level and use query store to identify the queries which have regressed.