Service broker ms sql что это
Автор: Роджер Дженнингс (Roger Jennings)
Опубликовано: 02.11.2006
Service Broker (SSB), новый компонент SQL Server 2005, интегрирует асинхронное программирование, очереди и надежный обмен сообщениями с СУБД.
Я начну с краткого описания возможностей SSB и демонстрации простого сценария обмена сообщениями с помощью T-SQL-скриптов, приведенных в примере кода, сопровождающем статью. Затем я покажу как использовать Service Broker для реализации уведомлений о запросах, также известных, как оповещения об изменениях в БД (DCN, см. Дополнительные ресурсы). DCN сейчас является наиболее распространенным Service Broker-приложением, поскольку позволяет Web-разработчикам обновлять кэш страниц ASP.NET 2.0 с оптимальным интервалом, для гарантии корректности данных. Я покажу также, как реализуются оповещения о событиях.
Пример кода включает проекты для SQL Server Management Studio (SSMS), демонстрирующие простое и сложное SSB-приложения, Query Notifications, Event Notifications и Database Mail. Для запуска большинства примеров вам потребуется SQL Server 2005 Developer Edition или выше, так как SQL Server Express (SSX) включает только клиентские компоненты SSB. В SSX также отсутствует поддержка Database Mail. SSB-клиент получает или отправляет сообщения только через более старшие версии SQL Server 2005. Вместе с тем, SSX поддерживает Query Notifications.
Сервисно-ориентированная архитектура (SOA) использует асинхронный обмен сообщениями для поддержки слабосвязанных приложений, поэтому Microsoft предлагает SSB в качестве инфраструктуры для сервисно-ориентированной архитектуры БД (Service-Oriented Database Architecture, SODA). Подход SODA напоминает Windows Communication Foundation (WCF, ранее Indigo), Microsoft Message Queue (MSMQ) и приложения BizTalk Server. Вы можете использовать SODA для управления потоками данных с помощью экземпляров хранимых процедур, которые активируются при получении первого из одного или более сообщений, а затем обрабатывают каждый набор связанных сообщений в отдельной транзакции. Активация нескольких экземпляров хранимых процедур обеспечивает масштабирование по мере роста трафика сообщений; один экземпляр может работать только с таким количеством сообщений, которое не вызывает ухудшения производительности.
Внутри приложения Service Broker
Service Broker-приложение включает три основные группы компонентов:
Очереди-FIFO обеспечивают хранение сообщений в скрытых таблицах, запрещающих SSB-приложениям операции INSERT и DELETE, но поддерживающих традиционные SELECT-запросы. SSB предлагает проверку корректности XML-сообщений и их соответствия XML-схеме, которую вы добавляете к XML SCHEMA COLLECTION вашей БД.
Рисунок 1.
Листинг 1.
Листинг 2
Листинг 3.
Вы должны задать разрешение GRANT CREATE на все SSB-объекты пользователям, которым потребуется создавать объекты, если эти пользователи не являются членами ролей sysadmin, ddl_admin или db_owner. Разработка промышленных SSB-проектов требует определения существенного числа объектов, так что DBA, как правило, исполняют SQL-скрипты для создания приложений сервисов. Пользователям с меньшими привилегиями обычно требуется разрешения CREATE SCHEMA, VIEW DEFINITION для связанных SSB-объектов, REFERENCES для CONTRACT, SEND на инициаторе целевого SERVICE-а, а также QUEUE и RECEIVE на целевых QUEUE.
Простые тестовые примеры
«Слабосвязанный» и «сервис-ориентированный» конкурируют с «Web 2.0» и «пользовательский контент» за звание «самых используемых бессмысленных слов современности». Но SSB, Query Notifications, Event Notifications и Database Mail предоставляют инструменты, необходимые, чтобы перенести множество БД-проектов в новый мир асинхронного обмена сообщениями без участия Windows Communication Foundation.
«С полным содержанием данной статьи можно ознакомиться в печатной версии журнала»
Copyright © 1994-2016 ООО «К-Пресс»
Service Broker
SQL Server Service Broker provide native support for messaging and queuing in the SQL Server Database Engine and Azure SQL Managed Instance. Developers can easily create sophisticated applications that use the Database Engine components to communicate between disparate databases, and build distributed and reliable applications.
When to use Service Broker
Use Service Broker components to implement native in-database asynchronous message processing functionalities. Application developers who use Service Broker can distribute data workloads across several databases without programming complex communication and messaging internals. Service Broker reduces development and test work because Service Broker handles the communication paths in the context of a conversation. It also improves performance. For example, front-end databases supporting Web sites can record information and send process intensive tasks to queue in back-end databases. Service Broker ensures that all tasks are managed in the context of transactions to assure reliability and technical consistency.
Overview
Service Broker is a message delivery framework that enables you to create native in-database service-oriented applications. Unlike classic query processing functionalities that constantly read data from the tables and process them during the query lifecycle, in service-oriented application you have database services that are exchanging the messages. Every service has a queue where the messages are placed until they are processed.
The messages in the queues can be fetched using the Transact-SQL RECEIVE command or by the activation procedure that will be called whenever the message arrives in the queue.
Creating services
Database services are created by using the CREATE SERVICE Transact SQL statement. Service can be associated with the message queue create by using the CREATE QUEUE statement:
Sending messages
Messages are sent on the conversation between the services using the SEND Transact-SQL statement. A conversation is a communication channel that is established between the services using the BEGIN DIALOG Transact-SQL statement.
Processing messages
The messages that are placed in the queue can be selected by using a standard SELECT query. The SELECT statement will not modify the queue and remove the messages. To read and pull the messages from the queue, you can use the RECEIVE Transact-SQL statement.
Once you process all messages from the queue, you should close the conversation using the END CONVERSATION Transact-SQL statement.
Where is the documentation for Service Broker?
The reference documentation for Service Broker is included in the SQL Server documentation. This reference documentation includes the following sections:
See the previously published documentation for Service Broker concepts and for development and management tasks. This documentation is not reproduced in the SQL Server documentation due to the small number of changes in Service Broker in recent versions of SQL Server.
What’s new in Service Broker
Service broker and Azure SQL Managed Instance
Cross-instance service broker message exchange is supported only between Azure SQL Managed Instances:
Transport security is supported, dialog security is not:
Service broker is enabled by default and cannot be disabled. The following ALTER DATABASE options are not supported:
No significant changes were introduced in SQL Server 2019 (15.x). The following changes were introduced in SQL Server 2012 (11.x).
Messages can be sent to multiple target services (multicast)
The syntax of the SEND (Transact-SQL) statement has been extended to enable multicast by supporting multiple conversation handles.
Queues expose the message enqueued time
Queues have a new column, message_enqueue_time, that shows how long a message has been in the queue.
Один день из жизни DBA Microsoft SQL Server
В арсенале Microsoft SQL Server есть одна интересная штука – service broker. По сути своей это очередь сообщений, встроенная в СУБД, способная обеспечить транзакционную целостность данных. Вещь удобная и, в грамотных руках, способная выстроить систему обмена между SQL Server’ами без применения дополнительных внешних сервисов – прямо из коробки.
С одной стороны service broker удобен, но с другой – от него не мало сюрпризов, способных поломать голову нюансами своей работы. О решении одного из таких сюрпризов поговорим прямо сейчас.
Обнаружили, что логи MS SQL Server, одной из наших, систем жутко забиты сообщениями от service broker типа :
Не долгие поиски на просторах всемирной сети так или иначе выводили на информацию о том, что какие то данные в БД закораптились и требуется их обнаружение (для дальнейшего восстановления) при помощи инструкции DBCC CHECKDB. Счастье было не долгим, ибо данный подход не выявил ни одной проблемы – ни в пользовательских БД, ни в системных.
Так как логи продолжали ужасать своим натиском (пару десятков записей за несколько секунд), а первый план по выявлению причин этого “наводнения” провалился – было решено вести поиски на стороне service broker, ибо читался след от брокера – как минимум по словам “dialog transmission ”.
Подобные поиски в системах начинаются с просмотра очереди на отправку sys.transmission_queue – не стал исключением и наш случай. Первый же select из sys.transmission_queue вывалил на экран тот самый “Could not continue scan with NOLOCK due to data movement”, что с одной стороны вселило уверенности, что копаем в правильную сторону, но с другой – как выявить проблему, если select заканчивается таким сообщением?
Лезем в данные по конечным точкам диалогов sys.conversation_endpoints. С ужасом обнаруживаю, что там висит около 12 миллионов не закрытых диалогов. Первые 10 минут зачистки ненужных накоплений показали, что впереди нас ждет целая неделя ожиданий – ибо чистка протекала крайне медленно. Само закрытие диалога было долгим. У нас был главный подозреваемый — куча не закрытых диалогов service broker’а и надо было его “расколоть”.
Так как сидеть целую неделю, и закрывать диалоги не хотелось, был накидан такой план:
через sqlcmd пачками вычитываем хендлеры подвисших диалогов
формируем sql batch на завершение полученых диалогов
через sqlcmd выполняем сформированный sql batch и гоняем это по циклу, пока не закроем все подвисшие диалоги
batch.sql (пример sql batch – для пункта 2)
end_conversation.bat (батник для пунктов 1 и 3)
Этот товарищ отработал чуть менее двух суток и закрыл все подвисшие диалоги. Как видно из примера – все эти диалоги были открыты для одного конкретного сервиса. С самим удаленным сервисом брокера разобрались до начала массового закрытия диалогов.
К счастью или сожалению, но после закрытия всех не нужных диалогов брокера – ошибки в логах так и не прекратились массово появляться.
Следующей мыслью было потушить service broker end point на СУБД в целом и отключить брокер на основной БД в частности, но и это, увы, не спасло от продолжающихся ошибок в логах.
В какой то из дней решения вопроса возникла необходимость переключиться на зеркало от основной БД. Именно в момент переключения на зеркало нашей БД и вылезла в логах информация о проблеме:
16:42:40.41 spid41s SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 6:260707; actual 559:-641654744). It occurred during a read of page (6:260707) in database ID 2 at offset 0x0000007f4c6000 in file ‘D:\TempDB\tempdb_mssql_5.ndf’. Additional messages in the SQL Server error log or operating system error log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Глядя на неё и стало понятно, что виновником торжества выступает tempdb нашей СУБД.
Как известно, у tempdb одно лекарство – рестарт службы sql. Выбиваем окно у бизнеса для простоя сервиса и перезагружаем основную ноду. После рестарта сервера логи перестало заваливать.
Как оказалось – проблема на самом деле крылась в закорапченных данных. Так, что применяя DBCC CHECKDB – не забывайте применять его и к tempdb и следите за диалогами.
Компонент Service Broker
SQL Server Компонент Service Broker предоставляют встроенную поддержку для обмена сообщениями и очередей в Компонент SQL Server Database Engine и Управляемом экземпляре SQL Azure. Это облегчает разработчикам создание сложных, надежных распределенных приложений, использующих компоненты Компонент Database Engine для связи между разнородными базами данных.
Когда следует использовать компонент Service Broker
Обзор
Компонент Service Broker — это инфраструктура доставки сообщений, дающая возможность создания ориентированных на службы приложений в базе данных. В отличие от функциональных возможностей классической обработки, которые требуют постоянно считывать данные из таблиц и обрабатывать их во время жизненного цикла запроса, в ориентированных на службы приложениях имеются службы базы данных, которые позволяют обмениваться сообщениями. Каждая служба имеет очередь, куда помещаются сообщения до их обработки.
Сообщения в очередях можно извлечь с помощью команды Transact-SQL RECEIVE или процедуры активации, которая будет вызываться всякий раз, когда сообщение поступает в очередь.
Создание служб
Службы базы данных создаются с помощью инструкции Transact SQL CREATE SERVICE. Службы могут быть связаны с очередью сообщений, созданной с помощью инструкции CREATE QUEUE:
Отправка сообщений
Обработка сообщений
После обработки всех сообщений из очереди необходимо закрыть диалог с помощью инструкции Transact-SQL END CONVERSATION.
Где найти документацию по компоненту Service Broker?
См. информацию об инструкциях CREATE, ALTER и DROP в разделе Инструкции на языке описания данных (DDL) (Transact-SQL)
Новые возможности (компонент Service Broker)
Service Broker и Управляемый экземпляр SQL Azure
Обмен сообщениями через Service Broker поддерживается только между управляемыми экземплярами SQL Azure:
Защита транспорта поддерживается, а защита обмена данными — нет:
Компонент Service Broker включен по умолчанию и его нельзя отключить. Следующие параметры ALTER DATABASE не поддерживаются:
В SQL Server 2019 (15.x) не были внесены значимые изменения. В SQL Server 2012 (11.x)появились следующие изменения.
Сообщения могут отправляться в несколько целевых служб (многоадресная рассылка)
Синтаксис инструкции SEND (Transact-SQL) расширен для включения многоадресной рассылки благодаря поддержке нескольких дескрипторов диалога.
Очереди предоставляют время нахождения сообщения в очереди
Очереди содержат новый столбец message_enqueue_time, в котором показано время нахождения сообщения в очереди.
Компонент Service Broker с группами доступности AlwaysOn (SQL Server)
В этом разделе содержатся сведения о настройке компонента Service Broker для работы с Группы доступности AlwaysOn в SQL Server.
Требования к службе в группе доступности для получения удаленных сообщений
Убедитесь, что группа доступности имеет прослушиватель.
Убедитесь, что конечная точка компонента Service Broker существует и правильно настроена.
Установите параметр LISTENER_IP в значение ALL. Этот параметр разрешает соединения по любому допустимому IP-адресу, привязанному к прослушивателю группы доступности.
Установите в параметре PORT компонента Service Broker одинаковый номер порта во всех экземплярах сервера.
В следующем примере создается конечная точка компонента Service Broker с проверкой подлинности Windows, которая использует порт компонента Service Broker по умолчанию (4022) и прослушивает все допустимые IP-адреса.
Дополнительные сведения см. в разделе CREATE ENDPOINT (Transact-SQL).
Компонент SQL Server Service Broker не поддерживает несколько подсетей. Задайте для параметра RegisterAllProvidersIP значение 0 и убедитесь, что кластер имеет необходимое разрешение в DNS для использования статических IP-адресов. Дополнительные сведения см. в разделе Настройка прослушивателя группы доступности. При попытке использовать отключенный IP-адрес Service Broker может отложить сообщение с состоянием CONVERSING.
Предоставьте разрешение CONNECT на конечную точку.
Предоставьте разрешение CONNECT на конечную точку компонента Service Broker роли PUBLIC или некоторому имени входа.
В следующем примере разрешение на подключение к конечной точке компонента Service Broker с именем broker_endpoint предоставляется роли PUBLIC.
Дополнительные сведения см. в статье GRANT (Transact-SQL).
Убедитесь, что база данных msdb содержит маршрут AutoCreatedLocal или маршрут к некоторой службе.
По умолчанию все пользовательские базы данных, включая msdb, содержат маршрут AutoCreatedLocal. Он соответствует имени любой службы и любому экземпляру компонента Service Broker и указывает, что сообщение должно быть доставлено внутри текущего экземпляра. Маршрут AutoCreatedLocal имеет более низкий приоритет, чем маршруты, в которых явно указывается служба, обменивающаяся данными с удаленным экземпляром.
Сведения о создании маршрутов см. в статьях Примеры маршрутизации для компонента Service Broker (версия SQL Server 2008 R2 электронной документации) и CREATE ROUTE (Transact-SQL).
Требования к отправке сообщений удаленной службе в группе доступности
Создайте маршрут к целевой службе.
Настройте маршрут следующим образом.
Задайте в параметре ADDRESS IP-адрес прослушивателя группы доступности, в которой размещается база данных службы.
Задайте в параметре PORT порт, указанный в конечной точке компонента Service Broker в каждом из удаленных экземпляров SQL Server.
Дополнительные сведения см. в статье CREATE ROUTE (Transact-SQL).
Убедитесь, что база данных msdb содержит маршрут AutoCreatedLocal или маршрут к некоторой службе. (Дополнительные сведения см. в подразделе Требования к службе в группе доступности для получения удаленных сообщенийвыше.)