Create Availability group with Automatic / Manual seeding with different paths for data and log file


Read-scale Availability Groups were introduced in SQL Server 2017 as one of AlwaysOn features that provides a read-only copy of your database for reporting and analysis purposes.


With Standard edition, since it is under the limitation of basic availability group, it can be used as a solution for disaster recovery only. More on this misleading functionality here.


My client asked us to plan a migration for their SQL server instances from Windows to Linux servers.

Our plan is to use a cross-platform availability groups to perform the migration. It’s a cluster-less mechanism.

The insights regarding the different seeding modes I will discuss here are not only relevant for Linux or for cross-platform availability group (AG) but also true for any AG created between any two or more replica servers.


SEEDING MODE


When creating an availability group (AG) in SQL server, one of the arguments is called Seeding Mode which can be manual or automatic.


Seeding mode is the mechanism in which the secondary replica(s) will be initialized.

Prior to SQL server 2016, manual seeding was the only way to do this. Meaning you had to use backup, copy, and restore of the database on every secondary replica.






AUTOMATIC SEEDING


SQL Server 2016 introduces a new feature to initialize a secondary replica - automatic seeding (or direct seeding), which seeds the secondary database files on secondary replica over the network.

Automatic seeding mechanism uses the log stream transport to stream the backup to each secondary replica for each database of the availability group using the configured endpoints.


Note! the seeding of the database is done through the network.

So, if your database is very large (above 5 TB size) or your network bandwidth is short, better reconsidering using the old fashion manual seeding mechanism.


In SQL server 2016, automatic seeding requires the same data and log file path for the database on all servers participating in the AG.


With SQL server 2017, you can use automatic seeding with different file paths as well. BUT! Your database file paths must be aligned with the instance configured default data and log paths.