Merge Replication Help.. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Merge Replication Help..

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!
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.
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.
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!
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.
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.
http://support.microsoft.com/kb/933831/
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.
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.
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?
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.
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!
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.
]]>