locked
how to build a real time one way replication for reporting? RRS feed

  • Question

  • I have a transaction database and need to be sync up constantly to a reporting database.  I've tried Mirroring with Snapshot in sql08r2.  Snapshot works great until there are long running queries as well as queries requires little latencies.  There is no medium ground in this application to change the snapshot frequency to satisfy both long running and real time queries.

    So, I need to find the best way to replicate data.  Currently, there is a transaction repl., which I think is overkill, because, the need is a one way replication.  secondly, there are frequent slow down (liked 1 update /sec ) on the subscriber table as the transactions are applying from the publishers.

    how best to build a real time one way replication, which can handle millions of updates in a day and be able to refresh to subscriber right away in real time? 


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.

    Tuesday, January 15, 2013 10:04 AM

Answers

  • This article has got step by step pictures of how to create a normal transactional replication

    http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

    More about transactional replication here

    http://msdn.microsoft.com/en-us/library/ms151176%28v=sql.105%29.aspx

    http://blogs.msdn.com/b/chrissk/ -- this is a good blog where chris has posted on different transactional replication troubleshooting stuff.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, January 17, 2013 9:41 AM

All replies

  • I too have a transactional replication for reporting and it does quite fine, the latency is just less than 10 seconds every time and it replicated between two datacenters which are in different domains and atleast 50 miles apart.It does 900000 transactions and 1.1 million commands which were replicated.

    I thought the default transactional replication was always one way replication!! Are you talking about updateable subscriptions??


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com


    Tuesday, January 15, 2013 10:07 AM
  • that sounds awesome. 

    could you share a bit more on the detail of the configuration and non-default setting making it possible?  i would like go deep dive on this.  thx!

    ps: where is a good source for a 3 servers set up (one domain) and 3 servers setup with multiple domains and non-domain.


    -- IBM has jobs for USA employee to work oversea (India) and get their wages.


    • Edited by light_wt Thursday, January 17, 2013 4:46 AM
    Thursday, January 17, 2013 4:09 AM
  • This article has got step by step pictures of how to create a normal transactional replication

    http://www.sql-server-performance.com/2010/transactional-replication-2008-r2/

    More about transactional replication here

    http://msdn.microsoft.com/en-us/library/ms151176%28v=sql.105%29.aspx

    http://blogs.msdn.com/b/chrissk/ -- this is a good blog where chris has posted on different transactional replication troubleshooting stuff.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Thursday, January 17, 2013 9:41 AM