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
*/
--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%)
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;
-- 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;
--Create a routine log backup (backup set 2).
BACKUP LOG Pub_db TO DISK = 'C:\Test1\Pub_dbFullRM.bak';
--Create table to publish
USE Pub_db
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]
/* 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)
--show newly inserted row
SELECT * from Pub_db..student
/*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'
SELECT [Current LSN],Operation, [Transaction ID],Description
FROM fn_dblog(null,null)
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'
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
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
--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
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
--Create Subscriber database
CREATE DATABASE [Sub_db] ON PRIMARY
( NAME = N'Sub_db_dat2', FILENAME = N'c:\test2\Sub_db.mdf' , SIZE = 1280KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'Sub_db_filestreamgroup1', FILENAME = N'c:\test2\filestream1' )
( NAME = N'Sub_db_log2', FILENAME = N'c:\test2\Sub_db.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
ALTER DATABASE Sub_db SET RECOVERY FULL;
-- Back up the Sub_db database to new media set (backup set 1).
BACKUP DATABASE Sub_db
TO DISK = 'C:\Test2\Sub_dbFullRM.bak'
BACKUP LOG Sub_db TO DISK = 'C:\Test2\Sub_dbFullRM.bak';
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'
-- 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'
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
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'
--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
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'
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
Browse the c:\test2 directory to see the file created.
path to file C:\test2\filestream1\8debcb27-1938-4f59-ad42-c693ba53bd83\3a8fc016-bfc7-4ea5-944b-090da45fca96
file name: 00000014-0000007d-0008
Copy the file to another directory
Open the file with Notepad.exe
Note it contains the inserted text.
--Read transaction log on subscriber
[Current LSN] = 00000014:0000007d:0008
[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
-- Dropping the transactional subscriptions
exec sp_dropsubscription @publication = N'FileStreamTranPublication',
@subscriber = N'<YOUR SERVER NAME>', @destination_db = N'Sub_db', @article = N'all'
-- Dropping the transactional publication
exec sp_droppublication @publication = N'FileStreamTranPublication'
USE master
sp_configure 'filestream access level',1
exec sp_replicationdboption @dbname = N'Pub_db', @optname = N'publish', @value = N'false'
Drop database Pub_db
DROP DATABASE Sub_db