Want a jump-start on testing the hottest scale-out feature in SQL Server 2012? Kendra Little will tell you why it’s critical that you configure your own lab, and what you need to get your test environment set up. She’ll also detail how to avoid some gotchas that could cost you hours of frustration. She’ll demonstrate the steps you need to enable the Availability Group feature in SQL Server 2012 and how to create and test your first Availability Group. This half-hour session is appropriate for DBAs with one or more years of experience with SQL Server:
Links mentioned in the video:
- Virtualization platforms – VirtualBox, Parallels
- Windows Server 2008 R2 Evaluation Download
- Re-arm command to extend evaluation period to 240 days
- SQL Server 2012 Evaluation Edition
- SQL Server 2012 Developer Edition
- How to create a domain tutorial
- How to use sysprep tutorial
- How to set up SQL Server 2012 Availability Groups tutorial
Liked this session? We’ve got webcasts coming up on Perfmon, heaps, Jes Borland, and more. Register for our upcoming free webcasts.
James Traub April 25, 2012 | 9:42 am
Perfect! I am getting this setup at home and you have made my life much, much easier! Thank you Kendra. You rock solid.
Brian Ezell April 25, 2012 | 12:45 pm
For those of us that don’t have access to hardware beefy enough, there is a cheesy alternative. Microsoft’s TechNet (http://technet.microsoft.com/en-us/virtuallabs/hh859575) has setup virtual labs you can connect to. That’s what I’ve been using. Be wary though! These are based on Denali CTP0 so some of the code base is a bit out of date. Also, sadly it only has 3 instances to play around with (and the 150 minute time limit leaves something to be desired)
– Brian
Stefan April 25, 2012 | 3:01 pm
Great webcast. I´m installing and cloning machines like crazy to get my lab environment up and running. you´re a real inspiration and source for learning all of you.
Thanks
Stefan
Peter Trast April 30, 2012 | 9:50 am
I have setup this up several times. I can do manual failover all day but automatic failover does not always work. More often than not, after the first simulated failover test, which is usually successful, I end up with the AG in a “Resolving” mode and have to restart the original primary instance to get the DB and AG online.
Have you seen this problem? And if so, how did you fix it? This happens regardless of how I test — by shutting down the primary instance or disabling the NIC.
Thanks!
Peter Trast
Brent Ozar April 30, 2012 | 1:38 pm
Peter – ooo, that’s a bit beyond what we can cover in a blog comment. Check out Allan Hirt’s book Pro SQL Server 2008 Clustering for more basic information on troubleshooting Windows clustering in general. For example, Windows will prevent rapid automatic failover back-and-forth between the same two nodes, so you might be hitting that. In order to get to the bottom of it, we would need to go through the error logs – I’d start there.
Kendra Little April 30, 2012 | 1:44 pm
I like what Brent said about the error logs. The question that comes to mind for me is whether it’s a quorum issue preventing it from coming online, and how exactly you have the quorum configured in the cluster.
I personally haven’t seen that error, and we have a client running AGs in production who has had automatic fail overs kick in in the real world and hasn’t had the issue– so it definitely doesn’t have to be that way!
Nathalie October 3, 2012 | 8:27 am
Hi! Peter, I’ve got the same problem. Please let me know if you’ve already find a solution. At the moment I’ve no idea about this bug
Kendra Little October 4, 2012 | 11:17 am
For automatic failover, you need to have both partners in synchronous commit mode. I noticed once that it was easy to set this up and not notice that the primary was not in syncrhonous commit mode, although the secondary was. I believe the symptoms were much like you are describing, so I would check that first.
Peter Trast April 30, 2012 | 3:16 pm
I made some changes. When I use Node and Fileshare majority (on 3rd server) and disable the AG Primary nic, I get failover and the disconnected node goes into “Unknown”, and stays there after begin reconnected until I go to manage the cluster which cause it to come online as secondary. This seems to be a proper behaviour. And from what I have read, stopping the primary instance service should NOT trigger a failure to prevent a split brain scenario because the secondary can still contact the box…? At least that is how it is acting.
So shutting off or disconnecting the Primary now causes failover, but stopping the Primary service does not. Did this act differently for you?
Rita Bhatt May 22, 2012 | 8:49 am
Kendra,
I was able to create the cluster on Vlab Manager. I had to disable the virus scan applications as well…Besides disableing the windows firewalls. I think you should add this to your tips….I have added my comments to your blog.
You rock!!!!
Thanks so much for all your help.
Rita
Bigbillguts July 10, 2012 | 1:58 pm
Excellent topic!
I went through setting up in a lab and evrything worked quickly and easily. When I got to setting up an N environment in the real world, however, there were some gotchas that I wasn’t ready for. Through my travels in AlwaysOnHighAvailability testing, I have found a few important considerations:
- Make sure to test setup using real-world permissions and access. To set up the Windows Serivices Failover Clustering components and listeners, you need to have domain privileges to create new domain computer objects. In my lab, I was given admin rights on the servers, but I was not allowed to have create domain rights. To work around this, I had to invoke the help of a domain admin to create the WSFC and to stage the listener entries in the domain.
- Size the quorum disk appropriately. When the cluster is set to use file share for quorum, the Availability Group setup will actually use the file share to hold backups of your database while initializing. Maybe not a big deal in most labs, but, in my lab, I am working with a production sized database (2 TB) and only sized the file share to 10 GB.
- If your lab doesn’t allow you carte blanche, be aware that each node in your cluster will need three IPs – one physical, one for the admin console, and one for the listener.
Just wanted to put this out there to help anyone else that is working through getting a test environment set up!
Cool DBA October 23, 2012 | 8:23 am
Question: In your Demo, how did you make it ‘synchronized’ on the database on your secondary replica asynchronous-commit mode?
Kendra Little October 24, 2012 | 12:14 pm
Hey– great question.
The demo was six months ago so I had to go back and look at exactly what I did.
I didn’t show the setup for the whole AG. The Squeals12 instance was using async and shows as synchroniZING. The Squeals13 instance was using synchronous commit with manual failover.
Tim Harkin November 25, 2012 | 8:29 pm
Once again, while researching something, I come to find the best information available is here. Thanks once again Kendra (and Brent, Jeremiah, and Jes). Thank you thank you thank you!
MArina February 7, 2013 | 6:21 am
On Failover ,when I issue query @@servername on Node1 it returns Node1 instead of Node2
any Ideas