Sub version for database (i want something for data values in the database, not for the schema)

Question

I am using github for maintaining versions and code synchronization. We are team of two and we are located at different places.

How can we make sure that our databases are synchronized.

Update:--

I am rails developer. But these days i m working on drupal projects (where database is the center of variations). So i want to make sure that team must have a synchronized database. Also the values in various tables.

I need something which keep our data values synchronized.

Centralized database is a good solution. But things get disturbed when someone works offline

Answer 1

if you use visual studio then you can script your database tables, views, stored procedures and functions as .sql files from a database solution and then check those into version control as well - its what i currently do at my workplace

In you dont use visual studio then you can still script your sql as .sql files [but with more work] and then version control them as necessary

Answer 2

let nhibernate generate your database schema for you.. hhehe

Answer 3

Have a look at Red Gate SQL Source Control - http://www.red-gate.com/products/SQL_Source_Control/

To be honest I've never used it, but their other software is fantastic. And if all you want to do is keep the DB schema in sync (rather than full source control) then I have used their SQL Compare product very succesfully in the past.

(ps. I don't work for them!)

Answer 4

These are some of the possibilities.

1. Using the same database. Set-up a central database where everybody can connect to. This way you are sure everybody uses the same database all the time.

2. After every change, export the database and commit it to the VCS. This option requires discipline and manual labor.

3. Use some kind of other definition of the schema. For example, Doctrine for php has the ability to build the database from a yaml definition which can be stored in the vcs. This can be easier automated then point 2.

4. Use some other software/script which updates the database.

Answer 5

Try Wizardby. This is my personal project, but I've used it in my several previous jobs with great deal of success.

Basically, it's a tool which lets you specify all changes to your database schema in a database-independent manner and then apply these changes to all your databases.

Answer 6

You can use Sql Source Control together with Sql Data Compare to source control both: schema and data. Here is an article from redgate: Source controlling data.

Answer 7

I feel your pain. I had terrible trouble getting SQL Server to play nice with SVN. In the end I opted for a shared database solution. Every day I run an extensive script to backup all our schema definitions (specifically stored procedures) for version control into text files. Due to the limited number of changes this works well.

I now use this technique for our major project and personal projects too. The only negative is that it relies on being connected all the time. The other answers suggest that full database versioning is very time consuming and I tend to agree. For "live" upgrades we use the Red Gate tools, they do both schema and data compare and it works very well.

Answer 8

http://www.red-gate.com/products/SQL_Data_Compare/. We were using this tool for keeping databases in sync in our company. Later we had some specific demands so we had to write our own code for synchronization. Depends how complex is you database and how much changes is happening. It is much simpler if you have time when no one is working and you can lock database for syncronization.