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:

Enable SQL Server authentication

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

Create a database user

PaperCut 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 (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 MF 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.

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.