Shared & Private on Same Database

sonicmsonicm Posts: 7
edited July 5, 2012 6:19AM in SQL Source Control Previous Versions
Hi,

We have a database that is on our main development server so is liable to be tweaked as development progresses. This has been setup as a shared database against TFS. We have also have our DBA that has it as a private database on his machine both linked to the same source control database.

The problem seems to be that when he signs items in on his private machine it all seems well. However, when we go to the shared machine it knows the structure has been updated but says nothing when you try to get latest. You can see the sign in, in the history. Also, on the shared database it wants to overwrite the changes and take them out again?

Is this setup not possible or are we doing something wrong?

Many thanks.
· Share on Twitter

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    It doesn't sound like this is the best way to source control a database. Assuming you have two separate databases, you should probably not be using the shared model. What happens, for instance, if changes are made to the "shared" database, and the stand-alone developer database doesn't "get latest" from source control before getting to work on his own copy? I can see potentially lots of conflicts and out-of-date objects.

    Everyone should really be working on the same database with the shared model, or everyone should be using the dedicated model. Once changes are done on the shared database, you can deploy the changes using SQL Compare to the build server, which is going to be a one-way operation.
    · Share on Twitter
  • sonicmsonicm Posts: 7
    Hi,

    Thanks for the response. I did wonder if it was good practice or not.

    So I feel we should look at the following:

    Devs work on local machine out of source control.
    Devs then update the shared DB with their changes and check it in.
    We can then use a migration script to deploy to the UAT box

    Sound about right?
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    That's almost the idea - the devs would typically all work on the same database (shared model) and then you would SQL Compare the dev to UAT and update the UAT with the agreed changes.

    Or everyone has their own copy of the database (dedicated model), checks in their changes, and you use SQL Compare to directly compare the files in source control to the UAT database and deploy the changes that way.
    · Share on Twitter
  • sonicmsonicm Posts: 7
    Hi,

    Yes that makes perfect sense I've changed our procedures to follow these guidelines.

    Many thanks
    · Share on Twitter
Sign In or Register to comment.