Centralized Asynchronous Auditing across Instances and Servers with Service BrokerBy Mladen Prajdić on 20 August 2007 | Tags: Administration , Service Broker In my previous article I showed how to asynchronously audit data in one instance with one central database that holds audited data from all other databases on that instance. So to continue showing the greatness that is Service Broker this article explains how to do the same with multiple servers or instances. This means that a single server holds audited data from all other servers. It's a classic many to one (one to many) scenario. Note that this technique works also if you want to send data from the master server to multiple children servers. New terminology we need to knowI've covered the basic terminology in the previous article so if you're not familiar with it go read that first. Done? Ok here we go
Building the whole systemFor a scalable and reusable system like this we need a few requirements:
We have number 1 already covered since we have only one target instance. Number 2 and 3 are a bit trickier though. Let's cover number 3 first. For instance, to have secure communication between each other (Transport Security) we can use Certificates or Integrated windows security. I've used the Certificate approach because it's harder to set up. These are the steps for setting up transport security on the master server and each child server. Setup in Master Database on Master Server
Setup in Master Database on all Child Servers:
If you want to use windows security instead of certificate-based then simply skip all of the steps that involve certificates and create endpoints with windows security. Now we have setup the Transport Security between the Master and Child Servers. This covers point 3 and half of point 2. The other half of point 2 includes routes. RoutesService Broker cross-instance communication is dependent on routes because they tell the messages where to go. For two service brokers in different instances to communicate with each other, each service broker must have a corresponding route to the other service broker - even if the data flow is only one way. Even then the acknowledgements must be sent to notify success when receiving a message. But this presents a problem because if we add a new child database and a new service in it we'd have to connect the Central Master Service Broker to the new service. This would mean a change on the Master Server for every new database which is unacceptable and clearly violates our point 2 (no changes on master instance). The solution is to create such a route on the master instance which would somehow correctly find the child service location (instance and database) from the child's service name. Because of this scenario a special kind of route called 'TRANSPORT' exists. This special route must be created on the Master instance in the MasterAuditDatabase like so: CREATE ROUTE [RouteDataReceiver] WITH ADDRESS = N'TRANSPORT' For transport route to work properly each child databases' services must be named like this: [tcp://xxx.xxx.xxx.xxx:PORT/UniqueServiceName]
In our case the services would be named: -- service to send data from server 1 and TestDb1 [tcp://123.123.123.123:4022/TestDb1/Audit/DataSender] -- service to send data from server 1 and TestDb2 [tcp://123.123.123.123:4022/TestDb2/Audit/DataSender] -- service to send data from server 2 and TestDb1 [tcp://456.456.456.456:4022/TestDb1/Audit/DataSender] -- service to send data from server 2 and TestDb2 [tcp://456.456.456.456:4022/TestDb2/Audit/DataSender] Note that if you have more than one instance on the same computer you have to use a different port for each instance. Dialog Conversation reuseDialog Conversation reuse simply means that we don't start a new conversation for every message but send messages on the existing conversation. This is good for performance reasons because a single conversation for all messages to the same target service takes fewer resources than opening and closing a new conversation for each message. For example: having 1 conversation with 100 messages will take a lot less resources than 100 conversations with each having only 1 message. Because in auditing there is a constant flow of data from child instances to master instance I've chosen to have one conversation per database constantly open. Each child database has an AuditDialogs table that holds the dialog for reuse. Not ending conversations can lead to some problems in case of failures but those are outside of the scope of this article. For any errors that might happen check dbo.AuditErrors custom table and sys.transmission_queue view in both MasterAuditDatabase and all child databases. Ending conversationsConversations should be ended from the target. If you start and end a conversation from the initiator the target won't be able to send back an acknowledgment of any kind even if an error happens (failed validation, permissions problem, etc.) when processing the received message on the target. If that happens the received message that resulted in an error will be lost." This isn't the desired behavior in auditing. Ending conversations from the target server solves this problem. For our scenario the best thing do to is setup a scheduled job on the master instance (the target) that will end conversations periodically. The interval is up to you. See what works for you. This script run in the MasterAuditDatabase will end all conversations: DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE EndConversationCursor CURSOR FOR SELECT conversation_handle FROM sys.conversation_endpoints OPEN EndConversationCursor FETCH NEXT FROM EndConversationCursor INTO @conversationHandle WHILE @@FETCH_STATUS = 0 BEGIN END CONVERSATION @conversationHandle FETCH NEXT FROM EndConversationCursor INTO @conversationHandle END CLOSE EndConversationCursor DEALLOCATE EndConversationCursor Now the state of the conversation in MasterAuditDatabase is DISCONNECTED_OUTBOUND while the state of all conversations in child databases is DISCONNECTED_INBOUND. For the conversations to be truly closed you have to run the same script in all child databases as well. This will then remove the conversation from the sys.conversation_endpoints child databases and set the state of the conversations in the MasterAuditDatabase to CLOSED. ConclusionHopefully I've shown how to easily implement a Many-to-One (One-to-Many) scenario in an understandable and easy to follow way. Service Broker is a powerful addition that opens up a whole new level of possibilities of use. Attached script useAttached script use order:
The script files are all well documented and hopefully clear. |
- Advertisement - |