how to monitoring SQL merge replication

HI all,

reading this now: https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx

https://msdn.microsoft.com/en-us/library/ms186795(v=sql.100).aspx

I don't quite understand this, we are using merge replication and how to execute sp_showpendingchanges in a good way, any example of that?  any existing good script you are using ?

we have publisher and subscriber on the same server.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
You seem to be asking open ended questions without providing context.
Scripting is a broad term.
Monitoring is as well.
You can use SQL agent job to run the two SPs. Since you want monitoring, the SQL agent job needs to notify either after the result of running the sp for the publisher, the subscriber, etc. is hitting a specific threshold.

You can use it to establish a baseline of current replication data transfer.

The question is how do you want to ve notified about it?
If not using SQL agent job => email notification
There are vbscript/powershell examples on how to connect to SQL server. You then would run the query the same way ....

You have to define what is the logic behind your question.
What is it you are looking the scripted process to do?

I.e. You run it every 15 minutes (half the time your current replication job runs)
marrowyungSenior Technical architecture (Data)Author Commented:
"You can use SQL agent job to run the two SPs. Since you want monitoring, the SQL agent job needs to notify either after the result of running the sp for the publisher, the subscriber, etc. is hitting a specific threshold.
"
of course I don't mean that, I focus on how to use sp_showpendingchanges and everything in https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx to make sure that everything is ok but those SP hard to use.

"What is it you are looking the scripted process to do?"

I can't successfully run the SP, I can't see why ? any example on how to use it?
arnoldCommented:
Please post the exec sp @ and where you are running it, publisher/distributor or subscriber.
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

marrowyungSenior Technical architecture (Data)Author Commented:
you mean the full command I execute, right?
arnoldCommented:
Yes and where you are executing.
I.e. Distributor/publisher command
Subscriber command
marrowyungSenior Technical architecture (Data)Author Commented:
basically I want to monitor the merge replication at all level in script level and  I am not sure if how well the merge replication is moving/doing !

so I might need:
1)  EXEC sp_showpendingchanges  ? really need to insert to a table and record all information, seems can't run this SP individually :

http://stackoverflow.com/questions/22164695/how-to-get-results-from-exec-sp-showpendingchanges-into-a-table

by that  page, https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx, seems I don't need that as it said :

"Replication Monitor is a graphical tool that allows you to monitor a replication topology. You can access the SAME monitoring data programmatically using replication stored procedures"

so reading https://msdn.microsoft.com/en-us/library/ms147874(v=sql.100).aspx seems useless, right? just replication monitor is ok ? but replication monitor how show how many record is pending but only errors or no errors ,right?

I want to see how efficient is the merge replication:
1) latency in ms.
2) how many records are waiting to EACH subscriber.
3) what records are pending.
4) what it is waiting for ?

so I am expecting when I keep pressing F5 for that script/command, the result set will change, this is what I want.

if I do this :

use <database name>
;
go


EXEC sp_showpendingchanges 

Open in new window


then SP runs slow and :
1) I run that one publisher/distributor and it returns destination_Server as the subscriber ? pub_name is the publication name ? destination_dB_name is the DB the replication the publisher will replicate to ?
2) what is is_dest_subscriber is about? it is a number.
3)Pending_Delete is the number of records pending for delete?
4) pending_ins_and_upd is the number of records pending for ins/update? this column seems means both but how can it be?

anyway to show ONLY the item has pending records only ?
marrowyungSenior Technical architecture (Data)Author Commented:
if I do this:

exec sp_showpendingchanges @destination_server= 'yyy', 
     @publication = '<publication>'   
    ,  @article= '<article>'
    ,  @show_rows=  0

Open in new window


what is show_rows = 0 is about ? I type 1 is shows nothing.

the link just said: If a value of 1 is specified, the result set contains the columns is_delete and rowguid.

but is_delete and rowguid is not in the result set of  sp_showpendingchanges.

also how to use this sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail   to monitor session information ? give me a sample command please.
https://msdn.microsoft.com/en-us/library/ms187726(v=sql.100).aspx
https://msdn.microsoft.com/en-us/library/ms186970(v=sql.100).aspx

it seem this 2 x command is very hard to find example from the web.
arnoldCommented:
Try a simple straight query to see what you get with optional parameters.
From the link
sp_replmonitorhelpmergesession [ [ @agent_name= ] 'agent_name' ]
    [ , [ @hours= ] hours ]
    [ , [ @session_type= ] session_type ]
    [ , [ @publisher= ] 'publisher' ]
    [ , [ @publisher_db= ] 'publisher_db' ]
    [ , [ @publication= ] 'publication' ]

