Keeping QA in sync with Production

Question

Keeping QA in sync with Production

Brookstone

SSCommitted

Points: 1745
More actions
November 11, 2008 at 7:49 am

#128688

Hello all,
We have a requirement to keep our QA systems in sync with production. In the past, we have performed selective database restores, however, the requirement is now real-time if possible. I wondering what others are using to manage this requirement in their environments. We are running SQL2000 SP4 with the intention of moving to SQL2008 mid-to-late 2009. Thoughts on native or third-party tools are welcomed.
Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

That can be difficult if you are QA'ing schema changes as most tools that I know of won't sync data if you change the schema. You can look at replication, log-shipping, something like RedGate's SQLDataCompare, or a custom solution using SSMS.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Brookstone SSCommitted Points: 1745 More actions · Answer 2

Thanks. Difficult is an understatement.

My next question would be what is reasonable? Are other shops keeping QA and production in sync? If so, what procedures/tools are used that work reliably.

-Tom

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 3

First, lots of companies won't do this because production data should not be disclosed to QA. Financial stuff, identity, etc. You're asking for issues. Lots of companies I know of obfuscate the data moving from production to QA so they have similar sizes and distributions, but not the same data.

If you need to do this, replication is a good choice though you'll have issues when you do need to restore. You might need to script it out so you can drop replication, backup and then restore, and then enable it again.

Can I ask why real time sync is being asked for?

Brookstone SSCommitted Points: 1745 More actions · Answer 4

Thanks,

I have raised similar arguments related to storing production data on QA. The information I am receiving is beneficial. There are sound reasons (as you noted) why shops are not replicating production on QA. However, to be fair, I have heard of others using QA systems for failover.

The requirement stems from QA testers who want to verify test results by matching production data. Without a match they are reluctant to approve the work.

-Tom

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

If you really need to do this then make use of replication as it also allows for ddl changes to be replicated along with data in sql server 2005.

MJ

ALIF-662928 SSCertifiable Points: 6054 More actions · Answer 6

we use T-Replication to keep sync one of our report server for reports purpose from the production, if your data for QA guys is ok to see then you can take advantage of replication without investing much on other softwares.

avm Mr or Mrs. 500 Points: 528 More actions · Answer 7

Hi,

Keeping QA in sync with Production may help the testers but becomes a problem for dba's. We had a daily job to restore prod backups on QA for the same reason to help the testers. The data was obfuscated after the restore on QA but still we used to have problems like test emails being sent out to real customers because the data obfuscation scripts were not maintained.

After this experience, we convinced the testers to always create test data rather than restoring from Prod. We also maintained that DB schema/lookup data changes should always flow from dev->QA->Prod. We also suggested the testers to create standard test data which we can script it out and run it with every build to QA.

BR,

avm

Raj Dholakia SSC Rookie Points: 44 More actions · Answer 8

Like most shops, we have Dev->QA->Prod.

We have static data items in Prod which can be configured many ways, and users sometimes want to test before they really do it in Prod.

So, we have an automated job which restores this database to a separate environment. It runs weekly but we can run it on demand.

NOTE: not all changes go through QA...e.g. changing an address for the client. This may seem like a minor point and "...of course, you dolt!" but the principle is more general.

Usually, when user's ask for realtime...it might be for the one occasion a year...in which case they may wait a couple of hours for the restore to work. But think hard about using a totally separate environment.

As for hiding data...a much bigger problem and one that we all try to grapple with. It is also a discussion to be had with the business.

We do ship our log files every hour...maybe that will work, if they give you a heads up to do a restore in, for example, your Disaster REcovery site? That also checks that things are working....but it might be a bit extreme for some shops.

Good luck!

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 9

I'm with Raj. Unless you are deploying changes daily, I'd say that real-time is a bad idea. Even using QA for standby doesn't work since you can't "trust" the data there unless it's read-only for all users. And then you can't test.

Go with once a day, QA needs some stability as well. They can't repeat tests or ensure that things are working properly if they have data changing every few minutes on their side as well. They're likely not thinking through the process.