How will Dynamics GP Document Attach affect my database size?

By Steve Endow

I've seen a few complaints about how the Dynamics GP Document Attach feature will cause chaos and havoc because documents are stored in the SQL Server database, rather than on the file system.

It seems that some people are envisioning massive database growth that will make Dynamics GP databases difficult to manage.

Is this a valid concern?  Will document attachments cause a significant increase in database size?  I think it depends.

First, let's consider the attachments.  Before you can assess the impact on the database, you'll need to understand what you will be attaching.

Thinking about volume, how many documents will you attach per day, week, or month?  Will you be attaching documents to master records like Customers?  Or transactions like Sales Orders?  Will you attach a document to every Customer record or every Sales Order transaction?  Or only a portion of your records and transactions?

Next, what types of documents will you attach?  Word documents?  PDFs?  Image files?  How large will each file be?

Checking a few random samples of files I have handy, here are some sample file sizes I'm seeing.

12 page Word document:  178 KB
21 page Word document:  515 KB
1 page PDF of scanned grayscale document:  328 KB
3 page PDF of scanned grayscale document:  1,099 KB
1 page PDF of color web page printed from web browser:  30 KB - 300 KB  (varied based on images in file)

Although the document sizes are relatively small, the sizes vary significantly, which is important if you need to store thousands of them.

Let's suppose that the typical document is a 1 page PDF of a scanned grayscale document, so I'll work with my 328 KB test file.  (You may very well have a better scanner or better scanning software that produces smaller files, so you'll have to do some tests to determine your average file size.)

I'll then assume that these documents will be attached to 50% of my Sales Order transactions.  Assuming that I process 100 Sales Orders per day, that will be 50 document attachments at 328 KB each.  There is some additional overhead to storing the attachments in the database, but for now, let's just work with the actual file size on disk.

328,000 bytes x 50 = 16,400,000 = 16.4 megabytes per day

16.4 MB x 250 work days per year = 4.1 gigabytes per year

So in theory, based on the assumptions in my scenario, I will be storing an additional 4.1 gigabytes per year in my database.  Is that a lot?  I think that depends on the business, but in general, I would say that 4.1 GB shouldn't be a huge issue for most customers.  A few considerations might be how that larger database will affect backup routines, or if you compress your backups, will they compress as well with the binary data from the attachments?

But there are certainly customers where it may be a problem.  Consider large customers that have 5 or 10 very active Dynamics GP databases.  If they have similar numbers of documents for all databases, that could mean 20 GB to 40 GB of documents per year.  And if you consider that the customer may retain those documents for several years, you could end up with a significant amount of space consumed for document storage.

So yes, it is possible that Document Attach could result in significant database growth for certain larger or high volume customers.

However, I think this has to be considered in a larger context.  Document Attach is an optional feature in Dynamics GP--you don't have to use it, and I suspect many customers will not.  For customers that like the feature, if you don't need to attach a lot of documents and have simple requirements, it will probably work well.  If you need to attach a lot of documents, or if you need to attach large documents, Document Attach may not be the best option for you.  You certainly have other options, as there are plenty of document scanning and management solutions (including third party add-ons for GP), and those options will probably have several other features (indexing, searching, routing and workflow) that are valuable to you.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com