Last weekend (May 5th, 2018), I attended SQL Saturday #710 and it was a fun and educational time. I quite enjoyed it AND I presented at it. First time presenting at a large event and had a blast.
My presentation was on Service Broker, which for those that don’t know is a system built into SQL Server that allows for asynchronous processing. The presentation went mostly smooth with only a few little things that came up that I was uncertain why they appeared. I’m still looking into it and will blog about it when I figure out why null messages were being sent after restoring from live to test. This should not have happened.
For those who have not attended a SQL Saturday, I strongly encourage it. It is an excellent networking opportunity as well as a great way to get some free training. Did I mention it is free? Completely free. The one in Edmonton offers a large lunch for $10 which I recommend.
If you are looking for an opportunity to speak on a topic, I recommend talking to your local PASS chapter leader and looking at the SQL Saturday website to find SQL Saturdays close to you that you want to present at.
This was my second time attending a SQL Saturday and I spent most of the time networking and preparing for my presentation and trying not to be nervous. In the end, I had nothing to be nervous about. One person had been using service broker for a while and had some interesting questions that I had not come across with my service broker usage related to encrypted databases. This is something I am going to be experimenting with and will hopefully have a good blog on. There were also some people who were very new to SQL let alone service broker and I think I taught them some fun stuff with it.
As SQL Server professionals, I feel we should be constantly learning new things and even while preparing for this presentation I learned something new. I learned that when you restore your test system to live, you don’t need to drop and recreate all of your database certificates and database master key! Everything I could find online indicated that that was the method you needed to do. Turns out you just need to regenerate your master keys! While I was thinking about that, I realized you likely don’t even need to regenerate the master key – you likely can just restore the master key from a backup! But I have not tested this and I don’t like to recommend or present on something I have not personally tested to ensure that my findings are correct and repeatable. Regenerating the master key is a simple 2 liner – first open the database master key then alter the database master key, but any messages that are currently queued up will be destroyed as it regenerates the certificates and thus cannot decrypt the certificates.
So while I prepare my presentations (I am breaking the “troubleshooting service broker” presentation up into 2 different presentations), I plan on doing more testing and figure out new and interesting ways of handling service broker restores! Next year I will have 2 presentations that I will be doing at the Saskatoon SQL Server User Group on service broker. First being a “what is service broker and how can it help me?” and the second is “my service broker is broken!!!”.
But until next time, keep on SQL-ing!