I've been reading some of the blog posts raving about the new document attach functionality in Dynamics GP 2013. To me, this is a scary sounding feature. Someone decided it was a good idea to store the documents in the database as blobs.
If you have a vendetta against your backup administrator, allowing users to stuff large files into the GP databases is a good plan of attack. You'll bask in glee as you delight over the thought of your foe having to contend with rapidly expanding databases. With this little time bomb, he'll be dealing with 100GB SQL databases in no time! What, there is corruption in the database and last night's backup needs to be restored? Sorry, the backup from 2 days ago is still running. We'll get that one restored for you as soon as possible.
Typically these forums are used for issues or questions not feedback. If you have feedback on a feature or would like it to be changed I would recommend you use the connect site.
I wasn't trying to to submit product feedback to Microsoft. I wanted to open up a discussion about the topic. A forum seems like the correct venue for something like that.
I agree with you, the location of where the attached document is stored, either in the database as it currently is or in some sort of file share on the network should be an option that customers could set for themselves.
Companies with a small number of files could use the SQL database for ease of backup reasons, while companies with lots of documents could use the file share to keep the size of the SQL database smaller.
However Jonathan is correct on where things like this be posted. Microsoft does watch the "connect" site, and does listen to users ideals. But a part of what it takes to get a user's request into the product is how many people "vote" for that request. Of course, there are other factors as well.
In fact, you can find this request on the "connect" site located at this URL, connect.microsoft.com/.../gp-document-attach-file-share-option-for-documents-after-attachment
So please feel free to go there and "Vote" for it.
Please, if this answers your question, mark it as 'Answered' so others reading this will know it resolved your issue.
For the benefit of others, if this answers your question, please mark it as "Answered".In addition, you can earn a “Badge” for yourself.
I disagree with both of you. John Lowther and I have had this conversation in person so I understand if in the end we agree to disagree. Here's why I like the way this feature was implemented:
1) Almost no companies effectively use the file storage based system in GP today. It's too easy to disconnect the location from the pointer. Companies screw up a new GP install and point the file store locally. Admins move the location on the network without realizing what's in it. I've seen way more cases of corruption of file storage for GP attached files than I have for SQL Server.
2) Large DB size are not abnormal now. If you plug in 3rd party document management system (Kwik Tag, Papersave, Meta Viewer, etc.) they are all database base. GP isn't doing anything outside of the norm here.
3) Backups are more likely to happen. One of the challenges of managing GP in the past is that you had to backup the DB, the dictionaries, the attached file storage locations, the FRx Sysdata file, any VBA and any other customizations. Now, MR is in the database eliminating the FRx sysdata file. The GP file storage is in the DB.VBA is being deprecated because it won't run on the web client. More reports are moving to SSRS, also in the SQL database. Changes to dictionary files and customizations tend to be point in time. They don't change daily so even an old backup is usually helpful. Yes this tends to put responsibility for backups and restores on the DB administrator, but that responsibility is usually there today anyway. Now the DB admin can actually control that responsibility and make an argument for better tools. It shouldn't take 2 days to restore a 100gig db from external storage.
4) The team had to do it this way. Sorry John, your hybrid idea isn't going to fly and it's because of the web client. The team is trying to maintain a single code base and common functionality across their client. Uploading to secure file storage would make the web client way more complicated than uploading to the DB. There's already a secure connection to the DB. A significant amount of pressure is being placed on the GP team from competitors who are web based and Microsoft who is pushing a cloud first agenda. We saw the ribbon interface first in the web client and now the desktop has caught up.
The file attach functionality in GP has been effectively broken for a long time. It's clunky to use, easy to setup incorrectly, and easy to break. DB storage is harder on DB admins and will require better education for customers, but in the end I think it's a better option for the business as a whole.
I finding it rather surprising in this discussion that no one has mentioned the fact that the SQL team has already recognized that "polluting" your database tables with BLOB data is sub-optimal. It is why FILESTREAM was developed for SQL 2008 R2. It does require some extra coding but you can have the best of both worlds. I would like to hear the reason why the GP team didn't implement using FILESTREAM as an option. Everything is handled within T-SQL. It could be just a simple case of resource allocation (which I completely understand). There are many, many reasons to like Doc attach. But I won't be encouraging my users much when I upgrade to 2013.
I wasn't intending to imply that the current file-based storage is ideal. It has all the problems that you mentioned, Mark. Storing the attachments in the database, though, is just swapping one set of problems for another.
My preferred implementation for an attachment system is to implement a server piece that marshals the attachment to and from file-based storage. It seems, however, that the GP team is trying to implement attachments in such a way that the desktop client can handle everything on its own. I would have preferred if the new Document Attach feature required the web client server piece as a pre-req even for the desktop client to make use of it.
The technology available for backup of files is just more advanced. "Incremental Forever" doesn't exist for SQL databases. Try developing an effective backup solution for a 1 TB database.
When we looked at Onbase, they were using file-based storage. So, not all document management solutions are on board with the idea of dumping everything into a database.