Post reply

Schema Updates to database in Availability Group

  • January 9, 2020 at 8:50 pm

    #3714123

    Hi all

    We have a database in a SQL Server 2016 SP2 AG that is being replicated asynchronously to a single secondary replica.  The AG was configured with automatic seeding mode.  Now we need to upgrade the application which will involve some schema changes.  Can I ask: which is the best way to do this? The database is quite large I believe (100s of GB).

    1. Keep replication running, just make the schema changes on the primary replica and let the log records propagate to the secondary.
    2. Stop replication, make the same schema changes separately on both primary and secondary replicas and hope that replication will continue when restarted (I'm fairly sure this is unlikely to work).
    3. Stop replication, make the schema changes on the primary, delete the database on the secondary, restart replication (how?) and hope that the automatic seeding will recreate the secondary's database and synchronize it.

    You'll have noticed that I don't have much knowledge of AGs!  Any other solutions are welcome.

    Thanks

    Gary

  • January 9, 2020 at 9:22 pm

    #3714131

    Normal schema changes won't be an issue for the AG - they will propagate to the secondary database very quickly.  With that said - if you are adding new columns and populating the new columns - the data changes will need to be moved across, and if that is a lot of data that needs to be added it could take some time.

    How much time these types of changes will take depends on how fast the network is between the two systems.

    Since you are set to asynchronous mode, how long it takes shouldn't be an issue - the secondary will just be behind until it catches up.  However, if it gets too far behind and is taking too long you could impact the primary system - mostly this will be related to the transaction log on the primary not being able to truncate and eventually growing to fill a drive.

    To give an example - I have a system with over 3 TB in one database where we have performed upgrades to the code (with some data changes) and have not taken the database out of the availability group.  In most cases we have not had any issues with this - and in the one case where we did have an issue it was because the vendor disabled the transaction log backups on the primary and filled the drive - which was not an issue with the availability group.

    I do not setup any of my databases with automatic seeding - I always perform the backup/restore and then join the database manually.  This gives me the best control and scheduling, especially when working with a VLDB.

    There are a couple of reasons to remove the database:

    1. Maintain a copy of the database prior to the upgrade that can be used to validate the changes.  For example, you could remove the database from the availability group - bring it online - and compare data before and after the changes.
    2. The changes on the primary involve a large volume of data changes - either moving data to new filegroups, partitions, etc... or adding data in new columns, or adding new tables, etc.  In this case you would remove the database, perform the upgrades/changes - then backup on primary and restore on secondary and manually join (instead of automatic seeding).

     

    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

  • January 13, 2020 at 10:18 am

    #3714843

    Thanks for the reply, Jeffrey, lots of useful information in there.

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply