Best Replication Type in this case

[aomara]

I have a table that should be replicated to another location.

This table contains about 20000 records and network link is 128 K bit.

In case the subscriber updates any record, this record should be retrieved again from the publisher.

I am confused what type to use?
a) Snapshot but the link is 128

Or

b) Transactional but records updated at the subscriber will not be refreshed from the publisher

Or

c) Transactional and make the snapshot refresh rate to be daily at 1 am

Please advice?

 

[Catadmin]

I don't think any type of replication is going to work well over that small of a network connection. Also, Transactional requires a persistant connection or it will break more often than it's already prone to do.

Lookup replication in BOL. I'm sure there's a minimum bandwidth requirement for Rep. I just can't remember what it is.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[mrdenny]

I'd have to agree. With a slow network connection like that Replication is going to be very slow.

You may want to look into writting your own logic to handle the data moving. Especially if it's only one table.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[aomara]

Actually we have a Merge Replication Connection done each houre in this 128 K Bit and it works perfect.

The point is I need the solution and let me tested over this connection speed.

 

[Catadmin]

You've got Merge and the subscriber is updating records. Are you saying you don't want the subscriber to be able to update records?

If so (if you want to lose all subscriber changes), then how many updates get done during the course of a day?

If not (if you want to keep your subscriber changes), then stick to what you've got. Merge is what you need.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[aomara]

Actually, I have some table that needs doulbe update from the both sides, so I use the merge replication with it.

Other table that need the one way replication is my problem.

Changes are not frequant, from the 20000 records about 500 is changed per day.

the point is, In case the subscriber updates any record, this record should be retrieved again from the publisher so the subscriber will always be typically the same as publisher.

 

[Catadmin]

Okay. If I understand replication correctly, when you set up a publication, you can pick the type of replication, so you should be able to have two different types of replication working at the same time as long as they're not updating the same tables. (I'm just talking out loud for someone to correct me if I'm wrong. Forgive me if you already know this).

Given your situation, for this particular publication/subscription set up, I would set up pull Transactional Replication.

I cannot guarantee it won't break. TransRep breaks all the time. Heck, replication period breaks all the time. And given your connection, I'm not sure how well it will work with another type of replication happening at the same time. But give TR a try and see if it helps you out.

If you can, try to scam a better connection setup from your boss. You may need it to make this all function properly without breaking every other day.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[aomara]

Yes it’s correct what you said in the First Paragraph.

We already work in upgrading the speed of the line, business requirements increasing which needs this speed.

I am intending to test it.

As I said that merge replication is working every hour (10:00, 11:00 .....).

I plan to make the other replication works every hour also but in start-up different time (10:30, 11:30....).

Many thanks

 

[Catadmin]

Glad I could help.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"