There exists 3 different Database Systems that are supported by PowerFolder: MSSQL, PostgreSQL, MySQL.

You are free to choose which database you want to use.

Install MSSQL Server

Microsoft provides a guide for installing SQL Server. Generally the guide provides the most basic steps to install SQL Server. A few changes need to be applied during the installation:

Configuring the database collation
Enabling mixed-mode authentication

Configuring the database collation

Microsoft SQL Server collation settings required by PowerFolder Server

It is important that you configure the following database collation options during the installation of SQL Server:

Collation: Latin1_General
Case-sensitive
Accent-sensitive

So the final database engine should result in: Latin1_General_CS_AS

Enabling mixed-mode authentication

Microsoft SQL Server support Windows authentication based logins, however for PowerFolder Server it is necessary to create a separate user and password which can access the PowerFolder Server database. During the installation of SQL Server it is possible to enable mixed-mode authentication, which supports external logins with dedicated users created within the SQL Server.

Create a SQL Server database and user

Download/install SQL Server Management Studio to connect to the database server.
Right-click on Security > Logins to create a new user.
Right-click on Databases to create a new database and assign the new user, we've just created as an owner of the new database.

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the Microsoft SQL Server:

Download the MS JDBC drive for SQL
Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
Download .zip or tar.gz depending upon your OS.
After extraction go in the extracted folder and under sub-folder jars you will find out the file mssql-jdbc-12.8.0.jre11.jar (JRE 11 supports the openJDK version of PF server).
Copy mssql-jdbc-12.8.0.jre11.jar from the package downloaded into the /libs sub-directory created in the step before.
Restart PowerFolder Server.

Setup the database connection

Once we added the driver for JDBC driver to connect to the SQL Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the SQL Server database and user you performed in step three of this guide.
```
hibernate.connection.url=jdbc:sqlserver://localhost:1433;databaseName=powerfolderdb;
hibernate.connection.username=powerfolderuser
hibernate.connection.password=powerfolderpass
hibernate.dialect=de.dal33t.powerfolder.server.db.sql.SQLAddNVarCharDialect
hibernate.c3p0.max_size=500
```
The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.
If you want to use the database connection without SSL and self signed certificate please use the following connection string:
```
hibernate.connection.url=jdbc:sqlserver://localhost:1433;databaseName=pf;encrypt=false;trustServerCertificate=false
```
Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

Install MySQL Server

MySQL Servers

PowerFolder provides support for:

Percona XtraDB Cluster 8.0 & 5.7
MySQL Server 8.0 / 5.X
Percona MySQL Cluster 8.0

The way to install MySQL Server depends on the operating system where you are installing it. In the official MySQL documentation there are MySQL Server Installation Guides for almost all operating systems available:

MySQL Configuration Settings required by PowerFolder Server

[mysqld]
...
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
...

It is important that you configure your MySQL Server to use UTF-8mb4 for encoding and InnoDB as storage engine as well raising the connection limit.

Configure those requirement in the MySQL Server's my.cnf configuration file:

Setting the default character set to UTF-8:

Setting the default storage engine to InnoDB:

[mysqld]
...
default-storage-engine=INNODB
...

Increasing the the connection/error and long query limits:

[mysqld]
...
max_connections                 = 3000
max_connect_errors              = 3000
long_query_time                 = 5
...

Increasing the InnoDB buffer pool size

[mysqld]
...
innodb-buffer-pool-size			= 2757M
...

Please note that you need to restart the MySQL Server service for the changes to take effect.

Create a MySQL Database and User

Use your MySQL super user (the "root" account) to login with the "mysql" command line tool:
```
mysql -u root -p
```

Create an empty database and a dedicated user for PowerFolder server:

CREATE DATABASE powerfolder;
CREATE USER 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';

Grant privileges to access the new database and a password for that user (MySQL 5):
```
GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost' IDENTIFIED BY 'powerfolderpass';
```
Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice. If PowerFolder Server is not running on the same server as your MySQL database server, replace "localhost" with the hostname or IP address of the PowerFolder server.

Grant privileges to access the new database (MySQL 8):

GRANT ALL PRIVILEGES ON powerfolder.* TO 'powerfolderuser'@'localhost';

Database connection using Kerberos authentication

The advantage of using Kerberos is that no username and password is needed in PowerFolder.config.

For more information please consult our documentation: Database connection using Kerberos authentication

Important

The Kerberos connection is only compatible with msSQL, other databases are not tested from our side.

When you are intrested, please contact us via support@powerfolder.com

Setting UTC time zone in MYSQL database

In the Sommer and Winter time changes there comes issues in the database connections and the communication between the PowerFolder-Server and the database can also be affected if following changes are not carried out:

