SQL Server – Image and Text Data – Using FileStream – Day 2

Background

In an earlier post we laid a bit of foundation on Image & Text Data.  We used what I will call the classic method of storing the images inside the database itself.

In this post, we will apply a technology known as FileStream. the OS’s File System using a technology known as FileStream.

 

What is FileStream?

Link
FILESTREAM integrates the SQL Server Database Engine with an NTFS file system by storing varbinary(max) binary large object (BLOB) data as files on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.

FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.

Outline

The steps are:

  1. At the OS Level, Enable the SQL Server Instance for FileStream capability
  2. At the SQL Instance level, enable the SQL Instance for File Stream
  3. At the Database level, add a Filestream Filegroup
  4. On the table level, add an Image Column and gate it for the File Stream

 

Steps

At the OS Level, Enable SQL Server Instance for FileStream

Using the instructions listed here Enable and Configure FILESTREAM ( Link ), configure enable Instance for File Stream

Actual Steps

  1. Launch the “SQL Server Configuration Manager“Application
  2. In the SQL Server Configuration Manager snap-in
    • In the left panel, choose “SQL Server Services”
    • On the right panel, locate the SQL Server Instance
    • Right click on the SQL Server Instance
    • From the drop down menu, choose Properties.
    • In the Properties window
      • Choose the FileStream tab
      • Make sure that the “Enable FileStream for Transact-SQL Access” is enabled

 

SQL Server Configuration Manager

sqlserverconfigurationmanager-services-brushed-up

FileStream – Original Screen

sqlserverconfigurationmanager-filestream-20161112-0206am

FileStream – Post Changes

sqlserverconfigurationmanager-filestream-20161112-0212am

 

At the SQL Instance Level, Enable SQL Server Instance for FileStream

Using the instructions listed here “Filestream access level Server Configuration Option” ( Link ), configure enable Instance for File Stream

Review Configuration

Code


EXEC sp_configure filestream_access_level

Output

filestream_access_level_20161112_0211am

 

Explanation

  1. Our current and run value is 0
    • 0 means we are not configured for “filestream access level

 

Adjust Configuration

Code


EXEC sp_configure filestream_access_level, 1

Explanation

  1. value 1
    • Means “Enables FILESTREAM for Transact-SQL
  2. value 2
    • Means “Enables FILESTREAM for Transact-SQL and Win32 streaming access

At the Database level, add Filestream Filegroup and OS folder

FileStream – Add FileGroup

Code

Syntax


USE [master]
GO

alter database [database]
	add filegroup [fileGroup]
		contains filestream
	;
	
go


Sample


declare @filegroup sysname

set @filegroup = 'fileGroupFileStream'


if not exists
(
	select *
	from   sys.filegroups tblFG
	where  tblFG.[name] = @filegroup
)
begin

	print 'Create FileGroup ' + @filegroup + ' ....'

		alter database [rbpivr1_20161111]
			add filegroup [fileGroupFileStream]
			contains filestream
			;
	
	print 'Created FileGroup ' + @filegroup + ''

end	
go


FileStream – Add FileGroup Folder

Code

Syntax


USE [master]
GO

exec xp_create_subdir N'[folder]'
go


ALTER DATABASE [db-name]  
ADD FILE 
	( 
		  NAME = N'[name]'
		, FILENAME = N'[folder-name-deeper]' 
	) 
	TO FILEGROUP [fileGroupFileStream]

GO


Sample


USE [master]
GO

exec xp_create_subdir N'Z:\Microsoft\SQLServer\FileStream\rbpivr1\'
go


ALTER DATABASE [rbpivr1_20161111]  
ADD FILE 
	( 
		  NAME = N'rbpivr1_filestream_01'
		, FILENAME = N'Z:\Microsoft\SQLServer\FileStream\rbpivr1\rbpivr1_filestream_01' 
	) 
	TO FILEGROUP [fileGroupFileStream]

GO


 

 

On Table, Add Image Column tagging as FileStream

Code



SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

begin tran

	if schema_id('fgFS') is null
	begin

		exec('create schema [fgFS] authorization [dbo]')

	end
	;


	/*
		DROP TABLE [fgFS].[ten95C]
	*/
	if object_id('[fgFS].[ten95C]') is null
	begin

		CREATE TABLE [fgFS].[ten95C]
		(
			[ten95C_id] int NOT NULL IDENTITY(1,1),
			[ten95C_ssn] [char](9) NOT NULL,
			[ten95C_emp_id] [char](9) NOT NULL,
			[ten95C_emp_loc] [char](2) NOT NULL,
			[ten95C_tax_year] [smallint] NOT NULL,
			[ten95C_showable] [char](1) NULL,
			[ten95C_view_cnt] [int] NULL,
			[ten95C_view_last] [datetime2](6) NULL,
			[ten95C_notes] [varchar](50) NULL,
			[ten95C_create_ts] [datetime2](6) NOT NULL

			--[ten95C_pdf] [varbinary](max) NOT NULL

			, CONSTRAINT [pk_ten95C] PRIMARY KEY CLUSTERED 
			(
				[ten95C_ssn] ASC,
				[ten95C_emp_id] ASC,
				[ten95C_emp_loc] ASC,
				[ten95C_tax_year] ASC,
				[ten95C_create_ts] ASC
			)
			WITH 
			(
				  PAD_INDEX = OFF
				, STATISTICS_NORECOMPUTE = OFF
				, IGNORE_DUP_KEY = OFF
				, ALLOW_ROW_LOCKS = ON
				, ALLOW_PAGE_LOCKS = ON
			) 
			ON [PRIMARY]
	
			, CONSTRAINT [ten95C_tax] UNIQUE NONCLUSTERED 
			(
				[ten95C_emp_id] ASC,
				[ten95C_emp_loc] ASC,
				[ten95C_tax_year] ASC,
				[ten95C_create_ts] ASC
			)
			WITH 
			(
				  PAD_INDEX = OFF
				, STATISTICS_NORECOMPUTE = OFF
				, IGNORE_DUP_KEY = OFF
				, ALLOW_ROW_LOCKS = ON
				, ALLOW_PAGE_LOCKS = ON
			) 
			--ON [PRIMARY]
			ON [FileGroupIndexes]

			, CONSTRAINT [ten95C_id] UNIQUE NONCLUSTERED 
			(
				[ten95C_id] ASC
			)
			WITH 
			(
				  PAD_INDEX = OFF
				, STATISTICS_NORECOMPUTE = OFF
				, IGNORE_DUP_KEY = OFF
				, ALLOW_ROW_LOCKS = ON
				, ALLOW_PAGE_LOCKS = ON
			) 
			--ON [PRIMARY]
			ON [FileGroupIndexes]


		) 
		ON [PRIMARY] 


		ALTER TABLE [fgFS].[ten95C] 
			ADD  DEFAULT ('') FOR [ten95C_ssn]

		ALTER TABLE [fgFS].[ten95C] 
			ADD  DEFAULT ((0)) FOR [ten95C_view_cnt]

	end
	

	/*
		Add new column - uid
			Default value is newsequentialid()
	*/
	if not exists
		(

			select *
			from   sys.columns tblSC
			where  tblSC.[object_id] = object_id('[fgFS].[ten95C]')
			and    tblSC.[name] = 'uid' 
		)
	begin

		alter table [fgFS].[ten95C] 
			add [uid] [uniqueIdentifier] 
				CONSTRAINT [constraintDefault.fgTS.ten95C.UID] 
				DEFAULT newsequentialid() 
				NOT NULL	

	end

	/*
		Create Unique Constraint on columns - uid
	*/
	if not exists
		(

			select *
			from   sys.indexes tblSI
			where  tblSI.[object_id] = object_id('[fgFS].[ten95C]')
			and    tblSI.[name] = 'constraintUnique.fgTS.ten95C.UID' 
		)
	begin

		alter table [fgFS].[ten95C] 
			add CONSTRAINT [constraintUnique.fgTS.ten95C.UID] 
				unique
				(
					[uid]
				)

	end


	/*
		Designate [uid] as ROWGUIDCOL
	*/
	if not exists
	(

		SELECT *
		FROM   sys.columns tblSC
		WHERE  tblSC.[object_id] = OBJECT_ID('[fgFS].[ten95C]')
		AND    tblSC.[name] = 'uid'
		AND    tblSC.is_rowguidcol = 1
	)
	begin

		ALTER TABLE [fgFS].[ten95C] 
			ALTER COLUMN [uid] Add ROWGUIDCOL 

	end


	/*
		Add new column ten95C_pdf varbinary(max) - FileStream
	*/
	if not exists
		(

			select *
			from   sys.columns tblSC
			where  tblSC.[object_id] = object_id('[fgFS].[ten95C]')
			and    tblSC.[name] = 'ten95C_pdf' 
		)
	begin

		alter table [fgFS].[ten95C] 
			add [ten95C_pdf] [varbinary](max) FILESTREAM NOT NULL

	end

