SQL Server Performance

Regarding Bulk Copy During Transactional Replicati

Discussion in 'SQL Server 2005 Replication' started by bajiprasadbehera, May 29, 2007.

  1. bajiprasadbehera New Member

    Hi,

    I got a very typical scenario in transactional replication.

    Let me explain my scenario.

    I#%92m doing transactional replication between two databases.

    When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

    My main intension was to create replication between different tables with different fields in which I got succeeded.

    But main problem is I want to stop this bulk copy from publisher to subscriber.

    Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.
    I don#%92t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

    I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

    As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)
    won#%92t get created in subscriber.

    Help me regarding the above scenario and I need it urgently.



    Regards
    Baji Prasad
  2. satya Moderator

    I would like toknow what is the problem in having this bulk copy?

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  3. bajiprasadbehera New Member

    hi,
    My client don't want bulk copy.

    he wants to create replication between two tables.which won't disturb the existing data in subscriber table.
    once replication has done.it won't have to disturb current data in subscriber if any new data added to publisher.automatically it has to be replicated to subscriber.
    it may sound different.but i don't have any other option.


    regards
    Baji Prasad.B
  4. satya Moderator

    I don't think you can achieve that, because depending on the type of replication you use, the table may be required to support features that prevent minimally logged bulk copy operations. For example, transactional replication depends on transaction log entries when replicating data, and merge replication changes the structure of the table and requires triggers to replicate data.

    If the client is loooking for performance based thenhttp://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tranrepl.mspx fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  5. MohammedU New Member

    quote:Originally posted by bajiprasadbehera

    Hi,

    I got a very typical scenario in transactional replication.

    Let me explain my scenario.

    I#%92m doing transactional replication between two databases.

    When publisher and subscriber created the data going to be bulk copied from publisher table to subscriber table.

    My main intension was to create replication between different tables with different fields in which I got succeeded.

    But main problem is I want to stop this bulk copy from publisher to subscriber.

    Scenario 1: my subscriber table may contain some previous data which will be replaced with publisher data due to bulk copy.
    I don#%92t want this .I want to avoid this bulk copy and wants to create procedures(for insert, update and delete transactions) in subscriber which will take care of replication.

    I achieved almost everything but not able to avoid this bulk copy during the creation of subscriber.

    As I know the only way I can stop bulk copy is by creating subscription without subscription agent. But here without subscription agent the procedures(for insert, update and delete transactions)
    won#%92t get created in subscriber.

    Help me regarding the above scenario and I need it urgently.



    Regards
    Baji Prasad

    If I understand correctly...
    you want to keep the existing data at the subscriber table and want to replicate new data from publisher to the same table...
    Right Click PUBLICATION/PROPERTIES/ARTICLES/select the TABLE/click ARITCLE PROPERTIES drop down list/select SET PROPERTIES OF HIGHLITED TABLE ARTICLE/
    Under the DISTINATION OBJECT/click the property ACTION IF NAME IS IN USE/ on the right hand side of the window you see the drop down list and select "Keep the existing table unchanged" or "Delete data in the existing table that matches the row filter statement" option based on your needs...




    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

Share This Page