Dedicated DB VS Shared (Centralized) DB Model
I recentrly had a brief email exchange with Stephanie from Red Gate, where I explained that we use a centralized Development database, instead of every developer having a dedicated local version of the DB against which they develop. I believe that the Centralized Dev DB model is the model used most in the field, mainly for two reasons. 1) Most companies don't want their developers to have local copies of the database for security reasons, and 2) Development DBs often hold a lot of test data which would not be practical for all of the developers to have locally.
Having said that, Stephanie explained that though SQL Source Control was designed to work with the "dedicated" db focus, for a few good reasons, it is still possible to use it with the centralized model if you're careful.
I believe in a perfect world, the centralized DB, once connected to a Source Control repository, would simply keep track of every change made to objects. In other words, if I altered a stored procedure and clicked execute, that change would be automatically recorded in my source control history, so later if I had to, I could right click on it, and revert to a previous version. To be honest, I wouldn't even need the "Commit" or "Get Latest Version" functionality, because the DB would always be in the "Latest Version" state, and every change would be committed automatically. Just my thoughts.
Allen Berezovsky
Allen_Berezovsky
shared this idea
SQL Source Control supports either model, http://www.red-gate.com/MessageBoard/viewtopic.php?t=12947.
v2.0 has the following enhancements for the shared model:
If you setup your database as shared, then you will see who last updated each object on the Commit tab and Undo dialog. Only objects that you last updated will be checked by default. This will prevent you from accidentally committing/undoing others changes that work on the same db.
We are also addressing the problem so that you will no longer see invalid conflicts on the Commit tab.
-
damianlucid
commented
Allen you have hit the nail on the head. I totally agree. But I can see where they coming from with Microsoft releasing MS SQL Express with most it tools today but it scares me as none of my developers are to comfortable with MS SQL so its easier to have me control the DB and they simply access this via the network and make there changes as they go along.
I suppose using Red Gates backup software to make check point backups with realtime access to backups we should be able to see those changes I suppose, however source control sounds more sexy ;-).
-
bdill
commented
Steph, You kindof answered one of your own questions:
Q: What are the main reasons you work on a centralized db?
A: Keeping everyone’s “dedicated” db in synch used to be really hard..
Another reason is that every so often (weekly, monthly, etc) we take a copy of production and stomp the dev DB to get a fresh update of the live data (after we have synced DDL with SQL Compare of course).
FYI, my work history is generally limited to smaller dev teams of 3 to 5 developers in close proximity. If I wanted to stomp the dev DB, I only had to shout out "hey, is it OK if I stomp the dev DB!?" -
Allen_Berezovsky
commented
Hi Stephanie,
You make a good point about the coments. However, sql server, in my opinion, needs to work differently from other applications. What I mean by that, is that if you are developing an ASP.NET application, for example, you are working locally, and you commit the changes to the repository because that's where we get the code for our builds. In the case of a centralized sql server model, you synch live to qa, and qa to dev. Nobody would really by synching to the repository, in my opinion. Therefore, once I make an update to the stored procedure, where it has been committed to the repository or not, that stored procedure is live on that particular server. And the problem with that will be, when you have lazy developers, is that the changes will never be committed to source safe, and we will loose the change history. I would rather loose a comment, then a version of a procedure. And even that can be solved by popping up an input box or something and prompting the user for a comment when they click F5 or ! Execute
-
SQL Source Control works on both dedicated and centralized db models. On a centralized model, you will see all developers’ changes on the commit tab and not just your own. So, you have to be a little more careful about what objects you select to commit. You’ll only want to select and commit your own, but they are all checked by default. You should never have to use the “Get Latest” tab because you’ll never have to pull changes out of source control that we’re committed from other developers.
We’d love to hear any ideas you have about improving SQL Source Control when using either a dedicated or a centralized model. Please post a new suggestion if you have a specific idea that you think would help your db development.
A dedicated db is exactly like application developers that get their own working copy (except there is also data). The benefits are:
1. User changes cannot be overwritten. (I changed a stored procedure and then closed my query tab and then someone else changed it, but they had opened it before I executed my change. A db backup wasn’t taken in this time and now all my hard work is gone. :-(
2. Sandboxing – Major changes are isolated. If you break something, it doesn’t impact anyone else. You can always go back to the working version in source control.
3. Keeping everyone’s “dedicated” db in synch used to be really hard, but SQL Source Control will make this easy because you just have to check the “Get Latest” tab! :-)This dedicated db approach can work even if you only have one centralized development db server. You would just have a few copies of the db on that server (eg., ProjectName_StephaniesCopy, ProjectName_DavidsCopy, etc.).
What are the main reasons you work on a centralized db? Do you ever have any problems working this way? How would you like to work? Are there any other pros/cons to these approaches?
-
Would you really want EVERY change to be committed automatically? Don't you want to test it first and make sure it's correct before committing? Also, source control provides the ability to add a comment or message when you commit the object(s). This allows you to track WHY the changes were made. (If every change was committed automatically, then you would lose this why.)
-
bdill
commented
Wouldn't you still have to commit your changes to the repository? FWIW, we use a centralized Dev DB as well.