Replication sample program RRS feed

  • Question

  • Hello i am new to SQL Replication.

    I have a application in which Publisher node insert/update/delete in a SQL server data base on it's own node.

    I have 3-4 different nodes which has SQL servers.

    My reqirement is that when ever i insert/update/delete on the sql server in the publisher node, all the subscriber should insert/update/delete the same data in their respective data base.

    so in short, all the publishers and subscribers should have same set of data. note that the subscribers are not doing any additional changes in their databases.

    Also , at some point of time (when there is a fail over in the subscriber database), while restarting the subscriber database, i need to first synchronize all the data which were lost during down time with the publisher database and then onwards continue replication.
    I will term this as startup synchronization step.

    Can i use the SQL Transactional replication for this purpose? DO i need some other mechanism for startup synchronization step.

    For example in the publisher i run this query,
    Insert into customer(customerid,name,itemname) Values(20,'faith','speaker')

    Once this query is executed on the publisher, i want to see that all the customer tables on the various subscribers node should have this record (20,'faith','speaker').

    Is Replication is good approach or any thing else is suggested.

    Can any body point to some sample tutorial or sample script to perform this kind of operation using Replication

    Thanks a lot in adavance

    Wednesday, April 12, 2006 8:14 AM

All replies

  • Yes. Transactional replication is good enough to implement your scenario.

    Whenever your data change happened on the publisher, subscriber will get the same change immediately (just default setting , continuous mode for both log reader and distribution agents). As for your "startup synchronization step", you can just resynchronize subscription, publisher present data will wrap up and load to subscriber through snapshot and distribution agent.

    Here is transactional replication introduction in SQL Books On Line. It will also point you to implementation sample through UI and/or scripts.




    Wednesday, April 12, 2006 6:17 PM
  • Hello, Thanks for the info, it was really useful.

    I have an extra complexity involved in my application. That is I have more than one set of publishers which are redundant to overcome the fail over situation.

    When a particular publisher goes down , all the subscribers which were connected to the this failed publisher node, should now switch to a new publisher(before starting the replication , this subscriber should synchronise it self with the new publisher).

    all the publishers independently updating there databases from a redundant lagacy application. that means i don't need to implement redundancy of publishers using the Replication.

    I just should be able to synchronise the subscriber with the new publisher, assuming that the new publisher has the latest data (It is same as the .

    There on, i should replicate the data from the new publisher to the subscriber.

    Please proveide some details on how to perform this mixed approach.

    Thanks in advance for your valuable feed back.

    Tuesday, April 18, 2006 8:29 AM