Click here to Skip to main content
14,335,077 members

MS SQL Server to FireBird migration

Rate this:
2.84 (24 votes)
Please Sign up or sign in to vote.
2.84 (24 votes)
4 Feb 2005
A real example of database migration (MS SQL -> FireBird).


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:

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:

CREATE GENERATOR "project_id_gen";
   NEW."project_id" = GEN_ID ("project_id_gen", 1);

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


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:

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.


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


About the Author

Belarus Belarus
Lead of TargetProcess Project (
TargetProcess is an integrated Project Management and Bug Tracking software

Comments and Discussions

GeneralThanks for sharing... Pin
mario.awad13-Dec-07 10:57
membermario.awad13-Dec-07 10:57 
QuestionHow to connect from to a firebird db on a different machine Pin
Sumith P D11-Aug-05 1:55
memberSumith P D11-Aug-05 1:55 
GeneralRight and Wrong.... Pin
Ricalawaba11-May-05 10:40
memberRicalawaba11-May-05 10:40 
GeneralI can't belive to what you wrote! Pin
retekmaster2-Apr-05 1:24
memberretekmaster2-Apr-05 1:24 
GeneralQuoting Pin
13-Feb-05 21:50
suss13-Feb-05 21:50 
GeneralRe: Quoting Pin
ariesT16-Feb-05 12:04
memberariesT16-Feb-05 12:04 
GeneralSounds just like Oracle Pin
GX9-Feb-05 2:44
memberGX9-Feb-05 2:44 
GeneralGenerators Pin
Anonymous7-Feb-05 13:50
memberAnonymous7-Feb-05 13:50 
GeneralPain Pin
Anonymous7-Feb-05 4:49
memberAnonymous7-Feb-05 4:49 
GeneralMS-SQL to Firebird Migration Guide Pin
Paul Vinkenoog7-Feb-05 0:59
memberPaul Vinkenoog7-Feb-05 0:59 
GeneralRe: MS-SQL to Firebird Migration Guide Pin
Paul Vinkenoog7-Feb-05 1:04
memberPaul Vinkenoog7-Feb-05 1:04 
QuestionBug? Pin
Anonymous7-Feb-05 0:31
memberAnonymous7-Feb-05 0:31 
GeneralQuoted & conversion tip :-) Pin
Martijn Tonies7-Feb-05 0:14
sussMartijn Tonies7-Feb-05 0:14 
GeneralMS SQL's fault - not FireBird's ! Pin
Marc Scheuner6-Feb-05 1:11
professionalMarc Scheuner6-Feb-05 1:11 
GeneralRe: MS SQL's fault - not FireBird's ! Pin
firefalcon6-Feb-05 22:57
memberfirefalcon6-Feb-05 22:57 
GeneralRe: MS SQL's fault - not FireBird's ! Pin
chriswa22-Mar-05 8:42
memberchriswa22-Mar-05 8:42 
GeneralHere is your solution:) Pin
Huisheng Chen5-Feb-05 20:11
memberHuisheng Chen5-Feb-05 20:11 
Generalother alternative: advantage database server Pin
christoph brändle5-Feb-05 3:57
memberchristoph brändle5-Feb-05 3:57 
GeneralRe: other alternative: advantage database server Pin
FZelle5-Feb-05 6:45
memberFZelle5-Feb-05 6:45 
GeneralRe: other alternative: advantage database server Pin
christoph brändle6-Feb-05 2:26
memberchristoph brändle6-Feb-05 2:26 
Generalsql2gdb tool Pin
gof4-Feb-05 22:57
membergof4-Feb-05 22:57 
GeneralOne Comment Pin
Anonymous4-Feb-05 19:24
memberAnonymous4-Feb-05 19:24 
GeneralAmen. Pin
Peter M. Lemmen4-Feb-05 12:01
memberPeter M. Lemmen4-Feb-05 12:01 
GeneralSee also Pin
Dan Letecky4-Feb-05 11:37
memberDan Letecky4-Feb-05 11:37 
General4 Hours for a database migrations... Pin
Colin Angus Mackay4-Feb-05 8:42
memberColin Angus Mackay4-Feb-05 8:42 

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.

Posted 4 Feb 2005


37 bookmarked