Post reply

Replication between Main server and remote sites

  • July 26, 2004 at 12:00 am

    #163018

    I am working on a project which requires centralised data collection. Project details are as below. I have one main SQL server and we have remotely installed MSDE2 with dynamic IP addresses. we want to exchange data between main server and remote sites. Products price stucture will be pusshed from main server and products sales detail will be pulles from remote site. remote sites are not permanantely connected to the serevr they will be connected via internet occasionaly and will perform post / get data activity. Please help about what type of replication i shall use and if replication is feasible in this situation.
    Regards.
    Imran

  • July 26, 2004 at 2:04 am

    #516041

    For pushing data from Main server I would use tranzactional replication.

    For pulling data from your MSDE2000 you have two options: merge or snapshot replication.

    I would use snapshot because is easier to configure and maintain.

    I have never tried running replication on dynamic IP ,always I used fixed IP.

    If you can make this work (running replication on dynamic IP) give more details.

  • July 26, 2004 at 3:56 am

    #516049

    If you will use both:

    Main site push data to sites

    Main site get data from sites

    You have two choises (as I see it). Either you use a two way transactional replication or merge replication. If the sites are mostly disconnected from the main site, you should use update on demand for the replication.


    robbac
    ___the truth is out there___

  • July 27, 2004 at 8:24 pm

    #516442

    Hi Imran

    I've been working on replication over the last couple of weeks, so have read a fair bit about it.

    I see you have two main questions:

    1. Which type of subscription to use?

    You will want to setup your MSDE2 servers (Subscribers) to use a Pull subscription from the main server (Publisher). This way when your MSDE2 users are online, the Subscriber machines are responsible for trying to connect to the Publisher and update.

    If you use a Push subscription, the Publisher/Distributor will fail every time one or more of the Subscribers are offline.

    2. Which type of publication to use?

    For data that is updated on the Publisher (master) server, and then a copied to the Subscribers, use Transactional Publication. That way, after they are setup, only changes are sent to them (I'm assuming some MSDE2 machines might be connected over a slow link, like a modem or something).

    For data that may be updated on both the Publisher and the Subscriber, Merge Publication would probably be the best. HOWEVER with Merge Publication, you will want to make sure your IDENTITY columns are created as NOT FOR REPLICATION, and you should make your IDENETITY INCREMENT step appropriately. This will prevent a subscriber from using an identity number someone else has used, and prevent a Replication Conflict, making your job easier.

    For example, if you have 1xPublisher and 10xSubscribers now and 5xPossible Future Subscribers, I'd make sure that the IDENTITY INCREMENT is say 20 (1 + 10 + 5 + more). Then set each of your subscribers to have a differnet IDENTITY SEED (eg: Publisher =1, Subsriber1 = 2, Subscriber2 = 3, etc).

     

    Julian Kuiters
    juliankuiters.id.au

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

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