Good day Paul and welcome tot he QnA forum
We are looking to upgrade
If I may, before providing the solution which best fit your question, I would like to give my 2 cent on upgrading version in general.
The short version is that I HIGHLY recommend NOT to upgrade existing running version of SQL Server! For most of the same reasons, I will recommend NOT to migrate your existing system automatically to a new server.
Why?
Upgrading is not a daily task and usually it is done after the system alive for very long time (years). In a system that has existed for so long there are always holes and points that can be improved and maybe even points where we regret decisions that have been made in the past. Basically this is why applications have new versions.
During that time you learned the system, you know what you need differently, you know where is the bottleneck in performance, you know what the size of the files which you have now and you know what are flows in the architecture.
MOVING TO NEW SERVER/VERSION IS A HUGE OPPORTUNITY TO IMPROVE THE SYSTEM!
Don't miss this opportunity as it will probably not come back for a few years!
This is the time to stop -> rethink -> redesign the system -> redesign the database and related app
Start a new database after you design the system and use the experience you have to configure the server and database better.
If you are not familiar with the SQL Server internals then you should read and learn a bit more in order top apply the best design. For example, how many Virtual Log File (VLF) do you have?!? I bet that is this is a big database or even medium then you have way way too many! If the database log file growth during time in multiple chunks then it is probably have hundreds of VLF and it needed to be fixed. Now that the database live for long time, you know what is the size of the file should be. You can create a new database and your log file will have only
using SQL Server 2012 and earlier versions SQL Server uses the following Algorithm each time the log file growth:
If the Log growth <= 64 MB, then 4 VLFs are created each time
If the Log growth between 64 MB and <= 1 GB, then 8 VLFs are added
If the log file growth in 1 GB or above then 16 VLFs are added.
Your file probably started as a few MB and what is the size today? How many times did i6t growth? When you start a new database then you can design the file and have a 100 GB file with only a few VLFs.
https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver15
SQL Server 2019 by the way improve the way the file is growth and have less issue regarding the VLF - obviously if you move the current files then you will not enjoy this ability as your file already "ready".
The above is only one example of hundreds of things that you can do better now that you already familiar with your system and it is live for years.
Therefore, my recommendation is: Don't miss this opportunity as it will probably not come back for a few years!
Design your system from scratch and build it according to your experience on the live system.
Back to the question and against my recommendation
Direct upgrade of the running system to a higher edition even if you are using higher version (as in your request: 2008R2 Enterprise -> 2019 Standard) is not supported since higher edition might include features that are not supported in the lower edition. So you need to choose a different solution and here is an option which provide the most close to your request probably.
Option 1:
Base your solution on replication
(1) Replication between SQL Server 2008 R2 and SQL Server 2019 is supported
https://learn.microsoft.com/en-us/sql/relational-databases/replication/replication-backward-compatibility?view=sql-server-ver15
(2) Replication from Enterprise edition to Standard edition is also supported (Distributor should be the Enterprise edition) - you might need to make some small fix if you used features that are not supported in Standard edition.
Note! SQL Server 2008 R2 End of support was in 2019! I did not try (1) + (2) together and I am not sure if this is support. You can by pass this using a Intermediate stage using developer version.
As much as I understand the following procedure is prefect for using developer version as we do not use it in production but only for the developing of the system (during the migration), but YOU MUST CONFIRM THAT THE LICENSE FIT AND NOT USE THE DEVELOPMENT IN PRODUCTION.
Note! SQL Server 2008 R2 Developer Edition is not free (it cost only about 50$), but starting from SQL Server 2014 Developer Edition is free.
If the license fit then use SQL Server 2014 Enterprise Edition as Intermediate stage and than do the replication to a full license SQL Server 2019 standard.
BEFORE YOU START BACUP EVERYTHING AND MAKE SURE THT YOU CAN GO BACK IF NEEDED!
This is NOT a recommendation as I wrote in the beginning of the message but an option that you can test if you want against my recommendation to redesign the system and start from clean machine/installation system.
(1) Upgrade the current server SQL Server 208R2 Enterprise Edition to SQL Server 2014 developer Edition
(2) Upgrade SQL Server 2014 developer Edition to SQL Server 2019 developer Edition
(3) Create replication from the SQL Server 2019 developer Edition to the SQL Server 2019 standard Edition
(4) bring the SQL Server 2019 standard Edition to production
This way, you do not need to buy any edition which you do not need.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 76%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EPR%3C/text%3E%3C/svg%3E)
