Available in PaperCut NG and PaperCut MF.

Upsize to an external database (RDBMS)

By default, PaperCut NG/MF uses an internal database product known as Apache Derby. You might, however, consider running PaperCut NG/MF on an external RDBMSA Relational database management system (RDBMS) is a program that lets you create, update, and administer a relational database. Most commercial RDBMS's use the Structured Query Language (SQL) to access the database, although SQL was invented after the development of the relational model and is not necessary for its use. if your organization:

  • has existing database infrastructure and wants to consolidate all applications on the same database platform.

  • has an existing database maintenance and backup procedure and wants PaperCut NG/MF to take advantage of this

  • uses 3rd party reporting and analysis tools (like Crystal Reports or Microsoft Access) to view and analyze the PaperCut NG/MF database

  • is very large and requires the performance benefits of a dedicated RDBMS. This also allows the database to reside on a separate server to PaperCut NG/MF, which improves the system scalability.

The high-level steps to upsize the database are:

  1. Stop the PaperCut NG/MF Application Server

  2. Perform a backup of the existing data

  3. Create a new database in the external RDBMS

  4. Configure the database

  5. Change the PaperCut NG/MF connection details

  6. Initialize the new database

  7. Load the data into the new database

  8. Restart the application

IMPORTANT

Each Application ServerAn Application Server is the primary server program responsible for providing the PaperCut user interface, storing data, and providing services to users. PaperCut uses the Application Server to manage user and account information, manage printers, calculate print costs, provide a web browser interface to administrators and end users, and much more. requires exclusive access to an external database. i.e. A single database cannot be shared between more than one Application Server. If you are running multiple Application Servers (e.g. a test and production instance), then each must have a separate database.

Step 1: Stop the PaperCut NG/MF Application Server

Stop the Application Server. This allows you to back up the data, guaranteeing that all data is saved and ready to load into the new database.

For more information, see Stop and start the Application Server.

Step 2: Perform a backup of the existing data

Perform a backup of the database. This data is loaded into the application in a later step. For more information, see Manage system backups.

To back up the database:

  1. On the Application Server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

  2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

    Mac: sudo su - papercut

    Linux: su - papercut

  3. Change (cd) to the server binaries directory. e.g.

    Windows: cd "C:\Program Files\PaperCut NG/MF\server\bin\win"

    Mac: cd "/Applications/PaperCut NG/MF/server/bin/mac"

    Linux: cd ~papercut/server/bin/linux-*

  4. Run the following command:

    db-tools export-db

    The output of the above command shows the name of the backup file.

  5. Take note of the backup file name because it is required in a later step.

Step 3: Create a new database in the external RDBMS

This step differs depending on the external database you are using. It is assumed that the administrator knows how to create a new database. The following steps must be performed regardless of the chosen database:

  1. Create a new empty database for dedicated use by PaperCut NG/MF. When creating the database, make sure you select the correct character encoding for your language.

    IMPORTANT

    Create the database with a character encoding suitable for your language. For SQLStructured Query Language (SQL) is a special-purpose programming language designed for managing data held in a relational database management system (RDBMS), or for stream processing in a relational data stream management system (RDSMS). Server, the character encoding is set in Collation on the new database screen. For other databases, such as PostgreSQL or MySQL, select a unicode character set (UNICODE or UTF8) that allows all possible characters to be stored.

  2. Create a new database user (and password) for PaperCut NG/MF to use to connect to the database.

  3. Assign the appropriate permissions to the new user to give them full access to the new database (for example, permission to create/drop tables, and select/insert/update/delete in all tables).

IMPORTANT

Make sure you have enough disk space for the growth of your PaperCut NG/MF database. A full disk can corrupt the PaperCut NG/MF database. For more information, see Database sizing and growth

We also recommend you set up notifications for low disk space as described here: Alert Email Notification through SCOM 2012 R2.

Step 4: Configure the database

After you have created you database, you must configure it for PaperCut NG/MF. For more information, see:

Step 5: Change the PaperCut NG/MF connection details

