October 20, 2016 at 5:28 pm
I am trying to investigate to use filestream for a .net application that wants to let users to upload and download document as attachment.
We have one old project that is done by consultant that currently no longer used. I see he set up two databases, one database is for the application main database for example called Evaluation, not filestream enabled, the other is a filestream enabled database, called EvaluationAttachment , which only have one table that is for the attachment .
I am wondering why he uses two databases, I guess it makes easier to maintain for backup and restore etc. But how they can be linked and used in code, I am not sure. I cannot find any code in the two SQL databases that refers each other.
Any ideas?
Thanks,
October 20, 2016 at 11:54 pm
Data can be linked between two databases just as easilyas between one, joins, etc, still work exactly the same way.
The only way to know how data links together is to know the data. In an ideal word, one table will have a foreign key, which is also appropriately named (for example having an Order table, with a field "UserID", which unsurprisingly is a foreign key for UserID within the User table).
I'm guessing that the fields aren't really making it apparent which one is correct, however, that's not something we can really answer for you. If you know that certain files within the filestream database are linked to certain records in the other database, I would start there, and see what denotes that they link (Do they have a simplefile path struture instead of a key?). Then it would, unfortunately, be a case of trial and error.
Worst case scenario, there is no relation, and the previous end users was using the database like a storage device, rather than what it is (a relational database).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 21, 2016 at 6:08 am
No idea why someone would use two databases in this fashion unless the filestream was added as an afterthought and they just didn't want to modify the existing database.
In general, I'm underwhelmed with filestream. The performance isn't great. Backup and restore includes all the files, so you have to have a lot more storage (unless you do a partial backup). The one thing you get out of it is basically integrity to know that if the database thinks a file exists, that file is there. Most of the time, that just doesn't offset all the pain involved in maintaining the filestream info. I'd prefer to simply link to the file and deal with any data integrity issues later. Separate database storage and maintenance from file storage and maintenance.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2016 at 9:38 am
From the two database ends I did a search cannot find any code that refer each other. No FK, no joins related to each other. In the main application database there are all tables, and the table that has attachment word is FormAttachement, it has below fields
CREATE TABLE [dbo].[FormAttachment](
[FormAttachmentID] [INT] IDENTITY(1,1) NOT NULL,
[FormID] [INT] NOT NULL,
[AttachmentUID] [UNIQUEIDENTIFIER] NOT NULL,
[AttachmentCategoryID] [INT] NOT NULL,
[AttachmentName] [VARCHAR](260) NOT NULL,
[AttachmentFileExtension] [VARCHAR](10) NOT NULL,
[AttachmentDesc] [VARCHAR](200) NULL,
[CreateDate] [DATETIME2](7) NOT NULL,
[CreatedBy] [VARCHAR](50) NOT NULL,
[ChangeDate] [DATETIME2](7) NULL,
[ChangedBy] [VARCHAR](50) NULL
)
In the other filestream database evaluationAttachment there is only one table:
CREATE TABLE [dbo].[Attachment](
[AttachmentID] [INT] IDENTITY(1,1) NOT NULL,
[AttachmentUID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[AttachmentName] [VARCHAR](100) NOT NULL,
[Attachment] [VARBINARY](MAX) FILESTREAM NOT NULL,
[CreateDate] [DATETIME2](7) NOT NULL,
[CreatedBy] [VARCHAR](50) NOT NULL,
[ChangeDate] [DATETIME2](7) NULL,
[ChangedBy] [VARCHAR](50) NULL
)
I cannot see how they linked from database end. The records count are the same, data content is the same too, other than the formAttachment table has additional columns.
October 21, 2016 at 9:47 am
Grant Fritchey (10/21/2016)
. Most of the time, that just doesn't offset all the pain involved in maintaining the filestream info. I'd prefer to simply link to the file and deal with any data integrity issues later. Separate database storage and maintenance from file storage and maintenance.
Is there any document or guide how to set that up in database and front end for this option?
Basically we would like some in our .net application, for users to upload a document for a case, and may be also later to download it, and open it and save it again.
Something like in SharePoint , you can add an attachment for a list item.
Thanks
October 21, 2016 at 10:32 am
sqlfriends (10/21/2016)
Grant Fritchey (10/21/2016)
. Most of the time, that just doesn't offset all the pain involved in maintaining the filestream info. I'd prefer to simply link to the file and deal with any data integrity issues later. Separate database storage and maintenance from file storage and maintenance.Is there any document or guide how to set that up in database and front end for this option?
Basically we would like some in our .net application, for users to upload a document for a case, and may be also later to download it, and open it and save it again.
Something like in SharePoint , you can add an attachment for a list item.
Thanks
Sorry I wasn't clear. By "link to the file" I meant simply to store, as a string in the database, the location of the file. That would probably be as a path the file (through the resource path, a URL, whatever). There's not a lot of "guide" needed for that.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2016 at 10:36 am
sqlfriends (10/21/2016)
From the two database ends I did a search cannot find any code that refer each other. No FK, no joins related to each other. In the main application database there are all tables, and the table that has attachment word is FormAttachement, it has below fieldsCREATE TABLE [dbo].[FormAttachment](
[FormAttachmentID] [INT] IDENTITY(1,1) NOT NULL,
[FormID] [INT] NOT NULL,
[AttachmentUID] [UNIQUEIDENTIFIER] NOT NULL,
[AttachmentCategoryID] [INT] NOT NULL,
[AttachmentName] [VARCHAR](260) NOT NULL,
[AttachmentFileExtension] [VARCHAR](10) NOT NULL,
[AttachmentDesc] [VARCHAR](200) NULL,
[CreateDate] [DATETIME2](7) NOT NULL,
[CreatedBy] [VARCHAR](50) NOT NULL,
[ChangeDate] [DATETIME2](7) NULL,
[ChangedBy] [VARCHAR](50) NULL
)
In the other filestream database evaluationAttachment there is only one table:
CREATE TABLE [dbo].[Attachment](
[AttachmentID] [INT] IDENTITY(1,1) NOT NULL,
[AttachmentUID] [UNIQUEIDENTIFIER] ROWGUIDCOL NOT NULL,
[AttachmentName] [VARCHAR](100) NOT NULL,
[Attachment] [VARBINARY](MAX) FILESTREAM NOT NULL,
[CreateDate] [DATETIME2](7) NOT NULL,
[CreatedBy] [VARCHAR](50) NOT NULL,
[ChangeDate] [DATETIME2](7) NULL,
[ChangedBy] [VARCHAR](50) NULL
)
I cannot see how they linked from database end. The records count are the same, data content is the same too, other than the formAttachment table has additional columns.
There won't be foreign key constraints across databases. That's not allowed. You might have a trigger to do that work. However, without the trigger, it's likely that they're not relying on anything to ensure integrity between the tables in the two databases. It makes you wonder why they'd set up filestream at all. You only do filestream because you want integrity between your database and the files. What they've done is using filestream, and all the problems it brings, while simultaneously removing the one positive factor, the integrity.
I suspect whoever did this might not have quite known what they were doing.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 21, 2016 at 11:10 am
Thank you, let me write an email to the consultant to find out.
October 22, 2016 at 4:33 am
Briefly looking at the data, however,I'd hazard a guess that the form links to the attachment by AttachmentUID, as both fields appear in both tables.
As said above, it's apparent that whoever was doingthis didn't really know what they were doing (they probably had some impression that 1 database = 1 table).
You could probably run something like this to get some information:
SELECT *
FROM application.dbo.FormAttachment FA
JOIN evaluationAttachment.dbo.Attachment A ON FA.AttachmentUID = A.AttachmentUID
I'd try testing that with a few knowns in your where clause first, as that could be a very big data set, depending on your database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply