In this article, we will learn how to implement availability group with automatic seeding using SQL Server Management Studio 17.x or above. This article will detail the steps to use of SSMS 17.x with automatic seeding and its limitations. 

In SQL Server 2016 the Availability Group Automatic Seeding functionality is available, but this new feature can’t viewed using SQL Server Management Studio (SSMS) 2016. However, you can install SQL Server Management Studio (SSMS) 17 or above in the server (or) client machine. 

Once we the installation is completed we are ready to create the availability group and seed the database to the secondary replica using the automatic seeding feature.

Specify the Availability Group Name as "AGTEST" as mentioned in the below screenshot.

Another feature to look out while creating the availability group is the database level health detection. When the availability group database is no longer online it will trigger the automatic failover of the availability group. You may turn on the database level health detection and per database DTC support later if you have missed it while creating the availability group.

You need to check database that will be part of availability group.

Select the replicas which are part of the availability group. Click on "Add Replica" to add the secondary replicas and then select Automatic Failover option, Availability Mode, and Readable Secondary options. 

You need to specify the backup preference and listener according to your requirements. There are four options to perform the initial data synchronization. We are only discussing about the automatic seeding option in this article.  By selecting the "Automatic Seeding" option, the database is created and data is synched automatically to all secondary replicas.This eliminates the need to perform the manual database backup and restore to the secondary replicas.

In SQL Server Management Studio 2016, you won’t be find the option to select the automatic seeding.

Check the results for the availaibility group validation. The listener is not created as part of this demo and that is the reason why there is warning message. Fix any validation related issues and then click on "Re-run Validation".

The default database data and log file should be same on all the replicas, otherwise you will see warning message.

Once you have all the steps completed, you can click on "Finish" button to finalise it.

Once the setup is completed, you will see the message in the below screenshot.

You can still create availability group with automatic seeding using T-SQL on SQL Server Management Studio 2016.

  --- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE.
  :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