This section walks through the connection settings needed for the PaperCut NG/MF Application Server to connect to your external database—including authentication, connection strings, and any database driver requirements.

  1. On the Application Server, open the server config file in a text editor (for example, Notepad):

    [app-path]/server/server.properties

  2. (New installations only) Comment out the line:

    database.type=Internal

    by adding a # (hash) character at the start of the line.

  3. (New installations only) Find the database connection details for SQL Server ( or PostgreSQL), and uncomment the lines by deleting the # (hash) character at the start of the line.

  4. (New installations only) Set the username and password to connect to the database, and delete the [ ]s:

    database.username=[your-db-user]

    database.password=[your-db-password]

  5. Set the database connection URL, which describes the location and connection details of the external database.

    ClosedSQL Server database connection URL format

    Example

    An example of the SQL Server database connection entry, used in the server.properties config file (where bold text is site specific):

    database.type=SQLServer

    database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

    database.url=jdbc:sqlserver://sqlserverhostname:1433;databaseName=databasename;socketTimeout=600000

    database.username=username

    database.password=password

    Do I need to download the database driver?

    No! From version 19.0 of PaperCut NG/MF, the SQL Server driver is included in the PaperCut installation. However it’s worth reviewing the Configuring Microsoft SQL Server for more detail, especially if you’re upgrading; the driver and URL strings might need updating. Also, if you’re using PaperCut NG/MF version 18.3.9 or older, please see Configuring Microsoft SQL Server.

    Notes

    The SQL Server URL format is:

    jdbc:sqlserver://[server][:port];databaseName=[database];socketTimeout=600000

    The default port is 1433.

    When using SQL Server instances, specify the instance name in the connection URL as follows (do not include the [ ]s ):

    jdbc:sqlserver://[server][\\instanceName][:port];databaseName=[database];socketTimeout=600000

    Where:

    • [server] is the name of the server running the SQL Server database, and must be resolvable from the PaperCut NG/MF server. If the SQL Server instance is running on the same machine, then you can use localhost.

    • [port] is the port the SQL Server edition is configured to listen on. For more information on configuring SQL, see Configuring Microsoft SQL Server.

    • [database] is the name of the SQL Server database you created in the external RDBMS.

    For more information see Microsoft's information on building the connection URL.

    IMPORTANT

    See Configuring Microsoft SQL Server for specific instructions to configure SQL Server.

    If you want to enable Windows authentication, add ;integratedSecurity=true to the connection string:

    jdbc:sqlserver://[server][:port];databaseName=[database];socketTimeout=600000;integratedSecurity=true

    ClosedSQL Server Express database connection URL format

    Example

    An example of the SQL Server Express database connection entry, used in the server.properties config file (where bold text is site specific):

    database.type=SQLServer

    database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

    database.url=jdbc:sqlserver://sqlserverhostname:1433;databaseName=databasename;socketTimeout=600000

    database.username=username

    database.password=password

    Do I need to download the database driver?

    No! From version 19.0 of PaperCut, the SQL Server Express driver is included in the PaperCut NG/MF installation. See Configuring Microsoft SQL Server Express for more information.

    Notes

    The SQL Server Express format is (not including the [ ]s ):

    jdbc:sqlserver://[server][:port];databaseName=[database]

    Where:

    • [server] is the name of the server running the SQL Server Express database, and must be resolvable from the PaperCut NG/MF server. If the SQL Server instance is running on the same machine, then you can use localhost.

    • [port] is the port the SQL Server Express edition is configured to listen on. For more information on configuring SQL Express, see Configuring Microsoft SQL Server Express.

    • [database] is the name of the SQL Server database you created in the external RDBMS.

    IMPORTANT

    See Configuring Microsoft SQL Server Express for specific instructions to configure SQL Server Express.

    If you want to enable Windows authentication, add ;integratedSecurity=true to the connection string (do not include the [ ]s ):

    jdbc:sqlserver://[server][:port];databaseName=[database];integratedSecurity=true

    ClosedPostgreSQL database connection URL format

    Example

    An example of the PostgreSQL database connection entry, used in the server.properties config file (where bold text is site specific):

    database.type=PostgreSQL

    database.driver=org.postgresql.Driver

    database.url=jdbc:postgresql://postgresserverhostname/databasename

    database.username=username

    database.password=password

    Do I need to download the database driver?

    No! The PostgreSQL driver is included in the PaperCut NG/MF installation.

    Notes

    The Postgres URL format is (not including the [ ]s ):

    jdbc:postgresql://[server]/[database]

    Where:

    • [server] is the name of the server running the PostgreSQL database, and must be resolvable from the PaperCut NG/MF server. If the PostgreSQL instance is running on the same machine, then you can use localhost.

    • [database] is the name of the PostgreSQL database you created in Step 3 above.

    ClosedMySQL database connection URL format

    Example

    An example of the MySQL database connection entry, used in the server.properties config file (where bold text is site specific):

    database.type=MySQL

    database.driver=com.mysql.jdbc.Driver

    database.url=jdbc:mysql://mysqlserverhostname/databasename

    database.username=username

    database.password=password

    Do I need to download the database driver?

    Yes! For more information on obtaining the MySQL driver, see Configuring MySQL.

    Notes

    The MySQL URL format is not including the [ ]s ):

    jdbc:mysql://[server]/[database]

    Where:

    • [server] is the name of the server running the MySQL database, and must be resolvable from the PaperCut NG/MF server. If the MySQL instance is running on the same machine, then you can use localhost.

    • [database] is the name of the MySQL database you created in Step 3 above.

    IMPORTANT

    See Configuring MySQL for specific instructions to configure MySQL.

    ClosedOracle database connection URL format

    Example

    An example of the Oracle database connection entry, used in the server.properties config file (where bold text is site specific):

    database.type=Oracle

    database.driver=oracle.jdbc.driver.OracleDriver

    database.url=jdbc:oracle:thin:@oracleserverhostname:1521/myServiceName

    database.username=username

    database.password=password

    Do I need to download the database driver?

    Yes! For more information on obtaining the Oracle driver, see Configuring Oracle (and Oracle Express Edition).

    Notes

    The Oracle URL format is (not including the [ ]s ):

    jdbc:oracle:thin:@[server]:[port]/[ServiceName]

    Where:

    • [server] is the name of the server running the Oracle database, and must be resolvable from the PaperCut server. If the Oracle instance is running on the same machine, then you can use localhost.

    • [port] specifies the port number that the Oracle services are listening on. By default this is 1521.

    • [ServiceName] specifies the Oracle service name or identifier used to identify the database. For Oracle Express edition this is XE.

    For example, for an Oracle Express Edition installation on the same server as PaperCut, running on the default port and SID, use:

    jdbc:oracle:thin:@localhost:1521/XE

    IMPORTANT

    See Configuring Oracle (and Oracle Express Edition) for specific instructions to configure Oracle.

