I create the following example using Transactional Replication with Filestreams for SQL 2008 using a single server as Publisher/Distributor/Subscriber.

The script performs the following:

1) Creates the published and subscribers DBs in directories call c:\test1 and c:\test2 to contain
 database file and log
 on disk filestream storage of data inserted
 for the Publisher and Subscrber files.

2) Creates table and inserts image data as a filestream file to be persisted on the disk in the filestream filegroup.

3) Enables Publication for Transactional Replication and created the Publication.
         Change @subscriber = N'<your server name>' to your publisher/subscriber server name

4) When synchronize, the file located in the filegroup is replicated to the subscriber.  Note that since the transaction log LSN number is used for the Create File command the LSN number on the publisher will be different then the number on the subscriber resulting in different filenames.

 

/*

SQL 2008 Example of Replicating Table containing Filestream data

Chris Skorlinski  (thanks to Bob Dorr for stream example)

Microsoft SQL Server Escalation Services

Feb 2009

 

NOTE: Global Search/Replace <YOUR SERVER NAME> with your SQL Server name.

 

1) Create 2 directory call c:\test1 and c:\test2 to contain

      database file and log

      on disk filestream storage of data inserted

      for the Publisher and Subscrber files.

2) Execute script to

      create publisher and subscribers databases

      insert record into filestream field which is then written to disk

      examine directory containing filestream file

      examine transaction log to confirm file name used by filestream

3) Create a Merge or Transaction Replication publication

      push to subscriber database

      examine directory containing filestream file

      examine transaction log to confirm file name used by filestream

*/

 

--Create Publisher database

USE [master]

GO

CREATE DATABASE [Pub_db] ON  PRIMARY

