125Gb heavy OLTP publication db w/ Transactional Replication replicating 400
tables to 5 different servers. No problems on remote distributor, cpu hardly
used, sits practically idle most of the day staying under 10%. Ram is used
however not getting taxed.
We added merge replication for 1 table and the cpu's pegged to 90 to 100%
each time the merge agents pick up transactions at pub. Every 20 minutes a
job populated this table w/ 100 to 200 inserts at publisher, the 5 merge
agents kick in immediately (continuously running) and spike cpu bringing
server to a crawl.
Publisher, Distributor, & all Subscribers on LAN w/ 100Mbps connections.
SQL 2000 SP3 on all servers. Distributor is a 6CPU PIII 700Mhz each. New
Distributor hardware not an option. Have to limp along until new budgets kick
in...
Thoughts...?
TIA,
Chris
Chris,
are the 100/200 inserts involving BLOB datatypes? How long does the spike
last? Anyway, I'd change the merge agents from running continuously, in
order to stagger their impact on the publisher.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
sql
Showing posts with label distributor. Show all posts
Showing posts with label distributor. Show all posts
Wednesday, March 28, 2012
Merge filtering HOST_NAME help
Hello
Im trying to create a merge replication.
I've a 2xSubscribers and a distributor/publisher
My published table has fieldnames describing what server this row should be published. When I create my replication a use a filter like this: ... where target_server = host_name()
I want to publish rows to different subscribers, for instance i have a server with hostname 'BOBAFETT' and another with hostname 'R2D2' and a distributor/publisher with hostname 'SKYWALKER'. When I insert a row into the publisher I cannot get it replicate
d unless i use SKYWALKER as target_server, but I will use BOBAFETT or R2D2 as target_server, thereby getting the correct row to the intended subscriber!!
Another strange thing, when I insert a row into the subscriber it gets deleted and nothing is inserted into the publishers table!!
My own thoughts is that the host_name() only applies to the publisher and not the subscriber, therefore im only able to publish tows with the publisher name as fieldvalue - That's not quite what I would expect.
Ohh btw. If I hardcode BOBAFETT in the filter where clause it gets the correct rows!!
Can anybody help me?
Kind regards
Janus, Denmark
Janus,
as SKYWALKER is your publication server, this should amalgamate all the rows. The other servers BOBAFETT and R2D2 will be restricted to having rows with their own server name in the filtering column ie on R2D2 you can add rows with R2D2 but you can't add
a record having the value SKYWALKER.
BTW, there is another way of setting it up which is less restrictive - you can use -HOSTNAME 'value' as a parameter in the merge agent's job. This means that you aren't restricted to having a column with R2D2 or BOBAFETT in it, and instead you could have
some business indicator.
HTH,
Paul Ibison, London, UK
Im trying to create a merge replication.
I've a 2xSubscribers and a distributor/publisher
My published table has fieldnames describing what server this row should be published. When I create my replication a use a filter like this: ... where target_server = host_name()
I want to publish rows to different subscribers, for instance i have a server with hostname 'BOBAFETT' and another with hostname 'R2D2' and a distributor/publisher with hostname 'SKYWALKER'. When I insert a row into the publisher I cannot get it replicate
d unless i use SKYWALKER as target_server, but I will use BOBAFETT or R2D2 as target_server, thereby getting the correct row to the intended subscriber!!
Another strange thing, when I insert a row into the subscriber it gets deleted and nothing is inserted into the publishers table!!
My own thoughts is that the host_name() only applies to the publisher and not the subscriber, therefore im only able to publish tows with the publisher name as fieldvalue - That's not quite what I would expect.
Ohh btw. If I hardcode BOBAFETT in the filter where clause it gets the correct rows!!
Can anybody help me?
Kind regards
Janus, Denmark
Janus,
as SKYWALKER is your publication server, this should amalgamate all the rows. The other servers BOBAFETT and R2D2 will be restricted to having rows with their own server name in the filtering column ie on R2D2 you can add rows with R2D2 but you can't add
a record having the value SKYWALKER.
BTW, there is another way of setting it up which is less restrictive - you can use -HOSTNAME 'value' as a parameter in the merge agent's job. This means that you aren't restricted to having a column with R2D2 or BOBAFETT in it, and instead you could have
some business indicator.
HTH,
Paul Ibison, London, UK
Monday, March 26, 2012
merge agent status
Hello All -
I have a push merge subscription using Sql Server 2000. The publisher &
distributor reside on the same host.
I would like to be able for clients to determine the status of their merge
agent at the publisher. This would be used as a verification at the client,
that their merge agent is properly running and there is no error condition.
In the ReplicationMonitor/Merge Agent display, the status "No data needs to
be merged" displays when everything is functioning properly. I figure this
status must be available in some system table for the given merge agent.
Anyone know of the field / table to retrieve, in order to determine the
status of a merge agent? Is there some system sp to run?
Thanks in advance.
Regards,
bruce
its in the distribution database, query the run_status column on
MSmerge_history
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:V8ebc.3251$WA4.2734@.twister.nyc.rr.com...
> Hello All -
> I have a push merge subscription using Sql Server 2000. The publisher &
> distributor reside on the same host.
> I would like to be able for clients to determine the status of their merge
> agent at the publisher. This would be used as a verification at the
client,
> that their merge agent is properly running and there is no error
condition.
> In the ReplicationMonitor/Merge Agent display, the status "No data needs
to
> be merged" displays when everything is functioning properly. I figure
this
> status must be available in some system table for the given merge agent.
> Anyone know of the field / table to retrieve, in order to determine the
> status of a merge agent? Is there some system sp to run?
>
> Thanks in advance.
> Regards,
> bruce
>
>
sql
I have a push merge subscription using Sql Server 2000. The publisher &
distributor reside on the same host.
I would like to be able for clients to determine the status of their merge
agent at the publisher. This would be used as a verification at the client,
that their merge agent is properly running and there is no error condition.
In the ReplicationMonitor/Merge Agent display, the status "No data needs to
be merged" displays when everything is functioning properly. I figure this
status must be available in some system table for the given merge agent.
Anyone know of the field / table to retrieve, in order to determine the
status of a merge agent? Is there some system sp to run?
Thanks in advance.
Regards,
bruce
its in the distribution database, query the run_status column on
MSmerge_history
"rr" <bruceradtke@.REMOVEspamREMOVE.earthlink.net> wrote in message
news:V8ebc.3251$WA4.2734@.twister.nyc.rr.com...
> Hello All -
> I have a push merge subscription using Sql Server 2000. The publisher &
> distributor reside on the same host.
> I would like to be able for clients to determine the status of their merge
> agent at the publisher. This would be used as a verification at the
client,
> that their merge agent is properly running and there is no error
condition.
> In the ReplicationMonitor/Merge Agent display, the status "No data needs
to
> be merged" displays when everything is functioning properly. I figure
this
> status must be available in some system table for the given merge agent.
> Anyone know of the field / table to retrieve, in order to determine the
> status of a merge agent? Is there some system sp to run?
>
> Thanks in advance.
> Regards,
> bruce
>
>
sql
Merge Agent retrying forever
We have a Merge Replication with Publisher and Distributor on SQL Server 2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing through VPN phone lines.
From time to time one or more subscribers can't reconnect to the Publisher. By inspecting their Merge Agents (through the Replication Monitor) in the Publisher Server we see that they are marked as retrying forever. In the distribution database MSmerge_hi
story table these agents have the "runstatus" column marked with 5 (retry).
We are not able to stop the agents. We have shut down the Publisher server but still the agents show the same status. We have noticed that in order to solve the problem we must synchronize those subscribers through UTP fast cables.
Any help will be appreciated
Righth click on the merge agent and click on Agent Properties. Click on the
Steps tab and double click on a step. On the Edit Step window, click on
the Advanced tab and change the number of retry attempts to a number other
than the default (usually 10 for the run Agent step and 0 for the other
two). I put 2 for all of my steps. You should also change the retry
interval, I put 1 for mine. Click apply and next to set the next step.
Hope this helps.
"Tony2000" <anonymous@.discussions.microsoft.com> wrote in message
news:F71724A8-457A-4B2F-BB8D-B92B0EC3ADD2@.microsoft.com...
> We have a Merge Replication with Publisher and Distributor on SQL Server
2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing
through VPN phone lines.
> From time to time one or more subscribers can't reconnect to the
Publisher. By inspecting their Merge Agents (through the Replication
Monitor) in the Publisher Server we see that they are marked as retrying
forever. In the distribution database MSmerge_history table these agents
have the "runstatus" column marked with 5 (retry).
> We are not able to stop the agents. We have shut down the Publisher server
but still the agents show the same status. We have noticed that in order to
solve the problem we must synchronize those subscribers through UTP fast
cables.
> Any help will be appreciated
sql
From time to time one or more subscribers can't reconnect to the Publisher. By inspecting their Merge Agents (through the Replication Monitor) in the Publisher Server we see that they are marked as retrying forever. In the distribution database MSmerge_hi
story table these agents have the "runstatus" column marked with 5 (retry).
We are not able to stop the agents. We have shut down the Publisher server but still the agents show the same status. We have noticed that in order to solve the problem we must synchronize those subscribers through UTP fast cables.
Any help will be appreciated
Righth click on the merge agent and click on Agent Properties. Click on the
Steps tab and double click on a step. On the Edit Step window, click on
the Advanced tab and change the number of retry attempts to a number other
than the default (usually 10 for the run Agent step and 0 for the other
two). I put 2 for all of my steps. You should also change the retry
interval, I put 1 for mine. Click apply and next to set the next step.
Hope this helps.
"Tony2000" <anonymous@.discussions.microsoft.com> wrote in message
news:F71724A8-457A-4B2F-BB8D-B92B0EC3ADD2@.microsoft.com...
> We have a Merge Replication with Publisher and Distributor on SQL Server
2000 SP3 and Subscribers running Personal SQL Server 2000 SP2 synchronizing
through VPN phone lines.
> From time to time one or more subscribers can't reconnect to the
Publisher. By inspecting their Merge Agents (through the Replication
Monitor) in the Publisher Server we see that they are marked as retrying
forever. In the distribution database MSmerge_history table these agents
have the "runstatus" column marked with 5 (retry).
> We are not able to stop the agents. We have shut down the Publisher server
but still the agents show the same status. We have noticed that in order to
solve the problem we must synchronize those subscribers through UTP fast
cables.
> Any help will be appreciated
sql
Subscribe to:
Comments (Atom)