Back to Overview

Adding Replicas into existing Availability Groups

Last week I have blogged about how you can run the Availability Group traffic through a different network card. In today’s blog posting I want to talk about some side effects of this approach when you want to add a new Replica into an existing Availability Group.

Add new Replicas

SQL Server Management Studio is great and awesome, but you really have to be very careful how you use it, because there are so many different side effects that you have to be aware of. Imagine you are routing your Availability Group traffic through a dedicated network card as I have described in my last blog posting. My lab environment consists of 3 VMs, which are using the following private IP addresses for the Availability Group traffic:

  • 10.10.1.1
  • 10.10.1.2
  • 10.10.1.3
And now let’ use SQL Server Management Studio to add – let’s say – a third Replica into an existing Availability Group:
 
Adding a new Replica
 
If you are just finishing the SSMS wizard, you will end up with a new Replica, which doesn’t really work. You are now maybe asking why? Well, the problem is that SQL Server Management Studio just uses the IP address of the public network card for the TCP address of the newly added Replica. Just look at the following screenshot:
 
This is wrong - completely wrong!
 
This will not really work, because at the TCP address TCP://sql2016ag-node1.sqlpassion.com:5022 there is *NO* Endpoint running and listening to incoming Availability Group Traffic! The (existing) endpoint is listening at the IP address 10.10.1.3! As you can see the SSMS wizard just breaks your Availability Group setup. If you want to add the Replica in a correct way, you have to specify the correct IP address through the SSMS wizard as shown in the following screenshot:
 
This is now correct!
 
Ouch…

Summary

When you route your Availability Group traffic through a dedicated network card, you have to be aware of this problem. Otherwise your newly added Replica will not be part of your Availability Group (it can’t be joined), and you have to troubleshoot the underlying root cause of the problem.

So please check the IP address accordingly, when you add the next time a new Replica into an Availability Group. In another upcoming blog posting I will also show you how easy it is to completely crash SQL Server Management Studio when you add a previous removed Replica back into an Availability Group – but this is a topic for another day… 😉

Thanks for your time,

-Klaus

Leave a Comment

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

SQLpassion Performance Tuning Training Plan – Signup

Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan – you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.