( NAME = N'Pub_db_dat', FILENAME = N'c:\test1\Pub_db.mdf' , SIZE = 2304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [filestreamgroup1] CONTAINS FILESTREAM  DEFAULT

( NAME = N'Pub_db_filestreamgroup1', FILENAME = N'c:\test1\filestream1' )

 LOG ON

( NAME = N'Pub_log', FILENAME = N'c:\test1\Pub_db.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

 

/*

  For demonstration purpose the database set to FULL RECOVERY

    and a Full and Log backups executed.

      This prevents the transaction log from being

      automatically truncated and removing the

      Filestream transaction.

*/

USE master;

ALTER DATABASE Pub_db SET RECOVERY FULL;

GO

-- Back up the Pub_db database to new media set (backup set 1).

BACKUP DATABASE Pub_db

  TO DISK = 'C:\Test1\Pub_dbFullRM.bak'

  WITH FORMAT;

GO

--Create a routine log backup (backup set 2).

BACKUP LOG Pub_db TO DISK = 'C:\Test1\Pub_dbFullRM.bak';

GO

 

 

--Create table to publish

USE Pub_db

GO

CREATE TABLE [dbo].[student](

      [pkID] [uniqueidentifier] ROWGUIDCOL  NOT NULL UNIQUE,

      [fsTextFile] [varbinary](max) FILESTREAM  NULL,

      [picture] [varbinary](max) NULL)

 

ALTER TABLE [dbo].[student] ADD  CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED

(

      [pkID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] FILESTREAM_ON [filestreamgroup1]

GO

 

/*    Insert row containing

            Unique Identifier (Primary Key)

            Filestream to disk binary value 0x4368726973 = CONVERT(varbinary,'Chris')

            0xaaaaa... as binary to simlulate picture

*/

INSERT into Pub_db..student values(NEWID(),CONVERT(varbinary,'Chris'),0xaaaaaaaaaaaaaa)

GO

--show newly inserted row

SELECT * from Pub_db..student

GO

/*sample output

 

pkID                                 fsTextFile                               picture

------------------------------------ ---------------------------------------- ----------------------------------------

604A00C3-31CA-44C2-83E2-F8A75163E4B1 0x4368726973                             0xAAAAAAAAAAAAAA

 

(1 row(s) affected)

*/

 

/*

Browse the c:\test1 directory to see the file created.

The filename is on based on Transaction Log LSN (log sequence number)

example:

      path to file C:\test1\filestream1\3ea4f478-5bea-4b4a-988d-321c3abea731\7201c6c2-e51e-4186-b736-8e1516b4252f

      file name: 00000013-00000089-0007

     

      WARNING: Never do this with real data!

            1) Copy the file to another directory

            2) Open the file with Notepad.exe

            3) Note it contains the inserted text.

 

Next read the transaction log for the transaction

      which recorded the creation of the Filestream file

     

To read portition of the log use WHERE clause of LSN or Create File Operation

      WHERE [Current LSN]= '00000013:00000089:0007'

      WHERE Operation = 'LOP_FS_DOWNLEVEL_OP'

*/

 

USE Pub_db

GO

SELECT [Current LSN],Operation, [Transaction ID],Description

      FROM fn_dblog(null,null)

      WHERE Operation = 'LOP_FS_DOWNLEVEL_OP'

GO

/*

Sample Output:

[Current LSN] = 00000013:0000007e:0005

[Operation] = LOP_FS_DOWNLEVEL_OP

[Transaction ID] = 0000:000001ef

[Description] = Operation CREATE;File Id 65537;Name 3ea4f478-5bea-4b4a-988d-321c3abea731\7201c6c2-e51e-4186-b736-8e1516b4252f\00000013-00000089-0007

*/

 

--It is a bit cryptic, but you can view the entire insert transaction

SELECT * from fn_dblog(null,null)

WHERE [Transaction ID] Like '0000:000001ef'

 

/*

Sample Output:

 

LOP_BEGIN_XACT

HoBt 72057594038845440:ACQUIRE_LOCK_IX OBJECT: 11:2105058535:0 ;ACQUIRE_LOCK_IX PAGE: 11:1:159;ACQUIRE_LOCK_X KEY: 11:72057594038845440 (6102e18128b4)

 

*/

 

--attempt to directly access the file

SELECT CAST(fsTextFile as varchar(10)), fsTextFile.PathName() from Pub_db..student

GO

/*

Msg 5592, Level 16, State 3, Line 2

FILESTREAM feature doesn't have file system access enabled.

 

Configure Filestream to allow file access to the filestream object

 

      sp_configure 'filestream access level',2

      RECONFIGURE WITH OVERRIDE

 

      How to: Enable FILESTREAM

      http://msdn.microsoft.com/en-us/library/cc645923.aspx

*/

sp_configure 'filestream access level',2

RECONFIGURE WITH OVERRIDE

 

--Try again to directly access the file

SELECT pkID,CAST(fsTextFile as varchar(10)) as '1st 10 characters',

      fsTextFile.PathName() as 'PathName' from Pub_db..student

GO

 

/*

Sample Output:

pkID                                 1st 10 characters PathName

------------------------------------ ----------------- ------------------------------------------------------------------------------------------------

604A00C3-31CA-44C2-83E2-F8A75163E4B1 Chris             \\<YOUR SERVER NAME>\MSSQLSERVER\v1\Pub_db\dbo\student\fsTextFile\604A00C3-31CA-44C2-83E2-F8A75163E4B1

 

(1 row(s) affected)

*/

 

--Create Subscriber database

USE [master]

GO

CREATE DATABASE [Sub_db] ON  PRIMARY

( NAME = N'Sub_db_dat2', FILENAME = N'c:\test2\Sub_db.mdf' , SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

 FILEGROUP [filestreamgroup1] CONTAINS FILESTREAM  DEFAULT

( NAME = N'Sub_db_filestreamgroup1', FILENAME = N'c:\test2\filestream1' )

 LOG ON

( NAME = N'Sub_db_log2', FILENAME = N'c:\test2\Sub_db.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

 

/*

  For demonstration purpose the database set to FULL RECOVERY

    and a Full and Log backups executed.

      This prevents the transaction log from being

      automatically truncated and removing the

      Filestream transaction.

*/

USE master;

ALTER DATABASE Sub_db SET RECOVERY FULL;

GO

-- Back up the Sub_db database to new media set (backup set 1).

BACKUP DATABASE Sub_db

  TO DISK = 'C:\Test2\Sub_dbFullRM.bak'

  WITH FORMAT;

GO

--Create a routine log backup (backup set 2).

BACKUP LOG Sub_db TO DISK = 'C:\Test2\Sub_dbFullRM.bak';

GO

 

/*

Using script below create Transactional Publication

      Subscriber (Push or Pull)

*/

 

-- Enable Publication for Transactional Replication

use [Pub_db]

exec sp_replicationdboption @dbname = N'Pub_db', @optname = N'publish', @value = N'true'

GO

-- Adding the transactional publication

exec sp_addpublication @publication = N'FileStreamTranPublication', @description = N'Transactional publication of database ''Pub_db'' from Publisher ''<YOUR SERVER NAME>''.',

      @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false',

      @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false',

      @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false',

      @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'

GO

exec sp_addpublication_snapshot @publication = N'FileStreamTranPublication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0,

      @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

GO

/*

Default schema_option = 0x000000000803509F for Transactional Replication

      does not replicate Filestream as files but as embedded objects.

      To replicated as files enable schema option 0x100000000

     

      0x00803509F (Tran)

      0x100000000 (BOL FileStreams)

 

Use:

 

      @schema_option = 0x000000010803509F,

 

*/

exec sp_addarticle @publication = N'FileStreamTranPublication', @article = N'student', @source_owner = N'dbo', @source_object = N'student', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop',

      @schema_option = 0x000000010803509F, @identityrangemanagementoption = N'manual', @destination_table = N'student', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dbostudent', @del_cmd = N'CALL sp_MSdel_dbostudent', @upd_cmd = N'SCALL sp_MSupd_dbostudent'

GO

 

--Edit @subscriber name as needed

exec sp_addsubscription @publication = N'FileStreamTranPublication',

@subscriber = N'<YOUR SERVER NAME>', @destination_db = N'Sub_db', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type = 0

GO

exec sp_addpushsubscription_agent @publication = N'FileStreamTranPublication', @subscriber = N'<YOUR SERVER NAME>', @subscriber_db = N'Sub_db', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 1, @frequency_interval = 0,

      @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 0, @active_start_date = 0, @active_end_date = 19950101, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

GO

 

/*

Using Managment Studio or Replication Monitor synchronize subscriber

      Run Snapshot Agent

      Run Distribution Agent

*/

 

--SELECT rows on Subscriber to confirm sync completed

USE Sub_db

GO

SELECT pkID,CAST(fsTextFile as varchar(10)) as '1st 10 characters',

      fsTextFile.PathName() as 'PathName' from Pub_db..student

 

GO

/*

Sample Output:

pkID                                 1st 10 characters PathName

------------------------------------ ----------------- ------------------------------------------------------------------------------------------------

604A00C3-31CA-44C2-83E2-F8A75163E4B1 Chris             \\<YOUR SERVER NAME>\MSSQLSERVER\v1\Pub_db\dbo\student\fsTextFile\604A00C3-31CA-44C2-83E2-F8A75163E4B1

 

(1 row(s) affected)

*/

 

/*

Browse the c:\test2 directory to see the file created.

The filename is on based on Transaction Log LSN (log sequence number)

example:

      path to file C:\test2\filestream1\8debcb27-1938-4f59-ad42-c693ba53bd83\3a8fc016-bfc7-4ea5-944b-090da45fca96

      file name: 00000014-0000007d-0008

           

      WARNING: Never do this with real data!

            Copy the file to another directory

            Open the file with Notepad.exe

            Note it contains the inserted text.

*/

 

--Read transaction log on subscriber

SELECT [Current LSN],Operation, [Transaction ID],Description

      FROM fn_dblog(null,null)

      WHERE Operation = 'LOP_FS_DOWNLEVEL_OP'

GO

 

/*

Sample Output:

[Current LSN] = 00000014:0000007d:0008

[Operation] = LOP_FS_DOWNLEVEL_OP

[Transaction ID] = 0000:0000024f

[Description] = Operation CREATE;File Id 65537;Name 8debcb27-1938-4f59-ad42-c693ba53bd83\3a8fc016-bfc7-4ea5-944b-090da45fca96\00000014-0000007d-0008

 

*/

 

/*

--Execute the cleanup script below to remove

--    Pub_db and Sub_db

 

 USE Pub_db

-- Dropping the transactional subscriptions

exec sp_dropsubscription @publication = N'FileStreamTranPublication',

@subscriber = N'<YOUR SERVER NAME>', @destination_db = N'Sub_db', @article = N'all'

GO

-- Dropping the transactional publication

exec sp_droppublication @publication = N'FileStreamTranPublication'

GO

 

USE master

GO

sp_configure 'filestream access level',1

RECONFIGURE WITH OVERRIDE

GO

 

exec sp_replicationdboption @dbname = N'Pub_db', @optname = N'publish', @value = N'false'

GO

Drop database Pub_db

GO

DROP DATABASE Sub_db

GO

 

*/