I would like to start by specifying I am no DBA and have basic understanding of advanced DB concepts. I am trying to find the optimal solution to replicate our production database to our pre-production environment automatically.
Our setup is as follow in production:
- Batch runs at night and populate staging database
- Once complete then our Mart database is loaded using staging data.
- Mart data does not change until the next load the following day.
- Mart data is currently 200Gb and we have planned growth up to 1TB
over a few years. - DB currently set to simple recovery mode and backup taken every night.
What I would like to achieve is
- One off replication of Mart data to separate pre-prod server
- Minimum impact on prod server during business hours or if prod impacted this has to be performed outside of business hours.
- Ability to run batch in pre-prod on an adhoc basis to test batch changes from recent prod data. This means pre-prod mart cannot be read only.
Currently this process is manual and due to DB size and resources availability cannot always be done as required.
In order to execute performance tests and reconciliation after changes it would be tremendously useful to have a daily process which can achieve this automatically.
Any personal experience or recommendation on the best way to achieve this would help.
Thanks.
Best Answer
My first thought was to restore the backup. At 1TB this becomes impractical, however. Log shipping would not allow you to write to the pre-prod instance either. I think it should be possible to engineer something around table partitioning - assign each partition to its own filegroup, perform backups after the nightly batch and restore that to pre-prod in piecemeal fashion. I have not tried this; I'm just hypothesising.
Replication will add more load onto the produciton server to read changed values and publish them. Having the pre-prod copy writable makes things a little more complicated here.
Instead, for the one-off load, I'd suggest you restore a backup of prod. It may take a while but it will be complete and simple to script with minimal load on the prod box.
For daily updates I'd go with copying the staging table(s) to pre-prod and re-running the load job there. There will be no additional strain on the production DB. You can roll back pre-prod and re-run a batch as often as you like, for performance tuning or debugging. If the staging DB starts each batch empty it would be simple to script a full backup - copy - restore - rerun sequence. This will be a good way to test future changes to the loader.
We've had good results with change tracking, using it to copy hundreds of thousands of rows per day. If your staging DB isn't rebuit each day, this may be a good way forward. It will put a modest run-time load on the instance hosting the staging DB.