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

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)?

(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'