Report Database Settings

Cerberus FTP Server Enterprise edition supports collecting and reporting detailed session and file access statistics using an ODBC-compliant database. A database connection must be configured in Cerberus before the server will begin collecting statistics. The reporting database connection will also be used by the Reporting Manager for generating reports.

The following databases are currently supported (others may work with appropriate ODBC driver installed):

• Microsoft SQL Server 2014 Enterprise and higher
• Microsoft SQL Express LocalDB 2014 and higher
• Microsoft Azure SQL Server
• MySQL Server 5.2 and higher
• PostgreSQL version 10 and higher

Selecting a Database

If you are setting up a new database connection for the first time you will need to enable statistics collection and select a database driver. For most Cerberus installations, we recommend using the free SQL Express LocalDB database as this will provide you with all the capability you need. If you have a very high volume Cerberus installation, however, it may be more appropriate to purchase or connect to a full Microsoft SQL Database. Instructions for installing and connecting to SQL Express LocalDB as well as connecting to a full SQL database are below

Installing SQL Express LocalDB

The quickest and easiest database option is Microsoft Server Express LocalDB. LocalDB is a lightweight, embedded database option from Microsoft that is suitable for local, low-utilization database traffic. It has a relatively small footprint and installs quickly. You will also need the SQL Server Native Client 11.0 ODBC driver for connecting to LocalDB

Important: If you are running the 64-bit version of Cerberus FTP Server, make sure you download and install the 64-bit version of SQL LocalDB and the corresponding 64-bit SQL Server 2014 Native Client driver. The same applies to the 32-bit version as well. 

After installing SQL Server Express LocalDB and the SQL Server Native Client, you can go to the Reporting page and select the SQL Server Native Client 11.0 driver.

1. Click Reporting on the Cerberus FTP Server menu to open the Report Manager page.
2. In the Drivers, field select SQL Server Native Client 11.0
3. The connection parameters available for your driver type will now appear. Select LocalDB 20xx for a Microsoft SQL Server 20xx LocalDB installation, where 20xx is the version matching what you installed (2014 for LocalDB 2014 and higher).
4. Press the Connect button to test your connection.
5. If there are no ODBC Connection errors after pressing the Connect button, press the Create Tables button to create the necessary database tables for Cerberus to write to the database.
6. Check the Enable Statistics Collection checkbox.
7. Press Update to save changes.

Connecting to an existing SQL Database

The basic steps for this task are detailed below. The ODBC database driver and individual driver settings will vary by database.

1. Click Reporting on the Cerberus FTP Server menu to open the Report Manager page.
2. Select the Database Configuration tab.
3. Open the Drivers select box and select the ODBC database driver appropriate for your database type (i.e. ODBC Driver 17 for SQL Server for a remote connection to Microsoft SQL Server).
   For Microsoft SQL Server installations other than LocalDB, we recommend downloading and installing the Microsoft ODBC Driver 17 for SQL Server. Some reporting features may not work with the default SQL Server ODBC driver installed on most machines.
4. The connection parameters available for your driver type will now appear and must be filled in.
5. After filling in the ODBC driver connection parameters, press the Connect button to test your connection. The Connect button will test that Cerberus can establish a connection to the database, and run a quick search for the necessary Cerberus statistics tables.
6. The ODBC Connection result should say Success. There should also be entries for the different database tables. If those tables haven’t yet been created then there will be error messages displayed beside each table entry. If you are using a database that requires a username and password, the user account must have permission to create a database, and tables in the database. Statistics collection and reporting will not work if the user account does not have create database and create table permissions.
7. Press the Create Tables button to create the necessary database tables for Cerberus to write to the database.
8. If Create Tables was successful then you will see each table listed in the status box with the message Created next to it. You are finished setting up your connection.
9. Check the Enable Statistics Collection checkbox.
10. Restart the Cerberus service using 'services.msc' to ensure the change in the statistics collection status is enabled. You also must restart the service if you turn Statistics Collection OFF before collection will actually stop.

Clean Tables

”Clean Tables” on the Database Configuration tab is an easy way to remove older records from your database. The goal here is to remove records from your database that are older than the selected interval, to help with database maintenance. Selecting an option from the “Clean Tables” drop-down will delete Audit Log and File Operation records older than 1 month/6 months/1 year/2 year, and delete any file records that don’t have matching file operations.

The time periods are calendar based. For example, if you’re doing a 1 month clean on March 31, 2021, @ 1:01:00 pm it would clean before February 28, 2021, at 1:01:00 pm. And a 6 month clean requested on September 1, 2021, @ 10:00:57 am would clean before March 1, 2021, at 10:00:57 am.

After selecting a time frame (2 years in this example) you’ll get a confirmation dialog before actually performing the database operation.

After confirming, audit records are created and logged to keep track of users cleaning the database in case there is a question of when an activity was done and by whom. Information on what occurred during the delete operation will be both inserted into the database and displayed on the Database Configuration UI screen.

Selecting “Connect” again for statistics on the current state of your database, we can see the effects. Here we see that the 6 orphaned file entries were deleted, and the count of unique files was reduced from 58 to 52. In addition, there are now 3 new user activity or audit records: the three types of deletions that resulted from the delete operation.

Cerberus FTP Server writes matching log messages to the output logs to also reference when the deletion attempts occurred and by whom.

Database Backup and Restore

The Backup and Restore buttons are currently only supported when connecting to Microsoft SQL Server databases. The buttons will be disabled when any other database type is selected. The buttons allow downloading a copy of a local database, and later restoring it. Note, the database will be saved to the local machine where the database is running. If you click the Backup button for a remote database, the database will be saved to the selected path on the remote server running the database. The same goes for restoring a database. The database must be on the local machine.

Non-SQL Server databases should be backed up using whatever backup system is native to that database.