New to my blog...

I have added a group of interesting links into my blog - "Interesting resources", and blogs of Bryan Oliver and Brent Ozar are the first in this list. Bryan and Brent are SQL Server experts and you can find a lot of useful information about SQL Server in their blogs!

New wizard

Toad for SQL Server has the following wizards to configure Service Broker Admin:

1) Service Broker Application Wizard
2) Transport Security Configuration Wizard
3) Dialog Security Configuration Wizard
4) Event Notification Application Wizard

You have to pass three first wizards in the order: 1 -> 2 -> 3 It’s very unhandy.
I think it will be better to combine these wizards into one, like this:
After the Welcome screen the wizard requests to select one of the following procedures:

• Completing a Conversation in a Single Database


• Completing a Conversation Between Databases


• Completing a Conversation Between Instances (transport security)


• Completing a Conversation Between Instances (dialog security)

During the next steps a user can create the necessary entities and configure security. With the last step a user can copy to file/clipboard the full script and use it afterwards without a wizard. That is much better than copying three parts of the final script from each wizard.

Interesting resources

I have added a new section to my blog: "Interesting resources" (on the right panel).
So I will add links to interesting resources about the Service Broker technology. Meet the first part:
http://rusanu.com/blog/ - this is a blog of the great expert in the Service Broker area. Blog contains an impressive volume of quite interesting publications covering Service Broker. And author cares about updating.
http://itknowledgeexchange.techtarget.com/sql-server/tag/service-broker/ - Denny Cherry’s blog: you will be able to find a series of articles "Back To Basics" there. These publications describe all basic entities and commands (such as SEND, RECIVE) in the Service Broker.
http://www.databasejournal.com/article.php/1503191/ - Marcin Policht’s journal contains several publications covering Service Broker (some parts of big series about the SQL Server 2005 Express Edition):
1) Part 26 - Introduction to Service Broker: http://www.databasejournal.com/features/mssql/article.php/3757581/
2) Part 27 - Implementing Basic Service Broker Objects: http://www.databasejournal.com/features/mssql/article.php/3759311/
3) Part 28 - Implementing Service Broker Conversation: http://www.databasejournal.com/features/mssql/article.php/3762261/

One more small issue

If you want to use a quote (‘) in the message body, you must duplicate it, otherwise you will get the following error:

Slight issue...

One of our customers has found a slight issue:

In the "Create Service Broker Application Wizard" user can't create a new queue without activation (for external activation), he has to create a stored procedure for activation or select existing. So if you are planning to configure your Service Broker using our wizard, keep in mind this restriction.

Anonymous transport security

In the "Pro SQL Server 2005 Service Broker" Klaus Aschenbrenner wrote about setting up anonymous transport security. It's a really good case for an online shop to use the Service Broker technology. The main advantage of this kind of security: you don't need to exchange certificates between the initiator and the target.

To set up anonymous transport security do the following:

On the initiator's side

-- create master key in the master database
use master
create master key encryption by password = 'pass1'

--create certificate
create certificate initCertificate
with subject = 'For endpoint',
start_date = '11/08/2008'

-- create the Service Broker endpoint
create endpoint initEndpoint
state = started
as tcp (listener_port = 4070)
for service_broker (authentication = certificate initCertificate)

-- grant connect on this endpoint for all users
grant connect on endpoint::initEndpoint to [public]


On the target's side

-- create master key in the master database
use master
create master key encryption by password = 'pass1'

--create certificate
create certificate targetCertificate
with subject = 'For endpoint',
start_date = '11/08/2008'

-- create the Service Broker endpoint
create endpoint targetEndpoint
state = started
as tcp (listener_port = 4070)
for service_broker (authentication = certificate initCertificate)

-- grant connect on this endpoint for all users
grant connect on endpoint::targetEndpoint to [public]

Now the "Transport Security Configuration Wizard" doesn't allow to configure anonymous transport security but we will fix it in next product version.

New events

Yesterday I worked on Event Notification and found new events in SQL Server 2008:

DLL (Database and Server)
CREATE_ASYMMETRIC_KEY, ALTER_ASYMMETRIC_KEY, DROP_ASYMMETRIC_KEY
ALTER_AUTHORIZATION
ADD_COUNTER_SIGNATURE, DROP_COUNTER_SIGNATURE
CREATE_CREDENTIAL, ALTER_CREDENTIAL, DROP_CREDENTIAL
CREATE_DEFAULT, DROP_DEFAULT, BIND_DEFAULT, UNBIND_DEFAULT
CREATE_EXTENDED_PROPERTY, ALTER_EXTENDED_PROPERTY, DROP_EXTENDED_PROPERTY
CREATE_FULLTEXT_CATALOG, ALTER_FULLTEXT_CATALOG, DROP_FULLTEXT_CATALOG
CREATE_FULLTEXT_INDEX, ALTER_FULLTEXT_INDEX, DROP_FULLTEXT_INDEX
CREATE_PLAN_GUIDE, ALTER_PLAN_GUIDE, DROP_PLAN_GUIDE
CREATE_SPATIAL_INDEX
RENAME
CREATE_RULE, DROP_RULE, BIND_RULE, UNBIND_RULE
CREATE_SCHEMA, ALTER_SCHEMA, DROP_SCHEMA
ALTER_SERVICE_MASTER_KEY, BACKUP_SERVICE_MASTER_KEY, RESTORE_SERVICE_MASTER_KEY
ADD_SIGNATURE, DROP_SIGNATURE
CREATE_SYMMETRIC_KEY, ALTER_SYMMETRIC_KEY, DROP_SYMMETRIC_KEY

DLL (Server)
CREATE_EXTENDED_PROCEDURE, DROP_EXTENDED_PROCEDURE
ALTER_INSTANCE
CREATE_LINKED_SERVER, ALTER_LINKED_SERVER, DROP_LINKED_SERVER
CREATE_LINKED_SERVER_LOGIN, DROP_LINKED_SERVER_LOGIN
CREATE_MESSAGE, ALTER_MESSAGE, DROP_MESSAGE
CREATE_REMOTE_SERVER, ALTER_REMOTE_SERVER, DROP_REMOTE_SERVER

New events for Event Notification Application Wizard are supposed to be in next version. But there are some more features we are planning to add in this wizard.