FILESTREAM Setup and Testing without Visual Studio

  • Hugh Thomas

    Ten Centuries

    Points: 1017

    Comments posted to this topic are about the item FILESTREAM Setup and Testing without Visual Studio

  • Adam Seniuk

    SSCrazy

    Points: 2281

    Great post, I was wondering how to setup FILESTREAM without the need of Visual Studio.

    One item I wasn't sure of was how is the file system security handled, since the file isn't encrypted.


    Over 12yrs in IT and 10yrs happily stuck with SQL.
    - SQL 2008/R2/2012/2014/2016/2017
    - Oracle 8/9/10/11
    - MySQL 4/5 and MariaDB

  • Hugh Thomas

    Ten Centuries

    Points: 1017

    One item I wasn't sure of was how is the file system security handled, since the file isn't encrypted.

    Ravi S. Maniam mentions this in his blog post on Filestream. Looks like the physical files should just be available, from an NTFS security perspecitive, by the account that the SQL service is running under. Because my testing was done in a sandbox where the SQL service account did match my windows login account, it wasn't an issue for me when I opened my test file.

    If you try to open the test file and are denied, this would be the reason. Sounds like a pretty decent security scheme as long as admins don't run around adding permissions to the filestream folder.

  • a.rajmane

    Old Hand

    Points: 377

    Will enabling filestream help if its not a BLOB?

  • Hugh Thomas

    Ten Centuries

    Points: 1017

    a.rajmane (9/3/2009)


    Will enabling filestream help if its not a BLOB?

    I'm not sure exactly what you mean, but I will try to clarify in hopes that it answers your question.

    In a traditional varbinary(MAX) field without FILESTREAM, the data is stored as a blob in the database. With FILESTREAM, the binary data is stored in the file system as an actual file, of any type. It could be a MS Word document, image file, PDF, or most any other type regular file. Therefore, whatever methods are available through ASP.NET, T-SQL, etc. to import or retrieve the data, the core data is an actual file where the database is simply storing a pointer to that file.

    I hope that helps.

  • ChiragNS

    One Orange Chip

    Points: 26137

    While creating the table to store filestream data you have specified the filestream file group with the keyword "FILESTREAM_ON". Is it necessary because i did not find the same in BOL.

    -- Create the test table for FILESTREAM

    CREATE TABLE [dbo].[tFileStreamTest](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [FileStreamTest] [varbinary](max) FILESTREAM NULL,

    [FileGUID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

    UNIQUE DEFAULT NEWID()

    ) ON [PRIMARY]

    FILESTREAM_ON [FileStreamGroup1];

    GO

    "Keep Trying"

  • mchou

    SSC Enthusiast

    Points: 102

    Hi,

    I follow your step-by-step process, however, I got "FILESTREAM feature is disabled" error at "Add Filestream Filegroup to db" step. I am little bit confused about this error msg because I did configure/reconfigure at step 1 as you described in the doc. Any tips? Thanks.

  • herman-991596

    SSC Rookie

    Points: 30

    Very good article. It works.

    I have a database for my wine inventory and i added filestream columns to store information for a particular wine (info from websites and other documents (jpg, pdf).

    I can store the documents with filestream. Great!

    But how can i show that info via sql or via MS Access which is still my frontend for maintaining my database?

    (I maintain de filestream info manually via a stored procedure, because i don't see a possibility via MS Access)

    THANKS!

  • Hugh Thomas

    Ten Centuries

    Points: 1017

    Chirag (9/3/2009)


    While creating the table to store filestream data you have specified the filestream file group with the keyword "FILESTREAM_ON". Is it necessary because i did not find the same in BOL.

    -- Create the test table for FILESTREAM

    CREATE TABLE [dbo].[tFileStreamTest](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [FileStreamTest] [varbinary](max) FILESTREAM NULL,

    [FileGUID] UNIQUEIDENTIFIER NOT NULL ROWGUIDCOL

    UNIQUE DEFAULT NEWID()

    ) ON [PRIMARY]

    FILESTREAM_ON [FileStreamGroup1];

    GO

    This is only required for partitioned tables as described here. Search for the text "If the table contains FILESTREAM data and the table is partitioned" to find the section that explains. Sorry for the confusion.

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice Post. Good one..

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply