SQL Server and File System Capture Storage

Issue

In KTA v7.10, it is possible to store capture binary data in both SQL Server & the file system using the SQL FILESTREAM functionality. This article acts as a guideline that customers can use to enable this setting in their environment post-installation.

Solution

To enable the FILESTREAM functionality in SQL, go to Start -> Programs -> SQL Server Configuration Manager -> SQL Server Instance -> Properties -> FILESTREAM. The settings should look as shown below.

The filestream access level needs to be changed to allow access for SQL queries and Windows Streaming. This can be done using the below T-SQL code.

EXEC sp_configure filestream_access_level, 2 
RECONFIGURE

NOTE: The SQL Server service will need to be restarted for this change to take affect

On the TotalAgility_Documents database, a FILESTREAM filegroup and database file should be created to store the binary data on the file system. The below SQL will create:

A FILESTREAM filegroup called DocumentBinaryData
A database file called BinaryData at C:\temp\FileStream that uses the DocumentBinaryData filegroup

USE [master]
GO
ALTER DATABASE [TotalAgility_Documents] ADD FILEGROUP [DocumentBinaryData] CONTAINS FILESTREAM 
GO
ALTER DATABASE [TotalAgility_Documents] ADD FILE (NAME=N'BinaryData', FILENAME=N'C:\temp\FileStream') TO FILEGROUP [DocumentBinaryData]
GO

After everything has been configured in SQL, the KTA Configuration Utility can be used to change the Capture Storage settings from SQL Server to SQL Server and File System. Save the settings.

WARNING: After saving, this change cannot be reverted so we'd advise backing up all KTA databases prior to making this change.

After making this change, any binary data greater than 512Kb will be stored on the file system. Any binary data less than this threshold will be stored in the database. This value has been configured for optimal performance with small to medium-sized files.

Level of Complexity

Moderate

Applies to

Product	Version	Build	Environment	Hardware
KTA	7.10

Article # 3040934