CREATE BROKER PRIORITY (Transact-SQL)

Άρθρο
12/29/2022

Applies to: SQL Server Azure SQL Managed Instance

Defines a priority level and the set of criteria for determining which Service Broker conversations to assign the priority level. The priority level is assigned to any conversation endpoint that uses the same combination of contracts and services that are specified in the conversation priority. Priorities range in value from 1 (low) to 10 (high). The default is 5.

Transact-SQL syntax conventions

Syntax

CREATE BROKER PRIORITY ConversationPriorityName  
FOR CONVERSATION  
[ SET ( [ CONTRACT_NAME = {ContractName | ANY } ]  
        [ [ , ] LOCAL_SERVICE_NAME = {LocalServiceName | ANY } ]  
        [ [ , ] REMOTE_SERVICE_NAME = {'RemoteServiceName' | ANY } ]  
        [ [ , ] PRIORITY_LEVEL = {PriorityValue | DEFAULT } ]  
       )  
]  
[;]

Arguments

ConversationPriorityName
Specifies the name for this conversation priority. The name must be unique in the current database, and must conform to the rules for Database Engine identifiers.

SET
Specifies the criteria for determining if the conversation priority applies to a conversation. If specified, SET must contain at least one criterion: CONTRACT_NAME, LOCAL_SERVICE_NAME, REMOTE_SERVICE_NAME, or PRIORITY_LEVEL. If SET is not specified, the defaults are set for all three criteria.

CONTRACT_NAME = {ContractName | ANY}
Specifies the name of a contract to be used as a criterion for determining if the conversation priority applies to a conversation. ContractName is a Database Engine identifier, and must specify the name of a contract in the current database.

ContractName
Specifies that the conversation priority can be applied only to conversations where the BEGIN DIALOG statement that started the conversation specified ON CONTRACT ContractName.

ANY
Specifies that the conversation priority can be applied to any conversation, regardless of which contract it uses.

The default is ANY.

LOCAL_SERVICE_NAME = {LocalServiceName | ANY}
Specifies the name of a service to be used as a criterion to determine if the conversation priority applies to a conversation endpoint.

LocalServiceName is a Database Engine identifier. It must specify the name of a service in the current database.

LocalServiceName
Specifies that the conversation priority can be applied to the following:

Any initiator conversation endpoint whose initiator service name matches LocalServiceName.
Any target conversation endpoint whose target service name matches LocalServiceName.

ANY

Specifies that the conversation priority can be applied to any conversation endpoint, regardless of the name of the local service used by the endpoint.

The default is ANY.

REMOTE_SERVICE_NAME = {'RemoteServiceName' | ANY}
Specifies the name of a service to be used as a criterion to determine if the conversation priority applies to a conversation endpoint.

RemoteServiceName is a literal of type nvarchar(256). Service Broker uses a byte-by-byte comparison to match the RemoteServiceName string. The comparison is case-sensitive and does not consider the current collation. The target service can be in the current instance of the Database Engine, or a remote instance of the Database Engine.

'RemoteServiceName'
Specifies that the conversation priority can be applied to the following:

Any initiator conversation endpoint whose associated target service name matches RemoteServiceName.
Any target conversation endpoint whose associated initiator service name matches RemoteServiceName.

ANY
Specifies that the conversation priority can be applied to any conversation endpoint, regardless of the name of the remote service associated with the endpoint.

The default is ANY.

PRIORITY_LEVEL = { PriorityValue | DEFAULT }
Specifies the priority to assign any conversation endpoint that use the contracts and services specified in the conversation priority. PriorityValue must be an integer literal from 1 (lowest priority) to 10 (highest priority). The default is 5.

Remarks

Service Broker assigns priority levels to conversation endpoints. The priority levels control the priority of the operations associated with the endpoint. Each conversation has two conversation endpoints:

The initiator conversation endpoint associates one side of the conversation with the initiator service and initiator queue. The initiator conversation endpoint is created when the BEGIN DIALOG statement is run. The operations associated with the initiator conversation endpoint include:
- Sends from the initiator service.
- Receives from the initiator queue.
- Getting the next conversation group from the initiator queue.
The target conversation endpoint associates the other side of the conversation with the target service and queue. The target conversation endpoint is created when the conversation is used to send a message to the target queue. The operations associated with the target conversation endpoint include:
- Receives from the target queue.
- Sends from the target service.
- Getting the next conversation group from the target queue.

Service Broker assigns conversation priority levels when conversation endpoints are created. The conversation endpoint retains the priority level until the conversation ends. New priorities or changes to existing priorities are not applied to existing conversations.

Service Broker assigns a conversation endpoint the priority level from the conversation priority whose contract and services criteria best match the properties of the endpoint. The following table shows the match precedence:

Operation contract	Operation local service	Operation remote service
ContractName	LocalServiceName	RemoteServiceName
ContractName	LocalServiceName	ANY
ContractName	ANY	RemoteServiceName
ContractName	ANY	ANY
ANY	LocalServiceName	RemoteServiceName
ANY	LocalServiceName	ANY
ANY	ANY	RemoteServiceName
ANY	ANY	ANY

Service Broker first looks for a priority whose specified contract, local service, and remote service matches those that the operation uses. If one is not found, Service Broker looks for a priority with a contract and local service that matches those that the operation uses, and where the remote service was specified as ANY. This continues for all the variations that are listed in the precedence table. If no match is found, the operation is assigned the default priority of 5.

