VLBD seeing in AG

Question

What is best way to initiate a 2 TB database on seondary server for SQL server Availability groups.

is automatic seeding a good option.

i dont want to use join only for some reason specifc to the environment.

what are the consequences of using automatic seeding, does the drive space get full if there is backup restore operation performed in the background.

Answer 1

Hi @NeophyteSQL ,

>is automatic seeding a good option.

When we adding a VLDB to SQL server AG, Join only option is a better choice than Automatic seeding. But this is not an absolute answer. Automatic seeding may or may not be practical to initialize a secondary replica, depending on the size of the database, network speed, and distance between the primary and secondary replicas. Refer to MS document.

>i dont want to use join only for some reason specifc to the environment.

If you still want to use Automatic seeding option to add VLDB to SQL AG, you can enable compression of the data stream for Availability Groups during automatic seeding. Turning on compression reduces network bandwidth and possibly speeds up the process, but the tradeoff is additional processor overhead. To use compression during automatic seeding, enable trace flag 9567. Refer to MS document Tune compression for availability group.

>what are the consequences of using automatic seeding, does the drive space get full if there is backup restore operation performed in the background.

Yes, when the DB is very large, the log file will growth to be large. During seeding, the transaction log on the database at the primary replica continues to grow and cannot be truncated until automatic seeding of that database is complete. The transaction log can then be truncated using a transaction log backup.

---

If the response is helpful, please click "Accept Answer" and upvote it, as this could help other community members looking for similar queries.