Step 6: Initialize the new database

The next step is to initialize the new database, creating the required database tables and initial data. To initialize the database:

  1. On the server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

  2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

    Mac: sudo su - papercut

    Linux: su - papercut

  3. Change (cd) to the server binaries directory. e.g.

    Windows: cd "C:\Program Files\PaperCut NG/MF\server\bin\win"

    Mac: cd "/Applications/PaperCut NG/MF/server/bin/mac"

    Linux (64-bit): cd ~papercut/server/bin/linux-x64

  4. Run the following command: db-tools init-db.

A message is displayed to indicate that the connection details are correct and the database was initialized correctly.

Step 7: Load the data into the new database

This step loads the data (that was exported in Step 2) into the database. To import the data:

  1. On the server, open a command prompt. (If you see 'Access Denied' errors, run the command prompt as an Administrator).

  2. On Linux or Mac, use su or equivalent to become the identity of papercut. e.g.

    Mac: sudo su - papercut

    Linux: su - papercut

  3. Change (cd) to the server binaries directory. e.g.

    Windows: cd "C:\Program Files\PaperCut NG/MF\server\bin\win"

    Mac: cd "/Applications/PaperCut NG/MF/server/bin/mac"

    Linux (64-bit): cd ~papercut/server/bin/linux-x64

  4. Run the following command: db-tools import-db "backup file name".

    This command shows the data import progress.

If no errors occur, the application is ready to restart.

Step 8: Restart PaperCut NG/MF

The data is now in the new database and you can restart the server.

For more information, see Stop and start the Application Server.

Wait 30 seconds for the server to start, then log in to the Admin web interface. If you can log in successfully, then the upsizing process worked successfully.