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:
|
1 |
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. |
Super Sleuth
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?
NOPE.
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!
19 Comments. Leave new
I don’t see a point in these local VMs/LAN servers to use encryption.
Have been biten before so my admin storedproc created endpoints are
FOR DATABASE_MIRRORING (ROLE=PARTNER, ENCRYPTION=DISABLED)'always. And UI is not a option when I have to failover 200+ dbs in under a minute or two.I have a feeling that the Darling Does SQL podcast would be kind of funny!
Nice post.
If you’ve ever tuned into Office Hours, you know that’s not the case 🙂
This is more likely NTLM authentication failure between DC and SQL server.
Did you recently change the account password?
Nope. The whole problem was different encryption algorithms between primary and witness.
It’s a test environment, so everything has always had the same password.
Oh.. The GUI is using different algorithm? I miss that part.
Gotcha.
Thanks,
ROFLCOPTER – very funny post with good info to boot
There is a recently published KB that looks releated:
https://support.microsoft.com/en-us/kb/3135852
I haven’t set up mirroring, we may decide to go AlwaysOn Failover Clustering at some point. I did enjoy the article and smiled at all your references to The Cure. INTO THE TREES!
That’s impossible. No one likes The Cure!
I’m awful at sales pitches, but this is probably the best $300 you can spend if you’re thinking about setting up any kind of HA/DR:
https://learnfrom.brentozar.com/product/dbas-guide-to-sql-server-high-availability-and-disaster-recovery-18-months-access/
That’s impossible, I love The Cure!
Nice H.P. Lovecraft reference, too.
I’m tickled by the idea that database problems are the manifestation of eldritch horrors.
We do mirror most of our DBs, those that can be are on witness failover.
Certainly saves you getting out of bed at stupid o’clock to fail a DB over because your website has gone down because (insert any number of causes) with the big plus that tuned right the website doesn’t even blip, let alone send you alerts.
You would be surprised what can cause the failure of the witness, especially when all the servers are VMs, even where using common credentials on a common build built with the same build script. A common cause is just that the VM has been live migrated since the Mirroring Endpoint connection was established (it doesn’t drop existing connections but prevents [I]some[/I] new ones via the GUI).
My first step on seeing that error is to allow the mirror to start without the witness. In the blank space where your witness was put ‘TCP://ORACLEDB.darling.com:5022’ and click ok. This seems to resolve most issues and tends to mean the next one you set up with the same principal, standby and witness works first time.
Should I trouble shoot for cause each time?
Maybe, depends on time commitments and the preference of the person paying the bills.
Sometimes what gets you up and running helps too.
thanks that’s useful info – maybe dbm just doesn’t like you – after all, you have used the term primary instead of principal 🙂
I think it’s just mad because it’s being deprecated.
Heh. I ran into the same issue but the other way around, starting with mirroring and then adding AG.
Hi Erik?
Nice post, and love the sense of humour!
I am recently having some “time wait” on my DB mirroring setup in HA with a witness for an ASPState database, freezing the the web application login page.
Have you experienced such a problem?
thank
Stephane – you don’t really want to use high safety (synchronous) mirroring with the ASPState database because it’ll be pretty slow. The ASPState database involves a lot of really tiny writes every time people hit web pages.
If you want a highly available, and highly inexpensive, back end for session state, check out these webcasts:
https://www.brentozar.com/archive/2013/03/saving-session-state-video/
https://www.brentozar.com/archive/2013/10/solving-session-state-slowndownsvideo/
great article! wondering how the mirroring encryption varies from the network encryption?!
also, I created an alert to call to a job to failover databases if one of the other DBs failed over. It is essentially pretending to be AG. Works pretty well, so creating automatic failover is doable.
P.S. I love the CURE