go

while (@@trancount > 0)
begin
	commit tran
end

go


 

Explanation

  1. FileStream Columns have huge requirements
    • They have to be tagged as FILESTREAM during creation
      • [column-name] varbinary(max) FILESTREAM
    • ROWGUIDCOL
      • One of the columns has to be declared as ROWGUIDCOL
      • That column has its own requirements
        • Needs to be a uniqueidentifier
          • I guess the name ROWGUIDCOL suggests as much
        • The column has be declaratively tagged as unique
          • Use UNIQUE Constraint or Create Unique Index
  2. One can not use “TEXTIMAGE ON” option to designate the FILE GROUP
    • The system simply assigns storage to the containing database declared filestream filegroups

 

Benchmark

  1. Database – File Group
    • Trial #1
      • Time :- Nov 12 2016 7:56 AM thru Nov 12 2016 8:04 AM
      • Number of records :- 50,000
      • Duration :- 25 .53 Minutes
    • Trial #2
      • Time :- Nov 12 2016 7:56 AM thru Nov 12 2016 8:04 AM
      • Number of Records :- 50,000
      • Duration :-  19.37 Minutes
    • Trial #3
      • Time :- Nov 14 2016  3:55 AM to Nov 14 2016  5:46 AM
      • Number of Records :- 81,198
      • Duration :- 111 Minutes 35 Seconds [ 111.35 ] { 6681 seconds }
  2.  FileStream
    • Trial #1
      • Time :- Nov 12 2016  8:33 AM thru Nov 12 2016  8:59 AM
      • Number of records :- 10,000
      • Duration :-  8 Minutes 13 seconds
    • Trial #2
      • Time :- Time : Nov 12 2016 12:11 PM thru Nov 12 2016 12:31 PM
      • Number of Records :- 50,000
      • Duration :- 19.58 minutes

 

SQL Server Profiling

File Group – Text And Image

2016-11-12 2:35 AM

activitymonitor-datafileio-filegroupimageandtext-20161112-0234bam

 

Explanation

When writing data to database text and image file group, we noticed the following

  1. Data being read from source database ( rbpivr.mdf )
  2. Data being written to Text and Image File group ( rbpivr1_TextImage_01.ndf )
  3. Data being written to the Log file of our destination db ( rbpivr1_log.ldf )

 

FileGroup – FileStream

2016-11-12 12:14 PM

activitymonitor-filestream-datafilelio-20161112-1214pm

 

Explanation

When writing data to the FileStream FileGroup, we noticed the following

  1. Data being read from source database ( rbpivr.mdf )
  2. Data being written to the Log file of our destination db ( rbpivr1_log.ldf )
  3. Data not being written to Text and Image File group ( rbpivr1_TextImage_01.ndf )

Metadata

Table Sizes

# of Rows is 50K

File Group – Text & Image

tablesize-fgti-20161112-1218pm

File Group – File Stream

tablesize-fgfs-20161112-1241pm

 

Explanation

  1. Data stored in the File System are not accounted for in SSMS

 

Gotchas

Transact SQL

Syntax

Syntax – File Group

 

Sample Error # Error Detail Explanation
 ALTER DATABASE [dbLAB]
ADD FILE
(
NAME = N’db_fs_01′
, FILENAME = N’Z:\dbLAB_fs_01′
, SIZE = 4096KB
, FILEGROWTH = 200MB
)
TO FILEGROUP [fsFS]
 Msg 5509, Level 15, State 2, Line 7 The properties SIZE or FILEGROWTH cannot be specified for the FILESTREAM data file ‘rbpivr1_filestream_01’. We are unable to set size nor growth pattern for FileStream data files

 

 

Syntax – Table

 

Sample Error # Error Detail Explanation
ALTER TABLE [fgFS].[ten95C]
ALTER COLUMN [ten95C_id] Add ROWGUIDCOL
Msg 2761, Level 16, State 1, Line 137 The ROWGUIDCOL property can only be specified on the uniqueidentifier data type. In our sample, we were trying to use a column named ten95C_id as our ROWGUIDCOL.

The error message clearly states that the designation can be applied to columns defined as uniqueidentifier

