2

Looking for some advice regarding replacing a database within a SQL 2012 High Availability Group environment setup as synchronous with auto failover. A little bit of background on the reason for this.

Every couple of months we get a MDB database from a supplier which then need to be imported into a SQL database. This is done by 2 step job that first truncates current database and then recreates and then a SSIS package is run to fill the database.

As I'm trying to automate the full process any suggestions and advice would be greatly appreciated.

I know I need to first remove the database from the availability group and then apply the changes, but was interested to find out what the best approach with the secondary is and best practice. Im guessing I can either delete the database from the secondary and then add it back into the Availability group or is it better to take a backup of primary and restore over secondary before adding back to HA

  • 3
    Why do you need to drop the database? Can't you simple truncate the tables , and re-fill them with the SSIS process? Then you'd not need to reconfigure the AG each time. – Max Vernon May 1 '15 at 11:39
  • 1
    Didn't think of taking that approach. I would need to make sure all constraint are disable to use that method. Also potentially wouldn't that approach have a high impact within the High Availability Group. Specially seeing how it configure to be synchronous.On top of that you would also have bloating of the logs. But I do see your point, – Steve May 1 '15 at 12:39
  • Just curious, how big is the database ? – Kin Shah May 1 '15 at 15:03
  • The Database is only about 2 Gb so isn't that big. My concern is more the capacity of the current server as I've inherited a mess where due to financial constraints they have merged multiple high transactional databases onto one instance and its struggling. So anyways I can mitigate the impact would be great. – Steve May 5 '15 at 8:26
1

With SQL Server 2012, there is only one way to synchronize a database when it is added to an availability group--backup and restore. So you will need to drop the database from the secondary and restore the primary's backup (or restore with overwrite) when adding the database to the availability group.

With SQL Server 2016 and later, automatic seeding is available for initial synchronization, so the backup/restore process can be skipped if seeding is enabled on the AG.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.