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:
The last of those points is also of relevance when discussing future growth and capacity…
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!
PaperCut MF supports the following external databases out-of-the-box:
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.
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
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.
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 ( “?”)’;
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.
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…)