What are the advantages of having a centralised SQL database server for development?

I have an idea to use for my fellow developers and basically wanted feedback.

The idea is to have a centralised SQL Server database server for all developers to use.

Each developer would have either own database on the server for the specific project (for example, SW_ProjectName).

Therefore if a developer was off for a week and we needed to hit a deadline we could easily hook into their database by changing our local config connection string.

In addition to this their would be a test database (for example ProjectName_Testing) which the developer would use to run test their sprint before deploying to Staging.

Can anybody think of any advantages and disadvantages for this approach instead of having everybody having their own local databases for development.

I don’t know of a single good comprehensive solution to this.

Local development means that developers don’t break other people working on their own code against a shared database. However, when you get latest code, you also need to get the database into the right state to match the code changes you’ve merged in. If two people are making changes at the same time, merging can be difficult since database upgrade scripts can be incompatible. Column order doesn’t normally matter in a database, but it can be a bit annoying for databases to be different.

There are good tools to compare schema and data and apply changes. I would say schemas should aim to be identical. However, typically you want lookup-type data updated between developers but not regular application data (new customer types, but not new customers). Configuration data you might want updated, BUT sometimes only a subset (new printer options but not file path settings).

You would think that ideally, you could JUST rebuild your local database completely once you’ve each merged changes. If you’ve set up a bunch of test scenarios through the application for testing (instead of in the build scripts), you now don’t have a script to get those changes back into the database. And this is more difficult as the scope of the database schema increases – with surrogate keys and parent-child relationships which all might have complex dependencies.

In the ideal scenario for a central database, you would have a developer DBA managed the database interfaces for the app and control that so that the exposed interfaces would consistently evolve over time and all developers about that level would see the same interface at the same time. But then you have two separate groups coordinating their different feature timelines.

Which I think goes a long way to showing why people are still attracted to a variety of other approaches which put more emphasis on code and less on the database. Ultimately, I think that just shifts the problem around.

You state some of the obvious advantages in your post. Sharing databases will allow you to reproduce bugs across environments better and you can all work on a shared dataset if needed.

One big disadvantage I can think of is that if someone introduces a bug in a shared database or a bug in the program that crashes the database server, you now have multiple developers unable to do work until the problem is solved.

My advice would be to use a hybrid approach. Having developers use their local database for new and untested code and as the code matures and moves into beta or RC status, then it can use the shared databases to allow for quicker bug fixes and more common unit and system tests.

Everything necessary to complete a project must be in version control, in a branch. To take over somebody else’s unfinished task, all we need to do is pull a branch from git.

This applies to test data etc. as well. This will allow the continuous integration server to build/populate a new test DB from scripts downloaded from version control, and run the test suite

I am not sure why was this question migrated from stackoverflow – this is IMO strictly programming.

1. When making the schema changes by multiple developers, you do not need to keep track of individual changes.

2. It will avoid redundancy otherwise De-Duplication will be required.

3. A bug is required to be fixed using centralized DataBase.

4. It is readily accessible by new Developers, otherwise Configuration Management is required.

5. It will protect from scattered records in different computers.

6. Save space by keeping the Database in Central location.

7. In case of Synchronization/Replication, Central Database will be good otherwise more efforts will be required in case of Multiple Databases.

Having a centralized SERVER isn’t a bad idea; I think other answers to the this question have focused on the pros/cons of having a centralized database. The only real negatives I see to having a centralized server are:

1. You have multiple connection strings to deal with, which increases the chance of error. In your hypothetical situation, if a dev is gone for a week and you have to get their code out of their database, you’d have to be really careful to change back so you don’t have your code in their database.

2. You lose the abiliity to have disconnected development; while you may have a great network infrastracture (VPN, etc), you can’t code on a plane (not exactly true anymore) if your database is on a server in the office.

I think you’d be better off using some form of source control for your database, and having your code shared via that method; it’s not necessarily easier to manage, but it does address the problems laid out above.

Personally, I would have my cake and eat it too.

Every developer should use a local instance but they should check in all of their database scripts and those scripts should all be incrementals that are deployed to the test server.

Every developer should write data migration and reversible scripts as well before checking in so that the test platform is always clean and easily reversible back to the last known good configuration.

Everyone will have access to the source that caused the changes this way and ‘volatile changes’ are left on dev machines where they belong.

Downside with this is that developers must integrate changes into their environment if there are multiple people doing DB development. But if they’re creating incrementals and roll back scripts as part of their dev process it should be fairly minimized.