Should we have separate database instance for each developer?

views:

512

answers:

+13 Q:

Should we have separate database instance for each developer?

What is the best way for developing a database based application? We can have two approaches.

One common database for all the developers.
List item Separate database for all the developers.

What are the pros and cons of each? And which one is better way?

Edit: More then one developer is supposed to update the database and we already have SqlExpress 2005 on each developer machine.

Edit: Most of us are suggesting a common database. However if one of the dev has modified the code and database schema . He has not committed the code changes but the schema changes has gone to the common database. Will it not possibly break the other developers code.

+16 A:

Both -

I like a single database that changes are tested on before going live, or going to a 'formal' test environmanet. This is your developers sanity check, it stays up to date with the live system, and makes sure they always consider each othres changes. The rule should be that changes don't go on here if they might break something else.

A database per developer is great (even essential) where more than one developer is making updates. It allows them all the development flexibility they want without breaking things for other developers.

The key is to have a process for moving database changes from development through to your live system, and stick to your process.

stupid-phil 2010-05-21 12:59:01

I've found that the "central" DB is also useful for testing against large amounts of data (for performance, etc.) or production data (where the data may not follow what you expect 100% of the time). As you said though, that shouldn't happen until after the developer has confirmed that his code doesn't break on his local DB

Tom H. 2010-05-21 13:25:13

"... and stick to your process" This is *critical* if you want to do give each developer their own db (a good idea IMO). Disaster *will* result if you start doing 'just this once' exceptions.

DaveE 2010-05-21 16:55:15

+8 A:

Shared database

Simpler
Less cases of "It works on my machine".
Forces integration
Issues are found quickly (fail fast)

Individual databases

Never affect other developers, but this is also a bad thing, in continuous integration

We use a shared development database and it works out nicely. Our schema rarely changes in a way that makes it backwards incompatible, but occasionally a design change will occur before we go live, and we simply ask the other developers to update.

We do have separate development application (web) servers, but they share the same database. Our developers do have the option to use their own database, as they know how to set this up, and will do that on occasion, but only temporarily. The norm, for us, is to share the database.

Marcus Adams 2010-05-21 12:59:43

I agree with Marcus... The individual database also allows for a single location for SourceControl integration as well.We only go with individual databases under special circumstances. The most typical is an extremely long project development cycle (we typically release code every 3 months - if a project is going to cross multiple releases then we consider a separate development database and then merge the code back into the primary dev database closer to the roll date to QA to prevent interference with the other projects).

John Franklin 2010-05-21 13:08:26

I think Shared Database is better when you can get away with it. Usually, after initial setup phase, the database changes come slowly enough for this to be okay.

Brian MacKay 2010-05-21 14:07:25

I respectfully disagree. The norm depends on the type and the size of the project. Also on the development methodology. For example, if you choose shared database approach, then you *can not* change schema often. Which is fine if you don't change it often :) But, as you say for certain tasks you use individual databases, so maybe in all fairness you should change the answer to state that for such and such tasks you use individual databases and for such and such you use shared database (if your schema does not change that often then it sounds that your development work is mostly maintenance).

Unreason 2010-05-21 14:12:05

@Unreason, when I said the norm, I was talking about our team and our application. I updated my answer to clarify that. Our database work is hardly mostly maintenance. The schema changes all the time, but we're mostly adding, not refactoring.

Marcus Adams 2010-05-21 15:33:17

@Marcus, well ok, so replace `change` with `refactor` in my argument :) My point is that you have a shared database, but for certain tasks you use local database. Couldn't it be said then that you use shared one for official testing of integration (and development providing the development will not break things) and when development needs to be isolated developers resort to local copies? (even though the decision might be at the discretion of a single developer or some group of developers). And that it is equally important?

Unreason 2010-05-21 16:53:56

+1 A:

Why on earth would you want a separate database for all developers? Have one common database for all, that way the table structure is consistent and the sql statements are as well.

user279521 2010-05-21 13:00:40

What if you want to change the table structure? What if you need to test things that require dropping all the data or changing all the data? The approach you suggest works well only if the schema gets frozen and even then not for all tasks. What if all the developers are not on the same network? these are just a few reasons on earth. :)