alter table [fgFS].[ten95C]
add [uid] [uniqueIdentifier]
CONSTRAINT [constraintDefault.fgTS.ten95C.UID]
DEFAULT newid
Msg 128, Level 15, State 1, Line 164  The name “newid” is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.  We supplied newid, but should have supplied the actual function clause.  And, that is newid() or newsequentialid()
ALTER TABLE [fgFS].[ten95C]
ALTER COLUMN [uid] Add ROWGUIDCOL
 Msg 4925, Level 16, State 0, Line 195 ALTER TABLE ALTER COLUMN ADD ROWGUIDCOL failed because a column already exists in table ‘ten95C’ with ROWGUIDCOL property. Qualify whether a ROWGUIDCOL already exists on the table.
Sample …SELECT *
FROM sys.columns tblSC
WHERE tblSC.object_id = OBJECT_ID(‘[fgFS].[ten95C]’)
AND tblSC.is_rowguidcol = 1

 

 

Connect Items

  1. FileStream filegroup is not listed in sp_helpdb
    • ID :- 312175
    • Link :- Link
    • Opened By:- Erland Sommarskog
    • Date Opened :- 2007-Nov-11
    • Status :- Closed
    • Date Closed :- 2008-Apr-11
    • Reason for Status Change :-
      • Erland, Thanks for the feedback. Your feedback is definitely appreciated.
        In as much as we would like to fix this issue, this is causing some backwards compatibility issues. Therefore we won’t be fixing this in the coming release of SQL Server. We will definitely fix this later.In as much as we would like to fix this issue, this is causing some backwards compatibility issues. Therefore we won’t be fixing this in the coming release of SQL Server. We will definitely fix this later.
  2.  FileStream FileGroups should be better recognized and represented
    • ID :- 3111325
    • Link :- Link
    • Opened By:- Daniel Adeniji
    • Date Opened :- 2016-Nov-12
    • Status :- Opened

 

Limitations

MSFT has quite a bit to say about the limitations one should consider prior to introducing FileStream into a Database Architecture.

The complete list is here.

  1. Database Snapshots
    • SQL Server does not support  database snapshots for FILESTREAM filegroups.
      • If a FILESTREAM filegroup is included in a CREATE DATABASE ON clause, the statement will fail and an error will be raised.
      • When you are using FILESTREAM, you can create database snapshots of standard (non-FILESTREAM) filegroups. The FILESTREAM filegroups are marked as offline for those database snapshots.
      • A SELECT statement that is executed on a FILESTREAM table in a database snapshot must not include a FILESTREAM column; otherwise, the following error message will be returned:
        • Could not continue scan with NOLOCK due to data movement.
  2. Database Mirroring
    • Database mirroring does not support FILESTREAM. A FILESTREAM filegroup cannot be created on the principal server. Database mirroring cannot be configured for a database that contains FILESTREAM filegroups.
  3. Contained Databases
    • The FILESTREAM feature requires some configuration outside of the database. Therefore a database that uses FILESTREAM or FileTable is not fully contained.
      You can set database containment to PARTIAL if you want to use certain features of contained databases, such as contained users. In this case, however, you must be aware that some of the database settings are not contained in the database and are not automatically moved when the database moves.

Summary

There is a lot that goes into making engineering decisions.

Microsoft has done a good job stating the limitations of the the FileStream implementation; especially as it relates to Database Mirroring, Database Snapshots, etc.

We did not notice appreciable performance gain, but our test case was very narrow.

At a later time, we will list the optimization tracks listed by the Vendor and by the community.

 

References

FileStream

  1. Using FILESTREAM with Other SQL Server Features
    Link
  2. Development > Designing and Implementing FILESTREAM Storage > Designing and Implementing FILESTREAM > How-to Topics > How To :- Enable FileStream
    Link
  3. Database Features > Binary Large Object (Blob) Data >  FILESTREAM > Enable and Configure FileStream
    Link
  4. Database Engine Instances (SQL Server) > Configure Database Engine Instances  > Server Configuration Options (SQL Server) > filestream access level Server Configuration Option
    Link
  5. Database Features > Binary Large Object (Blob) Data > FILESTREAM > Create a FILESTREAM-Enabled Database
    Link

 

Tables – Identifying Columns

  1. Designing and Implementing Structured Storage > Tables > Creating and Modifying Tables > Designing and Implementing Structured Storage Tables  Creating and Modifying Tables
    Link

 

Q/A

  1. SQL Server – Enabling FileStream after a DB has been created
    Link
  2. ROWGUIDCOL
    • Alter uniqueidentifier primary key to be ROWGUIDCOL in SQL Compact 4
      Link
    • A table with FILESTREAM column(s) must have a non-NULL unique ROWGUID column
      Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s