2.6 Database Planning

You should consider the following guidelines before installing and configuring any database system for File Reporter.

2.6.1 Determine a Database Type

You can utilize either a PostgreSQL database or a Microsoft SQL Server database. Here are some considerations for choosing one over the other:

  • You might prefer to utilize Microsoft SQL Server if you have a Microsoft Licensing Agreement that entitles you to Microsoft SQL Server.

    File Reporter supports the Standard and Enterprise versions of SQL Server. It does not support the Web or Express editions.

  • You might prefer to utilize the PostgreSQL database if you are proficient with Linux.

2.6.2 Use a Dedicated Server

Due to the potential size of the collected scan data and the I/O processing needed for large database installations, we strongly recommend that you install the database on a dedicated server.

2.6.3 Use a Dedicated Database Instance

In addition to sizing requirements, we recommend that you use a dedicated SQL Server instance or PostgreSQL cluster to prevent conflicts with other vendor software. File Reporter needs access to manage the database security principals and roles, which requires access at the instance level. In addition, File Reporter ships with optional CLR extensions for SQL Server, which requires enablement at the instance level.

In short, do not install the File Reporter database in an instance or cluster that shares databases with other software.

2.6.4 Provide Sufficient I/O Bandwidth

Relational Database Management Systems are by nature very I/O intensive, especially when it comes to persisted storage on disk. For best performance, consider the following:

  • Provide SSD backed storage if possible, for the database tablespaces or filegroups*.

  • Alternatively, provide RAID-10 spindle storage for database tablespaces or filegroups*.

  • Do not use RAID-5 storage for database storage.

  • Do not use Network Attached Storage for database storage.

  • If using a SAN, be sure to provide at least 10 GB or more throughput (ideally, the SAN link should be faster than the I/O capacity of the backend storage system, so that it is not the bottleneck).

  • Be sure to enable battery-backed cache for RAID and SAN controllers.

  • For SQL Server, optionally place tempdb on a separate RAID-1 or SSD.

  • Optionally, place the transaction logs on a separate RAID-1 or SSD.

    This can be done either during the installation of the SQL Server instance or afterwards.

For procedures on moving database files after the installation of an SQL Server instance, see https://msdn.microsoft.com/en-us/library/ms189133.aspx.

For PostgreSQL, moving database files is a simple process of stopping the database server, relocating the pg_xlog folder, and then creating a symbolic link to the new path.

The need for separate disks for transaction logs is minimized if the main storage is already on RAID-10 or SSD, and the I/O channel is not already saturated.

*For basic information on SQL Server filegroups, see https://msdn.microsoft.com/en-us/library/ms189563.aspx.

*For basic information on PostgreSQL tablespaces, see https://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html.