This is a bit long, but I want my explanation to be thorough. I'm trying to get a couple of large databases added to a new Distributed AG setup. The setup consists of 4 servers, 2 in each of 2 sites. The 2 servers in each site are contained in a Local AG and then a Distributed is built on top of the 2 Local AGs.
I already set up one Distributed AG with the same number of servers, consisting of 3 small sized databases (none of 1 gb). The 'seeding_mode' of both Local AGs and the Distributed AG was set to "automatic". The Local AGs and the Distributed AG were all defined ahead of time, without any databases initially in them. At migration time, while the application was still available to the users, I took a Full backup of the 3 databases involved, copied those backups to the Site1-DB1 server, and restored them using "with norecovery". We then took the app down and took a Tran Log backup of the 3 databases, copied those to the Site1-DB1 server and applied them using "with recovery", so the databases were solid on the first server. I then added each one to the Local AG on the Site1-DB1 server and the AG processes took care of copying the databases to the other 3 servers and synchronizing them. All was well and good.
I am now working on a second Distributed AG setup. However, in this case, I will have 2 databases that will be in the AGs and both databases are over 100 gb, and I have to keep the downtime of the databases to a minimum during migration. Initially, for a mock migration, I tried an approach similar to my first setup, but the process of the AG creating the database on the Site2-DB1 server is taking too much time, thus will require too much downtime for the apps.
So I started again with the AGs all defined fresh, but still not yet containing any databases. Then I left the 'seeding_mode' of the 2 Local AGs to "automatic", but changed the 'seeding_mode' of the Distributed AG to "manual", thinking that I would create the database on both of the DB1 servers, but let the AG processes create it on both of the DB2 servers. I then took a new Full backup of the two databases on their current system, copied the backup to both of the DB1 servers (i.e. Site1-DB1 and Site2-DB1) and created the databases on both of those servers by restoring the backup and specifying "with no recovery". Then I took a Tran Log backup of the databases on their current system; restored it to the Site1-DB1 server using "with recovery" and to the Site2-DB2 server using "with norecovery" so that it is still in restoring status on that server (read somewhere that was required). The databases did not yet exist on either of the DB2 servers. I then added the database to the Site1-DB1 Local AG, expecting it to synchronize the database on the Site2-DB1 server and create the databases on both of the DB2 servers, due to the Local AGs set to "automatic seeding".
However, while the database ultimately showed up in the Databases list (via SSMS, under Availability Groups) for both Local AGs on all servers, the database did NOT get created on any DB2 server and the one on the Site2-DB1 server never showed as 'synchronized'.
Can I get the AG to create the databases on the 2 DB2 servers with me creating it on the Site2-DB1 server? If so, what settings do I need to change or what commands do I need to enter on which servers? If I have to, I can also restore the databases to both of the DB2 servers myself, but then I'm still not sure what commands I need to enter on which servers to get the databases properly into all of the AGS -- and do I need to modify the seeding specifications?
Thanks,
Sheila
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(41.6, 51%, 14%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EP3%3C/text%3E%3C/svg%3E)
