October 11, 2019 at 12:42 am
So every couple of weeks, we have a release of our online software, some UI changes, some middle layer code changes, but a lot of it is on the SQL Server DB end (adding new tables, columns, proc changes, index adds/rebuilds, etc). We are granted a small downtime window. This window is about to be closed.
I would like to be able to do maintenance to the databases without the benefit of a downtime window. Half of the time, simple changes like column adds take virtually no time, but the part that takes time would be any seeding of new columns and index adds/rebuilds, scenarios that may require some downtime. It doesn't help that we are using the very awkward DacPac software in our Jenkins release software, which has a bad tendency to want to rebuild tables when it doesn't have to.
Since we have Always On and redundant servers all over the place, I'm wondering how feasible it would be to set up an A/B scenario where one database server (we'll call it B) is being maintained while the other server (A) is still going, then re-sync when it is done, then switch over to the other server (B) to do the maintenance while (A) is now the active server, then re-sync and flip once more once (B) is done. I wish we could simply apply the transaction logs but unfortunately that's not in the cards. There would theoretically be virtually zero downtime in this scenario. One thought I had would be to break the mirroring, flush out all the transaction logs, start making the changes to B and at the same time capture the transaction log on A. Then when B is done, use a transaction log reader like Apex SQL Log to capture the SQL in the transaction log and run all the SQL on B. Then reverse things on the other server - make changes to A and capture the transaction log on B, then apply the log on B to A. This scenario can be hokey because it's possible that some SQL executions could fail, depending on what type of maintenance we are doing (for example if we delete a column and the other server still has inserts that include that column). Or there's so much SQL in the transaction log that it takes forever to sync.
So I'm wondering if there's a better way setting up a "zero downtime maintenance window" using multiple servers. Certainly many companies must have this same 24x7 uptime requirement where they flip from one server to another.
October 12, 2019 at 12:25 am
since this is an exercise in synching between machines, is it possible for me to spin up a new server not using AlwaysOn, backup/restore, then set up some sort of two-way merge replication? Or peer-to-peer replication? I'm not sure if transactional replication will work, since I'll have to go both directions.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply