Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jerome Mackels
Starting Member
4 Posts |
 Posted - 2003-03-07 : 10:12:15
|
| Hi,For the moment, I’m trying to put in place the best solution for my problem. All help is welcome.To begin, I describe the structure:We have 2 servers: production and reporting server. Reporting server is a replication of the production server. The reporting server doesn’t update data which comes from production. It means this a one way replication. We have daily update for the reporting.The production DB is average 1.4Gb. The table replicated took several hundred Mb.In my view, I see a Transactional Replication. It’s not possible to use a Snapshot Replication (several hundred Mb), I think maybe that I’m wrong? That’s why for the moment we have a Transactional Replication with Log Reader. The Log Reader is only launch once by day.Questions: Which kind of replication is the best for me?How do you set up the Log Reader? Always activated, once by day but always before the Subscriber try to replicate… Give me some details please.The second thing concerned FTP. The replication allows anonymous subscriber. The initial snapshot compressed is on a FTP. No problem with that.Questions: How SQL Server manage the daily differences with a Log Reader? I suppose that it doesn’t change anything if I have the Replication Folder on a FTP ServerIf I have a Log Reader, the subscriber must be connected with the publisher (reporting must be connected with production) Is-it correct? I suppose that it’s not possible to use only the FTP to provide the daily update? I also suppose it’s directly done between the servers. Could you give me more information about how it works? I’m really interested by the data exchanged between servers.Now, I will add more complexity. In fact, we try to know if it’s possible to work in a “disconnected mode”. At the end, we want to know if it’s possible with a 128K Connection available only available during the night for transferring the Modified Production Data.If we work with snapshot replication, the problem is easier but transfer the snapshot will be too long, I think.If we work with transactional replication, the problem will be the connectivity between the servers. For example, now we have the configuration described higher on a 100 Mb LAN and it took average 10 minutes each day. On a 128K Connection it’s impossible. To give more details, about our current stats on the 100Mb LAN:Transactions Commands Duration (sec) Delivery Rate (cmds/sec) Latency (ms)26065 32299 02:09 412.614 6840919063872 75238 05:11 1421.92 3445393082182 121910 10:24 393.438 3467411541142 49324 02:39 512.526 34079211461 643 00:07 153.009 64706982109505 261296 19:04 295.161 3500251917833 22849 01:24 688.943 3359006516107 41946 02:55 402.23 3763439414469 17881 01:20 1290.98 36036003For me it’s impossible to put the replication in place with a 128K connection and also impossible to transfer daily updated in a compressed file by FTP. I know that it’s possible to do it, but not with the standard tools. What do you think about that? How do you think it’s possible to do it? Maybe we can ask to have a better connection than 128K. But do we need a permanent connection? I know that VPN is slower due to the encryption. And maybe all stuff exchanged between servers is too big.Thanks for your help.Jérôme |
|
|
|
|
|
|
|
Transactional Replication thought Internet