The problem around incorrectly measured IO times due to CPU time drift was fixed by the SQL Server 2005 SP3 release. I have talked about this issue and explained the problem in my post: http://rusanu.com/2008/10/28/event-id-833-io-requests-taking-longer-than-15-seconds.

Service Broker services use certificates for authenticating message origin and for encrypting messages. I have explained in detail how this authentication works in my earlier post Conversation Authentication. The certificates used for service authentication are most times self-signed certificates created directly by SQL Server using CREATE CERTIFICATE and they expire by default one year after creation. When these certificates expire they have to be replaced and this article is goal is to help you do thisreplacement with no impact on production systems.

Identifying the certificates used by services

Here is a recap of the criteria Service Broker uses to pick a certificate to represent an service identity:

Read the rest of this entry »

Recently I wanted to go over again Rick Vicik papers on high performance programs on the Windows platform. These papers are a true Bible for anyone in need to write truly highly scalable and high performance server applications. They address the back end C/C++ programming and explain how to properly use the Windows threading, optimize I/O and specially the importance of data cache conscious programming, NUMA object allocations and access locality and impact of data sharing on performance. I do find however that many of the principles explained there apply just as well to C# and .Net programming. I wanted to refresh my memory on some issues so I searched for them and to my delight I found that Rick updated the papers for Vista and Windows 2008 and had posted them as a three part series on the Windows Performance blog and I wanted to share these with my blog audience:

• Designing Applications for High Performance – Part I
• Designing Applications for High Performance – Part II
• Designing Applications for High Performance – Part III

Mr. Denny has a take at the conversation reuse and recycle topic: http://itknowledgeexchange.techtarget.com/sql-server/improving-sql-service-broker-performance/. This solution allows for a more flexible policy on conversation reuse replacing the hard coded @@SPID affinity I used in my original post with an application specific settings table.

I have covered before how certificate based authentication works for endpoints. I think is only fair to have a similar article covering how services use certificates for authentication and security. Service Broker conversations are very different though from endpoints in the way they use certificates for authentication and security. Unfortunately there is no similar protocol I could reffer to, like it was the case with endpoints and TLS.

Service Broker conversations need authentication in order to allow or reject the sender of a message as being permitted to send messages to a destination service. The message sender in this context is the service that initiated the conversation. In Service Broker does not authenticate the user that sent a message (ie. the user logged in that issues the SEND or BEGIN CONVERSATION statement). Because Service Broker is designed as a mean to communicate between applications, users connected to an application are a local concept that cannot be used for authentication purposes remotely. For example when Joe from accounting logs in to the Accounting application and this application has some data from the Inventory application, this later application will authorize the Accounting application not Joe. Perhaps the Inventory application doesn’t even have Joe’s credentials, and no one wants to add additional tight coupling between the applications by having them be aware of each other user base just to be able to communicate.

Read the rest of this entry »

The error 833 is usually associated with hardware or system driver problems and the typical recommendation is to replace the hardware or update the drivers and firmware used. However there is a common scenario that leads to this problem when your hardware is fine and sound.

Read the rest of this entry »

In my previous post I have explained how Database Mirroring and Service Broker use certificates for endpoint authentication. The only thing validated by SSB/DBM on a certificate are the valid-from date and the expiration date. In fact, even if SSB would not validate these dates, the TLS protocol used underneath by SSB/DBM authentication mechanism would validate these dates. In practice the only one that matter is the expiration date since the valid-from date is usually valid from the moment the certificate was created. Although if you follow this blog you know that I have already talked about a problem that may appear with certificates not yet valid, see http://rusanu.com/2008/08/25/certificate-not-yet-valid.

Read the rest of this entry »

Service Broker and Database Mirroring may use certificates for authenticating endpoints as an alternative to NTLM/Kerberos authentication. This alternative is actually the only possible one whenever the servers involved are members of unrelated domains (or aren’t even members of a domain) and the default Windows based authentication is not possible. For Service Broker this scenario is more of the norm rather the exception, while for Database Mirroring this is the exceptional case. To configure an endpoint to use certificates for authentication you must specify the CERTIFICATE keyword in the CREATE/ALTER ENDPOINT statement:

CREATE ENDPOINT [mirroring]
	STATE = STARTED
	AS TCP (LISTENER_PORT = 5022)
	FOR DATABASE_MIRRORING (
		AUTHENTICATION = CERTIFICATE [MyCertName],
		ROLE = PARTNER);

‘Certificate based authentication’ for Service Broker and Database Mirroring sounds esoteric, yet is really nothing else but a variation of the SSL protocol used to authenticate web sites. To be strict, SQL Server will use TLS not SSL.

SSL and TLS provide a secure way to transmit a certificate from the server to the client and to establish a common secret later used to encrypt and sign traffic. How this is achieved is perhaps out of the scope of a database development oriented discussion, but if you really want to know the gory details MSDN documents the process in the SChannel SSPI reference:

1. Client calls InitializeSecurityContext and sends to the server the output buffer(s).
2. The server calls AcquireCredentialsHandle. The pAuthData parameter contains an SCHANNEL_CRED structure that describes the certificate used by the server for authentication.
3. The server calls AcceptSecurityContext passing in the buffer(s) provided by the client. Any output buffer is sent back to the client.
4. The client receives the buffer(s) from the server and calls again InitializeSecurityContext passing in the buffer(s) from the server. If any output buffer results, it is sent to the server.
5. The server receives more buffer(s) from the client and calls again AcceptSecurityContext passing in the buffer(s) provided by the client. If any out buffer results, it is sent to the client.
6. Steps 4 and 5 are repeated until no more output buffers are produced.
7. The client calls QueryContextAttributes on the resulted security context and asks for the SECPKG_ATTR_REMOTE_CERT_CONTEXT attribute. With this call the client has obtained a copy of the certificate used by the server in step 2 to initiate the authentication process.
8. Further traffic between client and server can be encrypted using the EncryptMessage and DecryptMessage functions.

Read the rest of this entry »

There is an unofficial WCF channel for SQL Service Broker posted by David Browne on www.codeplex.com/ssbwcf. To quote from the project homepage: It allows the WCF developer to leverage SSB while staying within the WCF Service Model or Channel Model programming models. The WCF SSB transport provides durable, two-way queue-based communications that requires only SQL Server 2005/2008 as its infrastructure.

While looking at the Google Analytics report for this very blog I found a new refferal site: http://questservicebroker.blogspot.com/. A blog with “servicebroker” in its name, I had to check it out :). Turns out that Quest is working on the next generation of tools for Service Broker administration and has opened a blog to discuss new features and request feedback. These are the same tools I’ve blogged about before here. If you want to suggest features you’d like an Admin tool for Service Broker to have, now is your chance.