locked
Merge Replication with an existing uniqueidentifier column RRS feed

  • Question

  • Environment: SQL Server 2000

    I am using a merge replication to replicate data from one server to another. I have added a new table which has a primary on a uniqueidentifier column (Name of the column is rowguid). When I added this table to merge replication, replication created another uniqueidentifier column called (rowguid12). Any data populated to this table is not replicating to the subscribers. What should I check? Is there a problem when a table has more than one uniqueidentifier column or is there a problem naming my own column called "rowguid"

    Please Help.

    Thanks

    Shafiq

     


    Shafiq
    Friday, April 30, 2010 1:04 PM

Answers

  • For your second problem - where data is not being replicated you need to check for status messages in replication monitor.

    Note that if you bcp data into your table, or use SSIS/DTS data may not be replicated.

     

    http://support.microsoft.com/kb/275680


    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
    Saturday, May 1, 2010 1:19 PM
    Answerer
  • Shafiqm, to have replication use the existing rowguid column you must do the following:

     

    1) make sure the datatype is quid or uniqueidentitier

    2) ensure it has a unique constraint with a default of a newsequentialid() for the rowguid column.

    3) ensure that the uniqueidentifier column has the  ROWGUIDCOL  attribute on it.

     

    Here is an example of how to do it.

     

    Create
     Table
     MyTable2(pk uniqueidentifier
    , col1 char
    )
    GO
    alter table MyTable2 alter column pk uniqueidentifier not null
    GO
    alter table MyTable2 alter column pk add rowguidcol
    GO
    alter table MyTable2 ADD CONSTRAINT MergeDefault default newsequentialid() for pk
    GO

    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
    Saturday, May 1, 2010 1:16 PM
    Answerer

All replies

  • shafiq,

    there is no problem with using rowguid as a column name with uniqueidentifier in a merge replication.  The merge engine can create its own column or use the same one you have depending on that particular column's settings.  if none of the data is populating to your subscribers then you have a different problem.

    i'll be more than happy to help you out if you see any error msgs and are able to elaborate more.

    Friday, April 30, 2010 8:36 PM
  • Hi Shafiq,

    Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one.

    If you want to add that column by your own and let SQL Server use it in merge replication, your rowguid column should have the following properties:

    - Should be of Uniqueidentifier datatype
    - Should have the ROWGUIDCOL property set and a unique index
    - Should not allow NULLs
    - Should have a default of newid()

    Regarding the second part of the question, Once the article is added to an existing publication you have to make sure the existing subscribers to this publication receive it.


    for PUSH subscriptions use:  Execute sp_addsubscription as shown in the following example on the publishing database:

    exec sp_addsubscription
    @publication = N'PublicationName', @article = N'TableName',
    @subscriber = N'SubscriberName',
    @destination_db = N'DatabaseName',
    @sync_type = N'automatic',
    @update_mode = N'read only'
    GO


    for PULL subscriptions use: Execute sp_refreshsubscriptions on the publishing database as shown in the following example:

    sp_refreshsubscriptions 'PublicationName'
    GO


    Try to use Replication Monitor to monitor the activity of each replication since you can find below a link about "How to troubleshoot SQL Server merge replication problems" http://support.microsoft.com/kb/315521

    Regards,

    Tarek Ghazali

    SQL Server MVP

    Saturday, May 1, 2010 11:46 AM
  • Thanks Tarek for the detailed information. I just added the table in an existing replication by just going to ariticles and checked the table. I have a question regarding the following

    exec sp_addsubscription
    @publication = N'PublicationName', @article = N'TableName',
    @subscriber = N'SubscriberName',
    @destination_db = N'DatabaseName',
    @sync_type = N'automatic',
    @update_mode = N'read only'
    GO

    Please confirm by running the above will add a new push subscription or it will add the new table in the existing push subscription.

    Thanks

    Shafiq

     


    Shafiq
    Saturday, May 1, 2010 12:28 PM
  • Shafiqm, to have replication use the existing rowguid column you must do the following:

     

    1) make sure the datatype is quid or uniqueidentitier

    2) ensure it has a unique constraint with a default of a newsequentialid() for the rowguid column.

    3) ensure that the uniqueidentifier column has the  ROWGUIDCOL  attribute on it.

     

    Here is an example of how to do it.

     

    Create
     Table
     MyTable2(pk uniqueidentifier
    , col1 char
    )
    GO
    alter table MyTable2 alter column pk uniqueidentifier not null
    GO
    alter table MyTable2 alter column pk add rowguidcol
    GO
    alter table MyTable2 ADD CONSTRAINT MergeDefault default newsequentialid() for pk
    GO

    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
    Saturday, May 1, 2010 1:16 PM
    Answerer
  • For your second problem - where data is not being replicated you need to check for status messages in replication monitor.

    Note that if you bcp data into your table, or use SSIS/DTS data may not be replicated.

     

    http://support.microsoft.com/kb/275680


    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
    Saturday, May 1, 2010 1:19 PM
    Answerer
  • Hi Hilary,

    Thanks very much for highlighting the issue with DTS (Use Fast Load Option). I have uncheck the option and on next run, all records are now replicating

    Thanks

    Shafiq

     

     


    Shafiq
    Sunday, May 2, 2010 12:53 PM