Database administration

This section describes differences in how database are managed in Firebird and MS SQL.

MS SQL 6.5 uses devices, which can be files or raw partitions, to manage data. This resulted in a hard-to-maintain system. MS SQL 7 and MS SQL 2000 corrected this by using normal files in place of devices. For each database, you will have at least two files: one with the database information itself, and one with a log of transactions performed.

Firebird does not rely on a log to keep track of transactions, and therefore uses a single file to keep everything.

The CREATE DATABASE statement in Firebird is simpler than the CREATE DATABASE statement in MS SQL; see the SQL reference for a full description of its capabilities.

One significant difference between the file management model is that MS SQL uses filegroups to partition a database over a set of files. Firebird can also use different files, but the model is simpler.

An additional consideration for Firebird is the use of shadow files. Shadow files are an instant replica of the database itself. It is typically used to have a hot backup readily available. MS SQL has no such feature, although MS SQL 2000 has a similar capability by the use of log shipping between database servers and replication.

back to top of page

In MS SQL 6.5, there are two objects to manage: logins and users. Logins specify a username/password combination used to access a database server; users specify the access rights on each database. Logins are then mapped to users in databases.

In MS SQL 7, a new kind of object is added to manage groups of users: roles. These are simply a security definition. Some roles are system-defined, such as backup operators or database administrators.

Firebird has a security model similar to MS SQL's, but without logins. Users supply a username, a password, and a role they wish to work under. There is a single security database per database server, which holds all information about permissions on every database, for every user, for every role.

Under both database systems, it is considered good practice to access all resources through stored procedures, and grant access only to stored procedures. Security can then be set up through the security assigned to stored procedures (in Firebird; in MS SQL, the stored procedure executes using the rights of its creator).

back to top of page

Firebird uses a backup and restore model which is much simpler than MS SQL, although it sacrifices flexibility. Backups are performed through command-line or GUI tools, and they backup a whole database at a time. A restore operation will restore a whole database on a server.

There is no operation to backup differences only, or to restore an isolated set of transactions.

Note that there is a very important option when backing up a database in Firebird: platform-dependant or portable. Performing a portable backup allows the administrator to backup a database on an operating system and restore that same database on another. This is typically used when development is performed on Windows workstations, and the operational database is then deployed on a more powerful Linux server, for example.