Lessons Learned Updating to SQL Server 2017 AlwaysOn Availability Groups
In late 2019, a project that had been in progress for well over a year finally came to a conclusion. I had collaborated with a number of people to migrate a stand alone SQL Server 2008 and two stand alone SQL Server 2008 R2 instances. Each stand alone SQL Server was migrated to a three node Availability Group. Here are a few things learned along the way.
Automatic Seeding
First, consider using automatic-seeding if you are migrating to AlwaysOn Availability Groups. I hadn’t previously worked with anything newer than SQL Server 2014 so I was unaware of this feature. It is new as of SQL Server 2016. In this case, the old SQL Servers were migrating up to SQL Server 2017, so this option was available to us. For a couple of months leading up to the migration event there had been various tests involving the set up of the AGs. One of those tests involved restoring a full backup followed by one or more log backups over on the secondary nodes. As the migration date approached, we started experimenting with auto seeding.