Should UAT data be a mirror of Production? And if so, how?

Question

We have been kicking about an idea where UAT can be tested with near live data (say maximum a week old). I strongly believe that development and QA environments should be in control of their own data, but UAT (the final tier before production) represents a bit of a grey area. So my questions are:

a) is this a good idea? I think so, but have nagging doubts.

b) if so, what are some proven techniques that people have used in the past?

• manually via SqlCompare or similar
• automated via scripting?
• how do you handle schema variations between UAT/Production (UAT will almost always be ahead of Production except immediately after a live deployment)?

Answer 1

(assuming that OP intended continual, real time schema and data synchronization)

Short Answer:

• Schema - No - in an evolving system under development, UAT will likely already be ahead of production, and UAT will have changes intended for future production rollouts.
• Data - Perhaps (in order to get good, recent, representative data), although any schema differences may need to be adapted. An alternative would be to apply a fake data generator.

Rationale

By 'mirror' I'm assuming that you don't mean real-time direct mirroring or replication (UAT testing typically requires painstaking data test cases to be set up which would get overwritten).

Here's how we do it in a corporate environment, FWIW (Our environments are Dev -> QA -> UAT -> Prod)

At defined intervals, at usually approximately 1 month intervals

• The last prod database backup is restored over the UAT environment
• An environment 'conversion' script is run on each database refreshed after restore (e.g. to point configuration, or to obfuscate sensitive financial, customer or user data, etc)
• All UAT scripts which weren't yet in PROD are then run against the databases (you will need good discipline with script management change control to easily track this - we still don't get this right all the time). After a refresh we do not directly compare QA and UAT (i.e. PROD Schema) and simply roll forward the changes back into UAT.
• This serves as good smoke testing / debugging, as these same vNext scripts would need to be run against Production at the point of Production release.
• Modern systems may not require explicit / external version migration scripts (e.g. Entity Framework Code First Migrations would attempt to upgrade the database schema at first run), although there may be risks to doing this when applying these to legacy or shared databases.

Some other considerations

• In an enterprise environment where systems are integrated with each other, it is advisable to refresh all system's databases at the same time, so that shared data and keys are 'in synch'
• In many corporates, changes in the UAT environment (including data refreshes) can require change control board approval, since UAT availability is critical to testing of new system rollouts and may affect many projects.

Just a note on the 'script' cycle to synchronise schemas - in our environments:

• DEV is free for all - any developer lead can make DDL or data changes.
• QA and UAT are locked down - scripts need to be generated (usually by SQLCompare) and then sent to DBA's for execution (in QA) and these scripts are reviewed and approval obtained for execution as they are promoted through the environment chain (especially UAT).
• These scripts are then checked into source control and tracked 'per environment'

Answer 2

Here is something that we did for the last company that I worked for. We had many state government projects and contracts. Here is an example of our level of environments that we utilized on some projects. In the example below the QA was for us, the UAT was for the client and Pre-Prod was another environment that we created sometimes, but not always; just depending on the project.

DEV ==> QA==> UAT==> PRE-PROD ==> PROD

Once all data was verified we copied down from Prod down to UAT and QA of just about everything, including anything DB related.

We also had a tool that was written for some aspects without having to always use SQL. We had a web based program, and I cannot recall what it was written in. We called it CTM - Control Table Management. In there we could roll certain changes in tables like updates, corrections, drop-down menus, spelling and grammatical errors, and really just any misc. anything. There were radio buttons to commit the changes and boxes to check which environments you wanted to roll the changes to.

Hope this is a help to anyone out there or give people some ideas. :-)

Thanks,

John