"Dedicated" versus "Shared" database

[JackAce]

What are the differences between setting up a database as "dedicated" versus "shared"?

We started out using a shared database, but we wish to transition to every developer using a local copy of the database. The database was initially set up as a shared db, but right now I am running the db locally. I am the only one doing this. All the other developers are linked to a server's copy of the db.

When I linked the db via Red Gate Source Control, I selected "dedicated". Should I have selected "shared"? Will we run into problems if we keep the environment mixed?

[andy.campbell.smith]

We've got a support article up about the differences between dedicated and shared models which you might find useful here:

http://www.red-gate.com/supportcenter/Content?p=SQL%20Source%20Control&c=SQL_Source_control%5Carticles%5CSSC_Development_Models.htm

It's a good idea to go for either one or the other, but you'll probably be fine keeping the environment mixed. If you do run into any trouble, let us know!
_________________
Andy Campbell Smith

Red Gate Technical Support Engineer

[JackAce]

The article is a good start, but I was looking for more detailed information under the hood. The article mostly says, "If you are working in X type of environment, you should do Y." I am looking for something that says, "If you are working in X type of environment, you should be aware of Y."


For example, some things that I noticed when working in a mixed environment (some developers with 'shared' and some developers with 'dedicated'):

• Sometimes you can right click a change in the "Commit Changes" tab and select "Undo Changes". Sometimes you can not. I haven't figured out the exact pattern yet.
  
• Working in a dedicated (local) environment is much faster especially because our database and source control servers are remote and we have to connect to them via VPN. This is frustrating when working in a shared environment because the Commit Changes tab usually times out when trying to analyze changes.
  
• One major thing that is a problem for us (working in the mixed environment) is that we automate our deployments to database environments. So our continuous integration server can deploy the versioned database to our development environment and our QA environment. If I check in a change from my dedicated (local) machine and deploy it to a shared database server (where other developers are working), their uncommitted changes will be wiped out.
  
• In order to avoid clobbering other developers' changes, I am currently using SQL Compare and SQL Data Compare to deploy my committed changes. This is not ideal (especially because only a few developers have licenses for these two products -- most only have licenses for SQL Source Control), and will go away once all developers can work locally.
  

I think an article that outlines "gotchas" like the ones above would be very beneficial.