10

Problem description:

In our project we have one "production database" and many "devs". We want to know how we can manage and install the changes. We already have some procedures but it takes a lot of time and sometimes causes errors.

We can't lose data - so we can't use "drop table". We can only use "alter table".

Our actual "db versioning procedure" is like this:

  • We have a table named [actual_version] that contains the actual version of the installed db schema
  • We have a file named "changes_script.sql" that contains all database changes - this file is stored on SVN

When a developer wants to commit a new revision on SVN he needs to change_script.sql by adding a block: 

if ([acctual_version].version < "23")) {
    --- sql script ----
updateVersionTo("23")
end if

When we want to upgrade the database schema we just "execute" the change_scripts.sql

Does anyone have a better idea?

Montag451
  • 1,168
  • 3
  • 14
  • 30
itdeveloper
  • 339
  • 2
  • 3
  • 14
  • Thanks for response... I think that all what we need its good DB Comparer tools. What I want to do its select dev_db and production_db and click button "find difference". Next I can copy it manualy to change script. Do you have some idea? We have tested Red Gate's Sql Compare and it wasn't realy useful – itdeveloper Feb 18 '10 at 11:03
  • 1
    Hi! I'm a product manager at Red Gate and I'm curious to know what the shortcomings of SQL Compare were for you. Please feel free to get in touch! My email is David.Atkinson at Red-Gate.com – David Atkinson Feb 19 '10 at 19:32

6 Answers6

5

Red Gate's Sql Compare tool can compare two tables (or two collections of DDL scripts, or one set of scripts to a table, etc) and generate the migration script for you.

If you're working in Ruby you can look into "Ruby Migrations", which is a formalized way of doing DB versioning in code. (There are similar things in .NET such as RikMigrations and Fluent Migrator, and I'm sure similar things exist for other platforms as well).

As Randy Minder said, you can also use VS DB Edition to manage your schema for you, although I think the RedGate tools work just as easily and don't tie you to a specific platform or IDE.

Seth Petry-Johnson
  • 11,845
  • 7
  • 49
  • 69
  • @Seth - VS Database Edition does far more than just schema comparisons, it is a database schema management/versioning tool. We use it to version control our DB schema's in TFS. – Randy Minder Feb 17 '10 at 16:17
  • @Randy: Point taken, I didn't mean to imply that DB edition was feature poor. My point was that I have used SQL Compare and TFS to version control schema changes between software versions, and that if a VS license is prohibitively expensive, or if someone isn't working on .NET, it's not too hard to roll your own system using a schema comparison tool, a VCS, and a little bit of process. – Seth Petry-Johnson Feb 17 '10 at 16:28
  • SQL Compare is a great tool and can be used in your current system to generate the scripts to make version upgrades. The have a trial version, down load it and test it between two DBs you think have the same schema, if they are the same, I'll eat my hat. – Hogan Feb 17 '10 at 22:25
3

I do it very similarly, but use database extended properties instead of a table to track metadata version and the upgrade steps are defined in application, rather than a single script. Most upgrade steps are just execute script Upgrade.vX.Y.sql.

I actually find your approach superior to schema comparison tools (and that includes VS DB deployment) for several reasons:

  • I don't trust schema comparison tools changing very large tables, I rather have a tested script specifically designed for my table of 150B records.
  • Schema comparison doesn't handle removal of obsolete objects
  • If the application schema was modified at a client site a comparison tool will blindly attempt to upgrade it, but a modified schema may need special treatment: inspection of the changes, evaluation of impact, billing of extra work.
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
1

Have you heard of Visual Studio Database Edition? If you are currently using VS Team Suite or VS Developer Edition, you get it free. It is a database schema management tool that version controls your schema, allows refactoring, builds, code analysis and deployment.

We use it to manage our DB schemas and deployments. Great tool.

Randy Minder
  • 47,200
  • 49
  • 204
  • 358
1

You describe a process that's being used in lots of dev shops, I'm pretty sure - including in my company, too.

If you want to stick with this, one tool that's helped us is SSW SQL Deploy which allows you to grab a whole bunch of scripts (we have a "changes" folder for each version, and the files are label "001_something.sql" through "999_somethingelse.sql") and execute those as a whole against a database. You can also include that into your .NET apps via an API, or you can launch it from the command line during an install.

The highest end of the spectrum is to adopt a whole database change management process, something like DB Ghost by Innovartis, which is a lot more than just a bunch of tools. But this would require you and your devs to adopt that style thoroughly and live and breathe it. Very interesting, but so far, I haven't managed to convince my devs and bosses :-( Maybe you have more luck?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

You can use database migrators tools like:

  1. .NET Migrator
  2. FluentMigrator
  3. Entity Framework Code First Migrations

These tools allow you to work with database versions.

pryabov
  • 702
  • 2
  • 7
  • 23
0

Have you looked at Liquibase? http://www.liquibase.org/ It manages the versioning for you with support for rollback and rollforward

AmitGaur
  • 51
  • 3