Creating Service Broker Endpoints in SQL Server

You can only have one service broker endpoint per SQL Server instance. When creating a service broker application you need to know what TCP port you endpoint is listening on, so your deployment scripts will be referencing correctly. For this reason I recommend using a common port across your enterprise. For example 4000. Since you can only have one endpoint you need a deployement script that will check both the existence of an endpoint and the tcp port conforms to your enterprise. The script below does just that.



--Check existence of a different port
if exists(select top 1 * from sys.tcp_endpoints where type = 3 and Port != 4444)
Begin
--Drop Endpoint
Declare @Endpointname varchar(100)
set @Endpointname = (select name from sys.tcp_endpoints where type = 3)
execute ('Drop Endpoint ' + @Endpointname)
end
go
--If no endpoint exists or was just dropped create
if not exists (select top 1 * from sys.tcp_endpoints where type = 3)
Begin
CREATE ENDPOINT [ServiceBrokerEndpoint]
STATE=STARTED
AS TCP (LISTENER_PORT = 4444, LISTENER_IP = ALL)
FOR SERVICE_BROKER (MESSAGE_FORWARDING = DISABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
End




If you try to create an endpoint that exist you’ll see an error that’s similar to the one below.






Msg 7862, Level 16, State 1, Line 1
An endpoint of the requested type already exists. Only one endpoint of this type is supported. Use ALTER ENDPOINT or DROP the existing endpoint and execute the CREATE ENDPOINT statement.
Msg 7807, Level 16, State 1, Line 1
An error ('0x800700b7') occurred while attempting to register the endpoint 'ServiceBrokerEndpoint'.