Replicate data from PROD into test

Question

Replicate data from PROD into test

river1

SSC-Insane

Points: 24309
More actions
November 22, 2017 at 1:46 am

#324587

Dear all,
We need to replicate our database from PROD into Test environment as we should have a copy of PROD for testing proposes.
Problem is that we cannot uise mirroring and probably also not replication as this is a data warehouse which we use with simple recovery model.
On the other hand, backup and restore is quite painful as this is databases with 200GB of infromation.
Can you please advice?
Thnak you,
Pedro

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

Backup Restore
Snapshot Replication
ETL/SSIS
Copy Database Wizard
Red-Gate SQL Clone

Main question is are you going to be governed by GDPR, if so you will need to work with your GDPR representatives to see what information you have in the databases in question to see if you need to do any cleaning up of data when moving into test.

Why is backup and restore so painful for you? 200GB in todays standards isn't that much.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

Pedro

Replication is still possible with a database in Simple recovery model. However, I would strongly recommend that you don't use replication from a live environment to a test environment, otherwise you'll find yourself in a situation where the health of your live system depends on that of your test system.

So much for what you shouldn't do - you haven't really given us enough information to help you with what you should do. Do you use your test database for running queries only, or do you actually make changes to it as part of your testing? Will the refresh be a regular occurrence, and if so how often, or will it just be an ad hoc process? Why not use the same process for building your test data warehouse as you use to build the live one? Backing up and restoring a 200GB database shouldn't be as painful as you might think, especially if you compress your backups. Whatever you decide, make sure you take Anthony's comments about data protection into account.

John

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 3

If your system has a SAN, most SANs have some option to do a SAN "Snapshot" either as a built in or optional feature.

I still think a restore is the best option here for multiple reasons.
1. It tests your production backup every night. Most people don't understand the importance of doing that, especially on smaller systems.
2. It tests your network. If you're setup well, a 200GB restore should take about 6-10 minutes.
3. It tests you. You'll learn a shedload about restores and the things that support automating them that you might not know now.

I also share the concerns about GDPR, PII, and the other things that people should be worried about but don't seem to care.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

leehbi SSCrazy Points: 2874 More actions · Answer 4

Snapshot from the SAN is very good if you have the IOPS available on the SAN to cover test use cases. It can snapshot DW very fast, even multi-tb DW 🙂