Can this be done without problems ?
Remove prod database from AG ( easy enough to do )
Do upgrade on database ( need to keep it accessible via Listener )
Add database back into AG on Primary
My concern was that as its a busy & critical database I'm being pressured to follow this method, but am concerned that it may cause transactions to drop etc. My preference is an outage but no one wants to do that.....( business vs techs argument, as always )
Any help appreciated.
We don't remove databases from an AG for maintenance.
Dropping a database from the AG won't cause any issues - but you will have to add the database back to the AG which will require a new backup/restore and transaction log restore on the secondary.
With that said - there is no reason to remove a database from an AG for an upgrade. You can perform application upgrades with no issues - and you can perform database upgrades (service packs, cu's, etc...).
I am not sure what the goal is by removing the database. If the goal is to eliminate downtime during the upgrade - that isn't going to be possible, unless this is a SQL Server upgrade.
Hi
The reason for the removal of the database out of the AG is to manage log growth while keeping the database accessible via the listener, while a significant application upgrade takes place. We arent sure how big the log will get and without a replication partner, the log will just keep growing.
Removing the database from the AG means we have a copy of the database in a known restoring state for then "if all else fails" scenario on the secondary node.
Then, as we were told we didnt have much of an outage window, we have to add the database back in to the AG, do a diff and log backup on prinary and restore to secondary and resynch using "skip initial data synchronization" option on the primary.
I came into this late in the day after the original decision had been made by non technical people. There has been multiple layers of decision making take place and the DBA winds up having to manage with what options are left.......it sucks.
If you remove a database from an AG, it is no longer accessible via the listener.
You can suspend synchronization while the application upgrade runs, and can still connect to the listener. That way you don't pay a penalty in sync traffic, but the log will still grow on the primary. Once the upgrade completes, resume synchronization and let the secondaries catch up.
If you change out of the full recovery model to prevent log growth, you'll have to resynchronize with a new full backup and restore, as Jeff mentioned, since the log chain will be broken. This will all take longer.
Suspending sync also leaves you the "all else fails" option; you would then remove the DB from AG, and RESTORE WITH RECOVERY on the secondary replica node to pick up the database state prior to the upgrade.
I do DB deployments all the time with the AG syncing, it takes longer sometimes but we are always online. I also make database snapshots prior to upgrade as an additional fail-safe.
You will not be able to perform a diff - and reset the secondary database from that diff. Once you remove the database it will require a full backup and restore to add the database again.
If the concern is about the transaction log growing - then during the upgrade run your transaction log backups more frequently. However, if there are going to be a lot of data changes it won't matter how often you run them - it will depend solely on how long it takes to commit them on the secondary.
If you suspend data movement - it won't help with the transaction log growth, in fact - it will cause more issues with the transaction log. Again, this depends on what changes are actually being made during the upgrade.
Normally - upgrades do not make a lot of data changes. There are a lot of code changes - some new columns added to tables, but not GB's of data being updated/inserted or deleted.
If there are not a lot of data changes - then I would not remove the database from the AG.