none
Replication of specific tables

    Pregunta

  • Hi
    I have 2 SQL servers SERVER1 and SERVER2 with different databases

    SERVER1 has table - MRData
    SERVER2 has table - MRData_New

    I want to estabilish replication between these 2 specific tables only.

    How can i achieve this. In the replication process, after i setup Publication, i get the option to select database for Subscriber. But i dont get an option to select the table in the database and map columns.

    Please advice how this can be achieved?

    Thanks.
    lunes, 25 de enero de 2010 9:44

Todas las respuestas

  • Merge can’t be used. For Merge table names should match.

     

    Still if you want to use Merge a possible solution would be to replicate with the same table name , have DDL triggers at the Subscriber on ‘MRDATA_NEW’ table which modifies the table created by replication on subscriber with name as ‘MRDATA’  and vice versa. In short you won’t be able to directly replicate ‘MRDATA_NEW’ table at the Subscriber but still you can replicate the changes by DDL triggers on ‘MRDATA_NEW’ which will populate the main replicated table ‘MRDATA’ at the Subscriber and replication will take care from here.

    I hope it’s not that confusing!!!

     

      

    Transactional Replication will probably allow this.

    While setting the Publication set the article properties and change the “Destination Object name” to the table name (‘MRDATA_New’) at Subscriber and set the property “Action if name is in use” to “Keep existing object unchanged” . Furthermore while creating the Subscription UNCHECK the “Initialize” option at the end of the Wizard (just to be on safer side  to avoid the drop and recreation of the table at the subscriber) . Doing this only incremental changes will be replicated once Replciation is all set. Just giving this plan thinking that 'MRDATA_NEW' already has some data which you do not want to loose.


    Regards Rishi Maini
    lunes, 25 de enero de 2010 10:04
  • Hi there,

    When you're creating the publication, after selecting the objects that will be replicated, you can define the destination object name, this allows you to replicate table MRData on Server1 to table MRData_New on Server2. I don't think you can define the columns mapping automatically, the columns in both tables should be equal.

    You can change the destination columns by changing the stored procedures that handle commands for that table.

    José Cruz

    lunes, 25 de enero de 2010 10:05
  • I dont think i am understanding either of you.

    When i create publication using the wizard, below are the steps i am guided through

    Choose database
    Select Publication type - TRANSACTIONAL publication
    Select Tables (Articles)
    any filters if you want to add
    Create Snapshot immediately
    Agent security settings
    Create publication
    Provide publication name

    No where in this setup i am asked to provide Destination details......

    Please provide me step by step info if possible...coz i dotn see any optiong to sleect a specific table in the Subscription database.

    It is only creating new table in the Destination database.


    lunes, 25 de enero de 2010 10:43
  • Choose database
    Select Publication type - TRANSACTIONAL publication
    Select Tables (Articles) --> Here is where you have a option on the right hand side with a drop down to set Article Properties .



    Regards Rishi Maini
    lunes, 25 de enero de 2010 10:45
  • Thanks i go this now. However i am stuck at a different level now.

    The replication required that the column names on both the tables are same (which is not the case in my current scenario)

    So on subscriber i created a view (v_MR_INT_MP5) for the table, aliased with column names to be matching same as the publisher table column names.

    After that when i run the replication, i am getting error. Please advice. Many thanlks.

    ------------------------

    Command attempted:

    exec sp_MSreset_synctran_bit @owner = N'dbo', @table = N'v_MR_INT_MP5'
    (Transaction sequence number: 0x0000000000000000001100000000, Command ID: 6)

    Error messages:

    Cannot release the application lock (Database Principal: 'db_owner', Resource: 'snapshot_delivery_in_progress_MOQDevelop') because it is not currently held. (Source: MSSQLServer, Error number: 1223)

    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. (Source: MSSQLServer, Error number: 3624)
    Get help: http://help/3624

    A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support. (Source: MSSQLServer, Error number: 3624)
    Get help: http://help/3624


    lunes, 25 de enero de 2010 12:05
  • If column  , table names are different , we are definitely making things complicated for Replicated environment .

     

    I would recommend that you go for the approach of same table/column name at Subscriber (simple replication) and then use DML triggers on the replicated table at Subscriber to populate the desired destination object. Does that sound feasible to you ?


    Regards Rishi Maini
    lunes, 25 de enero de 2010 12:37
  • Such a topology is not possible using merge replication, you are better off having a view over the table on the subscriber which represents the schema you want it to look like.

    With transactional replication and a custom sync object and custom script you are free to have completely different schemas of both sides, but you can't do bi-directional replication easily between the two.
    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    lunes, 25 de enero de 2010 16:04
  • My requirement is transactional replication
    Only in 1 direction - From publisher to Subscriber.....

    I dont know how to customize syn object..... and what custom scropt are we talking about. I usually setup replication only using WIZARD.

    Please advice...
    martes, 26 de enero de 2010 4:50
  • thats a pretty complicated scenario, the best choice for you would be the trigger at the subscriber. otherwise you should use the a custom stored procedure to map the columns the way you want, you can use it by sp_register_custom_scripting, define the name of the new stored procedure in the ins, update or delete statement

     

     

    sábado, 17 de abril de 2010 0:45