问题[service-broker](dba)

问题：

在运行 sqlpackage.exe 以应用 sql dacpac 时，它偶尔会在代理/SignalR 相关对象上失败，这会破坏构建：

*** Could not deploy package.
Error SQL72014: .Net SqlClient Data Provider: Msg 15151, Level 16, State 1, Line 1 Cannot drop the service 'Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver', because it does not exist or you do not have permission.
Error SQL72045: Script execution error.  The executed script:
DROP SERVICE [Xxxxxx_xxxxx_0de7d69e-48ac-4a2b-95ef-d758d69b2a1e_Receiver];

这是我们的sqlpackage.exe命令行参数：

/p:TreatVerificationErrorsAsWarnings=True /p:DoNotAlterChangeDataCaptureObjects=True /p:DropIndexesNotInSource=True /p:DropObjectsNotInSource=True /p:ExcludeObjectTypes=Users;Logins;RoleMembership;Permissions;Credentials;DatabaseScopedCredentials;LinkedServerLogins;LinkedServers /p:BlockOnPossibleDataLoss=False

同样，失败率是每 10 次运行中的 1 次，并且没有人手动删除这些对象，因此我不知道为什么如果它知道它就无法删除该对象。有什么好的解决方案可以使其始终如一地应用吗？

我发现了一些处理这个问题的旧帖子（5 岁或以上），但我对这个过程感到困惑，或者这些信息是否过时，因此我提出了新问题。我在 sql 2016 SP2-CU11 企业版上。

我需要在可用性组中的数据库上启用服务代理。它的大小为 1.8TB，所以我真的不想把它完全从 AG 中取出，如果可以避免的话，我真的不想用新的备份重新播种它。一些较早的帖子说您必须完全删除数据库并将其重新添加到 AG 中，其他人引用只是运行一些代码来创建端点，以及我以前从未遇到过的路由。

下面的过程是在未启用的 AG 中的数据库上启用服务代理的唯一方法吗？

1. 删除数据库的辅助副本，然后从 AG 中删除主副本。

2. 运行以下代码以激活服务代理：

   ALTER DATABASE XXXXXX SET ENABLE_BROKER；去

3. 进行完整备份，将其还原到辅助服务器，无需恢复。

4. 将数据库加入辅助节点上的 AG。

突然我的数据库 .mdf 文件大小增加到超过 250GB，但我的总表数据大小计数仅为 4GB。我做了数据收缩，但没有结果。然后我做了下面的查询：

    SELECT sch.[name], obj.[name], ISNULL(obj.[type_desc], N'TOTAL:') AS [type_desc],
       COUNT(*) AS [ReservedPages],
       (COUNT(*) * 8) / 1024.0 / 1024.0 AS [ReservedGB]
FROM sys.dm_db_database_page_allocations(DB_ID(), NULL, NULL, NULL, DEFAULT) pa
INNER JOIN sys.all_objects obj
        ON obj.[object_id] = pa.[object_id]
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = obj.[schema_id]
GROUP BY GROUPING SETS ((sch.[name], obj.[name], obj.[type_desc]), ())
ORDER BY [ReservedPages] DESC;

它显示了这个结果：

我有一个服务经纪人。

任何建议删除或如何最小化 queue_message 表大小？

SELECT COUNT(*) FROM sys.conversation_endpoints;从这里开始计数为 7，Dan 的答案中的查询返回 4 activation_procedure 和 4 返回 NULL。

• dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Receiver有 361840332 行，is_retention_enabled0，激活过程 - NULL
• dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_Sender有 0 行 is_retention_enabled0 和激活过程[dbo].[dbo_Attendances_ee941a6a-d4e9-484c-b3d6-4922604ec585_QueueActivationSender]

情况：我使用Service Broker开发数据推送方式。

现在我考虑一个场景：

