Replication Issue: Multiple Publishers, One Subscriber, Same Schema..Please Help!

Question

Replication Issue: Multiple Publishers, One Subscriber, Same Schema..Please Help!

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sturner SSC-Insane Points: 22471 More actions · Answer 1

First of all, if I wanted to collect data from multiple sources into one table as it occurs I would consider a service broker solution. This methodology is a lot more scalable than the way you are doing it.... requiring all those individual publishers & subscribers and then more triggers to effect an insert into a result table.

I would consider writing the transactions to a service broker queue on each server, then have a single multi-threaded process on the subscribing server , one thread for each source ("publisher") server. Have a trigger on each source table that inserts a wake-up message into the broker queue that will cause the thread to snatch the new rows inserted and insert them over on the destination table, along with the server name (or serverID) from where they came.

This solution would be extremely tolerant of servers going up and down or the network connectivity interruptions because all of the source servers can continue to run, queuing up new rows even if the destination server is down, or the connectivity between them is down. When the connectivity comes back they will immediately reconnect and synch the new rows.

As far as why that column contains 0 sometimes instead of the correct value I can't begin to conjecture without looking at your code. That's my $.02

The probability of survival is inversely proportional to the angle of arrival.

Deerap SSC Enthusiast Points: 132 More actions · Answer 2

Thanks sturner, I would want to replace the mechanism with service broker or better off was thinking of an SSIS mechanism where in I can transform the data and add that extra bit of info i.e. the DBId while pushing it in the central DB.

But the thing is on live server now and I will need this quick fix. So related to the trigger, here is the code of the after insert trigger.

"

USE [CustomerSure]

GO

/****** Object: Trigger [dbo].[trgAfterInsert] Script Date: 06/25/2013 16:32:54 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER TRIGGER [dbo].[trgAfterInsert] ON [dbo].[IncomingCalls]

FOR INSERT

AS

declare @CallerID varchar(15);

declare @Callstart datetime;

-- The Column with the issue.....

declare @user-id int;

declare @CallEnd Datetime;

declare @DataBaseID int;

select @CallerID = i.CallerID from inserted i;

select @Callstart= i.Callstart from inserted i;

-- I pick up the value from replicated row.....

select @user-id= i.UserID from inserted i;

select @CallEnd= i.CallEnd from inserted i;

select @DataBaseID = 42 from inserted i;

insert into Calls

(CallerId,CallStart,UserID,CallEnd,DatabaseID)

values(@CallerID,@CallStart,@UserID,@CallEnd,@DatabaseID);

PRINT 'AFTER INSERT trigger fired.'

"

Also one thing to mention here is the ColumnType in the Calls table where am inserting is nvarchar(50), whereas in the table which is being replicated, it is int, but I suppose this is not the issue for it works sometimes around, and I also tried Convert function as well to first convert the UserID into an nvarchar and then insert it, still it behaves the same, anything for a quick fix here???

sturner SSC-Insane Points: 22471 More actions · Answer 3

Okay, for one thing your trigger will fail if more than one row was inserted. Think set-based. You don't need all those local variables.

All your trigger needs to do is this:

insert into Calls

(CallerId,CallStart,UserID,CallEnd,DatabaseID)

select i.CallerID, i.Callstart, i.UserID, i.CallEnd, 42

from inserted i

oh and you don't need a semi-colon after every statement. As a C++/C# programmer at heart I have a tendency to do that too but the semi-colon is only required in certain situations.

The probability of survival is inversely proportional to the angle of arrival.