Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?

Replication system disk performance issue after 1 month

Summary: Started replication April 1 of 4M xact / day publishing system to subscribing system.

Performance was good. Latency was ~ 5-7 seconds.

May 10 we noticed that the DB was behind (latency was 12 hours).

All performance counters seem good with the exception of the disk.

. Performance spikes are 8 minutes apart and last from 30 - 60 seconds.

. During this period, Disk % Busy (1 - Disk % Idle) is 100%

The publisher DB publishes about 50-52 xacts/sec.

Rate of distribution (distribution DB to Subscriber DB) is ~ 47 xacts / second, so latency is increasing (currently at 33 hours). Previously my Subscriber system's "capacity" was 150 xacts / sec.

I know this because several weeks ago, the network went down, we were 24 hours behind.

When the network came back up the replication subscriber system was able to catchup at around 150 xacts / sec, or 3X the production system rate.

What has changed between then and now? Not much. We did install Tivoli Service Manager (IBM's backup system) a couple of weeks ago. It seems to run fine on a nightly basis, but I don't see any periodic heavy Disk I/O from that. Just to be sure, I've had them shut the TSM services down just to be sure.

We've also eliminated all extraneous processes other than those I need for performance monitoring (there was a RTVscan, virus scan process).

I've eliminated Autogrowth's as an issue as I've bumped the growth so that they are very infrequent (several days at this point. When we resolve the problem, I'll dial this down to something more reasonable.

My disk configuration is not ideal I realize (single Raid-5 disk with 3 partitions), however, this has not changed in the 6 weeks.

Thanks for any help on this!

Jack Griffith

Configuration:

Subscribing System:

SQL Server: 2000, SP4 - 8.0.2039

CPU - 2.8GHZ Xeon, Quad Dual-core

Memory - 3.5GB RAM

Disk: 3 partitions on a single RAID-5 disk with 1118 GB of space:

C: 39GB System and Programs

D: 97GB Log space

E: 982 GB Data space

Replication configuration:

- nosynch, continuous Transactional Replication

- Distribution db is on Subscription system

- distribution - Publication of approx. 50 transactions / second

Subscriber DB configuration:

DB size: 64458 MB

Logging: Simple (at this point)

distribution

DB size: 3111 MB

Logging: Simple (at this point)

Is the whole topology (pub,dist and sub) SQL 2000 SP2? Do you know if logreader agent or distribution agent is the bottleneck? Did you add any new indexes or triggers at the subscriber db?sql

Replication Performance?

I'm having a tough time finding any good resources on Sql Server Replication Performance. Are there any benchmarks / state of any kind? How well does replication scale out?

In my scenario, I have one central publisher and several large tables, all with hundreds of millions of records. Every day I may insert/update millions of records in the publisher, and then I need to replicate the changes (in a few hours at most) onto a pool of subscribers, while they remain online.

Is the replication story robust enough to handle a situation like this?

Is this merge or transactional replication, and on what version lf sql server? And what is the approximate transaction size (meaning how many commands per txn)? Assuming this is transactional replication, performance will depend on your hardware, network bandwidth, size of changes (in K or MB), # of indexes at the subscriber, type of changes (ins, upd or del), existance of triggers, etc.

Throughput of several thousand commands per second is not out of the question, but as I mentioned above, it depends on the above. Tell us more about your scenario and we can comment further. You can reference this basic performance guide, http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx.

Replication Performance tuning question

I am trying to make sure the new replicate
distribution server is the best it can be. Below
are some tips I read on a site. It seems like
the first two tips almost conflict with each
other. however, they could be talking about
maybe a log reader agent on the first one. Most
of the publications I am going to be setting up
are large (5gb each x 20). anyone have some good
settings for a log reader profile settings for
publications around this size? Lots of these
publications are all going to be setup at the
same time, which seems to cause havoc on log
readers for replication.
Are the tips below good ones? can anyone add
some?
Thanks,
You can enhance the performance of transactional
replication in your application and on your
network by:
- Running agents continuously instead of on
frequent schedules.
- Reducing the distribution frequency when
replicating to numerous Subscribers. (we do
15min, default is 1hr)
- Configuring the Distributor on a dedicated
server. (we do this)
- Increasing memory on the Distributor. -
Minimizing the retention period for transactions
and history.
- Increasing the read batch size for the Log
Reader Agent.
- Using custom stored procedures for inserts,
updates, and deletes at Subscribers. (
The items you mention seem reasonable, apart from the last one which I
haven't heard of, apart from in the context of bidirectional transactional
replication. For an indepth, numerical analysis of parameters, have a look
at this article:
http://www.microsoft.com/technet/pro.../tranrepl.mspx
Regards,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||For small numbers of subscribers run continuously. The more subscribers you
have the more likely you will run into locking contention at some point in
time. You will then have to start scheduling your subscriber agents.
The log reader agent should always run continously.
The merge agent runs best when it processess small chunks of data. So if you
have a high volume application and a small number of subscribers run it
continously. If you have many subscribers you should schedule it to run
frequently. The frequency you run your merge agent at is a function of the
rate of transactions and the number of subscribers. Then you have to factor
in the fact that if you have too many subscribers running simultaneously
there will be locking.
If you have a large numberof transactions occuring up your batch size for
the log reader. Most of the time your log reader will keep up with the
transactions been written to the log. Issue sp_repltrans to get an idea of
the number of transactions in the queue to be read. If there are many
transactions there you might want to think about increasing the batch size.
When they talk about using custom stored procedures they are talking about
the custom stored procedures that SQL Server generates to apply the
transactions. Your other option is to use SQL statements, ie insert, update,
select. These SQL Statements are not optimized.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Combfilter" <adsf@.asdf.com> wrote in message
news:MPG.1bb3cc80fffa6a039896ae@.news.newsreader.co m...
> I am trying to make sure the new replicate
> distribution server is the best it can be. Below
> are some tips I read on a site. It seems like
> the first two tips almost conflict with each
> other. however, they could be talking about
> maybe a log reader agent on the first one. Most
> of the publications I am going to be setting up
> are large (5gb each x 20). anyone have some good
> settings for a log reader profile settings for
> publications around this size? Lots of these
> publications are all going to be setup at the
> same time, which seems to cause havoc on log
> readers for replication.
> Are the tips below good ones? can anyone add
> some?
> Thanks,
>
> You can enhance the performance of transactional
> replication in your application and on your
> network by:
> - Running agents continuously instead of on
> frequent schedules.
> - Reducing the distribution frequency when
> replicating to numerous Subscribers. (we do
> 15min, default is 1hr)
> - Configuring the Distributor on a dedicated
> server. (we do this)
> - Increasing memory on the Distributor. -
> Minimizing the retention period for transactions
> and history.
> - Increasing the read batch size for the Log
> Reader Agent.
> - Using custom stored procedures for inserts,
> updates, and deletes at Subscribers. (
|||In article <OMBeaAMnEHA.608
@.TK2MSFTNGP09.phx.gbl>, hilary.cotter@.gmail.com
says...
> For small numbers of subscribers run continuously. The more subscribers you
> have the more likely you will run into locking contention at some point in
> time. You will then have to start scheduling your subscriber agents.
> The log reader agent should always run continously.
> The merge agent runs best when it processess small chunks of data. So if you
> have a high volume application and a small number of subscribers run it
> continously. If you have many subscribers you should schedule it to run
> frequently. The frequency you run your merge agent at is a function of the
> rate of transactions and the number of subscribers. Then you have to factor
> in the fact that if you have too many subscribers running simultaneously
> there will be locking.
> If you have a large numberof transactions occuring up your batch size for
> the log reader. Most of the time your log reader will keep up with the
> transactions been written to the log. Issue sp_repltrans to get an idea of
> the number of transactions in the queue to be read. If there are many
> transactions there you might want to think about increasing the batch size.
> When they talk about using custom stored procedures they are talking about
> the custom stored procedures that SQL Server generates to apply the
> transactions. Your other option is to use SQL statements, ie insert, update,
> select. These SQL Statements are not optimized.
>
thanks once again hilary..I appreciate yours and
pauls help..
-comb

Replication performance question

Hello to all,

I have a performance question: we have a cluster with 2 SQL instances on 1 node (another instance is on another node, but no link with my current problem!). Let's call them C1SQL1 and C1SQL2.

This node is a Hyperthreaded Xeon 2.8Ghz with 1 gig of memory.

These 2 instances are using transactionnal replication and are configured as the distributor and publisher. C1SQL1 is not using much power, it's a small replication with around 10 agents. C1SQL2 is a bit heavier, with around 100 distribution agents. C1SQL2 has around 50 subscribers in 12 publications, but not all subscribers are used in each publications.

Once in a while, this cluster node impacts our production environment (since it's also a production server) and we're wondering if performance wise, it's really not powerfull enough to be the distributor?

I've isolated C1SQL2 on it's own logical CPU, and in idle mode, the replication workload (history, checking if new transactions are made) peaks at around 15-50% each 4-10 seconds.

Can I have any input on this?

Thanks!

Can you give some more detail, I'm not clear on what the problem/symptoms are. Regardless, have you been monitoring disk, memory and CPU to rule out hardware bottleneck? The replication workload you mention in the last sentence, are you talking about logreader agent or distribution agent? Is this SQL 2000 or 2005? Are you running Repl MOnitor in the background?

Replication performance Problem with SQL Server -> Oracle9i

We are replicating from SQL server 2000 DB to Oracle 9i database using a 2 Mbps line. When there is a mass update on our DB, the same is replicated to the Oracle 9i side, but apparently the SQl Server sends as many update/delete requests to Oracle as the
number of rows affected on SQL side (17000). The update therefore takes enormous time on the Oracle side and the whole system on Oracle side freezes for hours till all the updates happen and then the single row updates take place in about 30 seconds time.
when we sneak into the execution on the Oracle side, it seems it commits for each of the rows it updates. we use OLEDB provider for Oracle. does this have any link with the Auto-commit settings or due to some other?..
This is normal. Replication breaks down set based operations to singletons
to ensure that data is consistent on both ends - the Publisher and
Subscriber.
You will get better performance using the Microsoft ODBC driver for Oracle
and the Microsoft OLE DB provider for Oracle
If you are using the Oracle OLE DB provider you should be selecting the
Allow InProcess option of your linked server.
"sunny" <anonymous@.discussions.microsoft.com> wrote in message
news:BEC3FC8A-34CB-4760-B145-08C94801FD39@.microsoft.com...
> We are replicating from SQL server 2000 DB to Oracle 9i database using a 2
Mbps line. When there is a mass update on our DB, the same is replicated to
the Oracle 9i side, but apparently the SQl Server sends as many
update/delete requests to Oracle as the number of rows affected on SQL side
(17000). The update therefore takes enormous time on the Oracle side and the
whole system on Oracle side freezes for hours till all the updates happen
and then the single row updates take place in about 30 seconds time. when we
sneak into the execution on the Oracle side, it seems it commits for each of
the rows it updates. we use OLEDB provider for Oracle. does this have any
link with the Auto-commit settings or due to some other?..

Replication performance measurement

Hello,

I am trying some replication sample.I create a table with thousands of records in the publisher side. as I create a subscription(the subscription database is on a remote machine),
the whole table is created on the remote database.

I wanted to measure the performence as:
1. how much time was taken in filling the whole table in the subscriber side?
2. If i insert some 10000 records on publisher side, I want to measure, how much time was taken in inserting the same records on the subscriber?

How do I measure this ? Can I use some Log reader stuff.

thanks in advance

Are you talking about transactional replication? You can use replication's monitoring tool, which displays some performance information about your syncs, you can also look at the history tables located in the distribution database, such as MSSnapshot_history, MSdistribution_history, MSlogreader_history.|||You can look at less than 1minute, if that is within your LAN, if its a WAN, measure your bandwidth,even worst bandwidth u had, it wont take up more than 5minutes. The memory allocated to your SQL server need some consideration, and another factor is depend on your db design.

Replication performance degrade in unidirectional Direction and lock time out (Update are high t

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication performance degrade in unidirectional Direction (Update are high than inserts)

We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.

1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).

2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..

on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.

can any one can also suggest me server level settings for aviding locking time out.

looking for any experieced solution/suggestion.

Thanks in advance.

Hi adrshen,

Need more info. What performance has degraded? You mean user transactions. What was the response time like before and what is it like now.

With merge replication, it would affect the performance as it uses triggers to capture the changes.

regards

Jag

|||plz read my question again .. I just edit it :)|||If it is unidirectional always from publisher to subscriber, then you should look at download_only_articles. This is a special type of setting on an article to indicate that the subscriber will not do DML and it will be more performant. However, note that the performance of the merge agent will increase, but I am not sure if your subscriber itself will start performing better. You can give it a try.

Replication Performance -- Many publishers, 1 subscriber

I have a situation where I need about 50 client machines that are
collecting data to synchronize with a network server on our local 100
Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
Have experimented with using MSDE on the client machines and using
merge replication with a push subscription to the server. Everything
seems to work good on a single client, but I'm wondering:
- How does this scale? Is it realistic to think I can do this with 50
machines?
- Locking implications. Since this is an automated process, The
ability to have real-time inserts needs to be always available
Typically happens 1 - 2 times per minute. (Updates can be handled
through standard error handling since a human operator is involved.)
- As time goes by and table sizes increase would this slow down, even
if the primary activity on the table is the addition of records?
Thanks in advance.
Mike
Mike,
I would say it is preferable to have a single publisher on the main server
and multiple push subscribers for this topology.
I'm not sure what your separation of inserts and updates is for - could you
explain a little further.
Inserts shouldn't cause any locking contention. For contention of resources,
you could stagger the synchronization, and limit the number of concurrent
merge processes.
Increasing table size for inserts should be no problem. You'll have a
gradual increase in metadata, which will be removed automatically though.
HTH,
Paul Ibison
|||do you need bi-directional replication? From what you describe you have
selected merge, where if you don't need bi-directional replication you can
get away with transactional which will offer better performance.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405070936.fcab9ad@.posting.google.co m...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||Paul-
Thanks for the info. It was helpful. The reason I was pursuing this
topology is because the clients are the ones collecting the data.
Which need to be warehoused in a central place. (There is also
static setup data that is coming one way from the server, which I'm
doing exactly what you describe.)
The inserts and updates separation issue is because some of the data
comes automatically from the machine while another, different type of
data, data comes from the operator. It's stored in separate tables in
MSDE. The person's data can be corrected by the person to fix
mistakes. The machine data "never makes mistakes" (or at least it
doesn't know it does) so it's only an insert operation.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#vLI3YGNEHA.1616@.TK2MSFTNGP12.phx.gbl>...
> Mike,
> I would say it is preferable to have a single publisher on the main server
> and multiple push subscribers for this topology.
> I'm not sure what your separation of inserts and updates is for - could you
> explain a little further.
> Inserts shouldn't cause any locking contention. For contention of resources,
> you could stagger the synchronization, and limit the number of concurrent
> merge processes.
> Increasing table size for inserts should be no problem. You'll have a
> gradual increase in metadata, which will be removed automatically though.
> HTH,
> Paul Ibison
|||Hilary-
Thanks for the info. Not sure exactly what you mean by "selected
merge"? Like horizontal selection? You're right. Actually, I don't
need bi-directional replication in this case but, to my knowledge,
MSDE (which is running on the client) doesn't support transactional
and I thought snapshot would be inefficient.
Thanks again,
Mike
mvonstein@.yahoo.com (Mike Von Stein) wrote in message news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||I meant "selected" in the sense of "chosen".
MSDE can be a subscriber to a transactional publication, or a publisher for
merge or snapshot publications. On further reflection I guess this is why
you have selected merge.
I have worked on a topology that used over 60 merge subscribers to a single
publisher, so it is highly scalable.
I have heard about people using merge with over 100 subscribers with no real
problems.
Achieving real time replication with merge is a problem. Set your
pollinginterval to something low - perhaps 10s or so.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405080525.16417da3@.posting.google.c om...
> Hilary-
> Thanks for the info. Not sure exactly what you mean by "selected
> merge"? Like horizontal selection? You're right. Actually, I don't
> need bi-directional replication in this case but, to my knowledge,
> MSDE (which is running on the client) doesn't support transactional
> and I thought snapshot would be inefficient.
> Thanks again,
> Mike
>
> mvonstein@.yahoo.com (Mike Von Stein) wrote in message
news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...[vbcol=seagreen]
|||Mike,
as you know either way works, but I'd still choose to use use a central
publisher and multiple subscribers, as the metadata and alerts etc are then
in a central place and maintenance is simpler. For merge, the distinction
between publisher and subscriber is not too relevant if you don't have
conflicts.
Cheers,
Paul
|||Paul-
Okay, I guess I was thinking since the clients would be the primary
information generators, they would be the "Publishers", but I guess
the names shouldn't be taken literally in this case. I think I see
the benefits you are suggesting.
One question though. How does this impact future schema changes?
Would you have to drop all subscriptions remotely at the clients and
recreate? (That's a maintenance concern, since there are so many.)
We're kind of in a "formative" phase here and I'm expecting to have to
do some tweaks...
Thanks,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#of5O$cNEHA.3328@.TK2MSFTNGP10.phx.gbl>...
> Mike,
> as you know either way works, but I'd still choose to use use a central
> publisher and multiple subscribers, as the metadata and alerts etc are then
> in a central place and maintenance is simpler. For merge, the distinction
> between publisher and subscriber is not too relevant if you don't have
> conflicts.
> Cheers,
> Paul
|||Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> MSDE can be a subscriber to a transactional publication, or a publisher for
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> I have worked on a topology that used over 60 merge subscribers to a single
> publisher, so it is highly scalable.
> I have heard about people using merge with over 100 subscribers with no real
> problems.
> Achieving real time replication with merge is a problem. Set your
> pollinginterval to something low - perhaps 10s or so.
> "Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
|||Transaction does not support bi-directional replication through the wizards. You can configure it for bi-directional replication using the replication stored procedures, or you could use queued replication (but it is tuned for less than 10 subscribers whi
ch rules it out in this case) or immediately updating susbcribers (only if your subscribers/publisher are always well connected).
Transactional does support the subscribers going offline, however you are updating the subscribers, so your best option in this case is merge.
Merge also supports having the publisher and susbcribers going offline.
The caveat in this is the more frequently your publisher/subscribers goes off line the greater the chance of having conflicts.
-- Mike Von Stein wrote: --
Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> publisher, so it is highly scalable.
> problems.
> pollinginterval to something low - perhaps 10s or so.
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...

Replication performance - how to measure it ?

Hello,

could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?

Regards,
Markmjan@.interia.pl wrote:

Quote:

Originally Posted by

Hello,
>
could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?
>
Regards,
Mark

Any answer you might receive regarding Oracle depends upon which
replication mechanism you choose and which release of Oracle you're
using. Absent such information it's difficult, if not impossible, to
supply an answer.

Since you can't, or won't, supply the above mentioned information take
a trip to http://tahiti.oracle.com and start reading the documentation
for the replication mechanism and release you're using. Monitoring
instructions are usually included in these docs. I could provide
information for Streams on 9.2.0, (and possibly 10.2) but the dearth of
information in your post, as well as having my own work to complete,
causes me to send you to the manuals. Should you decide to provide
more detail I, or someone else in this newsgroup, could possibly supply
a better response to your question.

As ye sow, so shall ye reap. Provide to us, we can provide to you.
It's that simple.

David Fitzjarrell|||mjan@.interia.pl wrote:

Quote:

Originally Posted by

Hello,
>
could you please advice on how to measure replication performance
in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?
I've got two servers with databases installed and configured,
I prepared set of data using DBGEN from TPC and I already imported them
into databases.Also, I configured the replication.
Now I have to do a test with a few kind of replications method
implemented in these RDMBS, but I don't know which tool or reports or
"v$iews" should I use to measure replication performance.
The replication is configured only between the same RDBMS, I mean
Oracle <-Oracle, DB2 <-DB2 and MSSQL<-MSSQL.
Most of applications are great for checking performance of local DB,
not for replicated/distributed.
I've found description of CA Unicenter Database Performance Management
for distributed RDMBS, and I think it could be the right one, but I
can't find any demo or trial version :(
Could you please advice any place to download it, or other application,
script, description, just whatever.
Perhaps just any other idea how to check the replication mechanism
efficiency ?
>
Regards,
Mark

Your question, as stated, is impossible to answer with respect to
Oracle.

You have not provided a version number and replication might be
accomplished in a wide variety of ways depending on your requirements.
Among the possibilities:

Advanced Replication (many variations)
Streams (many variations)
Change Data Capture (3 different modes: Synchronous or Asynchronous?
Hotlog or Autolog?)
Data Guard (3 different modes: Maximum Protection, Maximum Availability,
and Maximum Performance)

You will find that depending on the Oracle option you choose you may
well find that neither SQL Server or DB2 has an equivalent technology.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org|||On 24 Aug 2006 06:17:40 -0700, mjan@.interia.pl wrote:

Quote:

Originally Posted by

>Hello,
>
>could you please advice on how to measure replication performance
>in Oracle, DB2 & MS SQL Server RDBMS installed in Windows servers ?

Hi Mark,

For MS SQL Server, check out the subject "Monitoring Replication" in SQL
Server 2005 Books Online. Here's a link (copy and paste into the URL
field in Books Online to be taken to the subject directly):
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/rpldata9/html/f182f43a-6af8-45bc-a708-08d5f7a6984a.htm

And if you don't have Books Online installed, here's a link to the
internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
--
Hugo Kornelis, SQL Server MVP|||And if you don't have Books Online installed, here's a link to the

Quote:

Originally Posted by

internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
>
--
Hugo Kornelis, SQL Server MVP

Thanks for fast reply, guys.
Hugo, thanks for the link about MS SQL Server monitoring :)

DA Morgan&fitzjarrell, you're right. I'm sorry, my mistake.
Oracle version is 10gR2 10.2.0.1.0, IBM DB2 v8.1.7
and Microsoft product will be probably MS SQL Server 2005

At present I've got configured replication only for DB2,
as concerns Oracle - I'm working on it.
Actually, I'm going to test almost all kinds of replication method
offered by these RDBMS,
so I need the most uniwersal benchmark&monitoring tool for this.
I know that it's hard to advice, but believe me that since a few days
I'm just searching&looking for information about a software or
procedure to evaluate replication performance.
On usenet, ftp's, WWW, P2P, DBA forums, etc. well, just everywhere.
But it seems, that not a lot of admins need to make a replication
tests.
Thats why I decided to ask for help here .

Cheers,
Mark|||diesel wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

>And if you don't have Books Online installed, here's a link to the
>internet copy: http://msdn2.microsoft.com/en-us/library/ms152751.aspx
>>
>--
>Hugo Kornelis, SQL Server MVP

>
Thanks for fast reply, guys.
Hugo, thanks for the link about MS SQL Server monitoring :)
>
DA Morgan&fitzjarrell, you're right. I'm sorry, my mistake.
Oracle version is 10gR2 10.2.0.1.0, IBM DB2 v8.1.7
and Microsoft product will be probably MS SQL Server 2005
>
At present I've got configured replication only for DB2,
as concerns Oracle - I'm working on it.
Actually, I'm going to test almost all kinds of replication method
offered by these RDBMS,
so I need the most uniwersal benchmark&monitoring tool for this.

No such thing in the Oracle world. Different tools for different
requirements and different modes depending on your priority.

The faster the replication the more risk of data being lost. Here's
something to consider. Is the transaction completed when it is committed
on the primary box or is it completed when it is committed both locally
and at the replication site? What happens if the link between the two
goes down? Do you continue local processing and hope to catch up later
or do you stop all transactions? These are all very important questions.

To just be testing speed alone is sort of like the airplane pilot who
cuts the engines at 35,000 feet and at 1,500 feet announces to the
passengers that fuel economy is really improved.

Quote:

Originally Posted by

I know that it's hard to advice, but believe me that since a few days
I'm just searching&looking for information about a software or
procedure to evaluate replication performance.

And what I am cautioning you about is that replication performance is
a meaningless metric unless you are comparing technologies that produce
the same outcome. Not just when all is perfect but in real world
situations. I can make anything really fast if I don't care what happens
later when there is a network glitch.

Quote:

Originally Posted by

On usenet, ftp's, WWW, P2P, DBA forums, etc. well, just everywhere.
But it seems, that not a lot of admins need to make a replication
tests. Thats why I decided to ask for help here .
>
Cheers,
Mark

I hope you do help ... but I see no evidence that you are really trying
to compare apples with apples. If you don't your will produce numbers.
Meaningless numbers ... but numbers. I suspect that is not your goal.
--
Daniel A. Morgan
University of Washington
damorgan@.x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Replication Performance

Hi,
I made a vb.net application that create a subscriber and subscribe it to an
article that I have created.
Yesterday when I start this application it took 2 minutes to create the
subscriber and copy the database from the publisher to the subscriber
Today I start the same application but on another laptop for another
subscriber and after 55 minutes the process was not finish. I stop the
application and I delete all the objects that was create in the replication
for that new subscriber.
Because of that, users that use another application that queries the same
database
get a connection timeout.
Do you have any idea of where should I look to find the nature of the problem?
Thanks in advance!
Can you figure out at what part of the process does it stall? Creating the
database, connecting with the publisher/subscriber, deploying the snapshot?
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
"GC" <GC@.discussions.microsoft.com> wrote in message
news:D747B6EE-9078-4FC6-8CF5-ABCB148164ED@.microsoft.com...
> Hi,
> I made a vb.net application that create a subscriber and subscribe it to
> an
> article that I have created.
> Yesterday when I start this application it took 2 minutes to create the
> subscriber and copy the database from the publisher to the subscriber
> Today I start the same application but on another laptop for another
> subscriber and after 55 minutes the process was not finish. I stop the
> application and I delete all the objects that was create in the
> replication
> for that new subscriber.
> Because of that, users that use another application that queries the same
> database
> get a connection timeout.
> Do you have any idea of where should I look to find the nature of the
> problem?
> Thanks in advance!
|||sp_who2
Find out who is generating a block.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"GC" <GC@.discussions.microsoft.com> wrote in message
news:D747B6EE-9078-4FC6-8CF5-ABCB148164ED@.microsoft.com...
> Hi,
> I made a vb.net application that create a subscriber and subscribe it to
> an
> article that I have created.
> Yesterday when I start this application it took 2 minutes to create the
> subscriber and copy the database from the publisher to the subscriber
> Today I start the same application but on another laptop for another
> subscriber and after 55 minutes the process was not finish. I stop the
> application and I delete all the objects that was create in the
> replication
> for that new subscriber.
> Because of that, users that use another application that queries the same
> database
> get a connection timeout.
> Do you have any idea of where should I look to find the nature of the
> problem?
> Thanks in advance!
|||The Process is not stall, there still activity.
When I go on the subscription of the subscriber I can see that the
subscription is running. If I do a refresh I can see that some row are
download to the subscriber
because the amount of rows that are downloaded differ each time I do a refresh
May be my problem is the way I create my Articles.
I am a beginner in replication so I dont expect to have the best strategie
the first time.
By the way I want to thank you for all the support that you do to all of us
It is appreciated.
"Hilary Cotter" wrote:

> Can you figure out at what part of the process does it stall? Creating the
> database, connecting with the publisher/subscriber, deploying the snapshot?
> --
> 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
> "GC" <GC@.discussions.microsoft.com> wrote in message
> news:D747B6EE-9078-4FC6-8CF5-ABCB148164ED@.microsoft.com...
>
>

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
RasheedI am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
--
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegroups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
Rasheed
I am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegr oups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>
|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?

Replication merge instance is not find in Performance Viewer

Hi all,
I set the configuration for Merge replication for Subscription on
one database and
I have created the Merge replication for publication on another
machine.
And I updated columns of subscription database table and I find
publication machine database table also updated with subscription
database table, here my problem starts,
My problem is when I run replication for merge transaction, I have to
find instance of the Windows on my machine in Performance Viewer (to
see performance viewer goto control panel ->Administrator->performance
in this select performance counter as SQL Server: Replication merge)
but I am not finding the instance
Please some body help me out.
RasheedI am not exactly sure what you mean here. In performance monitor the
counters are by instance. The option to select an instance or all instances
is grayed out. This is by design.
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
<sk.rasheedfarhan@.gmail.com> wrote in message
news:1163512500.562695.183350@.h48g2000cwc.googlegroups.com...
> Hi all,
> I set the configuration for Merge replication for Subscription on
> one database and
> I have created the Merge replication for publication on another
> machine.
> And I updated columns of subscription database table and I find
> publication machine database table also updated with subscription
> database table, here my problem starts,
> My problem is when I run replication for merge transaction, I have to
> find instance of the Windows on my machine in Performance Viewer (to
> see performance viewer goto control panel ->Administrator->performance
> in this select performance counter as SQL Server: Replication merge)
> but I am not finding the instance
> Please some body help me out.
> Rasheed
>|||thanks for resopnse Mrs. Hilary cotter
I will explain......i am using SQL Server 2000, when I run replication
(it may be transaction or merge replication) windows components will
run internally.
1) when I was running replication transaction I can see instances in
Performance
viewer but
2) when I was running replication merge I cant see instances in
Performance
viewer
so my doubt is for replication merge can we have the Instance or not ?