Firebird ADO.NET Provider 1.7 Release Candidate 2 is available:

• Download
• Release notes

Features:

• Automatically loads all tables in both databases and compares their fields
• Uses Foreign Keys to determine the correct order of tables
• It can create ALTER TABLE and CREATE TABLE scripts needed to update the destination database if desired
• It generates a simple textual definition file with list of tables, which you can edit to select only the tables you really need
• You can manually edit definition file if you wish to copy from views or stored procedures
• For each table, you can add WHERE clause
• You can do it all as single atomic transaction or one transaction per table
• Works on Windows and Linux

More at http://fbexport.sourceforge.net/fbcopy.html

Vulcan is a codename of Firebird next major version. The planned new features include:

• Support for 64-bit architecture
• Support for SMP architecture (multiple processors)
• Configurable security managers
• Per-database authentication information sources (now there is one user database per server)
• SQL statements for creating, updating and dropping users
• New configuration file

Read more about Vulcan:

• Vulcan Overview [PDF] - the original copy is not being served well at the moment
• Vulcan Architecture
• Other Vulcan development resources...

Firebird ADO.NET provider supports connection pooling. By default, connection pooling is turned on. That means that when you call Close method on FbConnection instance the connection to the server is not closed but is returned to the pool.

Connection pooling is useful especially for Web applications. Each page request would otherwise need to open a new connection and that can be time expensive.

You can modify the behavior of connection pooling in the connection string by using these parameters:

Parameter	Default value	Description
Pooling	true	Enables or disables connection pooling for this connection. If it is false, a new connection will always be opened.
MinPoolSize	0	The minimum connections that are always open in the pool
MaxPoolSize	100	The maximum connections that will be in the pool.
Connection Lifetime	0	How long should the connection remain in the pool (in seconds) after its creation. It is checked when the connection is returned to the pool. 0 means that the connection never expires.

Since version 1.7 of the Firebird ADO.NET Provider you can check the number of connections in the pool using FbConnection.GetPooledConnectionCount().

You can explicitly clear a pool using FbConnection.ClearPool() or all pools using FbConnection.ClearAllPools().

Things to remember:

• The connection pools are created per connection string. If you modify the connection string a new connection (and a pool) will be created.
• The connection is returned to the pool when calling Close() method of FbConnection.
• When you use connection pooling it is better to open the connection as late as possible and close as soon as possible.

Nice introduction to using character sets: Everything You Need to Know About InterBase Character Sets

Some links to performance tuning articles (most of them about Interbase and Delphi and are a bit older but you will get the idea):

• Ten things you can do to make InterBase scream [PDF]
• Improve Interbase client server performance (requires registration)
• Interbase server configuration and optimization
• Specifying query access plans in the InterBase optimizer

Firebird newcomers often complain that you need to specify the full path to the database file in the connection string, like this:

FbConnection c = new FbConnection(@"Server=localhost;Database=C:\data\mydb.fdb;User=SYSDBA;Password=masterkey");

In Firebird 1.5 and higher you can use database aliases. Just add the database path to the aliases.conf file on the server:

mydb = c:\data\mydb.fdb

Then you can use this alias in the connection string instead of a database path:

FbConnection c = new FbConnection(@"Server=localhost;Database=mydb;User=SYSDBA;Password=masterkey");

This has many advantages:

• The connection string is shorter.
• Client applications don't have to know the exact location of a database file on server.
• You can change the database file location without changing the connection string.
• You can limit the databases that can be open to those in the aliases list (see Firebird server security).

This statement executes SQL or DDL command specified as a (string) parameter. It is a great tool for stored procedures where you can dynamically create the SQL code to be executed.

Syntax:

/* For statements that return nothing or a single row */
EXECUTE STATEMENT statement [INTO variable_list];

/* For statements that return multiple rows */
FOR EXECUTE STATEMENT statement INTO variable_list DO
compound_statement

Example:

SET TERM ^ ;

CREATE PROCEDURE MYPROC 
RETURNS (
  ID INTEGER,
  MYTEXT VARCHAR(20) CHARACTER SET UNICODE_FSS)
AS
BEGIN
  FOR EXECUTE STATEMENT 'SELECT id, mytext FROM mytable' INTO :id, :mytext DO
  BEGIN
    SUSPEND;
  END
END
^

SET TERM ; ^ 

You see that this is a really dumb example. It doesn't create the SQL dynamically so there is no reason to call it using EXECUTE STATEMENT. Ability to create the SQL dynamically gives you much more power in your stored procedures.

EXECUTE STATEMENT can be used for example for:

