SQL Centre of Excellence

When setting up an AlwaysOn Availability Group with a synchronous replica you may find that the secondary replica never becomes synchronized and you are presented with the following error “Data synchronization state of some availability database is not healthy”. Effectively the databases restore to the replicas, but synchronization does not start.

The first thing to check is network connectivity. We must bear in mind that the setup Wizard uses the netbios name and the endpoint then transmitts the compressed log stream uses the fully qualified domain name on the endpoint. (AlwaysOn uses the same endpoints as mirroring with the same default port -  5022)

In my case the cluster installs a hidden virtual network device for heartbeat communication and this network connection had made its way to the top of the binding order, messing up connectivity.

You can double check the network by using ipconfig to verify the binding order and also pinging the node and its partners to check correct IP addresses are used.

We can see from the below that the ip used is one from the failover cluster and not the proper network card


The screenshot below shows that routing is coming from the hidden failover
cluster virtual network and not the proper network card.



So what’s the solution ?

Two options spring to mind.

a) Use fixed IP addresses for all nodes and then add entries to the “hosts” files for both netbios and fqdn.

b) Download the Nvspbind tool to change the binding order from the command line

After using nvspbind and rebooting I now get the proper IP address and not the automatically assigned one from the hidden “Failover Cluster Virtual Network Interface” (see below).



Lessons for Production Servers for Always On

1. When setting up a Failover cluster for Always On consider using fixed IP addresses for nodes.

2. Do a ping test of both computer name and FQDN of all nodes before creating Availability Groups!

The next SSAS Maestro course is coming to Milan, Italy on July the 9th-13th and is open for places!


For anyone serious about Analysis Services, this is a MUST attend course.Sure it’s €4,500 euro, but the early SQL Ranger courses were about €20,000 excluding accommodation and having to take a few weeks off work as a billable resources. This is only a week for a chance to learn from some of the top SSAS minds on the planet.

I really like how the Maestro has evolved from an MS internal and SQLCAT initiative into a privately run offering. I was lucky enough to attend the second series of internal rotations and it looks like Chris, Marco and Thomas have taken on board a lot of feedback from earlier attempts:

  • Its now open to anyone, but you’d probably need to have at least a good few years experience to get the most out of the course
  • It has been expanded to a full week. The original was only really three days and bulleting through 60 slides and hour its hard to soak as much in as you need to.
  • Most importantly Marco and Chis have committed to have results out within three months (with 2 months for time to submit homework). This is a breath of fresh air as marking on the first two rotations was a lot longer than anyone would have hoped for, up to 9 months!
  • On my rotation the Instructors didn't get a lot of prep time to own the content, as it was shorter notice for them. Marco, Chris and Thomas will have delivered this a few times now and ironed out any kinks I am sure. As well as adding a lot of unique value from their own backgrounds. 
  • I like Redmond myself as you get more guest appearances from developers like Akshai, but Milan looks like a good setting as well. Not that you will get much spare time to sight see.
  • It has been expanded to include some Tabular content

An old complaint about Analysis Services is we don’t really have as many “rock stars” as the SQL Server DB Engine community like Paul Randal and Kimberly Tripp who have arguably the most slick, professional, and high end courses with their flawless MCM track record and the “immersion” series. This Maestro course is definitely the closest there is Winking smile

Personally I would love this to be expanded to a three week course and cover a broader area such as: SSIS, SSRS, SharePoint, and more on MDX and DAX tuning. This will help it grow into the “BI MCM” Maybe there could be 2-3 weeks with each week optional – this is the format that the immersion now follows.

For those of you who didn't get to my sponsor session at #sqlsat105, I’m presenting a deep dive on SQL 2012 Always on at the Dublin SQL Users Group on the  23rd May in Microsoft Atrium Building, details are here:


If you are working with high availability or using replication to offload reporting, this session is a must see for you.

I plan to step through the main features of SQL 2012 Always on and migrate a live enterprise application from SQL 2005 to SQL 2012 to always on, while demoing features such as:

  • zero down time migration
  • Installation and Configuration
  • planned failover
  • cluster enhancements
  • unplanned failover
  • Using secondary replicas
  • Disaster Recovery
  • Some insight into the deeper workings: Virtual Network Names, Listeners, node voting, quorum, health model, etc.

Come along on the 23rd May. I’m planning to go for some pints afterwards too, so will be around  to discuss anything SQL or BI related.

Note this is on Wed, not the usual Tuesday night!

Page List

Page List