Click here to Skip to main content
15,861,168 members
Articles / Database Development / SQL Server
Article

MS SQL Server to FireBird migration

Rate me:
Please Sign up or sign in to vote.
2.84/5 (24 votes)
4 Feb 20054 min read 123.4K   37   28
A real example of database migration (MS SQL -> FireBird).

Introduction

Recently I read an article Embedded Firebird at CodeProject and decided to try FireBird. I like the idea of embedded database. FireBird allows .NET developers to put a DLL into bin directory, create database file and use usual database access. It sounds very attractive and simple in theory. So I decided to migrate from SQL Server to FireBird.

Database Creation and Tools

Embedded version of FireBird is very light, just 1.5 Mb. FireBird Data Provider for .NET is also not so big - 0.7 Mb. I downloaded all these stuffs and put required DLLs into bin directory. Then I started database creation. However, FireBird Embedded distributive does not include any tool for database administration. Well, I downloaded standard distributive (4 Mb) and installed full featured SuperServer on my workstation. I dug into Program Files in the hope of finding a DB administration tool and found one. It was an ISQL console tool. "OK, no GUI" - I thought. - "This database is for real geeks". I opened QuickStart guide, found a section about database creation and tried to create one. But, as you might guess, without success. There was strange behavior, the ISQL refused to believe me that I was really creating new database and told that database is unavailable.

Fighting with console for awhile, I decided to find a GUI tool. There were several free tools at IBPhoenix web site. First I downloaded IBSQL (1Mb). It ran, but, when I tried to create new database, showed an error "gds32.dll was not found". I hunted around and found out that original DLL could be renamed and put into the System32 directory. I started IBSQL and it worked! A new database was created!

Do you think the migration is close to completion now? Not at all. Schema importing became a really tough action. At the very beginning, I discovered that FireBird does not support IDENTITY keyword. That's not a problem, I removed it. Then things became weird. IBSQL tool did not execute query popping up the error in the line with second's table CREATE statement. I tried to correct statements and understood that the tool was unable to run several statements! There were about 40 statements to execute, and I was not very enthusiastic about doing that one by one. "Fine, let's find something more user friendly". I found FeniSQL - very light (0.5Mb) tool. The same bug! I was slightly tired of that. I found several advices in newsgroups about IBOConsole tool and got them (2Mb). Yes, this tool is really great, has appealing GUI and useful features. And has no silly restrictions on statements' execution.

SQL compatibility

However, SQL was still wrong for FireBird. I used columns named "password", "value" and "time". These are reserved keywords. MS SQL Server eats them, but not FireBird. As you understand, renaming several columns is an issue, but I want to set up database at least. So I renamed fields in schema and ran SQL script again. The database schema was created successfully.

Do you think the migration is over? I thought so in that moment, but FireBird converted all tables and columns names in upper case. And the application refused to query database correctly. Ok. Google newsgroups are helpful and I found out that names should be quoted:

SQL
CREATE TABLE "releases"
(
            "goal" VARCHAR(500),
            "finish_date" DATE,
            "start_date" DATE,
            "name" VARCHAR(150) NOT NULL,
            "project_plan_id" INTEGER NOT NULL,
            "release_id" INTEGER  NOT NULL,
             CONSTRAINT "pk_releases" PRIMARY KEY ( "release_id" )
);

I changed SQL and… Yes! I got it! I run the application and all screens seem to be working. Then I tried to add a new Project and… Yes, I got an error (I was not surprised in fact). FireBird does not support autoincremental columns. There is a workaround using Triggers and Generators. The main idea is to create generator and invoke it before insertion to increment primary key. Something like that:

SQL
CREATE GENERATOR "project_id_gen";
 
CREATE TRIGGER SET_ID FOR "projects";
BEFORE INSERT
AS BEGIN
   NEW."project_id" = GEN_ID ("project_id_gen", 1);
END

As I understand, this had to work properly. But it wasn't.

Conclusion

I spent four hours, downloaded six distributives, several times queried Google and almost resolved all problems (the keyword is almost). Well, maybe I am not a DBA guru, but have a good experience with several major databases and never encountered such difficulties with them. FireBird's behavior is illogical for me in some cases, but I am going to complete migration today. No, I am not a stubborn donkey, just because of the embedding. This way is agile and should be simple, but it is hard. That's sad.

Today I played with GENERATORS again. And (finally!) I found just one post about ID_GENERATOR problem. The solution is to create table id_generator with two fields: id (int), next (int). I created ID_GENERATOR table, changed trigger to:

SQL
NEW."project_id" = GEN_ID (ID_GENERATOR, 1);

And new Project action has been fixed. FireBird is very tricky you know...

About the author

Michael Dubakov is a leader of TargetProcess project - XP Planning and Bug Tracking Software.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Belarus Belarus
Lead of TargetProcess Project (www.targetprocess.com).
TargetProcess is an integrated Project Management and Bug Tracking software

Comments and Discussions

 
Generalsql2gdb tool Pin
gof4-Feb-05 22:57
gof4-Feb-05 22:57 
GeneralOne Comment Pin
Anonymous4-Feb-05 19:24
Anonymous4-Feb-05 19:24 
GeneralAmen. Pin
Peter M. Lemmen4-Feb-05 12:01
Peter M. Lemmen4-Feb-05 12:01 
GeneralSee also Pin
Dan Letecky4-Feb-05 11:37
Dan Letecky4-Feb-05 11:37 
General4 Hours for a database migrations... Pin
Colin Angus Mackay4-Feb-05 8:42
Colin Angus Mackay4-Feb-05 8:42 
GeneralRe: 4 Hours for a database migrations... Pin
firefalcon4-Feb-05 10:22
firefalcon4-Feb-05 10:22 
GeneralSend an email to Pin
Anonymous4-Feb-05 8:00
Anonymous4-Feb-05 8:00 
GeneralFirst -Read the manual Pin
Anonymous4-Feb-05 7:36
Anonymous4-Feb-05 7:36 
http://www.firebirdsql.org/pdfmanual/Firebird-1.5-QuickStart.pdf

OR


http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_document



then
download a free tool for management

http://www.hksoftware.net/download/ibep_2004.12.14.1_full.exe
and read
http://www.ibexpert.info/documentation/

Other great tool is :

EMS IB/FB Manager

http://sqlmanager.net/products/ibfb/manager


firebird is great . I made an application for salesmans to store a disconected version of database on firebird embeded and sync with a sql server using webservices. Works great . firebird is fast , small easy to maintain.

Sorry for your problems , but do more reaserch








General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.