A funny thing happened on my way to set up Mirroring…

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.

Everything looks good!

Everything looks good!

Alright, let’s set up Mirroring…

Configuring stuff is cool, right?

Configuring stuff is cool, right?

Yeah yeah next next next

Yeah yeah next next next

Service accounts whatever BORING

Service accounts whatever BORING

GREEN LIGHT GO!

GREEN LIGHT GO!

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.

I'm hitting this button and jumping into a mile of Laphroaig.

I’m hitting this button and jumping into a mile of Laphroaig.

HIGH FIVES ALL ARO-

Y THO?

Y THO?

This is the error text:

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~~

The Endpoint is showing up on the Witness and what is this?

The Endpoint is showing up on the Witness and what is this?

Where are we now?

This is a good time for a quick recap

  1. Mirroring is up and running synchronously
  2. The endpoint is configured on the witness
  3. We get an error when we try to connect the witness

TO THE ERROR LOG!

I should have done this hours ago.

I should have done this hours ago.

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.

The Cure - Primary

The Cure – Primary

Oh. AES. Okay. Cool. Thanks.

Oh. AES. Okay. Cool. Thanks.

RC4 WHY WOULD YOU DO THAT?

RC4 WHY WOULD YOU DO THAT?

Since we have them both scripted out already, let’s just drop and re-create the Witness Endpoint with the right encryption algorithm.

INTO THE TREES

INTO THE TREES

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.

Scriptin'

Scriptin’

Meanwhile, back on the Primary…

I AM SO SMRT

I AM SO SMRT

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!

, ,
Previous Post
Introducing Our Latest Employee, Richie Rump (aka @Jorriss)
Next Post
When Shrinking Tempdb Just Won’t Shrink

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.

    Reply
  • I have a feeling that the Darling Does SQL podcast would be kind of funny!

    Nice post.

    Reply
  • This is more likely NTLM authentication failure between DC and SQL server.

    Did you recently change the account password?

    Reply
  • ROFLCOPTER – very funny post with good info to boot

    Reply
  • There is a recently published KB that looks releated:
    https://support.microsoft.com/en-us/kb/3135852

    Reply
  • Micah Harwell
    February 3, 2016 8:19 am

    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!

    Reply
  • I’m tickled by the idea that database problems are the manifestation of eldritch horrors.

    Reply
  • Matthew Holloway
    February 3, 2016 4:04 pm

    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.

    Reply
  • thanks that’s useful info – maybe dbm just doesn’t like you – after all, you have used the term primary instead of principal 🙂

    Reply
  • Alex Friedman
    February 4, 2016 2:02 pm

    Heh. I ran into the same issue but the other way around, starting with mirroring and then adding AG.

    Reply
  • Stéphane Berhault
    February 4, 2016 10:59 pm

    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

    Reply
  • 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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}