May 25, 2010 at 2:05 pm
The infrastructure:
The Service Broker initiator is a SQL Server 2005 Enterprise Mirrored pair (production)
The Service Broker target is a solo SQL Server 2005 Enterprise server (reporting)
The background:
This Service Broker deployment has been successfully running for almost a year.
The mirror principle has switched sides several times over the last year without any problem for Service Broker.
The problem:
Yesterday the principle switched between the servers automatically (overload caused by a backup process), and then back to where it started manually when it was noticed.
The error message:
From that point the messages have been stuck at the Service Broker initiator in the sys.transmission_queue with the error message "The database master key or conversation session keys are unavailable".
Sadly, this error message appears to be un-google-able.
What to do?:
The sysadmin says he hasn't made any changes to the master or conversation session keys - and I certainly haven't.
Is this just some sort of corruption of the keys?
Am I safe to just recreate the source and target certificates? I don't want to recreate the Master key as it seems like I've read somewhere that if you do that the in-flight (currently sitting in the transmission queue) conversations are toast.
Should I recreate the certificates on both the initiator and the target? If it is corruption, it should just have effected the initiator (during the mirror switch) - right?
Thanks for any advice!
Jim - of the growing transmission_queue
May 25, 2010 at 7:48 pm
So far: I've recreated all the certificates / routes / remote service bindings.
Immediately after doing that, the transmission_queue error changes to: "Please create a master key in the database or open the master key in the session before performing this operation."
But then within seconds it goes back to the previous error: "The database master key or conversation session keys are unavailable"
So my focus has switched to the Master Key.
If I run the following:
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Password'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
What changes would that make to the DbMK?
My understanding is that it encrypts it. But presumably it's already encrypted having been working for the last year.
Since I successfully recreated the certificates, I believe the DbMK is NOT corrupted. (right?)
Has my DbMK become UN-available for some reason? Would the above OPEN/ALTER/CLOSE code would solve all my issues?
I'm just very wary of touching the DbMK at all.
Ugh. Help?
May 25, 2010 at 11:00 pm
The fix:
OPEN MASTER KEY DECRYPTION BY PASSWORD = '<password>'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
My guess as to why this worked:
None of the keys/certificates were corrupted.
Somehow in the failed principle/mirror switch the Database Master Key got unassociated from the System Master Key. This code "reconnected" the two so the Database Master Key could be available to sessions needing it.
I'd be happy to be corrected in my reasoning - I'm just glad it worked! That one goes into the toolbox.
Jim
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply