Showing posts with label broker. Show all posts
Showing posts with label broker. Show all posts

Sunday, March 25, 2012

Automatically enable broker after restore or attach

Is there a way to automatically enable Service Broker on SQL Server 2005 Express Edition after a database has been attached or restored?
My initial idea was to check if broker is disabled whenever the client application starts and enable it from there but the problem with this is that the current user might not have the required permissions. So now I'm looking for another way to enable it right after restore/attach if at all possible. Any ideas would be appreciated.

Simplest approach would be to enabled it via a procedure that does have the appropiate permissions (using EXECUTE AS and code signing).

Other possiblity would be to create a server event notification and use an activate procedure in msdb to enable the database:

Code Snippet

use msdb;

go

create queue [dbevents]

create service [dbevents] on queue [dbevents] ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);

create event notification [dbbackuprestore]

on server for AUDIT_BACKUP_RESTORE_EVENT

to service 'dbevents', 'current database';

Monday, February 13, 2012

Auto reminder emails?

I don't know much about broker service so I have question. We have a content management system the we developed locally, and what we have are catagories and subcatagries. When people choose a topic we send them an email about the topic they are interested in. We are now selling webinars and white papers and articles on demand. We would like to be able to send a reminder email to anyone who signed up for a webinar or special event. Is this possible with broker service?While you could use SSB to build your fan-out and scheduling for the mails, it will not be capable of actually delivering the mails to the users, since it can only send messages between two SQL Server instances. The Database mail functionality in SQL can achieve that though (see http://msdn2.microsoft.com/en-us/library/ms175887.aspx)|||So I can use service broker to send the email to the dbmail engine and than to the customers right?|||Yes, but db mail engine already does that (sp_senddbmail uses Service Broker to communicate with the mailing engine)