Removing/Adding Multiple Databases from Always On Availability Group
-
Afternoon!
Apologies I've tried to find this everywhere but cannot find exactly what I am looking for - so I'll try my best to explain what I'm trying to do and maybe someone knows whether it's even possible let alone how to actually do it!
So currently I have an MSSQL setup consisting of 2x servers in 1 always on availability group (lets call them AG1) and 2x servers in an always on availability group in another location (AG2) and I've linked these using a distributed availability group.
I use DB1 in AG1 as the primary which serves as my primary DB for my App server. My application whilst not fully supporting high availability uses the listener to know which DB is up BUT I believe I have the limitation also that the application understands it is in an always on availability group and doesn't allow updates whilst this is the case.
This means to do an app update I have to effectively break down all of my availability groups and DAG, do my update on the App server/DB1 then re-set everything back up from scratch.
As you can imagine this is not ideal - so I'm wondering is there a way I can remove the DB1 from AG1, do the upgrade, and re-add it somehow and then let it sync all the changes? OR remove the specific databases from AG1, do the update, re-add them and let it sync all the changes?
Bit confusing I know but hopefully this makes sense to someone
-
Hi,
I don't think, that you have to remove a database from the AOAG to do some updates.
If so, you can take a look to dbatools, there are very good scriptes, to automate your steps, add a database to the aoag, etc.
If you use a default connection stringt, your application should be able to do the updates.
Kind regards,
Andreas
-
Thanks - I haven't actually tried it yet - I'm a small MSP running Kaseya - This is what it states on their knowledgebase:
"Always On Clustering/Log Shipping for SQL Server
It is possible to run Kaseya with the SQL Server in a Always On Environment (or using log shipping), however, there are limitations. During patches and upgrades, Kaseya will require alter database commands which will fail if Always-On/Log Shipping is enabled. In these cases, Always-On/Log shipping needs to be disabled before maintenance occurs"
Effectively the application doesn't really care what database you're connected to it just uses the IP/name and credentials. The wording mentions alter DB commands which may fail - I don't know exactly what these are unfortunately but this is the bit that has lead me to think it won't work.
-
Hi,
okay, than just take a look at the link to dbatools. It is quite simple and nice to use.
We do a lot of maintenance and work with this scripts.
good luck,
Andreas
-
Thanks will do - appreciate that!
-
It appears your vendor is doing something rather problematic during upgrades. I have a vendor that used to do the same thing - and that is they change the recovery model to simple during the updates.
There is a misguided belief that this improves the performance of their updates - or that it somehow reduces the impact on the transaction log. Both of which are false...
They also might be disabling agent jobs during their update, in which case your transaction log backups would not be running - and that would cause issues when the log filled up to the point where it fills the drive and causes the updates to fail.
Unfortunately - the only solution is for the vendor to change their update scripts and not change the recovery model to simple and not to disable transaction log backups, but that is a battle you probably won't win and isn't worth the fight
As to your issue: you can remove the database from the AG and add it back in using automatic seeding. Automatic seeding should synch the database with all changes performed during the updates.
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 -
Morning,
Yes i have considered approaching the vendor but I can almost guarantee it won't help.
I do have automatic seeding turned on - so I assume if i remove the offending database from the AG group, perform my updates, and re-add, it should be absolutely fine to sync up the changes made to the secondary and further replicas?
Thanks
Rob
-
From my reading on automatic seeding - yes, that should handle removing and adding the database. Regardless of the answer from the vendor - notifying them of the issue will document it in their system as an issue that needs to be addressed.
The vendor will eventually address the issue - or they will find customers moving to other vendors that do respond to customer concerns and provide better customer service.
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 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply