Alter database vs alter master key

• Fraggle-805517

  SSCrazy Eights

  Points: 8907

  More actions

  January 16, 2009 at 7:10 am

  #198495

  Good morning everyone.

  I have a question that I have not been able to find a good answer on. Well, it is actually a couple of questions.

  Last night we had an issue with Service broker after moving a database to a new server. The move occurred the night before last.

  The service broker sys.transmission_queue was throwing out the message:

  "The session keys for this conversation could not be created or accessed.

  The database master key is required for this operation"

  Come to find out that the individual responsible for doing the ALTER Database XXXX Set New_Broker forgot to do it. There are now 30+ connections on the server and almost 4K messages in the transmission_queue.

  We didn't want to kill all the connections and wipe out the queue if we could help it, but the larger priority was not killing the connections as these were orders that were being placed for our client.

  To correct the error without killing the connections, I used the following commands:

  open master key decryption by password = 'Password'

  alter master key regenerate with encryption by password = 'Password'

  My questions are this:

  1) What is the difference in the system between setting a new broker with the ALTER DATABASE command and ALTER MASTER KEY Regenerate command. To me is sounds like the Alter DAtabase used the current master key and then creates the service broker off it this, while the alter key command actually changes the master key for the database and all corresponding keys to go with it.

  2) I was not able to determine if the almost 4k worth of messages got flushed out of the service broker when doing the open/alter. Does anyone know if they did go through or if they got flushed?

  3) For future moves, which is better. Alter Database or Alter Master Key?

  Thanks for the assists.

  Fraggle.