December 13, 2022 at 7:38 am
Hi,
Ive been playing around with the new SQL Contained AlwaysOn, and its looking really promising so far!
But one thing im missing from the documentation / my PoC, is how to restore the master database of the contained availability group.
Like we normally can do, with a bit of twerks.
The best solution that i have found, is from this guide - guide section "Reuse old Contained Availability Group system databases"
But that means i have to delete the entire availability group and then recreate it with the reuse existing system databases setting - which seems a bit overkill 🙂
What is your recommendation, on how to restore the system level databases of a contained availability group?
December 14, 2022 at 5:12 am
I believe contained database has the stored credentials and validation done on DB end. So you don't need system databases or users to be created.
MS brought in contained Db's to Always on to eradicate this issue.
Hope this is clear.
Regards
Durai Nagarajan
December 19, 2022 at 10:24 am
I believe contained database has the stored credentials and validation done on DB end. So you don't need system databases or users to be created.
MS brought in contained Db's to Always on to eradicate this issue.
Hope this is clear.
Hi,
But what if some new DBA accidentally deletes all the tables from the contained master database, how would you then restore the master database?
The only way i can think of, is to delete the entire availability group, and then create it again using the "reuse system databases" - but then i would have to sync a fresh set of data to all nodes = massive overload on the infrastructure.
December 19, 2022 at 3:29 pm
Even if it is Always on , hope you are going with Full backup , Diff Backup and Log backups for any recovery in Primary server.
I hope DBA will be given access only after 2 or 3 months to gain experience and policies, Experienced from other companies hired will not do this silly mistakes and knows to recover it.
Regards
Durai Nagarajan
December 19, 2022 at 7:53 pm
Even if it is Always on , hope you are going with Full backup , Diff Backup and Log backups for any recovery in Primary server.
I hope DBA will be given access only after 2 or 3 months to gain experience and policies, Experienced from other companies hired will not do this silly mistakes and knows to recover it.
Of course we are doing backups on our production & test systems.
But how would you restore the contained system databases, from the backups you have made?
December 20, 2022 at 4:04 am
Same as Normal DB / secondary server.
In Always on of you change primary DB with backups, do the same in secondaries too after stopping Always on traffic and start once done. This will ensure both DB's has same data.
Regards
Durai Nagarajan
December 20, 2022 at 8:27 am
Same as Normal DB / secondary server.
In Always on of you change primary DB with backups, do the same in secondaries too after stopping Always on traffic and start once done. This will ensure both DB's has same data.
Do you have a guide on this?
I normally used this one https://www.sqlshack.com/restore-an-existing-availability-group-database-participating-in-sql-server-always-on-availability-groups/
But then you actually have to delete / remove the database, which you cant in contained AlwaysOn.
December 29, 2022 at 4:35 pm
Link looks good. sorry for the delay.
Regards
Durai Nagarajan
February 8, 2023 at 6:49 pm
This was removed by the editor as SPAM
February 8, 2023 at 6:50 pm
This was removed by the editor as SPAM
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply