Configuring Microsoft SQL Server

Microsoft SQL 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 in Microsoft’s documentation: Setting the connection properties and Connecting with encryption .

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.

What’s next?

If you’re in the process of migrating to the external database for the first time, head over to continue with the steps (Step 5: Change the PaperCut NG/MF connection details) from our Upsize to an external database (RDBMS) article.

If you’ve already migrated (and changed the connection details as documented in Upsize to an external DB ) and are looking to confirm that the PaperCut Application Server is pointing to the external database successfully, you can check this using the system information in the PaperCut admin interface.

Navigate to the About tab > System info > Database, and you’ll see the type of database server listed. Database: Internal indicates that the App Server is still using the Internal ‘derby’ database, whereas Database: SQLServer indicates it’s using an SQL DB Server.