What kind of database refactoring tools are there?

Question

I am looking for something to integrate to my CI workflow.
I've heard of dbdeploy (http://dbdeploy.com/) but I'm looking for something else. The reason I don't like dbdeploy is I don't want to install java on my server.

I would prefer of course that the solution doesn't involve stringing some shell scripts together.

Answer 1

Redgate will probably do everything you need. Expensive though.

EDIT - Specifically: http://www.red-gate.com/products/SQL_Refactor/index.htm

Answer 2

Yep, Redgate is magic. And Not that expenssive for what it provides.

Answer 3

Erm.. Maybe I should add that

1. I'm working on a opensource stack(backend usually mysql or postgres)
2. it's for my pet projects, nothing commercial
3. in other words , something free or really,really cheap

:D

Answer 4

It's not a tool, but Ambler and Sadalage's book, Refactoring Databases: Evolutionary Database Design is quite good.

Answer 5

Visual Studio Team system (database edition) does some refactoring.

I read the Refactoring databases book. I think it's helpful.

But in software dev, you build tests so that you are safe refactoring. They don't touch on tests in the Refactoring Databases book, which was my big disappointment with it.

Answer 6

I think those tools are very good, but for my purpose I have written a custom own. The main reason for this was because of I'm working on a SQL Server Compact 3.5 database, so none of the listed tools worked.

Of course it isn't as powerful as the tools from Redgate but you get the most important features very quick.

It's able to rename all kinds of database objects and migrating columns to other tables and create a diff script for 2 databases.

Answer 7

You mentioned that you like dbDeploy and the fact that you do not want to install java on your server. Are you aware of the .NET port of this tool?

http://sourceforge.net/projects/dbdeploy-net

I used this recently with a team and we were very happy with it. In our case we were targeting SQL 2000, but it could easily be configured to run against other DB platforms, including MySQL. Of course it will require you to have the .NET Framework installed on the server... if that's an acceptable prerequisite vs. the java runtime.

Answer 8

Possible it's not your case, but if you decide to use Java take a look at liquibase - http://liquibase.org/

Answer 9

Try Agile DBRIRE for Continuous Integration workflow. It's easy to set and allows to generate test db from Dev DB. Also it allows to generate incremental DB updates for Staging and Production. The tool can compare DEV and Staging/Production DB and generate metadata and data update SQL scripts. The tool is free.