During my testing about the new SQL Server 2022 feature Contained Availability group I faced an issue during the creation of the system databases on the secondary instance.

I used the wizard to create a new Contained Availability Group and chose the Full database and lo backup synchronization method to restore database on my secondary instance.
I generated the script and ran it.

I was a bit confused to receive an error message.
It looks like that the master database has to be joined and recovered before the user databases in a Contained Availability group:

Msg 47148, Level 16, State 13, Line 180
Cannot join database ‘AdventureWorks’ to contained availability group ‘AAG_Thor9091_C01’. Before joining other databases to contained availability group, contained availability group master database has to be joined and recovered. Make sure contained availability group master database has been joined and recovered, then retry the operation.

If I check on my primary, my Contained Availability Group looks good with my master & msdb system databases and my AdventureWorks database are marked as Synchronized:

But if I check the dashboard of my Availability Group or even my secondary instance I can see that my AdventureWorks database is on Restoring mode, as pointed out by the previous error, and I have no master nor msdb databases:

If I check the SQL logs on the primary instance I can see that my master and msdb database have been backed up but I have no trace of the restore on my secondary instance compare to the AdventureWorks database for which I can find a restore operation.

I dropped my Contained Availability Group and recreate the exactly same one but with the seeding synchronization method, I generate the scripts and run it:

No all is working perfectly, my master and msdb databases have been restored after some seconds to my secondary, the DMV dm_hadr_physical_seeding_stats shows that the success of the seeding method and my Contained Availability Group is synchronized on my 2 nodes.

I did those test with SQL Server 2022 CTP2.0 so no doubt that this issue will be fixed for the RTM version 😉
You can also read the blog of my colleague Steven on Contained Availability Group to have an overview of this feature.


Thumbnail [60x60]
by
Stéphane Savorgnano