SQL Server Performance Forum – Threads Archive
Transactional Replication trought Internet
Hi, For the moment, I#%92m 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#%92t 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#%92s not possible to use a Snapshot Replication (several hundred Mb), I think maybe that I#%92m wrong? That#%92s 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#%92t change anything if I have the Replication Folder on a FTP Server If 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#%92s not possible to use only the FTP to provide the daily update?
I also suppose it#%92s directly done between the servers. Could you give me more information about how it works? I#%92m really interested by the data exchanged between servers.
Now, I will add more complexity. In fact, we try to know if it#%92s possible to work in a “disconnected modeâ€. At the end, we want to know if it#%92s 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#%92s impossible. To give more details, about our current stats on the 100Mb LAN: TransactionsCommandsDuration (sec)Delivery Rate (cmds/sec)Latency (ms)
260653229902:09412.61468409190
638727523805:111421.9234453930
8218212191010:24393.43834674115
411424932402:39512.52634079211
46164300:07153.00964706982
10950526129619:04295.16135002519
178332284901:24688.94333590065
161074194602:55402.2337634394
144691788101:201290.9836036003 For me it#%92s 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#%92s possible to do it, but not with the standard tools. What do you think about that? How do you think it#%92s 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
I am not going to try to answer all your questions, but one thing you might want to consider instead of using transactional replication is to use log shipping instead. And since you are going over the Internet and need to keep data to a minimum, use a product such as SQL LiteSpeed or SQLZip to create compressed backups of your database and transaction log. You will want to write your own version of log shipping, but it should do what you need done with much less overhead and admin burden than using replication. ——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
]]>