Post reply

Replication vs Transation Logshipping

  • October 10, 2005 at 12:08 am

    #110618

    Hi,

    I am working on a project currently for a Share Brokerage firm. We have an SQL server 2k which holds all transations(buying, selling of shares, new users setup etc) in 5 databases on the server. The total size of all databases is around 10GB and is expected to reach around 30GB in next 6 to 12 months.

    There are around 1500 users connected all the time through the Trading application for sale /purchase of shares.

    We need to setup a backup server for Disaster Recovery so that if current server goes down the business can start on backup server. The Recovery Point Objective is 0 to 5 mins and Recovery time objective is 15 mins to 30 mins. 

    I am evaluating whether to use Transational replication or Logshipping for achieving this objective.

    Can anyone advise which option would be best suited in this situation.

    Please advise.

    Thanks & regards...

     

  • October 10, 2005 at 12:39 am

    #596207

    I'd use some form of Replication as well Log Shipping.

    Replication to a local server to cover server failure. Log Ship the replicated server offsite to cover site failure.

    Also, the minimum interval for replication is 1 one minute.

     

    --------------------
    Colt 45 - the original point and click interface

  • October 10, 2005 at 1:53 am

    #596219

    Thanks Phill.

    We donot have another site right now so I think we would go for replication to another server at same site for the time being.

    Does replication or Logshipping affect performance in any way?

    Thanks..

    Anurag

  • October 10, 2005 at 2:33 am

    #596223

    I think the good ways it's use the Log Shipping, because Log Shipping do not need a lot of resource (performance), and Log Shipping designing for DRP, but replication designing for distributed data.

  • October 10, 2005 at 6:07 am

    #596237

    Both Log Shipping and Replication will have an impact in some form or another.

    Replication will have less effect on the server performance and more effect on the database and it's design. It can also be a bit troublesome to diagnose problems. Migrating changes from development to production can also be a real hassle.

    Log shipping will have less effect on the database and application, but depending on your setup, will require a bit more server resources. It's easy to setup and as it is basically an SQL backup/restore operation diagnosing problems is relatively easy.

    That all said and done, it's really a business decision that drives which direction you take. Just think how valuable the data can be. How long can the business users sit doing nothing while the database is brought back online. I also work for a share broking company, although we don't quite have that many active users on the system. Every transaction in our main database is replicated to a hot-spare and then "log-shipped" off-site. By using transactional replication we can be sure of transaction consistency and we don't have to wait for transactions roll-forward/rollback in the event of a failure. The quick fail-over is quite critical because as you would know millions can traded in mere minutes.

    Contrary to what Deni Kusdeni says, from Books Online,

    "Using replication as part of a customized standby server strategy. Replication is one choice for standby server strategy. Other choices in SQL Server 2000 include log shipping and failover clustering, which provide copies of data in case of server failure."

    --------------------
    Colt 45 - the original point and click interface

  • October 10, 2005 at 7:36 am

    #596256

    Thanks to you all. I think I get the point and will use replication and then logshipping.

    thanks ..

    Anurag

  • October 10, 2005 at 12:23 pm

    #596355

    Phil, what do you mean by " .. Also, the minimum interval for replication is 1 one minute."

    If you choose a continuosly updating subscriber, then latency is only a few seconds.

  • October 10, 2005 at 4:22 pm

    #596394

    Isn't there a dependency on an SQL Agent job, or am I thinking of something else?

     

    --------------------
    Colt 45 - the original point and click interface

  • October 11, 2005 at 7:45 am

    #596531

    The most frequent setting for a SQL job is every minute, but if you choose a "contimuosly updating" subscriber, then the SQL job is "never ending" so it's running all the time, just like the log reader job on the publisher.

  • October 11, 2005 at 1:24 pm

    #596641

    When you say ""contimuosly updating" subscriber" do you mean a combination of SQL Agent and Replication executables?

  • October 11, 2005 at 1:29 pm

    #596643

    When you choose between Replication and Logshipping I think someone should consider the fact that with Replication the database corruption isn't propagated, just sql statements are, but with Log Shipping corruption is propagated.

    Also consider that using Replication change database structure and even that Replication is easy to setup is not that easy to maintain.

  • October 11, 2005 at 1:35 pm

    #596646

    " ... When you say ""contimuosly updating" subscriber" do you mean a combination ..."

    Replication creates the SQL Agent jobs. When you create the subscription, you can choose:

    "Continuosly updating" - to guarantee that data modifications will be propagated immediately between the Publisher and the Subscriber.

    "Scheduled" - runs the Distribution Agent hourly (default), or daily, weekly, or monthly. However, choosing a frequency other than "Continuously" increases the latency between the Subscriber and the Publisher

    "On demand only" to run the Distribution Agent only when specifically requested. This option also enables a Subscriber to use Microsoft Windows Synchronization Manager.

    (I stole the definitions from BOL)

Viewing 12 posts - 1 through 11 (of 11 total)

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