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.

Author: Sri

Hello Friends, This blog is to help the IT professionals who want to become a professional SQL Server DBA but don't know how and from where to start with. So, I am going to share my experiences and my learning in this blog. Will talk about what are the pre-requisite skills required to become a Professional SQL Server DBA, how much time it takes to be a good DBA and what are the additional skill sets are required to become a good DBA. Apart from that I will also post real time sql server settings on server level and database level, Configuring High Availability. Also will share the Client requirements with real time setups if possible I will provide you with screenshots.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.