Saving sql database schemas synchronized between developers

Question

We are a small development team (about 5), executing a dev project from different places. We use SVN as a code repository.
The biggest problem that we are facing right now is that our database schema is not completely synchronized between all of us. I have the following options: 1. Work out the "central" database. This is a bad idea and most likely will not happen 2. The developer has a "gatekeeper" who will store the database version and each developer will follow the changes. 3. Ask each developer to check their changes for database script changes. It can get very dirty.

Sorry this is a .net C # project

Any ideas?

Answer 1

This is a difficult problem. We reviewed it by revising the sql used to create the schema (automatically created from Enterprise Architect). We had huge problems when people did not update their database schemas because they thought it took too long to re-create a dataset that would have reliable test data.

Our solution was as follows:

• Add SQL Schema Generation to SVN
• Add data entry scripts to SVN
• Add schema / data dumps to SVN

We used Hudson to configure automatic database builds that would check for changes in the revision. It automatically re-creates the circuit, inserts all the data, exports the dump file, and then transfers the dump file to SVN.

Basically, it came down to starting a database import, which took about 20 seconds. Once you quickly create a database, developers will not have to do this often.

Answer 2

Why is working with the same database server a bad idea again? Because all developers make changes that can harm others? In this case, I will have one person with schema changes and using a VPN to enter the network with the database server. Now I am in the same boat, I just picked up a Cisco router to solve my cheap VPN needs (<$ 100).

Answer 3

I read the artist a few years ago from Paul Graham about Agile Database Development. I have problems finding. It seems that all of these terms are too general, and my memory is too blurry to get closer.

I came across http://code.google.com/p/migratordotnet/

It was modeled on the Rails ActiveRecord migrator (mentioned earlier), but aimed at .net. I am not a .net programmer, but it looks like what you are looking for.

Answer 4

We had the same problem in the project in which I am currently working. We adopted Tarantino as a solution that works surprisingly well. Each developer works with a local database. When a developer needs to make changes to a schema, he creates a script and validates it.

Tarantino keeps track of which scripts each developer has already run in their local database and applies new scripts. Therefore, if developer A makes changes and checks the SQL script, developer B will receive the changes when he / she receives the signature files from the original control. When Developer B launches Tarantino locally, only the most recent scenarios will apply.

Of course, most of this can be done manually. Tarantino makes it easier, but it's not perfect. One of the advantages is that it can be easily integrated into the assembly process. Scripts for storing data in databases can also be created.

Answer 5

MS is a bit behind in that it doesn't have a standard solution yet, but your option # 3 (shift scripts) is the way to go now. Most other modern modern languages ​​use the form of it at present in various different tastes. e.g. Check Rails Migration.

This post discusses many third-party .NET solutions. In my experience, migratordotnet is a great choice. For a more detailed study of the problem, check out the Martin Fowler article on this subject.

Answer 6

Have you considered using Visual Studio Team Database Edition? This allows you to put the entire source database in the source control, and each developer can independently work on their changes, and then, when they check, another developer can easily deploy these changes in his working copy.

Not sure if there is an SVN plugin that will work with the MSSCCI provider, but I suppose there must be something for this.

Answer 7

Assuming you are not using Team Edition Team Edition visual products, I would choose option 3. You also have a sql script.

Keeping their local databases in sync is no different from having to work with the latest source code.

If you use Team products, start using the database version (supplied with Developer Edition) to manage your database under source control

Answer 8

I am also working on a small team, and now we have our SQL scripts and data insert scripts that are checked in the repository in the same way as our code.

You need to make sure that people are constantly updated with the latest "source." We are inclined to do this in such a way that in case of any major changes / changes we meet and discuss them in any case, in order to inform everyone about what has changed, as well as provide an opportunity to view any database updates before their release.

Answer 9

This problem is not becoming more common. At some point, each team should ask this question. I made a general approach to the database and a separate approach to the database. The team always returned to the general database. It's just easier to maintain, and everyone needs to sync early, often sync. This does not deny the need to save schema changes and definitions in version control. You need to repeat the process of updating the test, intermediate and production environment. Net SQL migrations are not always sufficient, at least at a time when you need to use your own objects to generate data or make decisions. The best system I've seen (reminiscent of the systems I built on Perl, php, and Java, but improved by a couple of points) is the Ruby on Rails migration system. Check it out and do something similar.