SQL Server Performance Forum – Threads Archive

Best Replication

Hi All,
I have 50 tables that needs to be replicated from my main server to another physical server located in Country X. When these tables get updated from my main server the other server should also be updated on an hourly basis. What is the best replication type or approach should I use? Snapshot? Transaction? Push or Pull? Thanks,
Jon M

---

Hi Jon, Lots of things to consider… is 50 tables most/all of the source database? if so, then you could consider log shipping too, which is simpler. – what percentage of data do you expect to change each hour
– how big is the link
– should the target database be available for reading during the replication of new/changed data this will give you an idea as to whether to use snapshot or transactional. Do you have any security implications, ie is one server in a more secure network than the other, if so then get the server in the more secure network to initial the replication Do you only have this one replication requirement? if so and if both servers are in networks of the same security levels, then push or pull is neither here nor there really… if your main server is already busy then consider putting the distribution database on the second server, this will off load some of the replication processing off your main production server Cheers
Twan

---

Thanks Twan. Right now, I may not be able to do replication due to limited resources and users need the data ASAP for reporting purposes. For now, my objective is to copy selected tables from my busy main transaction server (while in production) every 10 minutes to the reporting server. What is my best alternative if I can’t do replication? Thanks,
Jon M

---

Hi Jon, if you can afford your destination server to be unavailable for any time, then a periodic copying of the data using a SQL or DTS job on the source server (using a linked server for communication) other than that without using replication itself, I can only think of writing your own form of replication, although I don’t see why you’d want to do that… replication is very simple though and would allow access to the tables during the replication. Snapshot replication is the easiest but it will truncate the table and then take the whole lot again. Transactional replication would probably be best for you, as it would only take across changes. Transactional replication is easy to set up and doesn’t cost any extra money. The wizard in EM does a pretty good job. It then just depends on what security devices you have between the two servers Cheers
Twan

---

Until the issues resolved why not deploy log shipping for easy porting of Transaction logs between production and reporting servers. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I believe you can’t access reporting db during log restore. If this is true then you are not going to have data available for reporting during that period.

---

Yes it is so, as the restore wouldn’t take too long to finish and it is not complete reliable solution. I was referring LS solution until the issues sorted out for Replication. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thank you guys. I might go with log shipping for the time being. Jon M

---

]]>