SQL Server 2008 表更改(插入/更新/删除)通知推送到代理 [英] SQL Server 2008 table change (insert/update/delete) notification push on broker

问题描述

我有一个相当复杂的大型数据库，其中包含大约 3000 多个对象(表/触发器/sps 组合).我继承了这个数据库并重组它可能需要 3-4 年的时间.同时，我需要为这些表上的任何插入/更新/删除实现一个 pub sub 功能.给定数量的表和现有查询可能查询通知(和 SQL 依赖项)将不起作用.我正在寻找的是一种在服务代理上推送更改(表中更改的内容 - 例如记录 PK 和表名称)的方法，以便我可以使用外部激活器然后检索更改，然后从那时起使用我的自定义发布订阅

I have a rather complex and large database with about 3000+ objects (tables/triggers/sps combined). I inherited this DB and restructuring it is probably 3-4 years away. meanwhile, I need to implement a pub sub feature for any insert/update/delete on these tables. Given number of tables and existing queries probably query notification (and SQL Dependency) will not work. What I am looking for is a way to push the changes (what changed in table - like records PK and table name) on the service broker so I can use external activator to then retrieve change,and then use my custom pub sub from that point onwards.

除了在服务代理上推送更改通知的方式之外，我几乎已经排好了所有的鸭子.

I have pretty much all the ducks lined up except for the way to push change notification on service broker.

感谢任何帮助/指示.谢谢.NM附注.我确实环顾四周并找到了一些类似的帖子，但是他们提到的 MSDN 文章似乎都被删除了 - 不确定 MSDN 站点上发生了什么.

Any help/pointers are appreciated. Thanks. N M PS. I did look around for similar postings and did come across a few however, MSDN articles they referred to seem to have all removed - not sure what's going on on MSDN site.

推荐答案

有关外部激活器，请查看 Microsoft SQL Server 2008 功能包-Microsoft SQL Server 2008 R2 Service Broker 外部激活器".

For external activator look at Microsoft SQL Server 2008 Feature Pack- "Microsoft SQL Server 2008 R2 Service Broker External Activator".

对于控制台应用程序(处理消息)，最好的办法是关注 codeplex.有很好的例子.

For console application (that processes messages) great idea is to drop an eye in codeplex. There is good examples.

放置事件通知(通知，将被外部激活器服务使用)代码如下所示:

To put event notification (notifications, that will be used by external activator service) code looks something like this:

Create Queue ExternalActivatorQueue;
Create Service ExternalActivatorService On Queue ExternalActivatorQueue
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])

Create Event Notification NotifyExternalActivator
On Queue dbo.ProcessQueue
For QUEUE_ACTIVATION
To Service 'ExternalActivatorService', 'current database'

在队列中发送消息:

    Declare @h UniqueIdentifier;
    Declare @x xml = '<tag/>';
    Begin Dialog Conversation @h
    From Service MyTableService
        To Service 'ProcessService'
    With Encryption = OFF;

    Send On Conversation @h(@x)

我为使其工作所做的所有步骤是这里，但只是在拉脱维亚语:).实际上几乎有你需要的东西(当数据插入表时触发发送消息......).

All steps i done to make it work is here, but just in Latvian :). There actually is almost what you need (trigger that sends messages when data are inserted in table..).

这篇关于SQL Server 2008 表更改(插入/更新/删除)通知推送到代理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！