Automatic Seeding in Always On Availability Groups in SQL Server 2016

(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
Both 3 options had their advantages and disadvantages. I have already seen a lot of different customer deployments where databases were prepared quite easily with a Full Database Restore and all sequential Log Restores. This works quite well for small databases, and when you don’t generate that much transaction activity on the primary replica. Otherwise it would take sometimes quite a lot of time until an additional added secondary replica can catch up with the primary replica.
 
A lot of customers are also using the Join Only option, where you are preparing the secondary replicas on their own, maybe by configuring up a Log Shipping session. This works also quite well for VLDBs. John Sterrett has blogged about this approach a few years ago.
 
Beginning with SQL Server 2016, Microsoft gives us a 4th new option how to perform the initial data synchronisation between the primary and new secondary replica: Automatic Seeding
 
Automatic Seeding in SQL Server 2016

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:

  • A Full Database Backup is performed on the primary replica through VDI
  • The taken backup is streamed through the network to the newly added secondary replica
  • The received backup is restored on the secondary replica
  • The secondary replica is finally joined into the Availability Group
This sounds at the first step quite simple and powerful. And to be honest it is quite simple and powerful!
Imagine the effort you are saving when you have an Availability Group with 9 replicas, and you want to add a new database into the Availability Group: with Automatic Seeding you don’t need to do anything, because SQL Server itself automatically seeds the newly added database across each replica automatically. You just watch, you relax, and you are able to enjoy a coffee in the mean time until SQL Server has done its job!

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:

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
The following picture shows you the content of sys.dm_hadr_automatic_seeding on my test system where I have performed a few seeding operations (and where 2 operations also have failed…):
 
Troubleshooting Automatic Seeding in SQL Server
 
The content of sys.dm_hadr_automatic_seeding is also persisted as long as the Availability Group exists.

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:

Thanks for your time,

-Klaus

2 Comments

  • Is it correct to say that the only difference between this and the “Full Database and Log Backup” option is that the backup set is not dropped to a share?

  • Klaus Aschenbrenner

    Hello Kenneth,

    Thanks for your comment.
    Yes, with Database Seeding SQL Server performs itself a VDI backup and restore operation over the network.

    Thanks,

    -Klaus

Leave a Reply to Kenneth Igiri Cancel reply

Your email address will not be published. Required fields are marked *

SQLpassion

Copyright © 2017 by SQLpassion e.U. · Imprint · Offerings · Academy · Contact · Go to Top