Profiling feature for SQL Server 2012

Question

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.

Answer 1

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

Answer 2

There are numerous ways to accomplish this but there is no SQL Server feature solution that I'm aware of. This is both a very common and rather difficult problem.

My preferred (and the most difficult) method - Abstract the persistence away
During development you can call an in-memory persistence service built to help you iterate and to pre-hydrate data for well-known scenarios and to help with tests. In production and during integration testing you hook into the database persistence service. This also helps you when you want to move some or all persistence to a different technology (you need to add search, move to CQRS, etc). A lot of people call this "database as a service" and often use domain rather than CRUD methods ActivateUser(UserID) vs UpsertUser(UserDTO). This takes a lot of work up front though.

Visual studio database projects
You can quickly switch between schemas by publishing you current branch's version of the database project. Since this is a model-first deployment method (as opposed to change-script based) it works great with source control, especially branching, but you may need to publish the schema each time you change branches if you share a database. You also may need to create hydration scripts if you keep dropping and re-adding columns and you may have coordination issues between people sharing the database. This is what I've used most often in practice and I love that after each publish I KNOW my schema is correct and I can go to any commit - publish it - and have that same guarantee.

Storage snapshots
If you use a SAN or NAS often they support the ability to take a snapshot and write to it. SQL server snapshots are read only so if you still want to write, this is your fastest option. It can instantly give you a new identical db to work from and you can have it be your production db if you'd like (obvious security concerns here!). This is tough to setup and I've never had an automated solution like this but I'm sure it's possible. You can accomplish the same thing with database backup/restore and it'll be easier to automate but if you have a big development db it may slow you down.

Synonyms
You can also use synonyms to accomplish exactly what you're looking to do although it'll be tedious to setup. We use synonyms to create fake replicated databases in staging environments. Anyway, create your real databases like DB_ABC123, DB_ABC124 and then create a database DB with synonyms that point to the objects in your desired database. You'll have to run a script each time you change branches to swap the synonyms (you may be able to automate this with GIT, I'm not sure) but this should allow you to switch between DBs without changing configs. Honestly I'd rather just have different configs tracked in my branches though.

Answer 3

you could create schemas for each version, and switch the default schema of the service account connecting to the database (if that's how your app works), but you'd still be maintaining the same set of tables/etc... they'd just all be under one database.