Synchronising databases

I have one main database and let's say 10 developers' databases.开发者_JAVA百科 I need some way to synchronize main database schema and developers' databases schemas. And I need some way to make different test data for developers' databases. One more nuance is that developers' databases may be run not on the same database server as main database.

What are your suggestions in implementing this task?

Build your changes and tests from Source Control and then use a comparison tool to make an incremental release script. Best practice is to automate Continuous Integration - build the database from source control regularly or on every check-in.

Have your developers write upgrade scripts for each change they make. Store these scripts in source control, along with the rest of the code. Before deploying, run the scripts on a backup copy of your production database (SQL Server makes this fairly easy - you can simply restore a backup file to a different database), and test the new code against that.

There are tools to diff databases, but they are complicated and far from perfect.

Visual Studio 2010 Database Project

• Working with Database Projects

or the RedGate tools