Shared & Private on Same Database

[sonicm]

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.

[Brian Donahue]

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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[sonicm]

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?

[Brian Donahue]

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.
_________________
Brian Donahue
Technical Support
Red Gate Software Ltd.
44 (0)870 160 0037 ext 8521
US and CAN 1-866-RED GATE ext 8521

[sonicm]

Hi,

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

Many thanks