Database

Database options include settings regarding database usage and recorded RDM data.

▪Go to the menu Preferences > Options... > Database
[Keyboard shortcut: Ctrl + Alt + O > Database]

Please note: Choosing a database management system is subject to availability here in the software. A different database system that is not provided as an option as listed above cannot be selected or chosen.

SQLite In Main Memory	SQLite File	PostgreSQL Server
▪Is the default option.		▪Is required when using the Big Data/History feature and data trends.
▪Is useful for short-term monitoring and when data does need not be saved and can thus be discarded without ramifications. ▪Is useful when using MADRIX RADAR only as configuration tool for device addressing, for example.	▪Is useful for short-term monitoring and the requirement to have data stored beyond any restart of the software. ▪Is useful when using MADRIX RADAR only as configuration tool for device addressing, for example.
▪Is the default option. ▪Does not require any configuration. ▪Only stores RDM-device data in the temporary memory of the computer [RAM]. ▪Data quantities are thus limited to the available system memory. ▪Data can thus be processed and accessed very quickly. ▪Any data is discarded if the software is closed in any way.	▪Only requires very little configuration. ▪Writes a separate file onto the hard drive in order capture, save, and preserve recorded RDM-device data. ▪Is an advanced system for data management, which ensures data integrity and data consistency, for example. ▪Data quantities are thus limited to the available hard-drive space. ▪Data thus cannot be processed and accessed as quickly as when stored in the system's memory. ▪Always overwrites parameter values and sensor data with updated information and thus does not enable data trends [that is, no data history is available]. ▪The data is still available even if the software is closed.	▪Is a sophisticated database system to capture, save, and preserve recorded device data. ▪Is an advanced system for data management, which ensures data integrity and data consistency, for example. ▪The data is still available even if the software is closed. ▪Can be set up on the same computer as MADRIX RADAR [locally] or on a different server/computer [remotely].
▪Is automatically provided together with the software's installation.	▪Is automatically provided together with the software's installation.	▪Is not included in the software's installation and needs to be installed separately. ▪A database needs to be created in PSQL after the installation. [The correct name of the database, login information, and more needs to be entered in the options as explained below. See: PostgreSQL Server]

When switching to a different option, you will be asked if you would like to scan for devices in the network again.
Please note: Each database holds their own devices. When switching databases, data will not be transferred from the previous database to the new database.

It is not necessary to set up any options for this type of database management system [other than selecting it as the chosen Type].

SQLite can be used to create a separate database file. Data is then stored beyond any restart of the software.

▪Data File - Defines the location of the database file on your computer.
- Browse... - Allows you to choose a different folder or directory.
- By default, <User Directory>\Documents\MADRIX RADAR\MadrixRadarData.sqlite is selected.
- A new file will be created when choosing a new directory.

Note: This type of database is not automatically installed together with MADRIX RADAR!

▪You need to install this server-based database management system separately if you wish to use it.

▪It can be installed on the same computer system or you can choose a different server/computer.

Install and configure PSQL on your local computer that also runs the MADRIX RADAR Software and set up the following options in MADRIX RADAR to enable it.

You need to set up PSQL before launching MADRIX RADAR or close the MADRIX RADAR Software should it already be running.

▪Run the PSQL installer and install the database management system on the computer that also runs the MADRIX RADAR Software.
- Remember the installation directory. It is required later.

▪Create a new database that MADRIX RADAR should use [via pgAdmin].
- Remember the name you gave the database as well as user names and passwords. They are required later.

▪See »https://www.postgresql.org/ for the download and detailed documentation.
- This user manual cannot provide full instructions on how to manage PSQL.
- Refer to an IT specialist if needed.

Set up the following options in the MADRIX RADAR Software to enable usage of PSQL.

▪Driver Directory - Defines the location of the required library files [*.dll] on your computer.
- Browse... - Allows you to choose a different folder or directory.
- Search for the correct directory on your computer or enter it manually. It is mandatory.
- Usually, the bin directory is within the PSQL installation folder: <PSQL Installation Directory>\<PSQL Version>\bin [such as: C:\Program Files\PostgreSQL\12\bin]
- MADRIX RADAR supports PSQL version 11 and higher.
- Changing the library path requires a software restart in order to load the required files.

▪Server - Defines the network address of the PSQL server, that is the computer that hosts the PSQL database management system.
- When using PSQL locally, the local address needs to be used: localhost, or alternatively 127.0.0.1
[The default value is localhost.]

▪Port - Defines the port that is used to establish the connection to the database. This option is available here since the port can be changed in the configuration of PSQL.
[The default value is the PSQL default port 5432.]

▪Database Name - Defines the name of the database. This can be especially useful when managing several projects/databases and the need to distinguish between them.
- Enter the name as defined by you during the creation of a corresponding database in PSQL!
[MADRIX RADAR provides a default name with MadrixRadarData. This is unlikely to be correct if you have not chosen the same name during creation of the database.]
[Do not enter and use the default database of PSQL named postgre, since it includes incorrect data.]

▪User Name - Enter the name that is used to establish the connection to the database; as set up during the configuration of PSQL.
[Any input will be saved and encrypted with the Advanced Encryption Standard AES 256.]

▪Password - Enter the password that corresponds with the user name; as set up during the configuration of PSQL. Any input will be hidden.
[Any input will be saved and encrypted with the Advanced Encryption Standard AES 256.]

Establishing a remote connection requires the involved systems to be [physically] connected.
In the most basic way, this means that the local computer that runs the MADRIX RADAR Software needs to be connected to the remote server that hosts the PSQL database.
[Local and remote refers to two separate computer systems, which still may very well be within your physical reach.]

▪Connect the MADRIX RADAR computer and the PSQL server over Ethernet network.
- Setting up unique and fixed IP addresses for each system is required.
- Make also sure to set up the correct subnet mask, which needs to be the same on both systems.
- Using a DNS is recommended, but not necessarily required [an IPv4 direct connection is possible].
- Refer to an IT specialist if needed.
[Connections over virtual networks, such as VPN, are not part of the explanation here.]

▪Run the PSQL installer and fully install the database management system on the remote server.

▪Create a new database that MADRIX RADAR should use [via pgAdmin].
- Remember the name you gave the database as well as user names and passwords. They are required later.

▪See »https://www.postgresql.org/ for the download and detailed documentation.
- This user manual cannot provide full instructions on how to manage PSQL.
- Refer to an IT specialist if needed.

This configuration is required before setting up options in MADRIX RADAR under step 5]. Otherwise, you need to restart MADRIX RADAR.

▪Connecting to a remote server is not enabled by default in PSQL. You need to allow a remote connection by editing a configuration file.

▪Navigate to the installation directory of PSQL and open the data folder [such as: C:\Program Files\PostgreSQL\11\data].

▪Scroll to the end of the file and enter the MADRIX RADAR computer that should connect to this PSQL server.
- You have various options on how to enter the computer, such as using the IPv4 IP address, IPv6 IP address, and device name [DNS lookup required].
- An example for valid entry can look like this: host all all 172.16.1.10/24 md5

▪See »https://www.postgresql.org/ for detailed documentation about the pg_hba.conf file and how to enter the computer.
- This user manual cannot provide full instructions on how to manage PSQL.
- Refer to an IT specialist if needed.

A separate rule needs to be set in the integrated firewall of the Windows operating system. If this rule is not set, MADRIX RADAR cannot access the database of the remote server.

▪1] In the Windows operating system, go to Start > Settings > Update & Security > Windows Security > Firewall & network protection > Advanced settings

▪5] Protocol and Ports: Choose TCP. Enter 5432 at Specific local ports: or the specific port you have set in pgAdmin.
Click Next > to continue.

▪7] Profile: Choose the network profiles to which the rule should apply [Domain, Private, Public].
Click Next > to continue.

▪8] Enter a Name: and optionally a Description for the rule you just created.
Click Finish

Please install PSQL before launching MADRIX RADAR or close the MADRIX RADAR Software should it already be running.

▪Download PSQL.
- The PSQL version can be higher than version used on the remote server.

▪Run the PSQL installer and partly install the database management system on the computer that also runs the MADRIX RADAR Software. The only required components are Command Line Tools
- Remember the installation directory. It is required later.

▪Make sure that a fully functioning network connection has been established between the local computer and the remote server!

▪See »https://www.postgresql.org/ for the download and detailed documentation.
- This user manual cannot provide full instructions on how to manage PSQL.
- Refer to an IT specialist if needed.

Set up the following options in the MADRIX RADAR Software to enable usage of PSQL on a remote server.

▪Driver Directory - Defines the location of the required library files [*.dll] on your computer.
- Browse... - Allows you to choose a different folder or directory.
- Search for the correct directory on your computer or enter it manually. It is mandatory.
- Usually, the bin directory is within the PSQL installation folder: <PSQL Installation Directory>\11\bin [such as: C:\Program Files\PostgreSQL\11\bin]
- Changing the library path requires a software restart in order to load the required files.

▪Server - Defines the network address of the PSQL server, that is the computer that hosts the PSQL database management system.
- When using PSQL remotely, the address of the remote server needs to be used.
- You have various options on how to enter the server, such as using the IPv4 IP address, IPv6 IP address, and device name.
- Example: 172.16.1.0
- Make sure to enter the correct address!
[The default value is localhost.]

▪User Name - Enter the name that is used to establish the connection to the database; as set up during the configuration of PSQL on the remote server.
[Any input will be saved and encrypted with the Advanced Encryption Standard AES 256.]

▪Password - Enter the password that corresponds with the user name; as set up during the configuration of PSQL on the remote server. Any input will be hidden.
[Any input will be saved and encrypted with the Advanced Encryption Standard AES 256.]

Since MADRIX RADAR uses database management systems to manage the data that is accumulated when working with RDM devices, a lot of data might ultimately be handled.
The following examples try to estimate the required storage space for different scenarios.

Please note: A definite answer cannot be provided, since the needed storage or memory depends on a lot factors.
Such factors are amongst others:

▪How many status messages or events may occur during the monitored period of time,

▪As you will see, using the Big Data features and a PSQL database can result in substantial amounts of data over time.

▪Example 01:
- 1000 RDM devices, with 25 parameters each, with 25 sensors each, are recorded for 1 month [data history] into a PSQL database, while data is queried once per day and each sensor value creates an event.
- Estimation for needed storage/memory : ~100 MB

▪Example 02:
- 200 RDM devices, which support nearly all parameters each, with 50 sensors each, are recorded for 1 day [data history] into a PSQL database, while data is queried every 5 minutes.
- Estimation for needed storage/memory : ~345 MB

▪Example 03:
- 2000 RDM devices, which support MIN/MAX values as well as recorded values, with 20 sensors each, are recorded for 6 months [data history] into a PSQL database, while data is queried every 4 hours.
- Estimation for needed storage/memory : ~5.2 GB

▪Example 04:
- 200 RDM devices, with 50 parameters each, with 50 sensors each, managed with SQLite (file) without data history.
- Estimation for needed storage/memory : 2 MB

▪Example 05:
- 2000 RDM devices, with 10 parameters each, with 10 sensors each, managed with SQLite (in-memory) without data history.
- Estimation for needed storage/memory : 4 MB