Service Broker independently assigns a priority level to each conversation endpoint. To have Service Broker assign priority levels to both the initiator and target conversation endpoints, you must ensure that both endpoints are covered by conversation priorities. If the initiator and target conversation endpoints are in separate databases, you must create conversation priorities in each database. The same priority level is usually specified for both of the conversation endpoints for a conversation, but you can specify different priority levels.

Priority levels are always applied to operations that receive messages or conversation group identifiers from a queue. Priority levels are also applied when transmitting messages from one instance of the Database Engine to another.

Priority levels are not used when transmitting messages:

From a database where the HONOR_BROKER_PRIORITY database option is set to OFF. For more information, see ALTER DATABASE SET Options (Transact-SQL).
Between services in the same instance of the Database Engine.
All Service Broker operations in a database are assigned default priorities of 5 if no conversation priorities have been created in the database.

Permissions

Permission for creating a conversation priority defaults to members of the db_ddladmin or db_owner fixed database roles, and to the sysadmin fixed server role. Requires ALTER permission on the database.

Examples

A. Assigning a priority level to both directions of a conversation.

These two conversation priorities ensure that all operations that use SimpleContract between TargetService and the InitiatorAService are assigned priority level 3.

CREATE BROKER PRIORITY InitiatorAToTargetPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = InitiatorServiceA,  
         REMOTE_SERVICE_NAME = N'TargetService',  
         PRIORITY_LEVEL = 3);  
CREATE BROKER PRIORITY TargetToInitiatorAPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = TargetService,  
         REMOTE_SERVICE_NAME = N'InitiatorServiceA',  
         PRIORITY_LEVEL = 3);

B. Setting the priority level for all conversations that use a contract

Assigns a priority level of 7 to all operations that use a contract named SimpleContract. This assumes that there are no other priorities that specify both SimpleContract and either a local or a remote service.

CREATE BROKER PRIORITY SimpleContractDefaultPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = ANY,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 7);

C. Setting a base priority level for a database.

Defines conversation priorities for two specific services, and then defines a conversation priority that will match all other conversation endpoints. This does not replace the default priority, which is always 5, but does minimize the number of items that are assigned the default.

CREATE BROKER PRIORITY [//Adventure-Works.com/Expenses/ClaimPriority]  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = ANY,  
         LOCAL_SERVICE_NAME = //Adventure-Works.com/Expenses/ClaimService,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 9);  
CREATE BROKER PRIORITY [//Adventure-Works.com/Expenses/ApprovalPriority]  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = ANY,  
         LOCAL_SERVICE_NAME = //Adventure-Works.com/Expenses/ClaimService,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 6);  
CREATE BROKER PRIORITY [//Adventure-Works.com/Expenses/BasePriority]  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = ANY,  
         LOCAL_SERVICE_NAME = ANY,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 3);

D. Creating three priority levels for a target service by using services

Supports a system that provides three levels of performance: Gold (high), Silver (medium), and Bronze (low). There is one contract, but each level has a separate initiator service. All initiator services communicate to a central target service.

CREATE BROKER PRIORITY GoldInitToTargetPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = GoldInitiatorService,  
         REMOTE_SERVICE_NAME = N'TargetService',  
         PRIORITY_LEVEL = 6);  
CREATE BROKER PRIORITY GoldTargetToInitPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = TargetService,  
         REMOTE_SERVICE_NAME = N'GoldInitiatorService',  
         PRIORITY_LEVEL = 6);  
CREATE BROKER PRIORITY SilverInitToTargetPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = SilverInitiatorService,  
         REMOTE_SERVICE_NAME = N'TargetService',  
         PRIORITY_LEVEL = 4);  
CREATE BROKER PRIORITY SilverTargetToInitPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = TargetService,  
         REMOTE_SERVICE_NAME = N'SilverInitiatorService',  
         PRIORITY_LEVEL = 4);  
CREATE BROKER PRIORITY BronzeInitToTargetPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = BronzeInitiatorService,  
         REMOTE_SERVICE_NAME = N'TargetService',  
         PRIORITY_LEVEL = 2);  
CREATE BROKER PRIORITY BronzeTargetToInitPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SimpleContract,  
         LOCAL_SERVICE_NAME = TargetService,  
         REMOTE_SERVICE_NAME = N'BronzeInitiatorService',  
         PRIORITY_LEVEL = 2);

E. Creating three priority levels for multiple services using contracts

Supports a system that provides three levels of performance: Gold (high), Silver (medium), and Bronze (low). Each level has a separate contract. These priorities apply to any services that are referenced by conversations that use the contracts.

CREATE BROKER PRIORITY GoldPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = GoldContract,  
         LOCAL_SERVICE_NAME = ANY,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 6);  
CREATE BROKER PRIORITY SilverPriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = SilverContract,  
         LOCAL_SERVICE_NAME = ANY,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 4);  
CREATE BROKER PRIORITY BronzePriority  
    FOR CONVERSATION  
    SET (CONTRACT_NAME = BronzeContract,  
         LOCAL_SERVICE_NAME = ANY,  
         REMOTE_SERVICE_NAME = ANY,  
         PRIORITY_LEVEL = 2);

Κοινή χρήση μέσω

CREATE BROKER PRIORITY (Transact-SQL)

Syntax

Arguments

Remarks

Permissions

Examples

A. Assigning a priority level to both directions of a conversation.

B. Setting the priority level for all conversations that use a contract

C. Setting a base priority level for a database.

D. Creating three priority levels for a target service by using services

E. Creating three priority levels for multiple services using contracts

See Also

Σχόλια

Πρόσθετοι πόροι