Is there any way to test whether particular database server is connected or not? Like ping operation.
I know some alternate way -test SQL Connection opening. Reason to not use that approach is my app is continuosly at each t seconds test the connection.
Thanks
Not sure why I'm doing this, considering you rarely accept answers, but here goes. Your question brings up a lot of issues, many of the implied. So bear with me....
Connectivity & Me
Connectivity can mean a lot of things. At a much lower layer, as @Paddy points out, PING will tell you if the OS is connected to a network you can reach. But a computer can be connected and SQL could be stopped, offline, or otherwise malfunctioning. SQL could be online, but a firewall blocks access from the requesting computer. The requesting computer could lack permissions. The list goes on.
My point here is that:
The ability to open a connection to SQL Server is the method to test connectivity.
If it were me I would put put a small ADO test on an async-thread. The test would try to open a connection using a short timeout.
Solving symptom or the problem?
SQL Server really is designed, and generally conceived of, as a high availability product. Meaning, if your SQL access is going out so much that you want to test for it (beyond a general error handler) I would suggest you solve the avilability issue. Even the most basic install combined with lots of bad luck should allow for 95% or higher up time.
Using SQL SMO to test
If I haven't dissuaded you by this point, there is an alternative. SQL SMO (the replacement for SQL DMO) is a series of objects that allow programmatic access to SQL server management.
The code below will require references to a few objects:
You can use this getting started article to see how to setup a project. Note you will need WINDOWS permissions (as opposed to SQL authentication) to connect to the SQL Server in question.
Now onto the code :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Wmi;
namespace SQL_SMO_Test {
class Program {
public const string SQL_SERVICE_BASENAME = "MSSQL";
public static bool SQLServiceIsRunning (string SQLServerName ) {
string[] nameComponents;
string ComputerName;
string InstanceName;
string ServiceName;
ManagedComputer mc;
ServiceCollection serviceList;
//split up SQL Name
nameComponents = SQLServerName.Split('\\');
ComputerName = nameComponents[0];
InstanceName = nameComponents.Length > 1 ? nameComponents[1] : "";
ServiceName = SQL_SERVICE_BASENAME +
((InstanceName.Length>0) ? "$" + InstanceName : "");
//get WMI Computer object
mc = new ManagedComputer(ComputerName);
serviceList = mc.Services;
//find any SQL Services & See if it's our instance
foreach (Service s in serviceList) {
if (s.Type.Equals(ManagedServiceType.SqlServer) ) {
Console.WriteLine("Service Found --" + s.Name);
if ( (!ServiceName.Contains('$')) || s.Name == ServiceName ) {
Console.WriteLine("..Correct Instance found" + s.Name);
if (s.ServiceState.Equals(ServiceState.Running) ){
Console.WriteLine("....Service is Running");
return true;
}
}
}
} //end foreach()
Console.WriteLine("service not found, or stopped");
return false; //service not found, or stopped
} //end SQLServiceIsRunning
/* format: servername\instanceIfAny */
public static bool SQLIsRunning( string SQLServerName ) {
Server srv;
Console.WriteLine("SERVICE RUNNING (" + SQLServerName + ") ?" );
Console.WriteLine("------------------------------" );
if (! SQLServiceIsRunning(SQLServerName) ) {
return false;
}
Console.WriteLine("------------------------------" );
srv = new Server(SQLServerName);
try {
Console.WriteLine("Status: " + srv.Status.ToString() );
return true;
} catch (Exception ex) {
Console.WriteLine("Exception: " + ex.ToString() );
}
return false;
} //end SQLIsRunning()
static void Main(string[] args) {
bool result = SQLIsRunning("MST-SQL01.mst.com");
Console.WriteLine("-----------");
Console.WriteLine("Press any key to close...");
Console.ReadKey();
} //end main()
}
}
You can ping a server:
http://msdn.microsoft.com/en-us/library/system.net.networkinformation.ping.aspx
But this will not tell you if the DB is up. Opening a connection (which authenticates etc. is probably your only way). Why are you continually pollling this way?
