2

I'm delving into SQL Server Availability Groups (AGs) and have come across a fascinating feature that seems to simplify the process of setting up replicas – the concept of automatic seeding. From my understanding, when we're configuring an Availability Group, there's an option that allows a single server to automatically seed our databases. This means that we don't have to provide a shared location accessible by both the primary and secondary replicas for initial data synchronization.

What intrigues me is the underlying mechanism of this feature. How exactly does SQL Server replicate the database to my secondary replicas without requiring a shared storage location? My initial thought was that it might utilize some form of data streaming or backup-restore operation under the hood.

Could someone with expertise in SQL Server Availability Groups shed some light on the process of automatic seeding? How reliable is this method, and are there any caveats or prerequisites that we should be aware of when using automatic seeding for database replication?

Looking forward to an enlightening discussion on this topic. Thank you in advance for your insights!

3
  • 1
    It's documented here, though it's a little light on the details: learn.microsoft.com/en-us/sql/database-engine/… Automatic seeding uses the log stream transport to stream the backup using VDI to the secondary replica for each database of the availability group using the configured endpoints Commented Dec 17, 2023 at 21:48
  • As for "how reliable is it", from my experience it's very reliable. A noteworthy downside is that you have to wait for it to sync, so if you have an async node for example with a lot of latency, it might have trouble to get synced in a decent time. Commented Dec 18, 2023 at 7:42
  • I wouldn't use it for large databases, only for <100 MB or so, maybe a GB. As Yannick mentioned, having lots of latency or a slow connection could impact how well this works. My preferred method is to manually restore a database to the secondary, bring it up to speed with log restores (WITH NORECOVERY) and then join to the AG. I have better control and for larger databases will probably get done faster. Commented Dec 18, 2023 at 13:58

1 Answer 1

4

How exactly does SQL Server replicate the database to my secondary replicas without requiring a shared storage location?

A backup is taken, per database to be seeded. This is internally streamed to a VDI client interface, which in turn streams it to a VDI client interface on the secondary replica, which is turn runs a restore command with the VDI client specified as the location.

How reliable is this method, and are there any caveats or prerequisites that we should be aware of when using automatic seeding for database replication?

It will use resources outside of the typical SQL Server resources that you're familiar with, thus if your settings for the server are to run it right up to the tipping point then this could cause issues for CPU, memory, and disk.

Full backups are streamed to the secondary replicas, this will incur a large disk hit, along with not being able to truncate the logs or take log backups while the database is being seeded. Plan accordingly or don't use on large databases if your infrastructure isn't built to handle it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.