Availability Group Automatic Seeding

Question

I'd appreciate some advice please.

I have configured automatic seeding for my SQL 2019 Availability Group. My two cluster nodes each has 8GB of RAM and 4 processors. The smaller databases in the instance are successfully added to the AG without any issues. However, a 30GB database keeps giving a warning about insufficient free disk space for data and log files when I try to add it to the AG. The data file storage has about 70GB free and the log storage about 74GB free.

I ignore the message and proceed with adding the database to the AG and got different behaviours as follows:

• Sometimes it appears to have successfully added to the AG, as the SSMS dashboard shows the AG as healthy. However the sys.dm_server_hadr_automatic_seeding DMV shows seeding status on one node sometimes as COMPLETED (failure_state_desc = NULL) while the other node FAILED (failure_state_desc = Check if Seeding Needed). Sometime both nodes as FAILED.

• A couple of times I had a SQL dump errorlog

• On one occasion, it appeared successful but I was unable to refresh the secondary node connection in SSMS and got a SQL dump errorlog

• On all occasions I found that the memory utilisation in Task Manager spikes to over 4GB. My SQL Server configured max mem is about 5GB.

Please note that the legacy system for this new deployment does not use AG technology and it uses much smaller sizes of disk space for data and log files and runs successfully on its older version of SQL.

My questions please:

• Is automatic seeding known to use up a lot of memory and/or disk space?
• If so, is there any way round this issue if I still would like to use automatic seeding?
• should I be looking to up the spec of the nodes or just better off using manual seeding?

I've done quite some research on the issue but will appreciate some advice, if anyone is able to provide some.

thank you

Answer 1

Is automatic seeding known to use up a lot of memory and/or disk space?

Depends on your definition of "a lot". Automatic seeding takes a VDI backup on the primary and streams the restore to (each) seeding secondary. Backups use hidden schedulers and memory outside of the buffer pool. So, pedantically, automatic seeding itself does not, however parts of the underling processes do.

If you have a tiny server (which I would personally consider 8 GB and 4 cpus a very tiny server) then this will take a greater percentage of overall resources than on a larger server (for example 24 cores and 128 GB of memory).

Additionally, automatic seeding can run 5 sessions concurrently, which means if you have 5 or more databases that need to be seeded, these will all run backups and restores, which means your system needs to be able to handle that many concurrent items.

If so, is there any way round this issue if I still would like to use automatic seeding?

Not really, no. I'm betting if you gave the server more disk space + cpu + memory, it'd seed just fine.

should I be looking to up the spec of the nodes or just better off using manual seeding?

I don't believe we can answer that question. We don't know why they are sized how they are or what underlying choices or decisions may go into this. For example, are there licensing costs, infrastructure restrictions, not enough concurrent workload to justify, etc.