1. 使用具有服务、队列和存储过程的单独数据库（称为代理）来发送数据。
2. 在必要的数据库和表上使用触发器，将数据转换为 JSON 并从Broker数据库执行 SP以发送数据。我在 I/U/D 的每个表中有 39 个数据库/264632 个表/单独的触发器。793896 触发。是的，我知道它很大，但我们有这样的数据模型，我无法更改它。
3. 现在我不使用激活存储过程，因为一些客户端会消耗来自 SB 目标队列的数据。

在Broker数据库中安装 Service Broker 的脚本：

-- installation
use master
go
if exists ( select * from sys.databases where name = 'Broker' )
begin
    alter database [Broker] set restricted_user with rollback immediate;
    drop database [Broker];
end
go
create database [Broker]
go
alter database [Broker] set enable_broker with rollback immediate;
alter database [Broker] set read_committed_snapshot on;
alter database [Broker] set allow_snapshot_isolation on;
alter database [Broker] set recovery full;
go
use [Broker]
go
create message type datachanges_messagetype 
  validation = none;
go
create contract datachanges_contract ( datachanges_messagetype sent by initiator );
go
create queue dbo.datachanges_initiatorqueue
  with status = on
     , retention = off
    , poison_message_handling ( status = on )
  on [default];
go
create queue dbo.datachanges_targetqueue
  with status = on
     , retention = off
     , poison_message_handling ( status = on )
  on [default];
go
create service datachanges_initiatorservice 
  on queue datachanges_initiatorqueue
  ( datachanges_contract );
go
create service datachanges_targetservice 
  on queue datachanges_targetqueue
  ( datachanges_contract );
go
-- conversation additional table
create table dbo.[SessionConversationsSPID] (
    spid int not null
  , handle uniqueidentifier not null
  , primary key ( spid )
  , unique ( handle )  
)
go
-- SP which is used to send data from triggers
create procedure dbo.trackChanges_send
  @json nvarchar(max)
as 
begin
    set nocount on;

    if ( @json is null or @json = '' )
    begin
        raiserror( 'DWH Service Broker: An attempt to send empty message occurred', 16, 1);
        return;
    end

    declare @handle  uniqueidentifier = null
          , @counter int = 1
          , @error   int;

    begin transaction

    while ( 1 = 1 )
    begin

        select @handle = handle
          from dbo.[SessionConversationsSPID]
          where spid = @@SPID;

        if @handle is null
        begin

            begin dialog conversation @handle 
                from service datachanges_initiatorservice
                to   service 'datachanges_targetservice'
                  on contract datachanges_contract
                with encryption = off;

            insert into dbo.[SessionConversationsSPID] ( spid, handle )
              values ( @@SPID, @handle );

        end;

        send on conversation @handle 
          message type datachanges_messagetype( @json );

        set @error = @@error;

        if @error = 0
          break;

        set @counter += 1;

        if @counter > 5 
        begin
            declare @mes varchar(max) = 'db - ' + @db + '. schema - ' + @sch;
            raiserror( N'DWH Service Broker: Failed to SEND on a conversation for more than 10 times. Source: %s. Error: %i.', 16, 2, @mes, @error );
            break;
        end

        delete from dbo.[SessionConversationsSPID]
          where handle = @handle;

        set @handle = null;

    end

    commit;
        
end
go
-- And dialogs creation to mitigate hot spot problem on sys.sysdesend table.
-- Described here: https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008/dd576261
declare @i int, @spid int, @handle uniqueidentifier
select @i = 0, @spid = 50;

while (@i < 150*3000) -- 450000 dialogs
begin
       set @i = @i + 1
       begin dialog @handle 
         from service datachanges_initiatorservice
         to service 'datachanges_targetservice'
         on contract datachanges_contract
        with encryption = off;

       if ((@i % 150) = 0)
       begin
              set @spid += 1;
              insert into dbo.SessionConversationsSPID ( spid, handle ) values (@spid, @handle)
       end
       
