In this article, we will explore how to implement availability groups with automatic seeding using SQL Server Management Studio 17.x or above. Availability groups provide high availability and disaster recovery solutions for SQL Server databases. Automatic seeding is a feature that simplifies the initial data synchronization process between primary and secondary replicas in an availability group.

Before we begin, make sure you have SQL Server Management Studio (SSMS) 17.x or above installed on your server or client machine. Once the installation is complete, follow the steps below to create an availability group and seed the database to the secondary replica using automatic seeding:

1. Launch SQL Server Management Studio and connect to the primary replica.
2. Create a new availability group by specifying a unique name, such as “AGTEST”.
3. Enable database level health detection to trigger automatic failover when the availability group database is no longer online.
4. Select the databases that will be part of the availability group.
5. Add the secondary replicas by clicking on “Add Replica” and configure the automatic failover option, availability mode, and readable secondary settings.
6. Specify the backup preference and listener according to your requirements.
7. Choose the “Automatic Seeding” option for initial data synchronization. This eliminates the need for manual database backup and restore to the secondary replicas.
8. Validate the availability group configuration and fix any validation-related issues.
9. Click on “Finish” to finalize the availability group setup.

Once the setup is completed, you will receive a confirmation message. It’s important to note that the automatic seeding option is not available in SQL Server Management Studio 2016. However, you can still create an availability group with automatic seeding using T-SQL in SQL Server Management Studio 2016. Here is an example script:

:Connect SQLTEST1
IF (SELECT state FROM sys.endpoints WHERE name = N'Mirroring') <> 0
BEGIN
   ALTER ENDPOINT [Mirroring] STATE = STARTED
END
GO

use [master]
GO

GRANT CONNECT ON ENDPOINT::[Mirroring] TO [DB\SQL2016]
GO

:Connect SQLTEST1
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO

:Connect SQLTEST2
IF (SELECT state FROM sys.endpoints WHERE name = N'Mirroring') <> 0
BEGIN
   ALTER ENDPOINT [Mirroring] STATE = STARTED
END
GO

use [master]
GO

GRANT CONNECT ON ENDPOINT::[Mirroring] TO [DB\SQL2016]
GO

:Connect SQLTEST2
IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER WITH (STARTUP_STATE=ON);
END

IF NOT EXISTS(SELECT * FROM sys.dm_xe_sessions WHERE name='AlwaysOn_health')
BEGIN
   ALTER EVENT SESSION [AlwaysOn_health] ON SERVER STATE=START;
END
GO

:Connect SQLTEST1
USE [master]
GO

CREATE AVAILABILITY GROUP [AGTEST]
WITH (AUTOMATED_BACKUP_PREFERENCE = SECONDARY,
DB_FAILOVER = ON,
DTC_SUPPORT = PER_DB)
FOR DATABASE [AGTEST]
REPLICA ON N'SQLTEST2' WITH (ENDPOINT_URL = N'TCP://SQLTEST2.com.au:5023', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)),
       N'SQLTEST1' WITH (ENDPOINT_URL = N'TCP://SQLTEST1.com.au:5022', FAILOVER_MODE = MANUAL, AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT, BACKUP_PRIORITY = 50, SEEDING_MODE = AUTOMATIC, SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
GO

:Connect SQLTEST2
ALTER AVAILABILITY GROUP [AGTEST] JOIN;
GO

ALTER AVAILABILITY GROUP [AGTEST] GRANT CREATE ANY DATABASE;
GO

By following these steps, you can successfully implement an availability group with automatic seeding using SQL Server Management Studio 17.x or above. Automatic seeding simplifies the initial data synchronization process and improves the overall availability and performance of your SQL Server databases.

Thank you for reading this article. If you have any questions or feedback, please leave a comment below.