Single Database Upgrade (From App side) which is part of Always On We have a task to plan out an Always On database upgrade (Schema upgrade) as per the Application. The connected application will do few schema changes as part of the application upgrade.

Database Size : 450 GB

Always ON Mode : Synchronous (With Automatic Failover)


  
  Can this be done without removing it from the AG.? (This is a request from App side)
  Will this cause any issues as it in in Synchronous state with the Secondary replica?
  


We have to give a proper downtime depending on this and do not want to go one way and do it another.

Right now our plan :


Remove the Db from AG.
Do the db upgrade (Schema change).
Add back the
db into AG with fresh backup.
Not sure I’m following why you need downtime at all. Aside from isolation and schema locks, changing a table is no different from changing data - it gets logged, sent to secondaries, etc. This is never instantaneous, but it can often be so close that there’s no way your app would notice.


  Unless both the replicas are in sync we are not allowed to keep the app running. Also we are a bit worried on the database size. If it was for a small database we would have gone straight as you mentioned. So, it will be fine to go ahead without breaking the AG?


It is 100% impossible to update both the primary and the secondary at the exact same moment in time, so let's throw that requirement out the window straight away. The app may require downtime in that case, which you can accomplish in other ways, but I meant database downtime. I don't think a schema change warrants pulling a database out of an AG. Synchronous should be very fast unless you have other configuration / latency issues already, and can you explain exactly why the rule about the replicas in sync and the app running? Surely your schema changes are backward compatible in any case?


  So you mean the Steps I mentioned is fine? Yes I meant the same in terms of downtime. Also This is set for Auto Failover which is why we want to stop the AG before doing any changes.


No, I still don't know why your steps include removing the database from the AG and putting it back in with a fresh backup. Failover seems unrelated, unless your schema changes are potentially so bad that they will cause a failover while you are deploying the changes. Also, once you add the database back into the AG, then the secondary will be out of sync until it catches up again, so this still seems to break your unnecessary rule about keeping the app running.


  Got your Point Aaron. And I totally agree. This is a critical vendor managed app and we are not sure on what kind of schema changes will happen during the upgrade. We are in discussion to finalize a full proof plan to go without any hiccups. In case they want to roll back, the secondary can save us. This is another reason we want to go that way.


Secondary can't help in your current plan, of course. If you pull the database out of the AG, there is no secondary.