We're hoping to move to a new branch-per-issue process with development. To prepare for this, a lot of research and experimenting has been happening in terms of version control, CI, and process of promotion. The one thing I don't want to fall off the project is the database and it's schema.
The current process we have in place puts us into an expensive position. We have four SQL Server boxes, each with their own environment worth of data. There is Staging (for development), Quality (for QA), Beta (for client prototyping), and Production. We're wanting to begin switching out some of our database model with MariaDB as we implement this new process as well. However, I'm having trouble putting my head around how we'll go about putting our SQL changes into version control.
Our new process will start off with a master branch, which will be our main code, followed by an integration branch and issue branches. Each developer will have their own web instance installed to make the needed changes on whatever branch they're working on at the time. However, we've always worked on the global Staging server for our SQL. This doesn't seem viable to me in the new process we plan on having. My guess for this is that each person will have a version of a database (MariaDB) that they make any schema changes to. So, that's my main question.
Do I use a process of only committing and merging schema changes? I wouldn't think that committing an entire database is a good idea. And at that point, how would this be stored in version control (we'll be using Git as our version control)? Would each schema change be stored as a separate file?
For clarity-sake, what we're thinking of using is a PHP/Apache server to serve out our web content, a Java API back-end, a MariaDB (which we'll move the SQL Server away from) and a Git repository for the version control on the web code and API code.