• Creating complex WHERE clauses
• Creating a new table or user in a stored procedure
• Modifying metadata
• etc....

This is a basic comparison of embedded Firebird 1.5 and Microsoft SQL Server 2005 Express. Remember that it compares the embedded version of Firebird which doesn't run as a separate service but is fully embedded in the application. I plan to come with a server-server comparison in some of the future posts.

Feature	Embedded Firebird 1.5	Microsoft SQL Server 2005 Express
Deployment and Administration
Single database file		Transaction log is in a separate file
Associate custom DB file extension with your application		No
Fully embeddable runtime (XCOPY runtime deployment)	Details...	Installation required*
Runtime installation size	2 MB	36 MB
Runtime size on disk	2 MB	160 MB
XCOPY data deployment	Details...
Hot backup (24x7 availability is possible)
.NET Framework version supported	1.0, 1.1, 2.0	2.0
Can run on Windows 98		No
Automated patching (Windows Update)	No
Free GUI management tools available
Scalability
Database file size limit	Unlimited***	4 GB
Maximum processors	Unlimited**	1
Addressable RAM	Unlimited	1 GB
Maximum server instances	Unlimited	50
Multiple concurrent users
Engine Features
Transactions
Views
Autoincrement fields
Stored procedures and triggers
BLOB fields
Unicode support
National character sets support (including sorting)
ADO.NET Provider
.NET CLR support on the server side	No
XML support	No
Licensing
Open-source license	Details...	No
License allows use in commercial applications	Details...
No registration required for redistribution		No
Switch to a standalone unlimited server		Not for free

