DB Refresh Steps for Alwayson Configured Database

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: