[SQL] note – FILESTREAM course practice

2013-09-26

  • Keep your version of SQL Server and Operation System on the same bits (32-bits or 64-bits)
  • open SQL Server Configuration Manager
  • Right click your instance of SQL server –> Propertites
SNAGHTML488801f






  • checked Enable FILESTREAM for Transact-SQL access and Enable FILESTREAM for file I/O streaming access
  • Customized your Windows Share Name (EX: SQL2012FILESTREAM)
  • If your FILESTREAM data stored in your shared, and the clients want to access them, please check Allow remote clients to have streaming access to FILESTREAM data
SNAGHTML54a41db
  • Open New Query, modify/enable your FILESTREAM Level, then Excute
    EXEC sp_configure filestream_access_level, 2

    RECONFIGURE


  • Configuration option 'filestream access level' changed from 0 to 2. Run the RECONFIGURE statement to install.





        • If you forget to do this, when you create a FILESTREAM enabled database, it will show the error
            1: (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233
          Msg 5591, Level 16, State 1, Line 1
          FILESTREAM feature is disabled.
        • Restart your SQL Service




        • Create a sample Database


        CREATE DATABASE Archive 

        ON

        PRIMARY ( NAME = Arch1,

            FILENAME = 'c:\data\archdat1.mdf'),

        FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,

            FILENAME = 'c:\data\filestream1')

        LOG ON  ( NAME = Archlog1,

            FILENAME = 'c:\data\archlog1.ldf')

        GO

        Then a filestream.hdr file and an $FSLOG folder appears under

        The filestream.hdr file is an important system file. It contains FILESTREAM header information. Do not remove or modify this file.

        SNAGHTML54aac7f



          NoteNote
          The underlying files are removed by the FILESTREAM garbage collector.

          Newer Older