Using SQL Source Control in a multi tiered dev environment

kevine323

We are in the process of evaluating SQL Source Control and so far we are very impressed with it's speed and ease of use. We are coming out of a VS dbproj environment that quickly became too cumbersome to manage. Our applications group uses VS 2010 with a TFS back end to source their application code. That TFS backend has 3 branches, a Production, PreProduction, and a Development environment. Their move their code up those branches in a normal development lifecycle. Each of these branches also has a corresponding SQL server.

I'd like to know if there are other SQL Source Control users out there who are using the SQL Source in a multi tiered environment like this. How do you have it setup? Are you SQL Source'ing all three environments? How are you handling Merging of the source from one branch to another? Should we just source the Production environment?

Any other users who could shed some light on this would be greatly appreciated.

James B

Thanks for your post.

As a general rule, we don't recommend you link a production DB to SQL Source Control - partly so the background polling doesn't cause any performance issues, and also to avoid accidental changes being made.
Generally, you'd use SQL Compare to deploy changes across to production, and you can use a source-controlled DB by using the SSMS Integration Pack to kick the process off from directly in Management Studio.

What kind of purpose does the Pre-production DB serve? Are further development changes made to that, or is it simply an interim staging/test DB? If the former, then you can certainly source control it as well; linking to a different location in TFS. Users can then work on dev and pre-prod separately, checking in changes as required. You can also use Compare via the SSMS-IP to update development to pre-production.

Hope that helps...

kevine323

James, thank you for your reply.

We are using our development environment for smaller unit testing. Once testing is done on that level, it is moved to PreProduction for larger system testing.

In our development environment, we use a shared database between multiple developers. What I'm worried about if we sourced from that point is security. I dont want developers checking in changes that haven't been approved or checked. I'm guessing that can be managed on the TFS level.

James B

Yes, there's no permissions settings in SQL Source Control - if you wanted to control which users can commit and so on, you'd need to manage that via TFS.
SQL Source Control should show you who made the changes if you're in shared mode, once you visit the commit tab- although this does rely on the default trace being readable, and having not rolled over.