In our last post – SQL Server Availability Groups in a Workgroup Failover Cluster inside Microsoft Azure, we created a domain-less Windows Server 2016 Technical Preview 5 Failover Cluster with SQL Server AlwaysOn Availabilty Groups. For anyone that has had to setup one of these in a new cluster, there are quite a lot of steps. Don’t let that scare you off though – this new functionality has the benefits of reducing hosting costs and complexity as you no longer need a PDC and SDC machine running for the cluster to work! In the past, we have setup simple A0 or A1 boxes for this purpose, but that is still at least $30-$100 a month of spend for very little reason.
We wanted to write up this quick post on how we tested the Availability Group was in fact functioning as expected; even under SQL Server 2014 SP2. We used this free Celedon developed SQL AlwaysOn Test tool which will constantly reconnect to the SQL cluster and echo the name of the server that was hit. You can download this tool HERE. Please note: This tool is provided “AS IS” and does not convey any warranty or rights.
We ran this tool from another VM as running it from one of the cluster nodes kind of defeats the purpose.
Once the application is downloaded and extracted, I opened up the SqlAlwaysOnTester.exe.config file with notepad and edited the relevant connection string parameters.
|
1 2 3 4 |
Data Source=tcp:ENTER_DB_IP/HOSTNAME (10.0.0.20 or 10.0.0.25 – SQL Listener or Internal Load Balancer) Database=ENTER_DB_NAME; (CeledonTestDatabase) User ID=ENTER_DB_USER; (SA) Password=ENTER_DB_PASSWORD (My SA password) |
After the connection string was updated and saved, I ran SQLAlwaysOnTester.exe file and the following command prompt window came up.
From this screen, you can tell that the SQL Listener/Azure Load Balancer redirected me to connect to USWSQLHA0.
I now want to test the AlwaysOn Availabilty groups so I logged into USWSQLHA0, right clicked on my Availability Group and initiated the failover wizard.
I then successfully failed over to USWSQLHA1.
I switched back to the test VM that was running the SQL_Tester application and I noticed immediately that I was redirected to USWSQLHA1 which is what I just failed over to.
So at this point, I know that the AlwaysOn Availabilty Group and Windows Failover Cluster is working as expected.