Well obviously I wouldn't drug him, I'm more curious about how scents and oils can help them feel calm, like lavender with people.
I don't want to go away though, because I want to watch the fireworks. I won't take him outside with me, of course.
Flooding is a behavioral technique that is intense, and not favored as much as systematic desensitization, but it has been used in the past. There is more emotional stress involved with flooding. But it basically works by exposing your dog to the feared sound while keeping the dog in a safe and calm environment. Basically, you would take Sherlock with you, keep him close and exude calm relaxation. You have to be careful that you are not nervous about stressing him out, as he could mis - interpret your stress to mean that both of you are in danger.
If there is a toy or blanket that he associates with safety, you should bring that along. Don't fuss at him for being nervous, reward him for staying with you. And be willing to leave the fireworks show early.
With systematic desensitization, you could wait for the show to start, and at the first sign of anxiety, work to calm him, by petting, remaining calm, giving a massage, etc. Once he is calm, take him outside, and repeat whatever calming techniques you started in the house, if necessary. Then you take him by degrees closer to the fireworks display. Stopping when necessary as you get closer, until Sherlock regains his compsure. With luck, you will arrive in time to see the finale.
Keep in mind that if you can work with him successfully, you could still miss out on this year's show, but may be able to take him to the next.
Trying to do all of this in one night might be a bit of a stretch. It can take weeks, or months with people to desensitize them to a feared stimulus. So don't be disappointed if you are not entirely successful.
You might start early by practicing some doggie calming exercises. I'm not real sure what those may look like: sitting next to him, breathing calmly and slowly yourself, while rubbing his tummy?
Good luck.