Dedicated vs shared database development

fgsimon

Sorry if this is already discussed, but exactly what is the difference (technically) if I choose Dedicated or Shared database development?

datacentricity

If you operate in "Dedicated" mode, each developer will have their own sandbox development database in which to code, test and experiment. "Shared" mode means that all developers develop and test against a single shared development database.

If (as a developer) you are working against a "shared" database, (from memory) SSC will show you all changes (including those made by other team members) but by default will only commit changes made you. Obviously with a "dedicated" development database you would only see your changes.

With a "shared" database all developers can see each other's changes immediately - whether they are ready for general consumption or not. This includes experimental schema and code changes, and any bugs. This approach also introduces problems around what happens if two developers are working on the same code module or when one developer starts coding against something written by another developer that is only partially complete or even gets rolled back altogether. The only advantage with the "shared" approach is that it reduces the number of development instances that need to be maintained by the DBAs (ignoring the fact that most SQL devs could quite happily do their own dev instance support/maintenance)

The advantages with the "dedicated" model is that the rest of your team will only ever see the changes you have committed to source control after all tests have been completed etc. You can experiment to your heart's content in your sandbox database safe in the knowledge that you won't break everyone’s shared development database/application. The "dedicated" model is also the only way for developers to write and run their own unit tests without clashing with the activities of their colleagues.

Generally speaking, the accepted wisdom is that “dedicated” is the only way to go.

Some companies worry about cost (SQL Server developer edition is dirt cheap) or, more often, control. If all databases are in the cast iron grip of the DBA team then senior management can sleep soundly at night safe in the (lack of) knowledge that their database developers are working with one hand tied behind their backs and one eye shut. If this situation applies to you, Troy Hunt has quite a nice piece on The evils of shared databases. Benjamin Day also has this to say on the subject.

fgsimon

Thank's, but I still don't fully understand what this means.. is it just that in shared mode all developers share the same database?

What identifies a user? Is it the Windows-login or the DB-login or something else?

Is the sandbox an abstract way to explain the behaviour or does it have a 'physical' representation - some sort of login or a separate database or some directory or something else?

datacentricity

Okay, let’s try leaving SSC out of the mix for a moment.

In the “shared” model, all database developers write their code against the same database. Hopefully they connect to this database with their own dedicated login (whether Windows or SQL Server) rather than some global dev user account. There is a single dev database and everyone develops in that.

In the "dedicated" model, each developer makes their changes against their own dedicated database. This could be multiple databases on the same SQL Server instance: PubsTom, PubsDick, PubsHarry etc but it is more likely to be set up so that each developer has their own dedicated SQL Server instance. This might be a VM or named instance on a shared physical server or SQL Server Developer edition (or even SQL Express) installed on each developer’s desktop.

User is what you think it is – a Windows or SQL Server login.

“Sandbox” is just a term used to describe a dedicated development environment where a developer can “play” without impacting on anyone else. It is a physical thing and you can only have a sandbox if operating the “dedicated” model as a shared database cannot, by definition, be a sandbox - unless everyone else has gone home for the night maybe

So the question in SQL Source Control when you bring a database under source control relates to whether all your database developers work in one shared database or whether they each develop in their own dedicated database. As far as I know, it is not possible to combine both approaches in SSC.

I am not sure how SSC would work if all your developers use a single global user account like “sa” to develop.

fgsimon

Thank's again for the detailed explanations, it's clearer now. I was mainly looking for the technical differences in the software (I think I already understand the different methods of working).