Restore PROD database to QA with AlwaysOn

Question

I want to create a script/job that will run weekly to backup my PROD database, and restore it to my QA server, then run a custom maintenance script against it to cleanup and prepare the data to be analysed.

The problem is that my QA server is running AlwaysOn Availability Group. I have access to Powershell, "DBATools.io" scripts, SQL Server Agent, and a remote SQL server if it can help. All under SQL2016.

I also just discovered today the SQL2016 feature called AUTOMATIC SEEDING. I tried it, and it allows me to bypass the complicated "Add database to AG" steps.

So, up to now, I can simplify the steps down to this:

ALTER AVAILABILITY GROUP [SQLQA] REMOVE DATABASE [ProdData];
RESTORE DATABASE [ProdData]
 FROM DISK=N'\\nas\Backup\ProdData.bak'
 WITH FILE=1, NOUNLOAD, REPLACE, STATS=5, 
 MOVE N'ProdData' TO N'S:\Data\ProdData.mdf', 
 MOVE N'ProdData_log' TO N'T:\Log\ProdData_Log.LDF';
-- Missing a step here to physically remove the old database from the secondary server, at this point in "Recovery pending". If the old database is still there, the AUTOMATIC SEEDING will not work, nor the manual steps of adding a database to AlwaysOn for that matter.
ALTER AVAILABILITY GROUP [SQLQA] ADD DATABASE [ProdData];
-- Run custom maintenance/cleanup SQL code here

The big question is: what are my options to get rid of the old secondary target database?

Alternatively, anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Thanks in advance.

Answer 1

what are my options to get rid of the old secondary target database?

You can create linked server and use Execute At LinkedServer. This is what I am doing in my current scripts when I have to refresh AG env in Dev / QA from PROD.

You can use powershell, etc .. but Execute At LinkedServer just works fine for my needs.

anyone has a super hot way to get Production data down to QA or DEV environments easily and automatically?

Refresh scripts to downstream environments are specific to company environment esp. the post restore process like desensitizing or masking data, etc. So, I would say, invest some engineering time and make the process repeatable that will save you a lot of time in future.

• Philippe: Oh, that is a new one for me. I will look into it. Thanks for the tip!

Answer 2

Following Kin's advice, here is the (semi)final result. If some people would add to it to make it even more modular/parametrizable/robust, I am sure it can get useful.

/* PREREQUISITES
1) Add Linked Server to the secondary with "RCP" and "RCP Out". Security account must have "DB Creator" rights
2) ALTER AVAILABILITY GROUP [SQLQA] MODIFY REPLICA ON 'SQLQA2' WITH (SEEDING_MODE = AUTOMATIC)
3) ALTER AVAILABILITY GROUP [SQLQA] MODIFY REPLICA ON 'SQLQA1' WITH (SEEDING_MODE = AUTOMATIC)
4) ALTER AVAILABILITY GROUP [SQLQA] GRANT CREATE ANY DATABASE
*/
-- Remove from AG
ALTER AVAILABILITY GROUP SQLQA REMOVE DATABASE TestDB;
-- Delete database on Linked Server
EXEC [SQLQA2].tempdb.dbo.sp_executesql N'DROP DATABASE TestDB;';
-- Restore backup on primary instance
RESTORE DATABASE TestDB
 FROM DISK=N'\\nas\Backup\TestDB.bak'
 WITH FILE=1, NOUNLOAD, REPLACE, STATS=5, 
 MOVE N'TestDB' TO N'S:\Data\TestDB.mdf', 
 MOVE N'Test_log' TO N'T:\Log\TestDB_Log.LDF';
-- Add DB to AG
ALTER AVAILABILITY GROUP SQLQA ADD DATABASE TestDB;
-- Here you can monitor the progression of the SEEDING and results
SELECT start_time, completion_time, is_source,current_state,failure_state,failure_state_desc FROM sys.dm_hadr_automatic_seeding
SELECT * FROM sys.dm_hadr_physical_seeding_stats;