SQL Server Performance

Best Replication

Discussion in 'Performance Tuning for SQL Server Replication' started by Jon M, Oct 20, 2004.

  1. Jon M Member

    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
  2. Twan New Member

    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
  3. Jon M Member

    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
  4. Twan New Member


    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
  5. satya Moderator

    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.
  6. mmarovic Active Member

    quote:Until the issues resolved why not deploy log shipping for easy porting of Transaction logs between production and reporting servers.

    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.
  7. satya Moderator

    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.
  8. Jon M Member

    Thank you guys.

    I might go with log shipping for the time being.

    Jon M

Share This Page