This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
Navigating the modern technology landscape requires reliable tools for ensuring consistent and secure database connectivity. Our recently unveiled Connectivity Probe is designed as a lifeline for applications relying on unswerving database interactions. It diligently probes connectivity status, wait times, DNS resolution, port availability, and executes test queries to guarantee your application's uninterrupted and efficient operation. With this introduction, let's dive deeper into the distinctive features and benefits offered by our Connectivity Probe.
Connectivity Verification:
Efficient Connectivity Checks: The code is adept at validating the efficiency of the connection to your SQL Database. By initiating a SqlConnection using a connection string, it tries to Open the connection and executes a test query (SELECT 1). The connection status, the result of the test query, and the time required to establish the connection and execute the query are logged to an output log file. These logs are invaluable for understanding and improving the performance of your database connectivity, providing insights into potential bottlenecks or issues.
Wait Times:
Intelligent Wait Time Management: The code incorporates a smart wait mechanism for connection attempts and query executions. With an initial delay set, it progressively increases the wait time between subsequent retries upon a failed attempt. This approach is not only efficient but also avoids overwhelming the server with connection requests and query executions, providing a balanced and intelligent strategy for handling connectivity issues.
DNS Resolution:
DNS Resolution Monitoring: DNS resolution is a critical aspect of network connectivity. Our Connectivity Checker code incorporates a systematic DNS resolution check using the Dns.GetHostEntry method. It not only attempts the resolution but also calculates and logs the time taken for the resolution, allowing for a clear understanding of any delays or issues encountered during this process.
Port Availability:
Port Availability Check: Ensuring the destination port is available and ready to accept connections is another vital check performed by the code. By initiating a TcpClient, it tries to Connect to the specified server and port. The result of this operation, along with the time taken, is then logged, providing clarity on port accessibility and responsiveness.
Conclusion:
Implementing a robust Connectivity Checker like the one described in our code is crucial for maintaining the health and performance of your applications. By monitoring connectivity, wait times, DNS resolution, port availability, and executing test queries, you gain unparalleled insights into your application’s database interactions, empowering you to identify, troubleshoot, and resolve issues proactively and efficiently. The code is a testament to our commitment to supporting and enhancing your application’s reliability and performance, ensuring it stands resilient in the face of connectivity challenges.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Net;
using System.Net.Sockets;
using System.Threading;
namespace CheckHealth
{
class Program
{
private static string serverName = "SERVERNAME.database.windows.net";
private static string Query = "SELECT 1";
private static int Port = 1433;
private static string connectionString = "Server=tcp:" + serverName +"," + Port.ToString() + ";Initial Catalog=dbname;Persist Security Info=False;User ID=username;Password=pwd!;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=5;";
private static string outputLogPath = "c:\\temp\\output_log.txt";
static void Main()
{
Stopwatch totalStopwatch = new Stopwatch();
Stopwatch connectionStopwatch = new Stopwatch();
totalStopwatch.Start();
TimeSpan initialDelay = TimeSpan.FromSeconds(5);
int attempt = 0;
bool isConnected = false;
while (!isConnected && attempt < 3)
{
attempt++;
AttemptDnsResolution(serverName);
AttemptPortConnection(serverName,Port);
try
{
connectionStopwatch.Start();
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
connectionStopwatch.Stop();
isConnected = true;
WriteToFile(outputLogPath, $"{DateTime.Now}: Connection time: {connectionStopwatch.ElapsedMilliseconds} ms");
ExecuteSelectQuery(connection);
}
}
catch (Exception ex)
{
connectionStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: Connection failed on attempt {attempt} - {ex.Message}");
Thread.Sleep(initialDelay);
initialDelay += TimeSpan.FromSeconds(initialDelay.TotalSeconds * 0.05);
}
}
totalStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: Total execution time: {totalStopwatch.ElapsedMilliseconds} ms");
}
private static void ExecuteSelectQuery(SqlConnection connection)
{
Stopwatch queryStopwatch = new Stopwatch();
using (SqlCommand command = new SqlCommand(Query, connection))
{
int attempt = 0;
command.CommandTimeout = 5;
command.CommandType = System.Data.CommandType.Text;
while (attempt < 3)
{
attempt++;
queryStopwatch.Restart();
try
{
command.ExecuteNonQuery();
queryStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: Query execution time: {queryStopwatch.ElapsedMilliseconds} ms");
break;
}
catch (Exception ex)
{
queryStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: Query execution failed on attempt {attempt} - {ex.Message}");
if (attempt >= 3)
throw;
command.CommandTimeout = command.CommandTimeout * 2;
Thread.Sleep(5000); // 5 seconds delay between retries
}
}
}
}
private static void WriteToFile(string filePath, string message)
{
try
{
using (StreamWriter file = new StreamWriter(filePath, true))
{
file.WriteLine(message);
}
}
catch (Exception ex)
{
// Handle the exception depending on your requirements
Console.Error.WriteLine($"{DateTime.Now}: Failed to write to {filePath}: {ex.Message}");
}
}
private static void AttemptDnsResolution(string serverName)
{
Stopwatch dnsStopwatch = new Stopwatch();
try
{
dnsStopwatch.Start();
IPHostEntry hostEntry = Dns.GetHostEntry(serverName);
dnsStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: DNS resolution time: {dnsStopwatch.ElapsedMilliseconds} ms. IP:" + hostEntry.AddressList[0]);
}
catch (Exception ex)
{
dnsStopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: DNS resolution failed on attempt - {ex.Message}");
}
}
private static void AttemptPortConnection(string serverName, int port)
{
Stopwatch Stopwatch = new Stopwatch();
try
{
using (TcpClient tcpClient = new TcpClient())
{
Stopwatch.Start();
tcpClient.Connect(serverName, port);
Stopwatch.Stop();
WriteToFile(outputLogPath, $"{DateTime.Now}: Successfully connected to port {port} - Connection time: {Stopwatch.ElapsedMilliseconds} ms");
}
}
catch (Exception ex)
{
WriteToFile(outputLogPath, $"{DateTime.Now}: Port check failed {ex.Message}");
}
}
}
}