Direct your database server so that it can recognize the timezone
```
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql
```
Set now the UTCin the MYSQL console
```
SET GLOBAL time_zone = UTC;
```
Set the time zone as UTC also in the MYSQL-Configuration file
```
default-time-zone = 'UTC'
```

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the MySQL Database Server:

Download the v8.0.28 Connector/J for MySQL. (MySQL 8)
Download the v5.1.47 JDBC Database Driver for MySQL. (MySQL v5.X)
Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
Extract the mysql-connector-java-<version>-bin.jar (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MySQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the MySQL Database and User you performed in step three of this guide.

hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false 
hibernate.connection.username=powerfolderuser
hibernate.connection.password=powerfolderpass
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.c3p0.max_size=500

* Without SSL:
hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder?serverTimezone=UTC&useLegacyDatetimeCode=false&useSSL=false

The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.

Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.

Install PostgreSQL Server

The way to install PostgreSQL Server depends on the operating system where you are installing it. In the official PostgreSQL documentation there are guides for the installation available.

Please make sure that the PostgreSQL Server will listen on the hostname and port, which you are going to configure later on in the PowerFolder configuration. Furthermore you've to allow the user created below to access the database created below in the PostgreSQL network configuration.

Create a PostgreSQL Database and User

Use your PostgreSQL super user (the "postgres" account) to login with the "psql" command line tool:
```
psql -h localhost -p 5432 -U postgres
```
Create a new user account:
```
CREATE USER powerfolderuser WITH PASSWORD 'powerfolderpass';
```
You might need to use backslashes for escaping special characters in the password.
Replace "powerfolderuser" and "powerfolderpass" with a username and password of your choice.
Create an empty database:
```
CREATE DATABASE powerfolder;
```
Create a user privileged to access the new database and a password for that user:
```
GRANT ALL PRIVILEGES ON DATABASE powerfolder to powerfolderuser;
```

Setup the JDBC driver

PowerFolder Server needs to be loaded with the correct JDBC driver to establish a connection with the PostgreSQL Database Server:

Download the JDBC Database Driver for PostgreSQL.
Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
Move the postgresql-<version>.jdbc41.jar (where <version> is the actual release number) to the /libs sub-directory created in the step before.
Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the PostgreSQL Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the PostgreSQL Database and User you performed in step three of this guide.
```
hibernate.connection.url=jdbc:postgresql://localhost:5432/powerfolder
hibernate.connection.username=powerfolderuser
hibernate.connection.password=powerfolderpass
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.c3p0.max_size=500
```
The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.
Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

We recommend you to install and use the above stated three databases, some of our customers are also using MariaDB and thats why we are taking the database in our documentation, it means that we are unable to support this database till is marked from QA-Department as tested and supported.

Install MariaDB Server

MariaDB v10.11.X GA

PowerFolder server supports the new MariaDB community or enterprise edition. We prefer to use the GA (general avaialabilty) version and not the development builds.

Depending on systems the MariaDB can be installed using following instructions:

Please follow the steps as stated in the documentation and you can also repeat the steps of installation of MySQL for installing a MariaDB Server.

After upgrading to PFS v14 the coding / charset of MariaDB 10.3 should be changed to default colation utf8mb4_general_ci, so that the database can open a connection and set the foriegn key.

character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init-connect = 'SET NAMES utf8mb4'

default-storage-engine=INNODB
max_connections = 3000
max_connect_errors = 3000
long_query_time = 5
innodb-buffer-pool-size = 2757M

Setup the MariaDB Connector/J

PowerFolder Server needs to be loaded with the correct MariaDB Connector/J to establish a connection with the MySQL Database Server:

Download the java file MariaDB Connector/J v 2.7.12-GA ( Version 3.5.X and other are not compatible with PowerFolder)
Create a sub-directory called /libs under the directory, where you installed the PowerFolder Server software, namely where the PowerFolder-Server.jar file resides.
Extract the mariadb-java-client-<version> (where <version> is the actual release number) from the package downloaded into the /libs sub-directory created in the step before.
Restart PowerFolder Server.

Setup the database connection

Once we added the JDBC driver to connect to the MariaDB Database Server, the next step is to tell PowerFolder Server to which database to connect and which credentials to use:

Open the PowerFolder.config file and add the entries below. Apply the changes which are necessary to match the setup of the Maria Database and User you performed in step three of this guide.

hibernate.connection.url=jdbc:mysql://localhost:3306/powerfolder
hibernate.connection.username=powerfolderuser
hibernate.connection.password=powerfolderpass
hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
hibernate.c3p0.max_size=500

The database.connections.max parameter can be adjusted according to the maximum number of connections your database server accepts.
Start PowerFolder Server. Now it should automatically fill the new database with tables and start to use it.

After the changes in the MYSQL-Console, Configuration and PowerFolder.config please restart the MSQL-Service and PowerFolder-Server also.

Overview: