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.
Best Answer
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.