September 28, 2021 at 5:33 pm
Hello
I have 2 database servers one as a primary and another as a secondary. I have synchronized them through availabity group settings.
The vendor of the database wants to do some updates on the database locally which means i have to take a back up of the database and send them the back up. They will do their thing and send a new updated copy. I then have to restore it on the database and get teh synchronization working again.
What is the efficient way to do it without needing to create new database instances and Availabity groups.
What I really want to do is
Really appreicate your solution/ suggestion in advance!
September 28, 2021 at 6:21 pm
You do not have to pause the availability group to make a backup. Are you currently taking backups, in which case you can simply send them your most recent backup?
Once they return the database to you, could you restore it to a different location, and compare the schema of the old vs. new database, and simply apply the changes to the old database?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2021 at 6:37 pm
Thank you Mr. John for the reply
I will first stop the front end application so user cannot change anything on the database. Then will take a back up file at that point.
Vendor will update the database and send me a new file and I need to restore it on to the existing database. I don't want to compare old and new but just overwrite existing old database with the new one.
Now if I have to restore it, I get lot of problems where MSSQL complains that Mirroring or Synchronization is set up so cannot restore.
I want to keep the Availabity group set up as is and want to restore it.
September 28, 2021 at 6:48 pm
Thank you Mr. John for the reply
I will first stop the front end application so user cannot change anything on the database. Then will take a back up file at that point.
Vendor will update the database and send me a new file and I need to restore it on to the existing database. I don't want to compare old and new but just overwrite existing old database with the new one.
Now if I have to restore it, I get lot of problems where MSSQL complains that Mirroring or Synchronization is set up so cannot restore.
I want to keep the Availabity group set up as is and want to restore it.
I'm not understanding the timeline.
If you shut off the front end, then take a backup, send it to the vendor, they make changes, send it back, and you restore it, how much downtime will that be? I'm guessing at least an hour, even with a very small database.
You cannot restore a database that is part of an availability group. You must remove it from the AG.
I would suggest to remove the DB from the AG, run a full backup, and send it to the vendor. When they return it, restore it an set up the secondary and primary again.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 28, 2021 at 6:51 pm
Why are you sending the database to the vendor.
Do you have NDAs and data policies in place with them so they don’t go and mess about with your data? This could be a GDPR/HIPPA/Legislative nightmare for you.
The vendor should be giving you an update package you apply yourself to the database/application. They shouldn’t be taking the database off you to do stuff.
But that is a digression.
You’re going to have to remove the database from the AG to then restore the database they give you back, then restore it on the secondary and readd the database back to the AG.
September 28, 2021 at 6:54 pm
If you enable automatic seeding - then you only have to restore the database to the primary and add the database back to the AG. So your steps would be:
You can enable automatic seeding at any point prior to adding the database to the AG - when you add the database back to the AG, automatic seeding will 'restore' the database on the secondary and synchronize. You may need to drop the database on the secondary and possibly remove backup/restore history for that database on the secondary - but I am not sure if that is required.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 28, 2021 at 6:59 pm
The ownder of the database is vendor and we only own the data. We do have the NDA so data is safe and vendor gurantees us both for data and database. The update package was not an option as vendor dont want to share the update procedures in the form of script but they do have thier own update engine through which the database is upgraded.
So basically what you suggest or what is possible is
I have tried a test based on this suggestion and what I get is an error. Primary is in synchronization state and no errors. But in the secondary I am getting this error.
At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.
September 28, 2021 at 7:03 pm
Thanks Jeffrey Williams
I have performed exact step that you mentioned but on the secondary I am getting this error.
At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.
When I tried to remove the database on the secondary as you have suggested through a script, it says the database no longer exists. I assumed that when you remove the database from primary AG then it get removed on the secondary automatically?
September 28, 2021 at 7:21 pm
No removing the database from the AG, leaves the database in a restoring state on the secondaries.
So you will need to manually go in and remove it if you want to go automatic seeding or restore the upgraded database over the top.
September 28, 2021 at 7:24 pm
Hello Ant-Green, you mean, I first remove the secondary AG and database manually then do the automatic seeding from primary?
September 28, 2021 at 7:47 pm
1. Remove db from the group on primary
2.delete database from secondary
3.restore Vendor upgraded on primary
4.add database to group on primary with automatic seeding.
OR
1 remove db from ag
2 restore vendor database on primary
3 restore vendor database on secondary’s with replace and norecovery
4 add database to ag
September 29, 2021 at 6:47 am
Thanks a lot Ant-Green for the reply
I have followed the exact steps but still not able to get path this error. Don't know what I am doing wrong.
At least one availability database on this availability replica has an unhealthy data synchronization state. If this is an asynchronous-commit availability replica, all availability databases should be in the SYNCHRONIZING state. If this is a synchronous-commit availability replica, all availability databases should be in the SYNCHRONIZED state.
September 29, 2021 at 7:19 am
Your missing something in the process then or something else in your setup isn’t correct.
September 29, 2021 at 11:15 am
Finally found the reason for the error. Thought add that so anybody who have this issue can benefit.
On the secondary database , properties, Advanced, Enable Contained Databases Option should be set to True.
Now it is working fine.
September 30, 2021 at 3:29 pm
Finally found the reason for the error. Thought add that so anybody who have this issue can benefit.
On the secondary database , properties, Advanced, Enable Contained Databases Option should be set to True.
Now it is working fine.
To clarify - that setting is at the instance level, and yes - you need to make sure every instance included in the AG is setup and configured the same way.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply