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

Question

We kicked the idea of ​​where the UAT can be tested with close data in real time (say, a maximum of a week ago). I strongly believe that the development and quality control environment should control its own data, but the UAT (final level before production) is a bit of a gray area. So my questions are:

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

b) if so, what proven methods have people used in the past?

• manually via SqlCompare or similar
• automated using scripts?
• How do you handle schema changes between UAT / Production (UAT will almost always be ahead of Production, except immediately after the actual deployment)?

Answer 1

(assuming the OP assumes a real-time continuous circuit and data synchronization)

Short answer:

• Scheme - No - in a developing system under development, the UAT is likely to get ahead of production, and changes will be made to the UAT for future product releases.
• Data is possible (to get good, fresh, representative data), although any differences in designs may need to be adapted. An alternative would be to use a false data generator.

rationale

By “mirror”, I assume that you do not mean direct mirroring or real-time replication (testing UAT usually requires painstaking test cases for data to be overwritten).

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

At regular intervals, usually after about 1 month

• Last prod database backup restored in UAT
• An environment conversion script is run for each database that is updated after recovery (for example, to fine tune or to mask sensitive financial, client or user data, etc.)
• All UAT scripts that were not already in PROD are then run with databases (you will need a good discipline with control over scripting changes to easily track this - we still don't always get it right). After the update, we do not directly compare the QA and the UAT (that is, the PROD scheme) and simply roll back the changes back to the UAT.
• This serves as a good smoke testing / debugging tool, since the same vNext scripts should be run against Production at the time of the release of Production.
• Modern systems may not require explicit / external version migration scripts (for example, when you first start the Entity Framework Code First will try to update the database schema), although this may be associated with a risk when applying them to outdated or shared databases.

Some other considerations

• In a corporate environment where systems are integrated with each other, it is recommended that all system databases be updated at the same time so that shared data and keys are synchronized.
• In many corporations, changes in the UAT environment (including data updates) may require the approval of a change management board, as the availability of the UAT is critical for testing new systems and can affect many projects.

Just pay attention to the 'script' loop for synchronizing circuits - in our environments:

• DEV is free for everyone - any developer can make changes to DDL or data.
• QA and UAT are blocked - scripts must be generated (usually SQLCompare) and then sent to DBA for execution (in QA), and these scripts are checked and approval is received for execution, as they move through the environment chain (especially UAT),
• These scripts are then checked in the version control system and monitored "for each environment."

Answer 2

Here's what we did for the last company I worked on. We had many government projects and contracts. Here is an example of our level of environment that we used in some projects. In the example below, QA was for us, 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

As soon as all the data was verified, we copied from Prod to UAT and QA almost everything, including all related to DB.

We also had a tool that was written for some aspects without always having to use SQL. We had a web program, and I can’t remember what she wrote. We called it CTM - Control Table Management. There we could make certain changes to the tables, such as updates, corrections, drop-down menus, spelling and grammar errors, and really just any errors. anything. Switches have appeared to commit changes and fields to check in which environments you would like to roll back the changes.

Hope this helps anyone or give people some ideas. :-)

Thanks,

John