I'm using SQL Server 2012 to develop php/web applications.
On my development machine, I have SQL Server also.
The php codebase is version controlled with git.
The various applications we have (and there's more than 30) are in the process of being updated. As this happens, the database structure sometimes changes, and the data (even on my local content) changes a lot too.
Even as we develop new code, we need to work fixes and amends into the old code as well.
We use branches to develop this within git and that works well.
However: maintaining a database set to match these is a serious hassle.
If a project connects to three databases, and for example, there are three branches, the development becomes confusing, as well as a hassle to set up.
So – on to my questions:
- Is there a database version of "profiling"?
- Is it possible to have parallel versions of the databases that can be switched to active/inactive – just as switching a branch separates what amounts to the same code?
- Switching a branch in git is a matter of seconds. Is there a tool to do the same within parallel versions of the current db structure?
This would save me some serious headaches (and the associated dangers of leaving a db-config pointing to the wrong database).
Any advice or tips to pointers to approach this problem would be appreciated.
Best Answer
If you are using Developer or Enterprise Edition, you can use Database Snapshots to get you most of the way. Basically, this allows you to make virtual "copies" of your database and apply your object changes. You will still need to change your connection string, but it could give you an easy to maintain alternative for standing up your alternate database branches.
Regardless of how you approach this, you will need to have some different database name and/or instance name for your database. Data and databases are stateful in nature, so simply swapping structures in and out isn't an option. After all, you can't just turn off columns and the data they contain. You have some options for disabling objects, like indexes and triggers. But beyond that, you can't just switch data "off".