Mar 12 2012

SQL Server 2012 AlwaysOn & A Thorough Setup

It’s surprisingly easy to set up the new AlwaysOn features. I’ve done it on VMs running on my laptop, from scratch, three times in the last few weeks. It’s easy because there are a set of validations that your run for the cluster and for the AlwaysOn setup that ensure you’re going to get a successful install… or do they?

I hit a situation where it didn’t work correctly, so I thought I’d share it in case others ran into it.

The setup is straight forward. I have network, contoso (yes, I’m using Microsoft training & documentation, it’s a beta, but you should see it available soon), with a domain controller and five servers all in a failover cluster. They passed the cluster test, so all five are hooked in. I went to use the Availability Group Wizard to set things up. I chose a database with a  full backup in Full Recovery mode. I added a second server to act as the secondary in the Failover Group. I chose a share where the backup was kept and was accessible to the other server (I even checked this). Then I ran the validations:

image

Which came back all green except for the warning about the listener configuration (which, you don’t need to set up an Availability Group, just to access one from an app, seemlessly).

I checked the summary and then built my group, which took WAY too long, and was presented with this:

image

What the heck? So I took a look at the error:

image

Connection not active? Yes it sure is. I went round and round through this. Seriously. First, I found out I had a database in 2008R2 compatibility mode (and how funny is that to be saying at last). Fixed it. No joy. Took a new backup (dummy). No joy. Revalidated every possible check from this server. Nothing.

In desperation I went to another server and tried setting up AlwaysOn between it and a neighbor. It worked… What. The. ****?

Then, I went back and reread the error message (always a good thing). “The CONNNECTION to the primary replica is not active” Well, that’s how I read it the second time. So I tried connecting to the primary from the secondary, just through SSMS. No connection. I went back to the primary & double-checked, yes, it could connect to all four of it’s brother & sister servers. Checked each of them for connectivity back… nothing. I had a network problem that I didn’t realize was there.

So, moral of the story, just because you’ve run the tests that MS provides for AlwaysOn doesn’t mean you won’t run into an issue.

14 Comments

  • By Brent Ozar, March 12, 2012 @ 8:54 am

    Another thing the wizard doesn’t catch – different drive letters/paths for databases. It’ll work at first, but when you try to add a data file to the primary, whammo, the secondary will fail.

    One minor quibble – I’d use the term AlwaysOn Availability Groups so the audience understands you’re talking about those. AlwaysOn also refers to the clustering piece. (I’m going to be struggling with this for years, hahaha.)

  • By Grant Fritchey, March 12, 2012 @ 8:58 am

    Too true. The nomenclature makes my head spin a bit. I’m working on it.

  • By Allan Hirt, March 13, 2012 @ 7:33 pm

    It also doesn’t check if you’re configuring it on a valid WSFC, but that’s besides the point.

    Brent – I’ve been trying to standardize terminology for clustering for years. See how successful I’ve been?

  • By Bob van der Sluis, March 14, 2012 @ 10:15 am

    Hello Brent,

    How easy is the setup of AlwaysOn without a domain? E.G. mirroring without domain it is more difficult to set up and I have never seen a website that covers that story, always the scenario with a domain.

  • By Brent Ozar, March 14, 2012 @ 10:29 am

    Bob – actually, you can’t. Domains are required.

  • By Bob van der Sluis, March 14, 2012 @ 10:34 am

    @ Brent,

    Thanks for the update. In our hosting environment we try to avoid the use of a domain so that means we will still use mirroring (with a witness)

  • By Mark S., March 14, 2012 @ 10:38 am

    For the new SQL2012 Always on features. Is this only available in the Enterprise Edition of SQL2012?

  • By Brent Ozar, March 14, 2012 @ 10:42 am

    Yep, just Enterprise Edition.

  • By Grant Fritchey, March 14, 2012 @ 11:03 am

    Wow, late to the party, but Brent is running the blog now. Cool. I’m taking a vacation.

    Like Brent says, Domain, yes, Enterprise yes.

  • By Terry Khatri, November 1, 2012 @ 10:51 am

    Hi
    Soon I am going to try to set up sql server 2012 high availability on my laptop for certification purpose, it would be great if someone please create a step by step guide for that – Tks

  • By Grant Fritchey, November 1, 2012 @ 11:00 am

    Hi Terry,

    There is. It’s the Bare Metal workshops from Microsoft. They’ll walk you through setting up everything. Check ‘em out here: http://sqlbaremetal.codeplex.com/

  • By Skip, April 4, 2013 @ 6:08 am

    Does a multi subnet setup require storage replication with always on?

  • By Grant Fritchey, April 4, 2013 @ 3:05 pm

    I have not done one, so take this answer with a very large dollop of salt… No, I don’t think so. But you’d be better off tracking down someone who knows more and has done that type of setup.

  • By yogi, May 18, 2013 @ 1:23 pm

    Terry – May be you no longer need it, but here is a step by step for alwayson AG, someone may find it interesting: http://binyoga.blogspot.in/2013/05/step-by-step-sql-server-2012-alwayson.html

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment