I have to create a new staging process for our development and testing.
At any given time, there are only around 4 git branches actively being developed and tested. Within each git branch, there may be database evolution scripts (straight SQL) that must be run, as well as evolution scripts from the backend for heavier processing (essentially these are HTTP routes that must be called within the app using admin credentials that perform database migrations and other changes that would be too difficult/not possible to script in the aforementioned plain SQL evolution scripts).
Our live DB is moderately sized ~ 4.2 GB. We have a brand new Dell PowerEdge server that is ready to be set up and is at my disposable.
I would love advice on the following questions, as well as to know how more experienced DevOps would approach this:
How can I run several different branches on the staging server? These branches pop up and disappear frequently as they pass QA and are merged into master and are released.
How would I set up the DB evolution system to make sure it always has appropriate DB for each branch? Each branch may make modifications to the DB in different ways that are not necessarily compatible with each other until they're merged.
How do I keep these branches up-to-date? Is there a way to auto pull commits on each branch?
Would love any more input as I'm a bit lost on how to set this all up. Current workflow is difficult for everyone involved: Devs have a completely isolated local copy of the app running locally and QA has 3-4 laptops in rotation that act as their staging "servers"