Available in PaperCut NG and PaperCut MF.

Configuring Microsoft SQL Server

Microsoft 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 provides enterprise class database performance. With this type of database, you should put in place a database re-index every month. This database optimization helps with general performance, and more specifically, large queries performed on the SQL database. These queries include Reporting, as well as Bulk Actions performed on multiple data points.

IMPORTANT

Ensure that SQL Server has the TCP protocol, and the server authentication option is set to SQL Server and Windows Authentication.

The database user created for PaperCut NG/MF should have only the minimum set of permissions required for the PaperCut application. The user should have full permissions to create/drop tables and have full access to any created tables. However, the user should not have permission to access other databases installed on the database server.

To configure Microsoft SQL Server, perform the following tasks on the machine with SQL Server installed:

Step 1: Get the driver for the SQL Server

NOTE

If you have PaperCut NG/MF version 19.0 or later installed, you don't need to do this step—go to Step 2: Enable SQL Server authentication.

  1. For PaperCut NG/MF versions 18.3.9 and below, download the latest version of Microsoft’s JDBC driver (sqljdbc_7.0.0.0_enu.exe) for SQL Server from this link:

    https://docs.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-2017

  2. Run the downloaded file to extract the files.

  3. Copy the file mssql-jdbc-7.0.0.jre8.jar from the location the files were extracted to, to <PaperCut MF or NG install directory>\server\lib-ext\

  4. Run the downloaded file to extract the files.

  5. Copy the following entries into the server.properties config file (where bold text is site specific).(Comment out the old entries—that will be your backup plan!)

    database.type=SQLServer

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

    database.url=jdbc:sqlserver://serverName:portNumber;databaseName=databasename;socketTimeout=600000

    database.username=username

    database.password=password

  6. If the connection requires additional properties (not expected, based on what we know), you can find them here: https://docs.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-2017

Step 2: Enable SQL Server authentication

PaperCut NG/MF requires SQL Server authentication to be enabled on the instance of SQL Server. To do this:

  1. In SQL Server Management Studio, right-click the instance of SQL Server to configure; then select Properties.

  2. Select the Security section on the left.

  3. Change the Server Authentication to SQL Server and Windows Authentication mode.

  4. Restart the Microsoft SQL Server service using either the standard service control panel or the SQL Server tools.

Step 3: Create a database user

PaperCut NG/MF requires a user to connect to the database. To create this user:

  1. In SQL Server Management Studio, right-click Security > Logins; then select New Login.

  2. Enter the username (for example, papercut).

  3. Change the Server Authentication to SQL Server and Windows Authentication mode.

  4. Enter the user's password.

  5. Disable password expiration.

  6. Click OK.

  7. After creating the PaperCut NG/MF database, assign this user db_owner permissions on the database, so that it can create the required database tables.

  8. Initialize the database.

Step 4: Set statistics to auto update

  1. In SQL Server Management Studio, right-click the database; then select Properties.

    The Database Properties dialog is displayed.

  2. Scroll to the top of the Other options list.

  3. In Auto Update Statistics, select True.

  4. Click OK.

  5. NOTE

    Alternatively, you can set up this optimization as a scheduled task after hours to ensure you see no performance issues due to database indexing problems.

    The optimization we recommend you run is:

    • exec sp_msForEachTable @COMMAND1= 'DBCC DBREINDEX ( "?")';

    • exec sp_updatestats;

    Databases with large databases with millions of print jobs can complete this command in less than three minutes. The result was seen where reporting took 10-15 minutes before optimization, and now takes a matter of seconds.