Let me first start by saying due to a lack of free diagnostics within Azure, you will need access to insights. This isn't something you would do with an MSDN subscription and might baulk at with your own paid for one. Why not with MSDN, because with MSDN you can only use a minimal amount of Network Watcher resource and you require network watchers to do any diagnostics at the network level. I hope you have a friendly boss who will give you a space to do some learning and develop these needed Azure skills. alternatively, or you have a bit of budget to do your own subscription and pay for resources. Make sure you have budget alerts to not blow out your costs and delete the Network watchers as soon as you have a working system.
We had to investigate the linkage between the Azure load balancer and the SQL Server Cluster and Network addresses on the SQL Server cluster. To see what was happening we needed App Insights and Network watchers.
What was the scenario
We had a SQL Server cluster without yet enabling Always on Services. We found that whilst we could connect to the node from which SQL Server was running we couldn't get a connection from any other server or system in our subnet, the problem was hidden as you cant do much to find out why what you are seeing is the problem and where it is.
We installed Wireshark, yes it is your best friend here and yes it is telling the truth even though it seems stupid it isn't seeing what you expected. We couldn't see any traffic when we tried the connecting to the cluster. We got the outbound packet to initiate a connection and then "crickets", nothing responding from the cluster or the load balancer.
Let's go back a step, one of the items in your list of tasks when doing this is to create the SQL cluster and then an internal load balancer.
Let me tell you the instructions on how to configure the internal load balancer in all the Microsoft documents and any blog posts I came across were terrible in the detail.
The load balancer requires Health Probe and that requires an active port on your individual nodes to validate their availability.
People have listed ports around 50k things like 52486 or 62159. Here is the missing bit it has to be an active service running on your server and it can't be anything to do with SQL Server, they are bound to the cluster and you are not able to access them via the individual node IP address
How do you work out a port, two things if you have a reason or don't overly care, you can install IIS and you will have port 80, however, try netstat -an and have a look, you will get entries like this
 |
| netstat - listening ports |
Proto Local Address Foreign Address State
TCP 0.0.0.0:445 0.0.0.0:0 LISTENING
Use one of these ports for your backend service that is the ones near the start of the list and with the Local Address starting with 0.0.0.0, these are listening on all IP addresses on your system.
Once you configure the load balancer with this in the above section you will find your cluster SQL Server will become available to your other services in your Azure space. You will now be able to use SQL Server Management Studio to connect to the active node in the cluster on the cluster IP address
No comments:
Post a Comment