Setting up and using file tables

Certain scenarios may require your SQL Server to interact with files on its file system. For example, some external monitoring software may write to local files. You may want your SQL Server to interrogate the contents of these files. Alternatively you may want SQL Server to act on changes made to files ie to act as a type of file watcher. In the past you may have used xp_cmdshell to implement such functionality. But now with file table functionality in SQL Server 2012 (included as a standard edition feature) implementations become simpler and much more integrated with the server as a whole. The following describes my setup for file tables and some test results:

My database setup was as follows:

CREATE DATABASE [FileTableDB]
CONTAINMENT = NONE
ON  PRIMARY
( NAME = N'FileTableDB_Data', FILENAME = N'C:\Databases\FileTableDB\FileTableDB.mdf' , SIZE = 4288KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
FILEGROUP [FilestreamFG] CONTAINS FILESTREAM  DEFAULT
( NAME = N'FilestreamData', FILENAME = N'C:\Databases\FileTableDB\FileTableDBFS' , MAXSIZE = UNLIMITED)
LOG ON
( NAME = N'FileTableDB_Log', FILENAME = N'C:\Databases\FileTableDB\FileTableDB.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [FileTableDB] SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'FileTable' )
GO

My file table was created as follows:

USE [FileTableDB]
GO

CREATE TABLE [dbo].[ErrorLogs] AS FILETABLE ON [PRIMARY] FILESTREAM_ON [FilestreamFG]
WITH
(
FILETABLE_DIRECTORY = N'ErrorLogFolder', FILETABLE_COLLATE_FILENAME = Latin1_General_CI_AS
)
GO

So the file system folder that corresponded to the ErrorLogs file table was \\MyServerName\mssqlserver\FileTable\ErrorLogFolder

For my testing I wanted to implement a type of file system watcher, a solution which would capture the activity happening in \\MyServerName\mssqlserver\FileTable\ErrorLogFolder. I wanted the captured information to be saved in a conventional database table. This is the definition for that table:

USE [FileTableDB]
GO

CREATE TABLE [dbo].[ErrorsReceived](
[id] [int] IDENTITY(1,1) NOT NULL,
[triggeredby] [varchar](10) NOT NULL,
[filename] [nvarchar](255) NOT NULL,
[errortext] [nvarchar](max) NOT NULL,
PRIMARY KEY CLUSTERED
([id] ASC)
)
GO

This table would be populated via the firing of triggers against the file table. These are the trigger definitions:

CREATE TRIGGER [dbo].[triErrorLogs]
ON [dbo].[ErrorLogs]
FOR INSERT
AS
BEGIN
INSERT INTO dbo.ErrorsReceived ([triggeredby],[filename],[errortext])
SELECT 'INSERT',name,
CASE WHEN SUBSTRING(file_stream,1,2) = 0xFFFE
THEN CONVERT(NVARCHAR(max),file_stream)
ELSE CONVERT(VARCHAR(max),file_stream)
END AS errortext
FROM inserted
END
GO

CREATE TRIGGER [dbo].[truErrorLogs]
ON [dbo].[ErrorLogs]
FOR UPDATE
AS
BEGIN
INSERT INTO dbo.ErrorsReceived ([triggeredby],[filename],[errortext])
SELECT 'UPDATE',name,
CASE WHEN SUBSTRING(file_stream,1,2) = 0xFFFE
THEN CONVERT(NVARCHAR(max),file_stream)
ELSE CONVERT(VARCHAR(max),file_stream)
END AS errortext
FROM inserted
END
GO

CREATE TRIGGER [dbo].[trdErrorLogs]
ON [dbo].[ErrorLogs]
FOR DELETE
AS
BEGIN
INSERT INTO dbo.ErrorsReceived ([triggeredby],[filename],[errortext])
SELECT 'DELETE',name,
CASE WHEN SUBSTRING(file_stream,1,2) = 0xFFFE
THEN CONVERT(NVARCHAR(max),file_stream)
ELSE CONVERT(VARCHAR(max),file_stream)
END AS errortext
FROM deleted
END
GO

With the setup completed I create a file named server1.txt containing the line ‘2014-04-02 16:10:server1:error text from this server’ and copy this file into the file table folder. This should result in an insert action against the file table. Here are the actual results:

The first result set is from the file table. You can see the file name, its type and size and the file_stream column that contains the actual data. The data for the second result set comes from the firing of the triggers. You can see that the file copy operation is captured as an insert followed by an update.

I’ll now edit server1.txt and save as a unicode encoded text file. Here is the result:

 

See the doubling of the cached_file_size and the file_stream column with its 0xFFFE header indicating UNICODE as well as the interleaved 00 in the double byte character representation. The trigger code uses the 0xFFFE to determine whether the file_stream data is unicode or not and does the appropriate conversion before saving to the unicode column in the ErrorsReceived table.

Finally I’ll delete file:

You see the file table is now empty and the delete trigger fired to save its results.

In summary, these simple tests shows that you can implement a file watcher using the file table functionality. Triggers can be created against the file table and the file content residing in the file_stream column can be parsed for further processing.