Database staging environment for non-IT personnel

Question

I'm in the process of proposing a database staging environment to my IT department. The idea is that a non-IT person like me (public works data analyst) would have a place to test solutions, and then either implement them in the live environment myself, or ask IT to implement them if needed. There are a few reasons/scenarios where this environment would be beneficial:

1. I have some basic database privileges in our live database environment (create table, create view, etc.). I make schema changes about once a week, but it seems insane for me to test and implement these changes in a live environment. There are countless dependencies on the database, so if something goes wrong, it could be disastrous. I'd much rather test things out ahead of time in a separate environment.
2. I don't have some of the more advanced privileges like create trigger or create function in the live database. This is fine, but I do have a few problems that could be solved by triggers and/or functions. I plan on proposing that I be granted these permissions in the staging environment so that I can develop and test some ideas, and if they work, propose that IT implement them in the live environment.
3. In general, my IT department doesn't have the time or resources to develop solutions for me. It's really that simple. So if I can do the legwork myself, then my problems are much more likely to be solved.

The 'staging environment for non-IT personnel' seems like a sound enough approach to me, but to be honest, I just made the idea up. I have no idea how this is typically done in the IT/database world.

Is there any sort of established IT/Database practice that would fit this scenario? (Am I on the right track when proposing a database staging environment for non-IT personnel?)

Answer 1

I agree with @Marcin Gminiski's answer that you ideally want to have an environment that mimics functionality available in your production environment. Although my 2 cents on the matter all come down to, "What can you afford?" Budget constraints are often the killer of good process, so really what you can afford will determine the complexity/elegance of your final solution.

Because you mention your IT department is lacking in time and staff to stage an environment for you, are you (or rather is your department/manager) able to bring any funding to the table? Procuring a small amount of annual funding would open up the possibility to look at the cloud. Cloud providers offer everything you need, and some solutions even include the appropriate licenses which is often your largest cost as it relates to Oracle. If you're not throwing sensitive data into this environment, the cloud becomes even more appealing.

There are all manner of cloud options available out there, but I'll point you toward Oracle RDS instances on AWS solely because they offer a License Included option, and you can turn it off when you're not using it to further minimize costs. An equivalent may exist on other cloud providers, but many cloud providers I'm familiar with require you Bring Your Own License (BYOL) for Oracle-based solutions instead of offering an inclusive license.

Final note here, an AWS RDS instance is ONLY the database, so any app-server infrastructure you also need would have to be be accounted for in addition. The cloud is a great option if you need a quick environment to test functionality out with, all while being a cost-effective approach; just make sure you keep on top of it and turn things off so you're not paying for idle servers.

Answer 2

Usually a decent environment would be made of at least DEV -> TEST -> PRE-PROD -> PROD. Development would normally have access to develop in DEV, acceptance testing in TEST and IT to release in PRE and PROD. If you use source control you will avoid problems around editing the same piece of code by different devs at the same time.

Technically you only need schema to be the same as in prod and not need production data below pre-prod but if you are ok with having prod data outside prod environment t you could have an automated restores into dev/test. I have done similar work with Visual Cron and it works wonders.

Likely in order to stay compliant IT personnel will have to release changes into pre and prod so to make this easier and more resilient you could go down the route of automated deployments.

Answer 3

Here is my experience:

Originally, we had a central development environment called simserver. Developers would test things simultaneously and it got messy.

Now, each developer has a local simserver that they deploy to for testing. Once they say it's ready, it gets pushed to the quality assurance (QA) environment. We have jira test cases for anything that needs to be checked, plus we test the new additions again (we have dedicated QA staff who don't do development; just QA).

Then it gets pushed live.

Making a local simserver is logical and easy. Once you get VM templates ready, the developers just deploy them on their personal computer (without access to the rest of the network -- just their local computer).