You are here: Installation > Advanced implementation > Deployment on an external database (RDBMS) > Configuring Microsoft SQL Server

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 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:

Enable SQL Server authentication

PaperCut NG 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.

Create a database user

PaperCut NG 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 (e.g. 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 database, assign this user db_owner permissions on the database, so that it can create the required database tables.

  8. Initialize the database.

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.


Comments

Share your findings and experience with other PaperCut users. Feel free to add comments and suggestions about this Knowledge Base article. Please don't use this for support requests.