Skip to content
Support
Search

Configuring Microsoft SQL Server

This page applies to:

Contents

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.

To ensure maximum compatibility with databases and languages all over the world, PaperCut uses a default setting of sendStringParametersAsUnicode=true. If this was set to false, and the collation of a database is set incorrectly, then you may well retrieve”???”’s or hieroglyphs for non Latin or special characters. However, as stated in the Microsoft article above, it’s recommended that you work with your DBA to ensure your database collation is set up correctly and then set that string parameter to false. 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).

Setting Collation:

Collation is a very specific requirement for each business, which means it’s not something PaperCut can release a generalised recommendation for. We would recommend talking to your DBA about the correct settings based on your region or the types of characters you think you will encounter in usernames, document names, or other attributes.

Microsoft has a page dedicated to collation here: Collation and Unicode support which details the character sets (or collation) that your database will use. Prior to MS SQL 2019, there was no “out of the box” collation to support a wide range of characters. With MS SQL 2019 and later, Microsoft did release a collation set that has _utf8 at the end which is close to a universal collation.

You can set the collation of a database without needing to create a new database: Microsoft covers this procedure here: Set or change the database collation . However this is best done by someone experienced in database administration, and with the database backed up first.

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: Check the database driver in use

Ensure that you’re using a supported Microsoft database driver - see Upgrading the Microsoft SQL JDBC driver (database driver) for more information.

Step 6: 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.

Comments