A quick guide to SQL server 2016 direct seeding
The Feature:SQL Server 2016 SP1 introduced direct seeding for Always ON Availability Groups. This is a short guide to understand what it is and some warnings when using it.
The Problem:In previous versions than SQL Server 2016 , in order to configure your "always on" replica, you had to backup your database, then take a couple of transaction logs, then copy these files to the target server, restore with no recovery option, then open up the wizard and configure your availability group. If you have lots of databases, this is a slow process that takes a lot of administrative efforts.
The Solution:This new feature works like this: you configure an always on group, and every time you add a database to that group, it will immediately show up in the replicas in the form of an empty database, then SQL Server will automatically take care of filling it up in an asynchronous way. This means that manual backups/synchronization isn't needed. This feature was introduced by 2016 SP1 and is known to only work through TSQL, no good GUI yet. So make sure to patch your SQL Server 2016 to use direct seeding. For using this feature you need to configure your "Always ON Group" by TSQL, last release of SSMS 17.1 (Management Studio) now supports GUI, however it is limited and won't support changes to your existing group (at this stage I strongly recommend to stick with TSQL commands). This feature turns creating secondary replicas into a really simple task. When you create your availability group you just need to add the "SEEDING_MODE" option:
CREATE AVAILABILITY GROUP AGG01
WITH ( ENDPOINT_URL = N 'TCP:// DB1.MyDomain.COM:5022' ,
FAILOVER_MODE = AUTOMATIC ,
AVAILABILITY_MODE = SYNCHRONOUS_COMMIT ,
BACKUP_PRIORITY = 50 ,
SECONDARY_ROLE ( ALLOW_CONNECTIONS = NO ) ,
SEEDING_MODE = AUTOMATIC )Then you just need to add a database to the group like this: ALTER AVAILABILITY GROUP AGG01 JOIN ALTER AVAILABILITY GROUP AGG01 GRANT CREATE ANY DATABASE; GO
ALTER AVAILABILITY GROUP AGG01 ADD DATABASE DB1;