Database Settings

PA File Sight needs a place to store the data that it collects during operation. There are two choices available for data storage.

SQLite

SQLite is a highly reliable open-source database. By default, PA File Sight stores all of its data in SQLite databases. This is the choice that you make by selecting the radio button titled "Store collected data in databases in the directory above." This is the simplest choice available and is the one that most users make when using PA File Sight.

Database files will be created and stored in the directory specified. Even if MS SQL Server is chosen for the database, a small amount of data will still be stored in the specified database directory.

Microsoft SQL Server

To use SQL Server for storage, you need to install the SQL Server Native Client library, which is Microsoft's latest database connection technology. The SQL Server Express databases are fine for most installations, but do be aware that they limit the total database size to 10GB (for SQL Server 2008 R2 Express).

If you did not install the Native Client Library at installation time, you can now by launching the installation file named sqlncli.msi, which will be located in the home directory of PA File Sight (normally C:\Program Files\PA File Sight.)

The following configuration data needs to be specified to use SQL Server:

• Server name - name of server on which SQL Server instance is located. (Note that with SQL Express, this is often {server_name}\SQLEXPRESS)
• Database name - the name of a SQL Server database which will be used for PA File Sight storage. The database must exist prior to use and can be empty.
• User name and password - as required by the SQL Server instance.
• Connection String - the connection string is automatically created by PA File Sight when you enter the configuration information above. You can hand edit the created connection string if you wish.
  Note: If you are using database mirroring, you can manually add the Failover_Partner parameter to specify the alternate database to connect to.

If you do not need or wish to use SQL Server as the database for PA File Sight, the SQL Server Native Client Library does not need to be installed.

Changing Databases

If you change the database settings, you will be prompted whether you want to copy your existing data from the current database to the new database. Depending on the size of your current databases, this can take a while (a large installation with 6GB of databases can take a day for the transfer).

Database Cleanup

No maintenance is required for the databases. All monitors automatically remove old data from the databases automatically to help control database growth. You can control how many days of data is kept for the monitors via the Database Cleanup button.

Data Location

With Satellites in use, the question of where to keep audit data arises. By default, each Satellite uses its own database settings (which means each Satellite will store data in its own local SQLite database by default). You can specify that each Satellite connect to a MS SQL Server database and store its data there. The check box at the bottom gives another option: centralized database storage.

Centralized Database Storage

By unchecking the check box at the bottom of the Database Settings dialog, you instruct the Satellites to forward all of their file I/O audit findings to the Central Monitoring Service. The Central Monitoring Service will then store that data in the database that it is configured to use (local SQLite or MS SQL Server as defined above). The Satellites will cache the file I/O records in memory, and then deliver the records to the Central Monitoring Service every few minutes. If the connection to the Central Monitoring Service is not available, the records will be cached in memory until the connection becomes available.

Performance Considerations

Naturally the Centralized Storage setting will put a little more load on the Central Monitoring Service, the network connection to it, and the database connection. In our testing with 500 Satellites connected to a Central Monitoring Service, we found that MS SQL Server could easily keep up. The embedded SQLite kept up with the write requests until around 100 to 200 servers. The network bandwidth used is surprisingly light. Each minute, each Satellite will make approximately 4 HTTPS connections. Approximately 4KB will be sent to the Central Monitoring Service, and approximately 2KB being sent back to the Satellite during each minute. These values can vary widely based on monitor configuration. The [System Summary Reports] -> System Statistics report shows bandwidth usage from each Satellite and is a good way to check this for your particular configuration.

Reporting is not affected by the Centralized Database Storage setting. If data is kept in a central database, reports will query that database. If data is stored out at the Satellite locations, the Satellites will be queried when a report is generated for their part of the report.

Note that when Centralized Database Storage settings are changed, existing data in the database(s) is not moved to the new location, and will in some cases become invisible to queries.