Wednesday, July 25, 2012 1:52 PM
I am currently part of a team that is using SSDT and Visual Source Safe to manage our DB objects in a small team. We really like the deployment feature, however, we've run into a few issues when using it as a team. We are all doing our development on a development DB that we all have access to (this is on a server we all connect to and share, rather than our own local DB).
The problem occurs in the following case:
- Developer 1 checks out a stored procedure, makes changes to it and then deploys it to the development server to test
- Developer 2 makes changes to other DB objects and deploys their changes, overwriting the changes developer 1 made with the old stored procedure (since developer 1 hasn't checked their changes in, or developer 2 never got lastest)
I was wondering if maybe there were some options we could set to always get latest on deploy and not deploying any objects that are checked out? If not, I am curious what your workflows are with using SSDT and Source Control on a small team to avoid issues like this.
Thursday, July 26, 2012 9:32 AM
One thing you can do is to take a snapshot of the freshly checked-out source, make your changes, then run a schema compare against the snapshot. The generated script should update your development DB without making any other changes. However, this might not really be what you want to do: if, for example, Developer 1 makes changes to a stored procedure A that references Table B, while at the same time Developer 2 makes a change to Table B that does not affect his copy of procedure A, this is going to lead to a lot of hair pulling while they both try to work out why their changes don't work.
I'm working in a small team using SSDT, and I can say without hesitation that the best way of working is to have a local copy of the database. The SQL Server localdb is small and lightweight, and it should already be installed on your machines. For my money, database changes should be tested on your local machine, and should only be deployed to the shared development server *after* they've been checked in. That way a) no-one's checking in broken code, b) the development server is always up-to-date (and it doesn't matter who deploys the changes, as long as they're working from a fresh check-out), and most importantly c) no-one is going to make breaking changes to other peoples code.
Just my two pennthworth. :o)
Thursday, July 26, 2012 10:10 AM
We've been mulling over this very problem here at Red Gate. Tom points out correctly that the ideal approach for using SSDT is to have individual local copies of the database. However, we've heard that many users aren't yet in a position to move away from a shared development environment, which is why we're hoping to augment SSDT with some functionality to support this use case.
We have some designs we'd like to test, so please get in touch with me if you're interested in seeing them. My email is David dot Atkinson at red-gate dot com.
Product Manager Red Gate Software
Thursday, July 26, 2012 4:53 PM
Great question, and Tom thanks for a great response. We definitely recommend the use of LocalDB in the way that Tom suggests.
You propose 2 great ideas otherwise about options that could help control the flow of changes between a specific shared database and source control (get latest before deploy, exclude checked-in objects from deployment).
I also posted a blog post addressing some possible workflows with source control here: http://blogs.msdn.com/b/ssdt/archive/2012/07/17/managing-database-change-in-source-control.aspx
One additional thing that can be used today for preventing the collisions you describe:
You can use Registered Data-tier Applications. This may sound a bit more difficult because we honestly haven't quite completed the end to end story in VS, but it actually can provide a nice solution. The concept of Register is that if you Register a database as a Data-tier Application, the *.dacpac file representation of the database is stored on the server along with the full database. When you go to deploy new changes, the system can check that the .dacpac on server still matches the database on server (drift check). So let's say the .dacpac is Registered by a deployment from what's in source control (can be automated or manual). Then Developer 1 makes a change directly to the database, not in source control. Developer 2 can get a drift report via sqlpackage.exe (simple command line tool) to see what has changed in the database from the .dacpac deployed and registered from source control. If he determines there are no differences or they don't conflict with his work, he can go ahead with his deployment in VS. If he sees something that might conflict, he can address it. Obviously, we'd want this type of capability integrated much more easily into your experience in VS so that it's just a quick step or configurable option in your workflow -- but it's not there yet.
Now that I've made things more complicated :), I will say that the LocalDB/isolated sandbox method is highly recommended, and that we are also working on ways to coordinate direct shared database changes with source control. We do also work closely with David and his colleagues from Red Gate, so improvements should be coming along. Hopefully we've given you some things to help in the meantime.
Sarah McDevitt Program Manager SQL Server Data Tools