Unreason 2010-05-21 13:59:53

@Unreason - strongly diasagree with you, we make schema changes all the time and use only one database. This way you find out much sooner if your change affects something unintended.

HLGEM 2010-07-13 14:28:17

@HLGEM, I was imprecise - I was thinking of schema changes that are not coordinated (developing an idea, testing certain scenarios, etc); you think about changes that are coordinated. I see your point and I think a good answer (stupid-phil) was accepted for this question; at the end it boils down to methodology.

Unreason 2010-07-14 07:34:30

+2 A:

I would opt for solution #1 : One common database for all the developers.

Pros

Less expensive for the infrastructure;
Only one dump is required when it's time to refresh the development database;
Everyone develops with the same data, so it closely represents the production environment;

Cons

If one developer performs a bad operation, this could impact a larger amount of developers.

As for solution #2 : One independant database for each of the developers;

Pros

This could be useful for new features developments, when development requires isolation;

Cons

More expensive for the company (infrastructure, licences...);
Multiplication of problems caused by eager isolation development environment (works in devloper's environement, not integrated);
Multiplication of dumps by the DBAs of the same copy from the production environment.

Considering the above, I would recommend, depending on your company size:

One database for development;
One database for testing the integration;
One database for acceptance tests;
One for new feature development that will perhaps require integration tests.

If your company doesn't require integration tests, then go with acceptance tests, this step is crucial before going to production.

Will Marcouiller 2010-05-21 13:09:36

+1 this is a good overview, but I have a lot of comments and will try to argue against the conclusion

Unreason 2010-05-21 13:45:26

'Everyone develops with the same data, so it closely represents the production environment' - this can be con - developing might require to drop all user data or some parts of it, or to test different scenarios; if you keep the central database, yes it can be useful sometimes, but it is also limiting

Unreason 2010-05-21 13:46:57

'Multiplication of problems caused by eager isolation development environment (works in developer's environment, not integrated);' - on the other hand if this will be the case then if people develop on the same server they will step on each other toes as they develop. I see this as a con, but you might argue that early discovery of integration problems is actually good. But, I would argue back that it shows that the changes were planned initially, and no mechanism for coordination was established.

Unreason 2010-05-21 13:51:32

+7 A:

Thought I'd throw this out there, but why not let every developer host their own instance of SQL Server Developer on their desktops and then have a shared server for each of the other environments (development, QA, and prod)? I think even the basic MSDN that comes with Visual Studio Pro (if you opt for it) includes a license for SQL Server Developer.

The developer can work on their desktop without impacting the others and then you can have them move the code to the next shared environment as you see fit (at will, with daily/weekly builds, etc.).

EDIT: I should add that the desktop instance allows developers to do things that he DBAs often restrict on shared environments. This includes database creation, backup/restore, profiler, etc.. These things are not essential but they allow the developer to become so much more productive while reducing the demands they make against your DBAs.

The shared environment is completely necessary for testing - I would not recommend going from desktop to production. But you can add so much by allowing the developers to have 100% control over a given database environment (including isolation from others) with a relatively minor cost.

Mayo 2010-05-21 13:15:17

Simple answer:

Have one development database, and if the developers want their own, they can just run their own instance on their own machines. Just be sure to test/publish on the shared.

naspinski 2010-05-21 13:21:08

It needs to be a team decision rather than an individual developer's decision. If a dev runs checked-out code on a central DB then that impacts the work of others in the team.

dportas 2010-05-21 18:24:38

+1 A:

Depends on your development, testing and maintenance cycles. Also on the size and location of the development team (and of course organization). If you support several versions of the database you might need even more environments.

In real world I found the following approach rather satisfying:

single central database/application for testing purposes, gets all the changes by various developers periodically merged into it
local copies for development (so you are free to drop and reload the whole database)
upgrade scripts are maintained for any changes to schema, auxiliary and sample data sets

Here are some further points:

If two developers (two teams) are working on changes that can affect each other then they should complete their tasks independently and then integrate/merge and test. For this it is much better to have separate development environments (unless they have to work together in which case I consider them to be a part of the same team; still they can work on their own copies of the database and share it if necessary)

If they work on the changes that do not influence each other they could work on the main server. Or on their own local copies of the database.

So, developing on the local copy has all the benefits with no risk in a general case (when you support multiple versions of the system and maintain upgrade scripts anyway).

Still it is great if you can share test cases so ability to dump/restore the database easily and quickly is a big plus.

EDIT:
All of the above assume that having a copy on the local machine of the whole system for testing purposes is feasible (size, performance, licenses, etc).

Unreason 2010-05-21 13:33:37

`..gets all the changes by various developers periodically merged into it` - How is this done? In previous jobs, we had to write manual SQL scripts for every change we made to update the schema/data, and manually run it on the server when we committed. Is there a better, more standard way?

BlueRaja - Danny Pflughoeft 2010-05-21 15:06:19

we have an XML that's used to produce script to create database and some generic code and some sample data sets; however the database upgrade script is manually edited since schema changes for the upgrade often need to manipulate existing data in non-trivial way. during development all particular issues that are worked on are clearly marked in the source and merged and tested both for new installations of system and for upgrades between version.

Unreason 2010-05-21 15:17:41

@BlueRaja, Ruby on rails has rather elegant way to do database upgrades. Similar system is probably available for other platforms. See http://guides.rubyonrails.org/migrations.html

Juha Syrjälä 2010-05-21 15:32:35

+1 A:

We do both:

We use code generation where I'm at and our database is generated as well. So we have an instance on each developer's box where the database is generated. Then we use the scripts that are generated to apply the changes to a central test database. If that goes well we apply the changes to the production database during a release.

What's nice with this approach is that when our "source of truth" is checked in to source control, all the database changes are automatically distributed to the other developers when they rebase and regenerate. It works well for us.

adam0101 2010-05-21 13:33:39

The best way is single database on Test/QA server and one database (probably on developer's local computer) for each developer (so, 10 developers work with 10 + 1 databases).

The same approach as for general development: each developer has own copy of source code on local machine.

Also, multiple-database approach simplifies the keeping database schema in version control systems. We are keeping database creation scripts in SVN.

We are using the approach, described here: http://www.sqlaccessories.com/Howto/Version_Control.aspx

SQLDev 2010-05-21 13:54:33

One per developer plus a continuous integration and build server to run unit and integration tests. That gives you the best of both worlds.

Having all developers modify a single dev database quickly becomes less productive once the amount of database change reaches a certain level because it forces a developer to deploy changes to the shared database before he is ready to check-in, which means other parts of the code line may break unnecessarily.

dportas 2010-05-21 14:56:29

You might also want to look at Refactoring Databases. Aside from discussing database changes, he includes discussions on going from development to production in a way that reduces risk.

Kathy Van Stone 2010-05-21 17:12:48

Excellent book!

HLGEM 2010-07-13 14:39:58

The biggest problems with developers having their own databases are:

First it is unlikely to be the size of the real production database (if you take all the databases we need to work with here, they would take up several hundred gigabytes of space, I don't have that available on my machine), this causes bad code to be written that will never work on a large database for performance reasons. SQL code should never be written against a data set significantly smaller than the one on prod.
Second, developers who use their own database create problems when they spend a long time developing something and then find out only after they merge with a real datbase that it affects something else. You find this stuff much faster when you share the environment. So there is inthe end less wasted development time.
Third developers working on related things need to know about the changes you are making, it will affect their change.

When you know you are going to affect others, I think you tend to be more careful what you do which isa plus in my book.

Now the shared database server should have what we call a scratch database, a place where people can create and test table changes, so if they are doing something that might need to drop and recreate a table (which should be a rare case!), they can test the process first by copying the table to the scratch database and running their process there and then changin to the real database when they are sure it works. Or we often copy a backup table to the scratch database before testing a particular change, so we can easily recreate the old data if it goes bad.

I see no advantages at all to using individual databases.

HLGEM 2010-07-13 14:38:52

ansaurus

tags:

views:

answers:

Should we have separate database instance for each developer?

related questions