SQL Server Performance

Merge Replication Help..

Discussion in 'SQL Server 2005 Replication' started by sql_jr, May 8, 2007.

  1. sql_jr New Member

    Hi,

    Need some help on merge repl.
    First, in general, how are changes to the schema handled?
    If a column or table definition changes, is this automatically handled? Is this is done via the snapshot agent?

    Second, I get this message when it broke, please explain:
    --------------------------------
    Error messages:
    The schema script 'if object_id(N'[dbo].[tblAlertsRules]') is not null exec('ALTER TABLE [dbo].[tblAlertsRules] ADD CONSTRAINT
    PK_tblAlertsRules_1 PRIMARY KEY CLUSTERED
    (
    AlertRuleID
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ')' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
    -----------------------------------
    Third, I heard there is a bug that is fixed in SP2 - any info on that?
    THX!
  2. satya Moderator

    http://msdn2.microsoft.com/en-us/library/ms143733.aspx
    http://msdn2.microsoft.com/en-us/library/ms143550.aspx
    http://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=238

    http://download.microsoft.com/downl...7-423d-bc8f-b11ecd4195b4/ReadmeSQL2005SP2.htm - fyi on the changes with SP2.

    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. sql_jr New Member

    Thx, Satya!

    Another question is ID cols. If the ID col is set NOT FOR REPL (the default), and the application is searching on or is a foreign key in another table, the app will break. What is the solution? To recode to search on the ms_repl_guid unique identifier? Please advise.
  4. sql_jr New Member

    Update:

    OK, my current understanding is that SQL 2005 claims that DDL changes ARE propagated during synchronization of the distribution/merge agent, this change is replicated to the subscriber as the same 'ALTER TABLE' statement, along with the requisite stored procedure changes (transactional) and metadata/trigger changes (merge). These DDL changes must always be made at the publisher and never at the subscriber, and this also applies to republishing scenarios, where the schema change will be automatically propagated from the publisher to the publisher-subscriber and finally to the subscriber.

    However, according to this KB: FIX: A schema change that is made to a published table may not be propagated to subscriptions in SQL Server 2005 prior to SP2. <-- I think this is the issue I'm having on SP1 SQL 2005.

    Does the above sound correct? Thx!

    Still, please answer my Q above about the ID cols....Best forum on the internet!

  5. MohammedU New Member

    From KB article...not always...

    If you start a synchronization of the subscriber by using the View Synchronization Status window, the status of published articles is changed. Therefore, successive schema changes to the published articles are not propagated during synchronization.

    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

    Specify the KBA you are referring above, I believe there are fixes in SP2 in thsi case.

    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.
  7. MohammedU New Member

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


    MohammedU.
    Moderator
    SQL-Server-Performance.com

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

    I knew it,
    quote:Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section. This problem was first corrected in SQL Server 2005 Service Pack 2.
    and prior to SP2 this has few issues for the propogation.

    With regard to the ID question: For merge replication, the allocation of identity ranges has been redesigned. The range threshold is no longer used, and each node is allocated a primary range and a secondary range of identities.
    Refer tohttp://msdn2.microsoft.com/en-us/library/ms152543.aspx &http://www.replicationanswers.com/AlterSchema2005.asp links in this case.

    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.
  9. sql_jr New Member

    Thx, guys! I have an update and a question/confirmation

    First, I asked a diff Q on the ID: "If the ID col is set NOT FOR REPL (the default), and the application is searching on or is a foreign key in another table, the app will break. What is the solution? To recode to search on the ms_repl_guid unique identifier? Please advise."

    UPDATE:
    I applied Service Pack 2 to both sides, reinitialized snapshot/resynch. Then I made a schema chang (via SSM), and voila, it WAS indeed propogated to the subscriber (so it in fact seems that was the issue! - very nice feature)
    Question: If the db's are compatibility level 80 (2000) on the SQL 2005 server, would the DDL changes STILL be propagated? One would think no, but it seems that it does. Thoughts?
  10. satya Moderator

    Yes it does as its applicable too.

    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.
  11. sql_jr New Member

    Awesome! Unless I should open a new thread, what is the way to handle potential conflicts for keys and identity columns?

    ie, I have an existing app I need to replicate. If table A has a pk id and is a foreign key in table B at the publisher, what if the app queries the the db on Subscriber, the values will be different, right? :-{ THX SO MUCH!
  12. satya Moderator

    See the links that are referred above as the conflict is explained over there.

    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.

Share This Page