Post reply

Setting up replication for OLTP database

  • October 21, 2008 at 6:40 pm

    #126129

    Guys,

    I have OLTP database which is 60GB, currently reports also run against this database. I want to replicate this database

    and have reports to run against the replicated (slave) database. I believe this way there will less read lock transactions

    on the master database.

    Is there anything I need to look out for while setting replication in terms of performance and frequency

    Any suggestions and inputs would help.

    Thanks

  • October 21, 2008 at 7:09 pm

    #887901

    If you want to replicate whole database. And you want your destination database only for Reporting, then I suggest you do log shipping instead of Replication.

    Thanks,

    Imran Mohammed.

  • October 21, 2008 at 7:18 pm

    #887905

    can you please elaborate on the advantages of log shipping vs replication.

    Apart from reports I also want the searches to go against the same replicated database (slave)

    Thanks

  • October 21, 2008 at 7:36 pm

    #887907

    Log shipping won't work in your situation as the log restore process would of necessity block people out. I'm not sure if database mirroring would work but you could research that a bit more and compare that with your requirements.

    Replication would cover the requirements from what I can see. The database size is not the factor as much as the amount of transactions that are taking place against that database. That will drive the replication load and lag if there is a ton of activity (inserts, updates, deletes). There are things you can do to improve that through the replication agent configuration.

    The best thing to do is to read up on transactional replication and start to consider how that would work in your environment. Start to define the tables that would need to be replicated, my guess is that you won't need all for the reporting efforts, etc. Start looking at the configuration, specifically ensuring that you are going to have a seperate distribution server if the transaction volume is high.

    Post with more questions as they come or if you have other questions related to this. Thanks.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • October 22, 2008 at 8:08 pm

    #888439

    Thanks for your reply, I have setup transactional replication for selected database objects which are used reports.

    I have concerns about the instances - the master database is 60GB (this is the only database on the instance), currently

    1. Distributor and Publisher on the same instance

    2. Subscriber on a different instance

    Ideally what is the recommended solution interms of settings up publisher, distributor and subscriber in sql instance

    what are the ramifications of setting up publisher, distributor and subscriber in the same instance

    Any suggestions and inputs would help.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply