SQL Server AlwaysOn is a great feature developed by Microsoft that allows you to create a high-availability solution using an actual copy of the database in real-time synch mode. Now, as part of routine maintenance, we may need to perform a database refresh. I attempted to summarise all of the points and processes involved in carrying out such an activity. I hope the following information is useful:
Step 1: Make a backup or copy a backup from one location to another.
Step 2: Create a backup of the users in the pre-production database.
Step 3: In Pre-Prod, remove the database from the Availability Group.
Step 4: Using the copied backup file from the Source server, refresh the database in Pre-Prod.
Step 5: After the Pre-Prod DB has been restored, drop the users.
Step 6: Using the script from Step 2, create the users.
Step 7: Check if there are any Orphand Users and Sync the Users.
Step 8: Refresh\Update your Database statistics
Step 9: On pre-production, reconfigure the Alwayson AG setup.
Step 10: Check the AG status and make sure the database is synchronized once the configuration is complete.
I’ll be providing a script to Automate DB Refresh for Alwayson Configured Database activity for Alwayson Configured Database very soon, and the script can also be scheduled as a SQL Job.