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: 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 2: 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 3: Choose a database collation

If you’re using SQL Server version 2019 or later, as documented on Microsoft’s Setting the connection properties article, the recommendation is to add the parameter sendStringParametersAsUnicode=false to the PaperCut NG/MF database connection string .

In Microsoft’s words, this is “for optimal performance”. We have seen customers achieve a significant performance increase for busy databases as a result of this.

However it’s essential that you work with your DBA to ensure your database collation is set up correctly, as mentioned in Microsoft’s article above. It’s especially important to test any collation settings if you have document names or usernames containing characters with accents or non-Latin characters (for example these may appear as ????? in the job logs or user lists, if the collation is not properly chosen and configured).

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.

 

Step 5: Continue with the PaperCut NG/MG DB upsizing steps

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 (DB) 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.