Post reply

Setting transactional replication

  • October 26, 2006 at 7:14 am

    #170761

    Hi,

    • I have a test database X of size 800MB and a production database of the same size and configuration on two separate servers which are geographically apart. 'X' is in UAE and 'Y' is in UK. 
    • The production server 'Y' is accessed by a website
    • I have set up transactional replication between the two servers. 'X' being the publisher as well as distributor and 'Y' being the subscriber
    • One of the publications contains a huge table that has about 1 million rows.
    • When i run transactional replication for this article, it takes a very very long time to complete. During this period, the corresponding tables in production database are getting locked and the website is affected.

    My questions are :

    • Is this time delay because the snapshot has to be transfered over a network ?
    • Will it take time only for the first time i apply snapshot, or for every subsequent replication also ?
    • Is there any best way to set up replication between 2 servers which are far far away ! ?
    • If i modify or add new tables in test database X , in which replication( i.e. publisher & distributor) is set, will i have to create and re-initialize snapshot again ?

    Would be great if you could throw some light on this . Thanks.

     

  • October 26, 2006 at 11:55 am

    #668311

    >>Is this time delay because the snapshot has to be transfered over a network ?

    Yes.

    >>Will it take time only for the first time i apply snapshot, or for every subsequent replication also ?

    Everytime you do a snapshot it will recreate the objects and move all the data across.

    I dont see why you would need initialize everytime. You only do it once.

    Is there any best way to set up replication between 2 servers which are far far away ! ?

    >> If your network bandwidth is good and you have enough space on your servers you should be okay. We are replicationg a 200 gb db. we have tables with a few hundred million rows.

    >>If i modify or add new tables in test database X , in which replication( i.e. publisher & distributor) is set, will i have to create and re-initialize snapshot again ?

    No. You just add the article to the publication. and only the data for the object added will be pushed across.

    Here's an article if it helps:

    http://dotnetjunkies.com/WebLog/dinakar/articles/144151.aspx

    Edit:

    I just saw your post was in SQL 2005. If you check out BOL for transactional replication, they even have a step by step instructions on how to setup replication from a backup copy. Any schema changes after that should be automatically pushed acrss. SQL 2005 replication is has been majorly overhauled. A lot of issues from 2000 have been resolved.

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

Viewing 2 posts - 1 through 1 (of 1 total)

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