Michael, Thank you, that's very helpful.
Michael L John wrote:
If there is a specific set of requirements, such as auditing or compliance, where you need to isolate these environments, there should be no need to keep things the way they are..
We don't have Audit / Compliance restriction.
This is the environment which, historically, we have only used to test 3rd party APPs. Nowadays we have strict requirements on 3rd Party Vendor code (any use of NOLOCK has to be authorised, database names have to conform to our naming convention, etc) ... but that is NOW, way back then we didn't have any such rules. Some of the 3rd party stuff is horrific ... full of hardwired links to other databases (from other 3rd party vendors that they pull data from) ... NOLOCK used like confetti ... and so on. One Vendor named their database "LIVE" which is not particularly helpful in knowing which Supplier Name / Product it refers to! ... but we are where we are on that.
So that test environment is intended to guarantee exact duplication with production, to ensure that 3rd party Vendor upgrade tests perform "identical to the live system"
I don't want that for our in-house stuff, I think I just need a different SQL instance, and a different Web Site, and then point browser at the appropriate sub-domain and Voila! you are connected to DEV / TEST / QA etc.
Pls assume that our Production environment is completely separate from DEV / Test
But I would like to have DEV / TEST / QA (and Help Desk) on separate SQL instances on a single machine (and one SQL licence). There are only 50 people in the company, and I can tolerate some lesser formality over who can do what on DEV vs. QA, but basically the way you have very helpfully described it is very similar to what we want.
In most places I've been, there are 3-4 environments, Dev, QA, staging or UAT, and production..
The dev environment is fairly wide open.
QA is more controlled, deployments are approved and access is limited to the QA testers.
UAT is the last place where the final sign off occurs when a change is made. This is secured in the same manner as production. You only access this by using the applications..
I think my objective is similar, but I would appreciate any comments anyone has.
We do fresh development on DEV. For us 99% of what we do is SQL. The APP is just an engine, and it uses Metadata in the database to figure out how to display stuff to the user. We don't do any APP programming, except for generating "meta data". For example, to make a Data Entry Form we just choose which Database Table Columns are to appear on the form. We do not create any HTML for the form, no APP data validation, just SProcs and Metadata. The actual form displayed to the user, and all the data validation, and which Sprocs to call etc., is all mechanically generated in real-time (well ... "cached")
So at the end of today on DEV we will have some modified SProcs and some new Metadata (and maybe some DDL changes)
We run an automated regression test system. Over night the "Base Test Database" is restored, all the Sproc / Metadata changes on DEV are applied, a new BACKUP is taken (for tomorrow's base restore) and then the regression test runs. It records any difference between Baseline and Current. Tomorrow we get a list of any differences and we either "Re-base" them (an intended change) or raise a ticket for a fix (an error)
When we get to a review milestone we produce a Rollout Script. (In practice we also run that through the Regression Test to check it didn't screw anything up ...)
We restore Production to QA and apply the rollout script, and then selected users review the changes we made.
(My naming of QA seems to be more like your UAT)
We usually have several cycles before a formal release. When we are ready for a formal release we make a Rollout Script, restore Production to QA and apply the script. We are reusing QA for this purpose ... maybe we should have another variant for that.
At this point QA is used for "user training" for any new features and so on. There is no harm if a user tries something and it all goes Pete Tong ...
Once its all signed off we run that same rollout script on Production.
Separate to that we have Help Desk copy databases. That is copies of each Production database copied to "HD" every night. When a problem is raised with Help Desk they will use that "yesterday's data version" to investigate. For us, HD are permitted to investigate on Production, but they would only do that if the issue can only be reproduced on Production (e.g. depends on data entered today) AND the problem will not wait until tomorrow.
[Production access is limited to admins
That's true for us at an SQL level, but the APP has loads of diagnostic capability, so Help Desk can do diagnostics on Production (i.e. restricted to the tools provided in our APP - basically "Reports")
All deployments are 100% automated via the DevOps deployment process.
We are heading that way. Ours is "largely automated" but there are a few manual-ish steps.
All environments are identical from the software and patching levels. The resources allocated to Dev and QA are somewhat less than production as far as memory and CPU. UAT and production are identical from a hardware perspective
I'm aiming for that too, thanks.
My "Separate SQL Server with multiple instances for DEV/TEST/QA" will just be a virtual machine and, so IT tell me :), the physical hardware available to it will make it pretty much indistinguishable from the Production SQL Server environment.
By comparison the server which hosts our "fully isolated test environment" has significantly less RAM than Production. That is a pain because when we RESTORE the Production SQL VM onto Test then SQL won't even start ... so we have to clutz about starting SQL in Manual/Single User Mode, change the CONFIG to reduce memory, and then we can start SQL for the Test environment.