November 26, 2007 at 2:32 pm
Hello room,
Question on Transaction Replication Setup.
•I setup Transaction Replication between sql 2005 and 2000.
•Using both manual and t-sql store procedure when setup database replication.
•It works fine until the publisher database implemented a data archive every 2 year.
How can I prevent the subscriber database not be deleted whenever the publisher database archived its data.?
Can anyone help and advise.? Thank you for your kid help.
TJ
November 26, 2007 at 3:22 pm
When you add article you can specify 'none' for @del_cmd:
exec sp_addarticle @publication = N'PubName', @article = N'TableName', @source_owner = N'dbo'
, @source_object = N'TableName', @type = N'logbased', @description = N'', @creation_script = N''
, @pre_creation_cmd = N'drop', @schema_option = 0x00000000080350DF, @identityrangemanagementoption = N'none'
, @destination_table = N'TableName', @destination_owner = N'dbo', @status = 8
, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTableName]'
, @del_cmd = N'none', @upd_cmd = N'SCALL [sp_MSupd_dboTableName]'
Or - visit delete procedure on subscriber and comment out del stmt.
Or - in gui goto article properties, for DELETE delivery format select 'Do not replicate delete stmt' -
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 26, 2007 at 3:37 pm
Chris,
Thank you for your help.
Enclosed is my database replication scripts
use master
go
--- Create an entry on the sysserver tables, mark it as the Distributor
--- and stores property information.
--- heartbeat_interval in minutes
EXEC sp_adddistributor
@distributor = 'DistributorServerName',
@heartbeat_interval = 20, --- 20 minutes
@password = 'Password'
go
--- Create a new distribution database and install the Distribution schema
exec sp_adddistributiondb
@database = 'Tobe_Named_Distribution_Center',
@data_folder = 'D:\BnB_ReplData\sql_data',
@data_file = 'Tobe_Named_Distribution_Center.MDF',
@data_file_size = 100,
@log_folder = 'D:\BnB_ReplData\sql_log',
@log_file = 'Tobe_Named_Distribution_Center.LDF',
@log_file_size = 25,
@min_distretention = 24,
@max_distretention = 72,
@history_retention = 72,
@security_mode = 0,
@login = 'LoginID',
@password = 'Password'
GO
--- Configure a Publisher to use a specified distribution database
exec sp_adddistpublisher
@publisher = 'PublisherServerName',
@distribution_db = 'Tobe_Named_Distribution_Center',
@security_mode = 0,
@login = 'LoginID',
@password = 'Password',
@working_directory = N'\\MahcineName\D$\BnB_ReplData',
@trusted = 'true',
@thirdparty_flag = 0
GO
-- Add a subscriber server
EXEC sp_addsubscriber @subscriber = 'Subcriber Server Name'
,@type = 0
,@login = 'LoginID'
,@password = 'Password'
,@description = 'Subcriber Server Subscription'
,@security_mode = 0
GO
sp_addsubscriber_schedule @subscriber = 'Subcriber Server Name'
,@agent_type = 0
,@frequency_type = 4
,@frequency_interval = 1
,@frequency_relative_interval =1
,@frequency_recurrence_factor = 0
,@frequency_subday = 4
,@frequency_subday_interval = 10
,@active_start_time_of_day = 000700
,@active_end_time_of_day = 235959
,@active_start_date = 20071114
,@active_end_date = 99991231
GO
----- Need to switch the database
USE [SiteData]
GO
--- STEP 1 Configure the server/database for Snapshot replication
EXEC sp_replicationdboption @dbname = 'DabaseSiteData',
@optname = 'publish', @value = 'True'
GO
--- STEP 2 Creating and configuring the Snapshot Agen
--- Set a replication database option for the current database
-- Adding the transactional publication
exec sp_addpublication @publication = N'DatabaseSiteData',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of SiteData database from Publisher GHS0017RPT.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 336,
@allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21, @ftp_login = N'anonymous',
@allow_dts = N'false',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@logreader_job_name = N'GHS0017RPT-SiteData-1'
GO
exec sp_addpublication_snapshot
@publication = N'DatabaseSiteData',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@snapshot_job_name = N'BlaBlaBla'
GO
--- Adds a login to the publication's access list
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'LoginID'
GO
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'distributor_admin'
GO
---- Stop di sini
-- Adding the transactional articles
exec sp_addarticle @publication = N'DabaseSiteData',
@article = N't_EditTags',
@source_owner = N'dbo',
@source_object = N't_EditTags',
@destination_table = N't_EditTags',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000000CEF3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_t_EditTags',
@del_cmd = N'CALL sp_MSdel_t_EditTags',
@upd_cmd = N'MCALL sp_MSupd_t_EditTags',
@filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO
Best regards,
Edwin 😛
November 26, 2007 at 3:49 pm
looks ok - just change @del_cmd = N'none' in your sp_addarticle call
you may want to drop subscription for table, then drop article from publication, and finally add article (sp_addarticle) back in specifying new @del_cmd param...
-- drop table from trans repl
sp_dropsubscription @publication = 'PubName'
, @article = 'articleName'
, @subscriber = 'all'
sp_droparticle @publication = 'PubName
, @article = 'articleName'
Chris
Chris Becker bcsdata.net
November 26, 2007 at 3:51 pm
Chris,
Not sure where located in GUI.
Is located in ----> Table Article Properties ---> Commands tab-->
Replace DELETE command with thhis stored procedure call.
Again, thank you for your kind help.
TJ
November 26, 2007 at 3:56 pm
Ah ha - so your publisher is SQL 2000... yes - just uncheck that checkbox. When you script out your publication it will appear as @del_cmd = 'none'
Chris
Chris Becker bcsdata.net
November 26, 2007 at 4:18 pm
Chris,
I performed some testing on my local box between sql server 2005 (subscriber) and sql server 2000 (publisher and local distributor).
I found out an odd. The transaction replication still deleted the subscriber database after I unchecked the “Replace DELETE command with this stored procedure call” on publisher article.
Any advise!!!!
Best regards,
TJ
November 26, 2007 at 4:33 pm
changes will not be saved if any active subscriptions exist on that publication. you should drop subscription for table, then drop article.
then add article in script:
exec sp_addarticle @publication = N'DabaseSiteData',
@article = N't_EditTags',
@source_owner = N'dbo',
@source_object = N't_EditTags',
@destination_table = N't_EditTags',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000000CEF3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_t_EditTags',
@del_cmd = N'none',
@upd_cmd = N'MCALL sp_MSupd_t_EditTags',
@filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO
then add subscription for this article:
sp_addsubscription @publication = 'DabaseSiteData'
, @article = t_EditTags'
, @subscriber = 'SERVERNAME'
, @destination_db = 'dbName'
, @sync_type = 'automatic'
run snapshot agent.
Chris Becker bcsdata.net
January 14, 2008 at 12:24 pm
Chris,
I bring this topic back the forum bcs it did not work.
The subscriber’s tables continued be deleted when I delete some records on publisher’s tables.
I have tested with both scripting and manual setup on Northwind database.
My publisher article scripts:
--- DailySummaries tables
exec sp_addarticle @publication = N'Kumeyaay_SiteData',
@article = N't_DailySummaries', @source_owner = N'dbo',
@source_object = N't_DailySummaries',
@destination_table = N't_DailySummaries', @type = N'logbased',
@creation_script = null, @description = null, @pre_creation_cmd = N'drop',
@schema_option = 0x00000000000000F3, @status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_t_DailySummaries',
@del_cmd = N'none', --- Preventing a deletion on subcriber tables
@upd_cmd = N'MCALL sp_MSupd_t_DailySummaries', @filter = null,
@sync_object = null, @auto_identity_range = N'false'
GO
Manual setup on publisher article:
----> Table Article Properties ---> Commands tab-->
Unchecked --?Replace DELETE command with this stored procedure call.
Any advise on this.?
Thanks a lots.
TJ
January 25, 2008 at 1:42 am
if you have already subscribe article and article have data dropping procedure and creating it again with commented code will work...remember to drop and create do not "modify".....it will work...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply