(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Over the last few weeks I have done a lot of work in the area of Always On Availability Groups, and therefore I had also the chance to test drive the Automatic Seeding option that is available beginning with SQL Server 2016.

In today’s blog posting I want to give you an overview about what Automatic Seeding is, how you can use it, and in which specific problems you can run into when using it (it’s not that straightforward as I have unfortunately seen…)

Automatic Seeding – what?

When you have already worked with Always On Availability Groups, you know for sure that you have to prepare somehow the databases on the secondary replicas that you want to join into an Availability Group. Until SQL Server 2016 there were only the following 3 options available:

• Full Database and Log Backup
• Join Only
• Skip Initial Data Synchronization

Automatic Seeding  – how?

The idea behind Automatic Seeding is quite simple: SQL Server sets up a newly added secondary replica (or a new database in an Availability Group) completely on its own by performing the following steps:

Automatic Seeding – possible problems

Up to now everything sounds quite interesting and promising, but there are a few possible problems that can (or even will) arise in combination with Automatic Seeding. One of the biggest challenges is that the Availability Group on a newly added secondary needs appropriate permissions to be able to create the database(s) that are afterwards seeded from the primary replica. To grant that permission you have to execute the following T-SQL statement after you have joined the Availability Group on the secondary:

ALTER AVAILABILITY GROUP [TPC_AG] GRANT CREATE ANY DATABASE
GO

To make things more complicated, there is a known issue as of SQL Server 2016 SP1 CU2 where a secondary replica must wait 3 minutes to allow the Availability Group to seed the database before executing the ALTER AVAILABILITY GROUP statement – as described here by Microsoft.
If the Availability Group has not granted the correct permissions, you will also see the following error message in the SQL Server log of the secondary replica:
Local availability replica for availability group ‘TPC-E_AG’ has not been granted permission to create databases, but has a SEEDING_MODE of AUTOMATIC. Use the ALTER AVAILABILITY GROUP … GRANT CREATE ANY DATABASE command to allow the creation of databases seeded by the primary availability replica.
In addition to the permission issue you also have to make sure that the disk layout on the secondary replica is exactly the same as on the primary replica. The folder where the database is created must already exist and be the same as the path on the primary replica.
If you have to troubleshoot Automatic Seeding, SQL Server also gives you with 2 DMVs (and a lot of different Extended Events) a great insight into the inner workings of seeding:

• sys.dm_hadr_automatic_seeding
• sys.dm_hadr_physical_seeding_stats

Summary

Automatic Seeding is a great new addition to Availability Groups in SQL Server 2016. Unfortunately there are a few side effects that you have to be aware of when you use Automatic Seeding. As soon as you have resolved the permission problem (also make sure to wait at least 3 minutes when you have added a new replica!) everything works quite smoothly.

If you want to know more about the basics of Availability Groups in SQL Server, you can also read my blog posting series about it that I have written a few years ago:

• Availability Groups – Part 1
• Availability Groups – Part 2
• Availability Groups – Part 3
• Availability Groups – Part 4

Thanks for your time,

-Klaus