Document Management

A number of event management software providers, including Pandarix, have opted to provide an MS/SQL Relational Database as the backbone of their systems offering. Such architectural decisions are necessarily taken very early in the design phase of the system development and influence many future decisions and feature offerings downstream. Document management and file handling are just a couple of those features impacted.

When SQL Server 2000 was released it was hyped as a great step forward as it provided the ability to store images or similar data types within the database. Again many vendors have utilized this feature in an effort to ensure the client has all types of documents, images, audio files, emails etcetera in a single location for ease of reference of both current and historical purposes. There is huge debate about the pros and cons of the use of the relational database as opposed to or in combination of the use of a File Server or a NoSQL database in similar manners is way beyond this ‘blog’. Essentially the NoSQL and document databases provide an alternative to relational databases, not a replacement.

In an attempt to keep this simple, suffice it to say Pandorix stores the documents, images and emails within the same server as the system database as Binary Large Objects (BLOBs). The SQL 2008 file streaming function (FILESTREAM) is used to manage the pointers to these files and speed up the references as well the management of the database overall. The binary files reside out the database, but included as data in the database is a file path or URL to the object.

Using the CREATE DATABASE statement and by declaring these files as a file group with a physical location in the file system. Because it is another file group, SQL Server now considers the data in the file system to be an integral part of the overall database. So when backing up the database, the BLOB data in the file system will be included in your normal full and incremental database backup.

Because SQL Server is now intimately involved with the file system behind the scenes, the performance benefit of keeping BLOB storage outside of your structured table file groups, without the worry of programmatically maintaining the link references between rows in tables and corresponding files in the file system is in you favour.

This method of BLOB management is faster because reading data from the file system involves a bit less overhead than it would had it read directly from the database. Without BOLBs, this means your database is naturally smaller.

If this Blog was of interest be sure to read other industry related Blogs that will be continually appearing. Pandarix Software Solutions has 15 years of service and delivery experience. Our technology represents the latest industry standards and our friendly staff are available and ready to assist, answering all of your questions.