Click here to Skip to main content
15,861,172 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

 
GeneralThanks for sharing... Pin
mario.awad13-Dec-07 10:57
mario.awad13-Dec-07 10:57 
QuestionHow to connect from asp.net to a firebird db on a different machine Pin
Sumith P D11-Aug-05 1:55
Sumith P D11-Aug-05 1:55 
GeneralRight and Wrong.... Pin
Ricalawaba11-May-05 10:40
Ricalawaba11-May-05 10:40 
GeneralI can't belive to what you wrote! Pin
retekmaster2-Apr-05 1:24
retekmaster2-Apr-05 1:24 
GeneralQuoting Pin
13-Feb-05 21:50
suss13-Feb-05 21:50 
GeneralRe: Quoting Pin
ariesT16-Feb-05 12:04
ariesT16-Feb-05 12:04 
GeneralSounds just like Oracle Pin
GX9-Feb-05 2:44
GX9-Feb-05 2:44 
GeneralGenerators Pin
Anonymous7-Feb-05 13:50
Anonymous7-Feb-05 13:50 
GeneralPain Pin
Anonymous7-Feb-05 4:49
Anonymous7-Feb-05 4:49 
You really are no "Database guru," as you said. It sounds like you don't even know how to execute sql commands in text mode - and you don't have to be a guru (or a geek) to do that. You will learn that in a first-year database class. You didn't read the manual, and then you write an article declaring that migrating to Firebird is a pain. How about this article title: "I have no database skills and I tried to migrate to a new product without doing any research and gave up after four hours. That's a pain!"
GeneralMS-SQL to Firebird Migration Guide Pin
Paul Vinkenoog7-Feb-05 0:59
Paul Vinkenoog7-Feb-05 0:59 
GeneralRe: MS-SQL to Firebird Migration Guide Pin
Paul Vinkenoog7-Feb-05 1:04
Paul Vinkenoog7-Feb-05 1:04 
QuestionBug? Pin
Anonymous7-Feb-05 0:31
Anonymous7-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
firefalcon6-Feb-05 22:57 
GeneralRe: MS SQL's fault - not FireBird's ! Pin
chriswa22-Mar-05 8:42
chriswa22-Mar-05 8:42 
GeneralHere is your solution:) Pin
Huisheng Chen5-Feb-05 20:11
Huisheng Chen5-Feb-05 20:11 
Generalother alternative: advantage database server Pin
christoph brändle5-Feb-05 3:57
christoph brändle5-Feb-05 3:57 
GeneralRe: other alternative: advantage database server Pin
FZelle5-Feb-05 6:45
FZelle5-Feb-05 6:45 
GeneralRe: other alternative: advantage database server Pin
christoph brändle6-Feb-05 2:26
christoph brändle6-Feb-05 2:26 
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 

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.