Managing Databases

This article explains managing databases for two system editions: ELMA Express and ELMA Standard. The Express edition uses the free Firebird database. IBExpert is used for administering Firebird databases. The Standard edition uses MS SQL Server. SQL Server Management Studio is a utility for configuring, managing and administering all the components of Microsoft SQL Server.

When you install ELMA, a new database is created. The database file is located in:

for ELMA Express, UserConfig folder. The path to the file should look like this: C:\\ELMA3-Express\UserConfig. The .fdb file is the database file. The .fbk file is the database backup file.
for ELMA Standard, MSSQLServer folder. For example: C:\ProgramFiles\MicrosoftSQLServer\MSRS11.MSSQLSERVER\MSSQL\DATA. The .mdf file if the database file. The .ldf file is the log file.

Attention!

Databases are not backed up automatically. You need to do it manually with DBMS utilities. To avoid data losses, back up the database periodically.

Let’s take a look at the main operations with databases.

Backing up a Firebird database

There several ways to back up a Firebird database, such as by using a bat-file (https://kb.elma-bpm.com/article-1834.html) or via IBExpert.

Important

Before backing up, terminate all the active connections with the database by disconnecting the database (see below) or stopping ELMA web server (or IIS server).

Consider backing up in IBExpert: in the Services menu select Backup Database. The Database Backup window will open. On the Backup Files tab, some required values are already specified. You can change the file name and size. The database backup copy will have the .fbk extension.

In the Output unit, select On Screen from the drop-down list, so that the backup protocol was displayed on the Output tab.

Click Start Backup. The Output tab will open. It displays all the messages of the backup process. You can back up to any number of files. To do so, click Append file in the toolbar and in the appeared string set the required characteristics of the backup file.

Backing up for MS SQL

There are several ways to back up an MS SQL database. This example describes backing up in SQL Management Studio. Right click on the required database in the list and select Tasks – Back up…

The Back Up Database window will open. Fill in the required data. In this case, the database backup copy will have the .bak extension. You can compress the backup, if you go to the Options page, and select Compress backup.

You can find more information in SQL Management Studio Help.

Backing up an Oracle database

There are several ways to back up an Oracle database. To learn more, read this knowledge base article.

Restoring a database

A database is restored from a backup file. During this procedure, the data from the backup file is loaded to the selected database. Do not mix up restoring a database and connecting a database. When you connect a database, you connect the database file itself. When you restore a database, you use a backup file.

Important

Before restoring to the current database, stop the web server (or IIS) or disconnect the database (see below).

You can restore a Firebird database using a bat file (https://kb.elma-bpm.com/article-1834.html).

Restoring in IBExpert

Let’s take a look at restoring in IBExpert. In the Services menu select Restore Database. The Database Restore window will open. The Files tab already contains the required values. In the Restore into field, Existing database is selected.

Select the line in the File Name unit. Click on the … button. In the opened dialog box select a backup file. Check the Replace existing database box.

You can change the size of a database page in the Page Size field.

The database management performance depends on the page size: the database file is paginated, and the data is read and written page by page. To increase the performance, set the page size to 16384 bytes.

Recommendations on selecting the page size:

for NTFS ‑ 4096 disks;
for FAT32 – 8192 or 16384 disks.

Click Start Restore. A window for entering user name and password will open. You can restore as the SYSDBA user. The database will be restored from the backup copy.

If you want to restore the backup to a database with another name, then select New database in the Restore into field; in the Database File field enter the full path to the new (or existing) database file; in the File Name field, enter the path to the backup file or use the Browse button to select the file.

Restoring in SQL Server

To restore a database in SQL Management Studio, open the context menu of the required database and select Tasks – Restore – Database. The Restore Database window will open. Fill in the required data.

You can find more information in SQL Management Studio Help.

Restoring an Oracle database

To learn how to restore an Oracle database, read this knowledge base article.

Database maintenance

Maintenance plans are used to create tasks, required to ensure optimum performance of the database and its regular backup copying. Maintenance plans are used to configure automatic database backing up, which allows avoiding data loss. It is recommended that you back up the database at least once a week, but you should take into account the performance of your systems.

For SQL, this operation is performed in SQL Management Studio using the maintenance plan wizard:

Expand the server.
Open the Management folder.
Right click on Maintenance plans and select Maintenance Plan Wizard. The wizard allows you to create a plan with custom maintenance parameters. You can configure back up of both databases and transaction logs.

For Firebird you can configure maintenance using the bat file or other commands.

Disconnecting and connecting a database

If a database is used by several clients, then it is recommended that you stop it to prevent corruption or loss of data and block access to the database during an operation. The database file remains where it is; the file is disconnected from the DBMS, which makes it possible to edit this file. Later you can connect the database back to continue working with it.

In SQL Management Studio, select Tasks – Disconnect. To connect the database, click Tasks – Connect.

Firebird: to disconnect a database, select Services – Database Shutdown. In the opened window, select one of the databases registered in IBExpert or enter the database file name. Select the mode: Forced, Deny Transaction or Deny Attachment, set the waiting time and click Shutdown.

To connect a database, select Services – Database Online. In the opened window, select a database and click Bring Online.

Connecting ELMA to a database

The information about ELMA connection to a database is specified in the configuration.config file (ELMA/UserConfig folder). You can change the database path (for FDB) or database name (for SQL) if you create a new database. For FDB, you also must move the database to the UserConfig folder.

If you are connecting ELMA to a database, which has earlier been used with another ELMA server, when starting the database, additional activation of components may be required (if the set of components in these servers is different).

In this case, when starting the database, an activation form will be shown, where you need to enter the server activation key you obtained from an ELMA representative or partner.

The list of components you need to activate as well as the system edition information, registration key and server token are available if you click View the list of required components, below the Activate button.

You can copy all the information in this window to the clipboard by clicking Copy to clipboard. To return to the previous window, click < Back.

Configuring ports, accounts

For ELMA Express, you can change the DBMS connection port and account data. You can do it in the configuration file located in the UserConfig folder:

Using transaction logs (or backup logs)

Each SQL Server database contains a log, that stores all the transactions and changes in the database, made by each transaction. A transaction log is an important component of the database. Understanding and managing this log is one of key duties of the database administrator

You can back up transaction logs, using back up copying, similarly to backing up a database. In the Type field, select Transaction logs or use the Maintenance Plan Wizard and select transaction logs as well.

Firebird DBMS does not use a separate log for saving transactions but uses a single file for storing all the data.

Handing a database over to support

Handing a database over to support allows quickly resolving issues, therefore you need to understand this procedure. You keep the original documents and files. You can hand over the database file; however, it is encouraged that you use the database backup file. The size of the backup file is much smaller than of the database, and you can compress it even more if you zip it. When you zip the file, break it down into parts of 50-95MB. Files of this size can be uploaded to the technical support website http://support.elma-bpm.com

Support specialists may request sending the .ldf file (for MSSQL) if it is necessary. Usually, you do not need to send log files.