I am in the process of migrating a server to AWS. Right now it is just for Dev/QA work, but will eventually be turned into our production server. It is a 2 node AG. Server is all up and running, but our data is over 2 months old, and our team would like to periodically (every 5 days) get new data into the DB from the existing Prod DB, and I have been asked to use log shipping to do so. The database needs to be usable, so keeping log shipping intact isn't an option since it will be read only or no recovery. We need to test how it works for eventually populating the nodes for go live, so 2 birds with one stone so to speak. The DB is 1.2TB, so transferring it over the NW to do a backup and restore normally is a bit time consuming. Would the below work, or does anyone have a better way to do this?
- remove the DB from the AG to allow it to be added as a secondary LS destination(i have not found info that an AG primary can be used as a LS secondary.)
- Restore the most recent backup of prod DB to the destination server, leaving it in restoring mode.
- Add the DB as a secondary and let the LS copy and restore jobs bring it up to sync. choose the no recovery option on the DB.
- once the DB is synced, disable the restore job and bring the database online using restore DB with recovery command.
- add DB back to the AG so they can test everything for the next 5 days.
So five days later, I would need to copy over a new backup since we only keep 7 days of log backups in order to have all the log backups needed to get new data. Is there a way to disable the log backup cleanup for a specific secondary? I don't want to bloat the live prod server with weeks log log backups...
Would this process work, or does anyone have a different way to do this?