Adding a database to an Availability Group (AG) in SQL Server can be a complex and time-consuming task. There are several manual steps and prerequisites involved, such as taking a backup of the database from the primary replica and distributing it across all other replicas in the AG. However, these tasks can be challenging due to network restrictions, limited disk space, and potential issues with backup/restore chains.

Fortunately, SQL Server 2016 introduced a feature called Automatic Seeding, which simplifies the process of adding databases to an AG. With Automatic Seeding, when you create a database on the primary replica and add it to the AG, the feature automatically communicates over the database mirroring endpoint and copies the database to all the secondary replicas.

To enable Automatic Seeding, there are a few requirements and steps to follow:

1. The data and log file paths must be the same on every SQL Server instance participating in the Availability Group.
2. The AG must be configured for automatic seeding.
3. The databases in the AG must be in the full recovery model and have a current full backup and transaction log backup.

To enable Automatic Seeding on each replica, you need to grant the AG permission to create databases by executing the following script against all the replicas:

ALTER AVAILABILITY GROUP [{your_AG_name}]
GRANT CREATE ANY DATABASE;
GO

Next, switch the AG into automatic seeding mode by executing the following T-SQL code on the primary replica for each replica in the AG:

ALTER AVAILABILITY GROUP [{your_AG_name}]
MODIFY REPLICA ON '{your_replica_name}'
WITH (SEEDING_MODE = AUTOMATIC);
GO

Additionally, you can enable compression of the data stream for Always On Availability Groups during automatic seeding using trace flag 9567.

To add a database with Automatic Seeding, open SQL Server Management Studio, connect to the primary AG node, navigate to the “AlwaysOn High Availability” folder, and find the AG where you want to add a new database. Right-click on the “Availability Databases” folder and select “Add databases…”. Choose the database(s) you want to add and select the option to skip initial data synchronization.

Once the database(s) is added, you will see messages on the secondary node(s) indicating that the database(s) has been restored. You can also use the T-SQL command ALTER AVAILABILITY GROUP {your_AG_name} ADD DATABASE {your_database_name}; to add a database in an AG.

The benefits of using Automatic Seeding in an AG include:

• Simplicity of the process
• Saving disk space by eliminating the need for backup files
• Saving time required for backup and restore operations

However, there are some drawbacks to consider:

• Significant network flow between replicas during the synchronization process
• Manual steps involved in the process

Overall, Automatic Seeding in SQL Server Availability Groups is a valuable feature that simplifies the process of adding databases and improves the efficiency of data synchronization. By automating the process, DBAs can save time and ensure high availability of databases in an AG.