Configuring a database
This section includes database specific configuration for use with PaperCut NG.
Configuring Microsoft SQL Express
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). Express provides enterprise class database performance for free. However, it does have some limitations when compared to the full version of SQL Server. But these limitations are not likely to adversely affect most PaperCut NG users. These limitations include:
4GB limit on database sizes (10GB for SQL Server Express 2008 and 2012)
Limited to use only 1 CPU
Limited to use only 1GB of RAM
This section described how to configure Microsoft SQL Express edition for use with PaperCut NG. It is assumed that SQL Server Express is already installed with the default configuration.
Once this configuration is complete, you can use the database with PaperCut NG by following the instructions in Upsize to an external RDBMS.
Enable TCP/IP connections
PaperCut NG uses TCP/IP to connect to the SQL Server database, but SQL Server Express does not enable TCP support by default. To enable TCP/IP:
On the machine with SQL Express installed, open the SQL Server Configuration Manager.
On the left, expand the SQL Server Network Configuration node.
On the left, select the Protocols for SQLEXPRESS node.
Right-click the TCP/IP item on the right; then select Properties.
On the General tab, change Enabled to Yes.
On the IP Addresses tab, under the IPAll node, clear the TCP Dynamic Ports check box.
In TCP Port, enter the port to listen on . For example, 1450. Remember this port, because it needs to be used in the PaperCut NG connection string.
Click OK.
Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.
Enable SQL Server authentication
PaperCut NG requires SQL Server authentication to be enabled on the instance of SQL Express. To do this:
On the machine with SQL Express installed, open the SQL Server Management Studio Express tool.
Right-click the instance of SQL Express to configure; then select Properties.
Select the Security section on the left.
Change the Server Authentication to SQL Server and Windows Authentication mode.
Restart the Microsoft SQL Server Express service using either the standard service control panel or the SQL Express tools.
Create database user
PaperCut NG requires a user to connect to the database. To create this user:
On the machine with SQL Express installed, open the SQL Server Management Studio Express tool.
Right-click the Security > Logins node; then select New Login.
Enter the username (e.g. papercut).
Change the Server Authentication to SQL Server and Windows Authentication mode.
Enter the user's password.
Disable password expiration.
Click OK.
After creating the PaperCut NG database, assign this user db_owner permissions on the database, so that it can create the required database tables.
To initialize the database, follow the instruction in Upsize to an external RDBMS.
Configuring MySQL
MySQL is a free/open-source database solution that provides robust, proven, and scalable storage at a great price. PaperCut NG supports MySQL 5.5 and higher.
PaperCut NG requires the use of the MySQL InnoDB table type, which provides full support for transactions. Ensure your MySQL database server is configured to support InnoDB (usually this is enabled by default).
Database driver
PaperCut NG does not ship with a database driver for MySQL because the MySQL licensing does not allow redistribution of the driver. Download these drivers for free from the MySQL website as described below.
To download the required version of the driver:
Visit the MySQL web site download page for the MySQL Connector/J product here: http://dev.mysql.com/downloads/connector/j/.
Select the appropriate driver version (the latest version is best).
Download the driver package and unzip the contents to a temporary directory.
Find the driver JAR file, which is typically named mysql-connector-java-X.Y.Z-bin.jar.
Copy the JAR file into the [app-path]\server\lib-ext directory. This allows PaperCut NG to find and load the driver.
Once the driver is installed into PaperCut NG, you can follow the standard upsizing procedure. For more information, see Upsize to an external RDBMS.
Configuring Oracle (and Oracle Express Edition)
Oracle is a high-end database solution that provides a very robust and scalable data storage solution. And with the release of Oracle Express Edition, it is available at no cost, but it does have some limitations that should not impact PaperCut NG installations.
PaperCut NG supports Oracle versions 9.2 and higher. Oracle 8 (and earlier) are not supported because they did not support the TIMESTAMP datatype required by PaperCut NG.
Database driver
PaperCut NG does not ship with a driver for Oracle because Oracle does not allow us to redistribute the driver. Also, the recommended driver depends on the version of Oracle used. You can obtain these drivers from the Oracle website as described below.
To download the required version of the driver:
Visit the Oracle web site here: http://www.oracle.com/technetwork/database/features/jdbc/index-091264.html
Select the appropriate Oracle version.
The required file to download differs depending on the Oracle version.
Download the ojdbc7.jar for your Oracle Database version.
Copy the downloaded file into the [app-path]\server\lib-ext directory.
Restart the service PaperCut Application ServerAn Application Server is the primary server program responsible for providing the PaperCut user interface, storing data, and providing services to users. PaperCut uses the Application Server to manage user and account information, manage printers, calculate print costs, provide a web browser interface to administrators and end users, and much more.. This allows PaperCut to find and load the driver.
Once the driver is installed into PaperCut NG, you can follow the standard upsizing procedure. For more information, see Upsize to an external RDBMS.
Best practices on Microsoft SQL Server
Microsoft SQL Server provides enterprise class database performance. With this type of database, it is recommended that the administrator puts 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. It's highly recommended that this optimization be set up 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;