Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2009
    Posts
    32

    Unanswered: Save a file inside SQL Server or outside?

    Could anyone outline advantages and disavantages in saving files inside SQL Server and outside SQL Server?

    Many thanks in advance

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    outside. I know about the filestream stuff, but I still vote for outside. it is a RDBMS not a file server.

    i was on a claims processing project once. all the claims came in as xml files or something. the architect decided they should be stored in SQL against my wishes. the database was growing at 20GB a week when I left the project and that was when 20GB was nothing to sneeze at.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Only one connection is required/permitted to interact with the database and the files. The required is the advantage. The permitted is the disadvantage.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    On my very first project we stored the files outside the database. The hard disk on the database server was failing (we found out later), so we had several system crashes that needed restores of the database. After each restore we ended up with a file server and a database that were out of sync. It was my job to get those two systems (database and file server) synchronised again. Time after time.
    At the end I chooses to zip the files and store them as BLOBs in the database, it saved me a lot of work. I did some tests to minimise disk space usage: the files were generated Word documents that the user could edit and change. I did some tests with .doc and .RTF files. RTF files could be zipped to the smallest sizes. That was what we stored, zipped RTF files.

    Next time the system crashed, those files were restored together with the database and there were no more synchronisation problems.

    You will have to look at your problem and see what is best for you. When your database would grow by 20 GB per week, that could be a good reason not to store files in the database. When you like to have your data and files synchronised (under transaction control), store them in the database.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •