"Dedicated" versus "Shared" database

A SQL Server Management Studio add-in to source control your database in Subversion or Team Foundation Server.

Moderators: Chris Auckland, David Atkinson, sherr, PhilScrace

"Dedicated" versus "Shared" database

Postby JackAce » Thu Nov 17, 2011 3:00 am

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?
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm

Postby andy.campbell.smith » Mon Nov 21, 2011 12:24 pm

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/C ... 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
andy.campbell.smith
 
Posts: 173
Joined: Thu Oct 20, 2011 11:19 am
Location: Red Gate Software

Postby JackAce » Mon Nov 21, 2011 7:41 pm

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.
JackAce
 
Posts: 45
Joined: Fri Jul 08, 2011 11:00 pm


Return to SQL Source Control 2

Who is online

Users browsing this forum: No registered users and 0 guests