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.

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

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.

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.