Example using Transactional Replication with Filestreams in SQL 2008


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


 


*/


 

Comments (0)