How to get notification about completion of applying snapshot

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Lavanya - 06 Dec 2006 12:07 GMT

All,

In Transactional Pull model, is there a way from any storedprocedure to
get notification from the distribution agent that the initial snapshot
has been applied on the subscriber?

This notification has to be got in my Windows forms C# application and
related UI changes done.

Can anyone help me?

Thanks,
Lavanya

Reply to this Message

Hilary Cotter - 06 Dec 2006 18:43 GMT

Have a final job step executed on completion of the snapshot job. This could
notify the C# application. You could do this through a file existence check,
mail slots, named pipes or some other mechanism.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> All,
>
[quoted text clipped - 9 lines]
> Thanks,
> Lavanya

Reply to this Message

Lavanya - 07 Dec 2006 05:24 GMT

I would require to obtain notification from the distribution agent and
not the snapshot agent.
And since the agent runs on the subscriber for pull model, how do i
track the end of application of the first snapshot?

Thanks,
Lavanya

> Have a final job step executed on completion of the snapshot job. This could
> notify the C# application. You could do this through a file existence check,
[quoted text clipped - 22 lines]
> > Thanks,
> > Lavanya

Reply to this Message

Lavanya - 19 Dec 2006 06:56 GMT

Am still not clear from where i can get the state of the
pull-distribution agent.
Fyi: I have a centralised read only subscriber which has Pull
transactional model configured.

1. Can i use sp_repltrans at publisher database and confirm that the
status of Log reader agent is idle if no rows are returned?
2. Can i use sp_browsereplcmds at distribution database and confirm
that the status of the distribution agent running at the subscriber is
idle if no rows are returned?

Is there any way i can find at the subscriber whether all my
publishers' distribution agents are in idle state?

Please reply asap which would help in increasing the robustness of my
application.

Thanks in advance,
Lavanya

> I would require to obtain notification from the distribution agent and
> not the snapshot agent.
[quoted text clipped - 30 lines]
> > > Thanks,
> > > Lavanya

Reply to this Message

Hilary Cotter - 19 Dec 2006 12:38 GMT

I don't believe you can use sp_repltrans as this only, nor is sp_
browsereplcmds helpful. I think you would be best off setting
historyverboselevel to 2 and then querying mdistribution_history looking for
messages like Delivered snapshot from the
'unc\ServerName_PublicationName\20061219073651\' sub-folder in 1522
milliseconds

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Am still not clear from where i can get the state of the
> pull-distribution agent.
[quoted text clipped - 55 lines]
>> > > Thanks,
>> > > Lavanya

Reply to this Message

Lavanya - 20 Dec 2006 05:53 GMT

Thanks for your response.

Just to confirm my understanding, both sp_repltrans and
sp_browsereplcmds will not help in finding the current state of
logreader and (pull)distribution agents

As you said, i set the historyverboselevel to 2 for the distribution
agent and tried to look into
MSdistribution_history table but could not find "Delivered snapshot..."
string. I found the below instead:
Applied script 'Table1_4.dri'
Applied script 'Table2_4.dri'
Applied script 'Table3_4.dri'
Applied script 'Table4_4.dri'
3 transaction(s) with 35 command(s) were delivered.
No replicated transactions are available.

Hence, can i look out for "No replicated transactions are available."
instead?
What is that 1522 milliseconds?

Also, is there a way to look into my centralized pull subscriber(SQL
2005) whether all the distribution agents for publishers(SQL 2000) are
currently idle or not?

Regards,
Lavanya

> I don't believe you can use sp_repltrans as this only, nor is sp_
> browsereplcmds helpful. I think you would be best off setting
[quoted text clipped - 71 lines]
> >> > > Thanks,
> >> > > Lavanya

Reply to this Message

Hilary Cotter - 21 Dec 2006 03:27 GMT

try looking for > Applied script 'Table4_4.dri' if that is the last thing to
be delivered in your snapshot.

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks for your response.
>
[quoted text clipped - 103 lines]
>> >> > > Thanks,
>> >> > > Lavanya

Reply to this Message

Lavanya - 27 Dec 2006 09:34 GMT

Thanks Hilary for your help.

For the snapshot successful delivery confirmation, i check the status
of 'No replicated Transaction available' comment in
MSdistribution_history table from my C# application. This is more
reliable as am not sure of the number that succeeds the table name nor
am sure of the table which is the last to be delivered. This works
fine.

Now, i have another clarification.

>From my C#.Net application, i need to check whether there are any

pending transactions from publ. to subs. before i allow the user to
remove replication setup from the UI.

As previously asked,
1. Can i use sp_repltrans at publisher database, check if no rows are
returned and confirm that no data is pending to be transferred ?
2. Can i use sp_browsereplcmds at distribution database,check if no
rows are returned and hence confirm that subscriber and publisher are
in total sync ?

Reply asap.

Thanks in advance,
-Lavanya

> try looking for > Applied script 'Table4_4.dri' if that is the last thing to
> be delivered in your snapshot.
[quoted text clipped - 115 lines]
> >> >> > > Thanks,
> >> >> > > Lavanya

Reply to this Message

Hilary Cotter - 27 Dec 2006 16:30 GMT

No, to both. You have to hack msdistribution_status for the info you are
looking for.

Query MSsubscriptions to get the values you need for your subscriber

SELECT t.article_id,s.agent_id,

'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0
END),

'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <= h.maxseq THEN 1 ELSE 0 END)

FROM (SELECT article_id,publisher_database_id, xact_seqno

FROM MSrepl_commands with (NOLOCK) ) as t

JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions
with (NOLOCK) ) AS s

ON (t.article_id = s.article_id AND
t.publisher_database_id=s.publisher_database_id )

JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM
MSdistribution_history with (NOLOCK) GROUP BY agent_id) as h

ON (h.agent_id=s.agent_id)

where s.publisher_database_id=57 --obtained from mssubscriptions

and s.publisher_id=0 --obtained from mssubscriptions

and s.subscriber_id=7 --obtained from mssubscriptions

and s.subscriber_db='test' --obtained from mssubscriptions

GROUP BY t.article_id,s.agent_id

Signature

Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

> Thanks Hilary for your help.
>
[quoted text clipped - 147 lines]
>> >> >> > > Thanks,
>> >> >> > > Lavanya

Reply to this Message