We’re going to delve into a topic that regularly crops up via emails and calls; deploying with – or upsizing PaperCut to – an external database, DUN DUN DUN! Don’t worry, it is not anything to be scared about. Cup of tea in hand? Great, then keep scrolling!

The process involved in deploying PaperCut with, or moving it to, an external DB can seem daunting at first, but in fact the process isn’t all that complicated, the steps listed in the PaperCut manual under Upsize to an external database will show you how to tackle it with speed and confidence (all you ever need in this game).

But first, it’s worth taking a moment to look at some reasons why you might want to consider doing it and how it may be beneficial. By default, PaperCut MF uses an internal database called Apache Derby. You might, however, consider running PaperCut MF on an external database if:

• Your organisation has existing database infrastructure and wants to consolidate all applications on the same database platform.
• Your organisation has existing database maintenance and backup procedures and wants PaperCut MF to take advantage of this.
• People want to use 3rd party reporting and analysis tools (like Crystal Reports or PowerBI etc) to view and analyse the data in the PaperCut MF database. The built-in Apache database won’t allow this due to concurrent connection limitations (your PaperCut MF install is all that is allowed to talk to the database).
  
• You want to keep that data super safe. Probably the most important area to consider for failover is in the database (as data is important, right?) anything else can be rebuilt/installed again.
• Your organisation is very large and requires the performance benefits of a dedicated database. This also allows the database to reside on a separate server to PaperCut MF, which improves the system scalability. Unsure? Email our pre-sales experts.

The last of those points is also of relevance when discussing future growth and capacity…

Database sizing and growth

One of the important things to consider for any database is how big will that database get? Care must be taken to ensure there is enough disk space to hold the growing database, which is one of the more popular issues we see on our support desk.
A PaperCut install can get very upset if it is trying to write info to the internal database and there is no space on the disk, the same can be said if there is activity in the database and the power to the machine is removed in a rude and abrupt fashion. PaperCut has a great article on database size, have a read over a cuppa and get the calculator out and at the same time remember hard drives are cheap!

Supported database

PaperCut MF supports the following external databases out-of-the-box:

• Microsoft SQL Server 2000/2005/2008/2012/2014/2016 (either 32-bit or 64-bit)
• Microsoft SQL Server Express 2005/2008/2012/2014/2016 (free from Microsoft)
• PostgreSQL 7.1+ (a free open source database)
• MySQL 5.5+ (a free and open source database) while not officially supported MariaDB and Percona also work as drop-in replacements for MySQL)
• Oracle 9.2+ (including the free Oracle Express Edition).

SQL, SQL, SQL

Sure, other databases exist we’ve even listed some above, but our stats show most people use good old MS SQL. So, if you are going to use an external database (and you should) and you are going to use MS SQL (one of our favourites) then the following tips might help make that experience better for you and your customer.

Use the MS SQL database driver

Let us start by setting you off on the right footing.

Anyone using MS SQL 2014 or later should look at using a certain driver from Microsoft to help their database interact with PaperCut in a nicer way. You can download the latest version of Microsoft’s JDBC driver for SQL Server here.

Now, to install it requires some extra steps but nothing tricky, if we take the version 7.0 driver as an example

1. Run the exe file, (in this example) sqljdbc_7.0.0.0_enu.exe to extract the files.
2. Copy the file mssql-jdbc-7.0.0.jre8.jar from the extracted directory and place it in C:\Program Files\PaperCut MF\server\lib-ext\
3. Comment out your current SQL entries from the server.properties file in C:\Program Files\PaperCut MF\server (these will form your backup plan)
4. Copy the following entries into the server.properties file and modify to point to your SQL server:

database.type=SQLServer

database.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver

database.url=jdbc:sqlserver://<serverName>:<portNumber>;databaseName=<databasename>;socketTimeout=600000

database.username=<user name>

database.password=<password>

Your database will love you for it.

Anything else? Good question! Our last tip would be to let you know that PaperCut 19.0+ ships with the 7.0 driver by default so any new installs of PaperCut are going to be super happy.

Set statistics to auto update

Each table inside of your PaperCut database has lots going on, especially when information needs to be found. All of that data is indexed nicely so if you are searching for all print jobs by “Eric Brooks” the database knows it can scan the indexes for “Eric Brooks” to get what it needs without having to read each record in the table.

To keep things fast that index needs some love sometimes, you can set up this optimisation as a scheduled task out of hours to avoid the database getting too busy during the workday.

The optimisation PaperCut recommend you run is:
exec sp_msForEachTable @COMMAND1= ‘DBCC DBREINDEX ( “?”)’;
exec sp_updatestats;

In PaperCut’s own words:
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 optimisation, and now takes a matter of seconds.

Why? Data loves a reindex, magic happens and things move faster, do it, do it now! As always, consult with the customers DB Admins before making any changes.

What’s next?

Any questions? Get in touch! We enjoy learning about the new challenges customers face and are more than happy to offer our experience to assist you. We also hope you all know who Eric Brooks is… (best Marvel Film ever…)