* Embedding. SQL Server Express can be installed with the host application but it still is a separate server. Firebird can be installed as a server or can be used as as a part of the application (just a single DLL copied into the application directory that doesn't expose any TCP/IP or other interface to other applications).

** Multiple processors support. Full multiprocessor support will come in Firebird 3.0 which is expected to be released in a few months.

*** Database file size limitation. Firebird is limited only by file system (e.g. 16+ TB on NTFS).

To summarize: Embedded Firebird is a clear win when you need a fully embedded database that users are not aware of. The clear advantages of Embedded Firebird are:

• Licensing. You can't beat Firebird's open-source license that allows use in commercial applications without any viral effect (i.e. no need to open your proprietary code). Any kind of a "free redistribution" license is just too far from that.
• XCOPY Data Deployment. A Firebird database is a single file. Just copy it somewhere on disk and open it. Compress it and send it by e-mail. Use your own file extension and associate it with your application.
• XCOPY Runtime Deployment. Not only the data but the runtime (which is a single DLL and optional supporting files) can be just copied to your application's directory.
• Runtime size. Compare the SQL Express 36 MB download with the 2 MB Firebird runtime.
• No performance limitations. With Firebird you can have a database of any size and open multiple connections without any penalty.
• Real embedding. Users and administrators don't need to be aware that your application is using Embedded Firebird because it is only accessible from your application.

Related

• Firebird and Microsoft Jet Feature Comparison
• Firebird and Microsoft MSDE Feature Comparison
• Firebird Can Be Easily Embedded
• Create a Database Programmatically

Securing embedded Firebird is a lot different from securing a server:

• Because the embedded Firebird doesn't allow remote connections you don't have to care about securing TCP/IP. Instead, you should rely on file system permissions.
• You need the file system permissions to read and write to the file to open the database.
• When embedded Firebird opens the database, the file is locked (that means it can't be open by a server or another embedded Firebird instance). Remember that the Firebird ADO.NET provider uses connection pooling by default. That means the lock is not released immediately after closing the last connection.
• If there is a Firebird server installed on a machine with the embedded Firebird database you need to prevent the server from opening the database file - either by configuring the server or by setting such permissions on the file that prevent the server from opening it.

Related

• Firebird Server security
• Firebird can be easily embedded
• XCOPY data deployment

These guidelines only apply to Firebird Server. When using embedded Firebird, there is no TCP/IP interface.

We are talking about securing Firebird on a server level. There will be a separate article about security on a database level.

1. IP Address Binding

By default, Firebird is bound to 0.0.0.0 IP address. That mean it will be reachable on all available IP addresses. When you are using Firebird only as a backend to your web application you should bind it to local 127.0.0.1 that makes it unavailable from network. You can change the bind address in firebird.conf:

RemoteBindAddress = 127.0.0.1

2. TCP Port

Firebird server runs on port 3050 by default. You can change it in firebird.conf:

RemoteServicePort = 3050

Use your firewall to limit access to that port. You should not expose it directly to the internet.

3. Database Paths

When connecting to Firebird, you can specify the database by either:

• Full path to the database file, e.g. @"Database=C:\data\mydb.fdb"
• Alias specified in aliases.conf

By default you can open any database that is located on the server and the server process is permitted to read/write by filesystem. To make your server more secure you can limit the databases that can be open to:

• Databases located in specific directories
• Databases listed in aliases.conf

The options in firebird.conf are as follows:

# Unrestricted access (default)
DatabaseAccess = Full

# Access restricted to listed directories
DatabaseAccess = Restrict c:\data1;c:\data2

# Access restricted to database aliases listed in aliases.conf
DatabaseAccess = None

4. Network Communication

Firebird uses unencrypted network communication. To make it more secure, you can use e.g. SSL tunnelling.

5. SYSDBA Password

The administrator account name in Firebird is SYSDBA and the default password is masterkey. (Actually, only first 8 characters of the password are significant.) You should change the SYSDBA password after server installation.

There is a new article on Firebird on Code Project: MS SQL Server to FireBird migration. That's a Pain!.

Firebird may sometimes seem strange to newcomers. A few tips to make the start easier:

• You need an admin tool. It's really hard to start without it. The free ones are really poor - you need to try the commercial ones. IBExpert has a free Personal Edition that does a great job.
• Install Firebird server on the development machine even that you want to work with embedded Firebird. The admin tool will connect to the database via the server.
• Make all names (table names, field names, SP names, etc.) uppercase in the database. These names are converted to uppercase when used from a client (and it might not match then). The other possibility is to use quotation marks on both server and client - but you are making your life harder. You should get used to uppercase on the server and you can use whatever case you want on client.
• Read more about auto increment fields.

Every database operation in Firebird runs inside a transaction. The transactions have these features: Atomicity, Consistency, Isolation, Durability (read more about ACID). Let's talk a bit about the isolation feature.

Isolation says that the transaction shouldn't interfere with other transactions. Achieving full isolation would mean serializing the transactions and that would slow down the database. Therefore there are four transaction isolation levels defined and every of them represents a different balance between isolation and performance:

• Read uncommitted
• Read committed
• Repeatable read
• Serializable

Firebird doesn't match the standard (SQL92) isolation levels exactly. The following table compares the isolation levels that are supported by Firebird ADO.NET with the SQL92 standard.

Firebird ADO.NET Provider IsolationLevel	Firebird Isolation Level	Corresponding SQL92 Isolation Level
IsolationLevel.ReadUncommitted	READ COMMITTED RECORD_VERSION	Read Committed
IsolationLevel.ReadCommitted (default)	READ COMMITTED NO RECORD_VERSION	Read Committed
IsolationLevel.RepeatableRead	SNAPSHOT/CONCURRENCY	Repeatable Read
IsolationLevel.Serializable	SNAPSHOT TABLE STABILITY/CONSISTENCY	Serializable

All transactions have these additional Firebird transaction options set:

• WAIT - when using READ COMMITTED isolation level it waits for uncommitted transactions to finish before reading a row
• READ WRITE - allows data modifications

You can use the predefined isolation levels by calling public FbTransaction BeginTransaction(IsolationLevel) or you can tune the transaction options more precisely by using this BeginTransaction overload: public FbTransaction BeginTransaction(FbTransactionOptions).

IsolationLevel.ReadUncommitted

ANSI/ISO Read Uncommitted isolation level is not supported by Firebird. IsolationLevel.ReadUncommitted behaves like ReadCommitted but it returns the latest committed version of a rowand ignores any other (uncommitted) versions.

Reading
What it reads	The latest committed version of a row and ignores any other (uncommitted) versions.
Dirty reads	No
Phantom data	Possible
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Short transactions that modify data.

IsolationLevel.ReadCommitted

Reading
What it reads	Waits for uncommitted transactions modifyinga row (to be either committed or rolled back).
Dirty reads	No
Phantom data	Possible
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Short transactions that modify data.

IsolationLevel.RepeatableRead

Reading
What it reads	When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads	No
Phantom data	No
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	Yes
Usage
Suitable for	Long transactions that require lots of reading and stable data view (reports).

IsolationLevel.Serializable

Reading
What it reads	When the transaction starts, a snapshot of the whole database is made. The transaction reads from that snapshot.
Dirty reads	No
Phantom data	No
Nonrepeatable reads	Possible
Writing
Modification of data modified by other transactions since this transaction started	No
Other transactions can modify data read by this transaction	No. All tables that the transaction has read from are locked. Other transactions are prevented from writing to that tables.
Usage
Suitable for	Long transactions that require exclusive access to a table.