April 27, 2022 at 12:00 am
Comments posted to this topic are about the item Migrating a Large Database
April 27, 2022 at 6:20 am
If your DBs are on-prem and already on at least SQL2017 a useful way to migrate to a new SQL version is to use a Distributed Availability Group (dAG).
You can have multiple dAGs going from your primary server, each to its own unique target. In the past the place I worked at used a dAG to synchronise data between the primary and BCO sites. When they wanted to upgrade to SQL2019 a new dAG was set up from whatever was the primary site to a new SQL2019 server. The read-only secondary feature helps with verification that all data is present. At cut-over time the dAG to SQL2019 was failed over and DNS aliases updated to point the applications to the new environment. After cutover a new dAG was set up from the new primary to a new BCO server and the data allowed to synchronise. This all helped both reduce downtime for the cut-over, and exposure to no working BCO, to a minimum.
Before the cut-over was done there had been extensive testing of the apps on a scratchpad SQL2019 environment so confidence was high everything would work ok. It had also been a few years since app connection strings had been changed to use DNS aliases instead of actual server names. As always there were some performance differences between the two versions. Most processes run in statistically the same time, about 30% were faster, but about 5% much slower. Most of the slow stuff critical to response time could be fixed on the old platform, but some went into technical debt to be fixed after the upgrade. They will likely use a similar plan to upgrade to SQL2022
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 27, 2022 at 7:50 pm
I think AGs are great, but as you noted, testing is important. I've backup/restore elsewhere and verify things work on the new version. That's really important. I'd also set up a DAG and failover a few times without clients to be comfortable that this works.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply