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:
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:
What the heck? So I took a look at the error:
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.
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.)
Too true. The nomenclature makes my head spin a bit. I’m working on it.
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?
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.
Bob – actually, you can’t. Domains are required.
@ 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)
For the new SQL2012 Always on features. Is this only available in the Enterprise Edition of SQL2012?
Yep, just Enterprise Edition.
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.
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
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/
Does a multi subnet setup require storage replication with always on?
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.
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