end

用户数据库中的典型触发代码：

create trigger [<SCHEMA>].[<TABLE>_TR_I]
    on [<SCHEMA>].[<TABLE>]
    with execute as caller
    after insert
    as
    begin 
    
        set xact_abort off;
        set nocount on;
    
        declare @rc int = ( select count(*) from inserted );
    
        if ( @rc = 0 )
        begin
            return;
        end
    
        begin try

            declare @db_name sysname = db_name();

            declare @json nvarchar(max);
    
            set @json = (
                select getutcdate() as get_date, ''I'' as tr_operation, current_transaction_id() as cur_tran_id, ''<TABLE>'' as table_name, @@servername as server_name, @db_name as db_name, ''<SCHEMA>'' as tenant_schemaname
                      , *
                    from inserted
                    for json auto, include_null_values
            );
    
            exec dbo.trackChanges_send
                 @json = @json;
    
        end try
        begin catch 
            
            declare @error_message nvarchar(max); 
            set @error_message = ''[''       + isnull( cast( error_number()    as nvarchar( max ) ), '''' ) +''] ''
                                             + isnull( cast( error_severity()  as nvarchar( max ) ), '''' )
                                +'' State: ''+ isnull( cast( error_state()     as nvarchar( max ) ), '''' )
                                +'' Trigger: '' + ''[<SCHEMA>].[<TABLE>_TR_I]''
                                +'' Line: '' + isnull( cast( error_line()      as nvarchar( max ) ), '''' )
                                +'' Msg: ''  + isnull( cast( error_message()   as nvarchar( max ) ), '''' );
    
            raiserror( ''DWH Service Broker: An error has been occured while sending data changes. Error: %s'', 0, 0, @error_message ) with log;
    
            return;
        end catch
    end
    go

所以，我的问题是：

1. 有时我会在触发器执行期间看到很长的 PAGELATCH_EX / PAGELATCH_SH 等待。问题是在 target_queue 表上等待闩锁。我不明白为什么当我将数据发送到 Service Broker 时，我会在目标队列dbo.datachanges_targetqueue上看到某种热点。我会理解是否存在与发送系统表或传输队列相关的等待。我在resource_description列 看到目标队列：

使用dbcc 页面，我看到该页面属于sys.queue_messages_597577167，它是 dbo.datachanges_targetqueue 的包装器。那一刻的等待会话总数约为 450，因此它可能是一个瓶颈。

同时，跟踪触发器执行的 xEvent 会话告诉

在那段时间触发器执行的时间很长（超过 10 秒，而通常少于 1 秒）。它发生在随机时间，所以我在这里看不到任何依赖

2. 我的第二个问题也与锁定有关。而且它发生在随机时间。我从目标队列（外部客户端仿真）读取数据的假脚本是

declare @i int = 0;
while ( 1 = 1 )
begin

    declare @mb varbinary( max );
    receive top ( 1000 ) @mb = message_body from dbo.datachanges_targetqueue
    
    set @i = @@rowcount;
    
    if @i = 0
      break;

end

由于触发活动，它也可以被阻止定期执行。我不明白为什么。

3. 可以使用一个队列和约 800000 个触发器吗？:) 我的意思是也许我需要考虑一些门槛。

4. 使用“我的”方法（一个数据库是发送者和目标）或使用“每个数据库都是发送者和一个目标”的优点/缺点是什么

SQL Server 有 Service Broker，它提供实例之间的消息传递和排队功能。

我是使用这项技术的新手，想知道它在实例之间发送哪种消息。我的意思是它试图通过发送消息来传达什么信息，以及其他实例如何理解该消息的内容。

我的组织已经成功使用服务代理、对话安全性几年了。

我有一个我想配置的新服务代理实例，但我相信我遇到了这样一种情况，即新代理实例被错误地放入数据库的顺序（现有代理实例就位）导致新代理实例失败。

我的错误在目标服务器上；找不到证书。无法检索安全上下文。

我的情况是：源服务器 A (dmz) 发送到目标服务器 B，其中已经存在代理实例以两种方式但在不同的数据库上发送消息。

测试 1：我调换了源服务器和目标服务器的角色，使服务器 B 成为我的源，服务器 A (dmz) 成为我的目标，同时使用相同的数据库。这是成功的。

测试 2：我删除了测试 1 实例并在每台服务器上创建了 2 个新数据库。我再次重新配置了源和目标角色，现在从服务器 A (dmz) 发送到服务器 B，这是成功的。新数据库中只配置了一个服务代理实例，因此放置顺序不是问题。

过去，当我们第一次设置开发/测试环境时，我遇到过这种情况；但是，情况略有不同，因为许多其他代理实例也在失败。不是全部，而是一些。一旦我意识到发生了什么，删除所有实例并以特定顺序重新创建它们，源实例在目标实例之前，情况就解决了。

我认为服务代理会自动在正确的订单位置添加一个新实例。

我认为必须在特定订单内配置代理实例是否正确？如果是这样，我如何在不中断现有代理的情况下添加新的代理实例？

请分享您的想法和建议。

我将数据库从 SQL Server 2014 Enterprise 还原到 SQL Server 2017 Standard。

还原过程顺利，没有任何问题/错误。

然后，我在数据库上运行 DBCC CHECKDB，并收到以下与服务代理相关的消息。理解这一点的任何帮助表示赞赏。

消息 8997，级别 16，状态 1，第 1 行 Service Broker 消息 33305，状态 1：引用 28671 的多播消息正文行的引用计数值应为 0。

在配置两个 Service Broker 实例成功将 msg 发送到单独数据库上的远程服务器后，我试图在相同的成功数据库之一上设置第二个 Service Broker 实例。

我仅在 ssbdiagnose 上收到错误。没有其他工具显示错误。2 在源服务器上运行时来自 ssbdiagnose 的错误。

1. 'target IP' 'targetDatabaseName' 用户 '' 使用的用于对话安全的证书 'source cert name' 未部署在数据库中。
2. '目标 IP' 'targetDatabaseName' 无法使用证书将 '源 IP' 上的 sourceDatabase '' 中的用户 '' 映射到此数据库。

第一个错误消息中的源证书名称是我以前在源服务器上存在的证书，用于将消息发送到目标服务器上的不同数据库。

所以它似乎在查看错误的证书。

在目标服务器上运行 ssbdiagnose 时，出现以下错误：

1. 登录失败。登录来自不受信任的域，不能与 Windows 身份验证一起使用。

谢谢

我的目标服务器没有收到消息，并且目标服务器错误日志中显示以下错误。Service Broker 登录尝试失败并出现错误：“连接握手失败。没有兼容的加密算法。状态 22。

我正在使用服务代理传输安全 - 基于证书的身份验证。我的主数据库密钥和证书已设置，权限已授予 broker_endpoint。我的用户数据库密钥和证书已设置，权限已授予服务。

我在每台服务器上的端点都配置了证书，具有主数据库证书的权限，并且我的所有数据库都由同一用户进行身份验证。

我还在目标服务器上的 Sql Trace 中收到错误：找不到证书。

这里的问题是它在同一行记录的服务，在 sql 跟踪中，是针对在源服务器上配置的不同服务代理，我在对话中没有引用。

当我在两台服务器上查看 sys.certificates 时，所有证书都与正确的 issuer_name 相关联。

任何帮助表示赞赏。

谢谢

在创建一些服务代理对象后，我在部署最新版本的数据库解决方案时遇到问题。我认为这可能与没有在数据库上启用服务代理有关，因为它是作为构建的一部分创建的，然后立即部署。

我在哪里可以设置“启用代理”选项，以便在部署之前执行它，但在 SQLPackage 创建数据库之后？