Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
 Posted - 2003-03-01 : 02:58:00
|
| Hi all:I am just beginning to design a relatively small Document Management System. Its pretty simple, with explorer-style navigation, tree-view, directories and files, etc... But I cant make up my mind about how to handle the storage of the files/documents in the system. I know its not such a good idea to store the files in SQL Server, as I am currently tempted to do, but if use a network share on a server, won't I have to give all kinds of permissions on the share? How can I ensure that nobody can access the folder oustide the system?I think these problems can be solved if I use a web interface (in ASP or ASP.Net) and give permissions only to the IUSR_WebServer account. But I am seriously considering the possibility of using a Windows application, particularly for the better interface it can provide. In that case, the application will run under the account of the user currently logged-on to that machine, and hence I will need to grant at least read and write permission to all those users for that folder. What are the other options I have, so that I can have my cake and eat it too?!Thanks allOS |
|
|
jfuex
Starting Member
29 Posts |
Posted - 2003-03-01 : 10:56:58
|
| I think I have written the app you describe about 5 gajillion times. I agree that storing the docs in SQL is attractive for both administration and security, however there are a few quirks to watrch out for.(1) If this thing will house a large amount of document data. My experience is that SQL isn't a very efficient way to store it. Consider that on a file system you can do incremental backups. In SQL you need to copy out the DB and backup the whole enchilada even if relatively small amounts of documents change over time.(2) IIS is pretty darn efficient at handing out files from the file system and decidedly less so for processing scripts especially those that access a separate database server. You can expect an order of magnitude dropoff in performance from storing the files in the file system on the web server. If the usage of the site is low this is probably not a big issue, but if you expect thousands of hits per hour, retreiving docs from SQL will require much beefier hardware.(3) Browser behavior (especially IE). It seems like a simple thing to create a link to an asp file which dynamically generates the content and sets a few headers so that it looks to the user like a link to a regular file. While my experience is that Netscape is a much buggier system than IE, this case is exactly the opposite. If you expect to dynamically generate file content or have the user download files named on the web server file system differently than the filename the user will want watch out. You will spend a LOT of time going through the MS Knowledge base and won't get very good answers. There are documented bugs in this area with no workaround that is very good. Also, getting a workaround that works in the various versions of IE alone and doesn't break netscape is a Gordian Knot if I ever saw one.Good luck!ooo Oooo O |
 |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-03-02 : 02:35:50
|
Thanx a lot for the expert comments, jf! 1) Excellent point on the incremental backups, I hadnt given any consideration to the backup & restore issues.2) Since the application will be used on a LAN and around 10 users, performance is not likely to be a problem. We are expecting around 5000 documents a year, which I think is a pretty small number  . Most of these files are images, some Word/Excel documents and small number of videos.3) Yes, I have seen some pretty weird behaviour when it comes to streaming documents, and its something I want to avoid if possible. 4) Oh, and if I store the documents on the file system, the application can take advantage of the MS Indexing Service. We ran some initial tests on it, and the results look very encouraging.Now its really looking like I am going two ways here - I want to develop a Windows application, perhaps in C#, and I really dont want to store the documents in SQL Server. So how do I manage permissions for the network share that will be used as the file-store? That is, how can I make sure that only my application can access the folder and change its contents?I have looked at some options like developing a Windows Service that runs on the server, but I am trying to avoid having to touch the server. Any ideas, folks?Thanks guysOS |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2003-03-02 : 03:56:09
|
| Hi!You could create a specific user for the system, and then impersonate that user everytime a user with the right permissions tries to reach a document.Take a look at the WindowsIdentity.Impersonate method (namespace System.Security.Principal) of the .NET framework.Here is a link to that topic of the SDK:[url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemSecurityPrincipalWindowsIdentityClassImpersonateTopic.asp[/url]Edited by - andraax on 03/02/2003 03:56:45 |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-03-02 : 08:03:58
|
| Thanx a bunch for the tip, Andraax. Impersonation is *the* keyword I was looking for all week!!OS |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-03-03 : 02:22:00
|
Sheesh...what a damper! It seems that to use the Impersonate methods, the current user must already have the elevated privileges to call some unmanaged APIs like LogonUser()  . Now that's a real Catch-22 situation! I am not getting anywhere so far with impersonation, I guess I need to try something else with my own identity .Suddenly, SQL Server is looking like an attractive place to store the data...Any other ideas?OS |
|
|
|
|
|
|
|
|
Design a Document Storage System