March 2, 2011 at 8:40 am
Hi,
(I hope I haven't broken any rules - I posted this in high availability but on second thoughts decided it would be better here - can't see how to move it :doze:)
I have somehow (having never even heard of service broker before I started!) created what is now a very stable, robust service broker setup to record website audit data (what was clicked, what appeared - that sort of thing) for our live website.
Right now, the source and target queues are in the same database, living on the live cluster that serves the website.
The audit data is growing fast, so what I need to do is leave the enqueue operation and the source queue on the live cluster where it can benefit from all the safety and security of the live environment, but move the dequeue operation and the storage part of the database to a massive new server we have commissioned just for storing the data (the new server is huge, but less well protected. The idea being that if it goes offline for maintenance service broker will look after the messages until the target is ready to receive them once more).
I could take the website down, copy the database over, create or recreate all necessary SSB objects, swap certificates etc. and get it running before restarting the website, but I am worried about how long the site will be unavailable, and about delays if the deployment doesn't go smoothly (again the downtime would be a worry), so I wondered if I could stop a queue - the source queue presumably - and allow audit messages to build up while I did all the moving and recreating behind the scenes without affecting the website at all, then once it was all sorted, re-enable the source queue (with its new routing and binding, new certificate, new dedicated user etc.) and it would resume processing as if nothing had happened?
Would that work? Is there anything I would have to be aware of or do specifically to ensure the stacked up messages would process successfully when I switch the queue back on? Or would the fact hat the messages were enqueued with a certain routing mean that they would fail under the new arrangement as everything had changed?
Thanks for your help!
March 2, 2011 at 8:53 am
Are you using activated procedures to process the messages or do you have an external process to handle messages? Reason I ask is because the answer to your question depends on the answer to this one.
The probability of survival is inversely proportional to the angle of arrival.
March 2, 2011 at 8:58 am
Both queues (source and target) have activated procedures to process messages.
March 2, 2011 at 9:11 am
Because you are using activated procedures service broker will shut the queues down if the activated procedures do not process messages. You can use ALTER QUEUE to set the activation status to off then let the messages build up, then turn the activation status back to on when you are ready to process again.
Configuring service broker for using local and remote services the service broker routes the messages to can be complicated and it not very easy when you want to reconfigure.
If it were me, I would avoid the activated procedures and build a separate service (process) that can reside anywhere (or multiple places) that reads the queue(s). Much easier to reconfigure and less sensitive to server load.
The probability of survival is inversely proportional to the angle of arrival.
March 2, 2011 at 10:41 am
I will agree with sturner that that multiple server brokering is much more complex, but worthwhile in the end. I find the Service Broker tool to be rather robust.
What you want to do here, though, is deactivate the target queue in the database, and move that over to the otherside. Your source queue can build things up for a good long while (just watch it, make sure you don't end up bloated by accident). As long as your source queue is still getting records, and you've got space on the source system to store them, you can take as long as you like setting up the delivery server... Just realize once you bring it online it's going to take a while to process all of that.
A recommendation, however. Setup a testing database between the two servers to pass a simplified queue setup between them to make sure you've got all your endpoints and the like setup properly. Service Broker gets complicated fast with all the moving parts, try to simplify the setup and routing to get a feel for it before you start testing the connections.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply