10

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

  • 1
    Is there any super sensitive data in the database you shouldn't access? It would seem like an easy work around is to have them send you a backup of the DB's to restore on your personal computer that you can play with without fear of breaking production. This won't require any additional resources and all they need to do is ship you the backup. This is highly dependent on the security needs of your environment however. – Shaulinator Dec 21 '16 at 16:43
  • 1
    Then I think a backup and restore to use in your own environment is a low cost and easy solution. Ideally though, you should have multiple environments ranging in tier of processing power that mimic each other and as you make changes, you slowly graduate them throughout the environments. We have a dev to play in and change, promote to test where things get QA'd, then another environment that is almost a 1:1 of Prod for hardware, and then Prod where things go live. This is obviously costly. Having another database on the server that's similar is another idea. – Shaulinator Dec 21 '16 at 16:59
  • 1
    I prefer to keep environments totally separate though, keeps the chance of causing an issue to a minimum in case you have a run away query. – Shaulinator Dec 21 '16 at 17:00
  • 1
    Oracle database express edition is probably your easy choice. Install it on your machine, setup tables as on your main db. Load with representative data and you can develop/test as much as you want. Once you are ready, you can send them changes to test/evaluate. – Raj Dec 21 '16 at 20:19
  • 1
    @Raj, the problem with Express Edition is there is barely any usable functionality within it. Wilson, because any real features for Oracle only come with Enterprise Edition, Oracle licensing is likely one of your largest obstacles I've not yet seen considered, though Shaulinator touches on it to a degree. Oracle doesn't offer a cheaper license for non-production environments option that I'm aware of, so you have a pretty expensive hurdle with licensing alone. The cloud may be a good option for you purely due to budgetary issues. – John Eisbrener Mar 23 '17 at 15:52
2
+100

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.

3

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.

2

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

  • 1
    I think, for the simserver, he means a locally hosted SQL environment that has some level of mirroring (simple backup restore?) of the prod environment... You do development tasks on that local environment. Jira is a project management and issue tracking platform that allows developers and QA to track changes. I think he is stating that a proposed change that gets done on a local instance gets posted to jira for dev. review. VM is virtual machine. – Wes Mar 24 '17 at 14:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.