[SQL] note – FILESTREAM course practice

• 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

• 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

• 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.

• Create FileStream-Enabled table

--Create FileStream-Enabled table
CREATE TABLE Archive.dbo.Records
(
    [Id] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
    [SerialNumber] INTEGER UNIQUE,
    [Chart] VARBINARY(MAX) FILESTREAM NULL
)
GO



• INSERT NULL FIRESTREAM COLUMN VALUE

--INSERT NULL FIRESTREAM COLUMN VALUE
INSERT INTO Archive.dbo.Records
    VALUES (newid (), 1, NULL);
GO



• Inserting a Zero-Length Record

--Inserting a Zero-Length Record
INSERT INTO Archive.dbo.Records
    VALUES (newid (), 2, 
    CAST ('' AS varbinary(MAX)));
GO



• Creating a Data File

INSERT INTO Archive.dbo.Records
    VALUES (newid (), 3, 
      CAST ('Seismic Data' as varbinary(max)));
GO



• Updating FILESTREAM Data / DELETING FILESTREAM DATA

--Updating FILESTREAM Data
UPDATE Archive.dbo.Records
SET [Chart] = CAST('Xray 1' as varbinary(max))
WHERE [SerialNumber] = 2;

--dELETING filestream DATA
DELETE Archive.dbo.Records
WHERE SerialNumber = 1;
GO

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