Network Configuration of SQL Server Servers and Always ON Availability Group Synchronization From a Different Ethernet Card

 

Before reading this article, I suggest you read my article “SQL Server Always ON Availability Group” to understand the operation logic of the Availability Group.

Always ON Availability Group has 3 types of network traffic.

Client Traffic: This is the traffic that applications access to the database.

Public Traffic: This is the traffic that Availability Group provides synchronization between the primary database and the secondary database. This process is provided by endpoints. I mentioned how endpoints are created in the article “SQL Server Always ON Availability Group“.

Heartbeat traffic: Windows Cluster checks whether the nodes in the Cluster are running and accepts the connection. If the Primary node is not responding, the automatic failover operation is performed.

As Best Practice, Microsoft recommends 3 separate Ethernet cards for these 3 networks. Of course, each ethernet card must be in a teaming structure and should have a backup on the ethernet backbone (I explained below). So each server must have 3×4 = 12 ethernet ports.

  1. if the traffic of application is high, then you should use 10gbps x 4 for client traffic
  2. If the traffic of applications is high, then you should use 10gbps x 4 for the synchronization of availability group
  3. You should use 1gbps x 2 for Heartbeat Traffic.

 

What is Teaming?

Imagine that you’re connected to the network switch in your organization through two Ethernet ports on the server. If you do teaming for this 2 ethernet port, the traffic will continue if another one is broken. 10 + 10 = 20gbps can be achieved by teaming.

In other words, we have a backup port and the traffic that can be done is doubled.

 

So what will happen if the netwok switch is broken?

Therefore, there are 2 network switches that can work with each other. If one of the network switches fails, the other will be active. The number of ports we needed with Teaming was at least 2. Since we use 2 Network switches, the number of ports we need should be at least 4.

If you are going to purchase a server to be used for SQL Server, I recommend that you determine the number and capacity of the ethernet ports on the server after reading the above descriptions.

Open the Failover Cluster Manager to see the network configuration on your cluster. If you come to the Networks tab via Failover Cluster Manager as below, you can see the ethernet cards defined in the windows cluster. In our example one is Cluster Only (for heartbeat traffic), the other is Cluster And Client (heartbeat, alwayson synchronization and client traffic)

 

If you right-click Cluster Only and click properties, the following screen will appear.

 

  • If you select Allow cluster network communication on this network, this Ethernet card will be used for heartbeat traffic. If you don’t check the checkbox just below it and click OK, it will appear as “Cluster only”.
  • If you check the allow clients to connect through this network checkbox, you will also allow client and always on traffic.
  • If you select “do not allow cluster network communication on this network”, you will prevent heartbeat traffic.

 

Under normal circumstances, if you have not set up a different Ethernet card for Always On Availability Groups, always on’s synchronization traffic will be made on the Ethernet card you set as Cluster and Client. But using a different port for always on’s synchronization traffic can be more useful in some cases.

 

What is the example network configuration of a server that uses SQL Server Availability Group?

 

You have 2 servers and you are using the availability group on these 2 servers.

You have reserved four 1 gbps ports for heartbeat traffic. (Two of them are going to primary network switch with teaming. The other two are going to backup network switch with teaming.) For Heartbeat connection, you need to set Cluster only as I explained above.

You have reserved four 10 gbps ports for heartbeat and client traffic. (Two of them goes to primary network switch with teaming. The other two are going to backup network switch with teaming.) For this Ethernet card you need to do Cluster and client for both heartbeat and client traffic.

You have reserved four 10 gbps ports for Always On synchronization traffic. (Two of them go to primary network switch with teaming. The other two go to backup network switch with teaming.)

For this Ethernet card, you can select “Do not allow cluster network communication on this network”. But you must show the IP that is supplied with teaming to the availability group endpoints.

 

For this process, when creating the endpoint with the help of the following script, you need to type the IP given with teaming instead of ALL in the “LISTENER_IP = ALL” section.

 

You need to type in parentheses when writing IP. Example-> (10.6.132.45)

 

If you have already created your endpoint, you should get your endpoint’s create script via SSMS.

You must enter the teaming IP instead of ALL as described above and ALTER instead of the Create statement.

 

If you change the IP of your endpoint with the drop create method, your secondary database will drop into not synchronizing mode after you do this. In order to re-synchronize, you must first take your secondary database to suspend mode as follows and then click the resume data movement as follows.

 

You must do this in the primary and secondary replicas of the availaibility group. You must set the IP of the ethernet card you define for the availability group on each server to the endpoint that will run on that server.

 

Example of Endpoint Configuration:

Assume that the teaming IP of the ethernet card defined for availability group on the primary server is 10.6.123.41. When you change the endpoint of the primary instance on this server, you must type (10.6.123.41)instead of ALL.

Assume that the teaming IP of the ethernet card defined for availability group on the secondary server is 10.6.123.42. When you change the endpoint of the secondary instance on this server, you must type (10.6.123.42)instead of ALL.

In this article I talked about Network Infrastructure. In the article “SAN Infrastructure of SQL Server Servers and the Story of a Query“, I will talk about the SAN infrastructure between SQL Server Servers and storage.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

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