Try running with the agent name, publisher, and see how each variant ion changes the data.
As the note on the page for the above said, if you run it on the subscriber, you'll gets limited recent set of historical data.

Running it on the publisher/distributor .........

Best thing is by trial and error, try with the broadest @publisher='publisher'
And see what you get .....

Based on that you can see whether what you want is a processing that converts your responses into counts.

This will also help you define a context and basis for notification ...........
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

I think I experience one more issue here, can't see why and it only happen recently, we keep having replication alert like this :

REPLICATION ALERT LongRunningAgent for publication yyy on subscriber xxxx. Subscriber running for 46 minutes.

but when I check application log and replication monitor, nothing shows as error and the alert above only appear 10 hour ago only !

why can't find alert log, any replication error log path can see it from publisher?  please note that xxxx is not the publisher, is one of the subscriber, must I also check the error log from that subscriber too ?
arnoldCommented:
Do you get these frequently? this seems to be an informational that is configured to notify should the Subscriber run in excess of a specified parameter at which this alert is generated with the duration.

Check the message headers to see when the email was generated and why it might have taken you this long to reach your inbox.


See if the below link helps you search for different alert types and perhaps see others within the same ...
https://msdn.microsoft.com/en-us/library/ms151752%28v=sql.105%29.aspx

I think the individual event checks/rechecks is way outside the scope of the initial question.
marrowyungSenior Technical architecture (Data)Author Commented:
Sorry for the problem I saw above, can that SP help me to find out why ?
marrowyungSenior Technical architecture (Data)Author Commented:
"Do you get these frequently? this seems to be an informational that is configured to notify should the Subscriber run in excess of a specified parameter at which this alert is generated with the duration.
"
yes, always.

but only these few days the log in the publisher don't show related information.

"Check the message headers to see when the email was generated and why it might have taken you this long to reach your inbox.
"

nono, I mean I received this email 10 hours ago during I am sleeping and this morning I come up and I find no log for this entries! I check all job log, application log and windows log. nothing related

I check this:
https://technet.microsoft.com/en-us/library/ms152758(v=sql.100).aspx

which one helps to check out replication latency most ?
arnoldCommented:
The message headers will tell you the origin of the mailing i.e. was it sent from the subscriber or the publisher.

There are manythings that your previous questions covered. At this time I do not recall that you were able to determine the size of the snapshots that are being replicated.  I believe there are several databases that are being replicated. The publisher might not have the bandwidth to replicate all of the data in real time, and that ..........

Identify the databases that are being replicated, see what the data size of the snapshots that are being sent and their frequency. Have a total of all of them summed up while doing that, get the bandwidth available to the subscribers.
a transfer of data uses the lower of the sender and receiver. So if your sender (publisher) has 100GB connection, but your receiver (subscriber) only has a 10MB connection, the transfer of data will be based on the Receiver's network management configuration. same with the opposite setup (in this case the publisher/subscriber and location would need to be reevaluated)...

Latency when you ping or traceroute from one location to the other could be the result of the configuration on the routers at each end (QoS) to prioritize certain network traffic while decreasing the importance of ICMP (ping/traceroute on windows uses)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"The message headers will tell you the origin of the mailing i.e. was it sent from the subscriber or the publisher."

yeah I know, you mean read the message header and where it comes from ,then read the log on that server, but not only on publisher to read all log for all publisher, distributor and subscriber, right?

someone tell me read all error  log /alert from publisher, right? but I also checked the log on subscribe (that one from subscriber), nothing related to this, is just last morning's alert, why disappear so quick ?

"The message headers will tell you the origin of the mailing i.e. was it sent from the subscriber or the publisher."

" believe there are several databases that are being replicated. The publisher might not have the bandwidth to replicate all of the data in real time, and that ..........
"

however ,we found out network connectivity issue between publisher and the DR server of that publisher, our network guy said it is the network hardware issue, so I will bring that up again.

"Identify the databases that are being replicated, see what the data size of the snapshots that are being sent and their frequency. Have a total of all of them summed up while doing that, get the bandwidth available to the subscribers."

so you mean here is the error/alert because of the network latency ?
marrowyungSenior Technical architecture (Data)Author Commented:
hi, I am also looking for an update here, I hope you can coach me there too, this is another post :

http://www.experts-exchange.com/questions/28665420/how-to-use-SQL-profiler-to-check-SQL-query-overloading.html
Guy Hengel [angelIII / a3]Billing EngineerCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Not enough information to confirm an answer.

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

angelIII
Experts-Exchange Cleanup Volunteer
marrowyungSenior Technical architecture (Data)Author Commented:
tks for that.  I might use Idera DM to monitor it first.
marrowyungSenior Technical architecture (Data)Author Commented:
I closed ticket already.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.