I’ve set up Mirroring about a billion times
I’m not bragging about that. I’d rather say that I set up a billion AGs, and not one of them ever failed. But then I’d be lying to you; those things fail like government programs. One thing I’d never done, though, is set up Mirroring with a Witness. I never wanted automatic failover, because it’s only one database at a time. If for some reason one database out of all that I had mirrored ever turned Ramblin’ Man and failed over to another server, there would understandably be some application consternation. Not to mention any maintenance and internal operations. They don’t react well to sudden database unavailability.
Of course, doing anything for the first time is horrible. Just ask my second wife.
Here’s where things got awkward
I have my databases! This is my top secret development environment. Stack Overflow is in an AG, and I had set up two other Mirrors: one synch and one asynch. I wanted to have a variety of setups to test some scripts against.
Alright, let’s set up Mirroring…
This is so easy. Seriously. Why doesn’t everyone do this? Why do you complicate your short, short lives with Availability Groups? Are they AlwaysOn? Are they Always On? WHO KNOWS? Not even Microsoft.
HIGH FIVES ALL ARO-
This is the error text:
The ALTER DATABASE command could not be sent to the remote server instance 'TCP://ORACLEDB.darling.com:5022'. The database mirroring configuration was not changed. Verify that the server is connected, and try again.
Alright, that’s silly. I used the GUI. Instead of going to bed I’ll spend some time checking all my VM network settings. BRB.
I’m back. They were all correct. I could ping and telnet and set up linked servers and RDP. What in the name of Shub-Niggurath is going on with this thing?
I can even see the Endpoint! So close, and yet so far~~
Where are we now?
This is a good time for a quick recap
- Mirroring is up and running synchronously
- The endpoint is configured on the witness
- We get an error when we try to connect the witness
TO THE ERROR LOG!
Well whaddya know? That’s a really good clue. Encryption and stuff. There’s no compatible algorithm. Ain’t that somethin’? You’d think that Microsoft would be cool about setting up the same kind of encryption across all the different Endpoints, if using different encryption would cause the setup to fail. Right guys? Heh. Right? Hey, hello?
Alright, let’s see what I need to be a matchmaker.
Since we have them both scripted out already, let’s just drop and re-create the Witness Endpoint with the right encryption algorithm.
That did not result in a forest fire. I’m hopeful. Sort of. It’s been a long night and I think I can see tomorrow from here.
Meanwhile, back on the Primary…
It worked! Now I have a Witness, and I can shut all my VMs down. That was so much fun.
What did we learn?
Microsoft hates you and doesn’t want you to sleep. Just kidding. Mostly. But seriously, why would they do that?
It mostly goes to show that it’s always a smart idea to use that little script button at the top of (most) GUIs in SSMS. Who knows what kind of foolishness you’ll find? A little reading can save you a lot of time troubleshooting errors that make you feel insane.
Thanks for reading!