SQL Server 2005 复制
环境： SQL Server 2005 SP2 (9.0.3077) 事务性出版物（生产版和测试版）
我遇到的情况是，我有两个不同的复制出版物设置，它们使用一些相同的文章。 这些出版物中的每一个都为不同机器上的订户提供服务。 这些共享文章之一是一张表格。 该表中的许多记录每隔一定时间就会老化并不再需要。 这时候就会调用一个删除记录的存储过程。
为了节省资源并缩短订阅者的延迟时间，我将此存储过程的复制属性设置为“执行存储过程”，而不是默认的“仅存储过程定义”。 这样，当存储过程删除 2,000,000 多条记录时，这些记录不会复制到订阅者。 相反，存储过程的执行会被复制，并且在订阅者上执行相同的复制存储过程，并删除相同的 2,000,000 多行。
我遇到的问题是我的第二次出版物。 我不需要这种类型的行为，因此我将存储过程上的文章属性设置为“仅存储过程定义”，并期望复制删除其他订阅者处的行，但事实并非如此。 订阅者的桌子不断增加记录。 因此，为了解决这个问题，我将文章属性设置为“执行...”并称其为良好。 这可能是最好的解决方案，因此测试版与生产相匹配，但它仍然感觉像是一个拼凑，因为发布属性应该彼此独立工作。
问题：为什么“存储过程的执行”文章属性优先并应用于其他发布，即使它在其他发布中设置为“仅存储过程定义”？
Environment:
SQL Server 2005 SP2 (9.0.3077)
Transactional Publications (Production and Beta)
I have a situation where I have two different Replication Publications setup that use some of the same Articles. Each of these Publications feeds a subscriber on a different machine. One of these shared Articles is a table. At a regular time interval many of the records in this table become aged and no longer needed. At this time a stored procedure that deletes records is called.
To save on resources and improve latency times to the subscribers I have set the replicate property on this stored procedure to “Execution of the stored procedure” instead of the default “Stored procedure definition only”. This way when the stored procedure deletes 2,000,000+ records these don’t replicate down to the subscribers. Instead the execution of the stored procedure is replicated and the same replicated stored procedure on the subscribers is executed and it deletes the same 2,000,000+ rows.
The problem I’m having is with my second publication. I didn’t need this type of behavior so I left the article property on the stored procedure set to “Stored procedure definition only” and was expecting replication to remove the rows at the other subscriber but it wasn’t. The table at the subscriber just kept gaining records. So to fix it I set the Article Property to "Execution..." and called it good. Which is probably the best solution so beta matches production, but it still feels like a kludge as the publication properties should work independently of each other.
Question: Why does the “Execution of the stored procedure” article property take precedence and get applied to the other publication even though it is set to “Stored procedure definition only” in the other publication?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我已经很长时间没有主动管理复制了，但我怀疑答案与日志阅读器的体系结构有关，并且您正在出版物之间共享一篇文章。 我的理解是，日志读取器将遍历日志并查找对已复制项目的操作。 根据项目设置，对数据的单独更改可能会发布到分发数据库中的表中，或者会发布过程调用的记录。 无论如何，这是文章的属性，而不是文章所属出版物的属性。 我假设（但尚未测试和验证）您可以在同一个数据库对象之上创建多篇文章，并使用 @type='logbased' 复制一篇文章，使用 @type='proc exec' 复制另一篇文章
。半信半疑：虽然我现在使用 SQL 2008 进行开发，但我最后一次使用复制进行任何操作是在 SQL 7 上。
pjjH
It has been a long time since I actively administered replication but I suspect the answer has to do with the architecture of the log-reader and that you are sharing an article between publications. My understanding is that the log-reader will trawl through the log and look for operations on items that are replicated. Depending on the article settings, the individual changes to the data may be posted to a table in the distribution database or a record of the procedure invocation will be posted. In any case, this is a property of the article and not the publication(s) that the article is a member of. I assume (but have not tested and verified) that you can create multiple articles on top of the same database object and have one be replicated with @type='logbased' and the other with @type='proc exec'
Take all of this with a large pinch of salt: although I now develop on SQL 2008, the last time I did anything with replication was SQL 7.
pjjH
我们在公司中广泛使用复制，因为我们在多个国家/地区拥有 38 个仓库，所有数据都复制回我们位于伦敦的主服务器。
首先，您的复制过滤器应该使用视图，即使是简单的视图。 这样，如果您需要调整过滤器（阅读 WHERE 子句），您只需更改视图即可完成。 否则你必须重新发布你的数据，并重新订阅每个人，这可能是一个真正的痛苦。
您提到您在订阅者和发布者上运行相同的删除以保持它们同步。 这让我脊背发凉。 您最好在一处删除它们并让服务器将所做的更改复制到订阅者。 自 SQL Server 2005 以来，复制现在非常快速且高效。 SQL 2000 的复制速度过去和现在都相当慢。 如果您使用 SQL 2005/2008，只需确保您的兼容性级别（右键单击数据库、属性、选项）设置为 90 (2005) 或 100 (2008)。 这会将 sql server 切换到快速高效的复制方法。
另一种方法是不删除数据，而是保留数据并使用发布中的 where 子句将其过滤掉。
We use replication extensively in our company as we have 38 warehouses in several countries all replicating back to our primary server in London.
Firstly, your replication filters should use Views, even the simple ones. That way, if you need to adjust the filter (read WHERE clause), you just need to alter the view and your done. Otherwise you have to re-publish your data, and re-subscribe everyone which can be a real pain.
You mentioned that you run the same delete on both subscriber and publisher to keep them in-sync. This sends shivers down my spine. Your far better off deleting them in one place and letting the server replicate out to the subscribers the changes made. Since SQL Server 2005, replication is very fast and efficient now. SQL 2000 was and is quite slow for replication. If your using SQL 2005/2008, just make sure your compatibility level (right click on db, properties, options) is set to 90 (2005) or 100 (2008). This switches sql server over to the fast and efficient replication methods.
Another way is to not delete the data, but to keep it and filter it out using a where clause in the publication.