June 24, 2009 at 10:09 am
We are trying to migrate Sybase server to MSSQL 2005.
The sybase server is using sybase replication. The scenario is like like:
table A in server A has a transactional replciation to table A in server B. Table A in server B is used for storing historical data. Everynight we have a purge script to purge data for table A in Server A. The purge script is doing the following things basically:
set replication off
go
delete tableA where c1 not in (1,5)
go
set replication on
go
In Sybase, when you run 'set replication off', the subsequent commands are not replcated until you run 'set replcation on', so the delete command does not get replicated into the Server B.
My question: does MSSQL's replcation have similar commands?
I checked MSSQL manual and online groups, but could not get answers. Please help. This is kind of crucial for our migration. Thanks in advance.
June 24, 2009 at 12:59 pm
I believe all you need to do is to stop the Log Reader Agent / Distribution Agent. Be careful with Log Reader Agent, if you stop it and the database is in full recovery mode, the transaction log will not get truncated!!!
Ed
June 24, 2009 at 1:08 pm
Sorry, I miss read the question. Yes you can do it by changing the custom stored procedure (sp_MSdel_dboTableName) or creating your own custom stored procedure.
Ed
June 25, 2009 at 7:02 am
thank you!
I also got some ideas from other posts. I put them together here.
If I add one extra boolean column (call it "cleanFlag" or something) to all replicated tables, I could set this column to default false.
Then, when executing the cleaning tool the tool could set this column to true right before deleting it.
Then, if the deleting replication stored procedure (sp_MSdel_...) checks for this flag, the delete statement could be skipped in the procedure!
For example:
Create table t1(c1 int, cleaning tinyint defult 0,primary key(c1))
Purge script:
Update t1 set cleaning =1 where c1 in (2,5)
Go
Delete t1 where c1 in (2,5)
Go
For sp_MSdel_dbot1:
ALTER procedure [dbo].[sp_MSdel_dbot2]
@pkc1 int
as
begin
delete [dbo].[t1]
where [c1] = @pkc1
and cleaning=0
if @@rowcount = 0
if @@microsoftversion>0x07320000
exec sp_MSreplraiserror 20598
end
go
The only drawback here compared to sybase replication is that those unwanted delete log records still flow through to the subscriber and you need one more update before delete.
June 25, 2009 at 8:56 am
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply