Detecting if a Database or Service is online
No matter what kind of project one develops, there usually is a requirement to use some sort of external service such as a database engine, WPF service, and the likes. Especially when dealing with a web application that needs to be online at all times, the typical method is to just throw exceptions and deal with it in some fashion. Usually, involves a ton of code and overhead - after all applications are only as intelligent as the programmer that coded it. However, there are timeouts to consider as well as having the application determine if the service is back online and go back into full mode. For instance - a service pack is installed to the database and requires a reboot, or a service is stopped to install a new update - you get the picture.
With Sites-Easy, and most other CMS / Portal Applications, a huge requirement is that 99% of all of the application settings and data reside in the database. While using the web.config or some other custom xml is a solution for retaining all those settings, personally they both have drawbacks such as:
- Any modification to the Web.Config, restarts the application. There is no way around it.
- The myriad of settings and lack of intellisense. In a recent WCF project, literally hundreds of lines required to define 20 some different bindings and associated communication channels etc...(The intellisense feature supposedly will work with web.config in Orcas)
- Nearly impossible to configure from remote systems or to invoke dynamic changes from the web application itself and have the changes persist (again there are routines but touching the web.config recycles that application....
- Where the update needs to be propagated to multiple servers - real-time with service interruption, creative techniques have to be used.
Data is another consideration. With a dynamically generated site, all of the content is in the database or hosted from within another service (in case of WCF SOA designs).
Typically, the way we detect if there are issues with data connectivity is to trap errors off the SQLCONNECTION con.Open(). There can be a huge delay in terms of application code trying to detect the error, especially if the database is down. One technique is to ping the server and check to see if the port is open, however, that doesn't truly work because the port is always opened or a firewall could be blocking the ping request. Even so, we still have the issue with an app restart and cached settings going poof and the likes...
One method I am devising, is to use a SQL Express datafile (mdf) on the server that contains a mirror of the main database tables and data required to actually launch the application (at least in a degraded mode). Not intended to act as a backup for the whole system, but is intended to allow the application to adapt to whatever condition may exist. For example with the main database down, the application could still load the main page with proper branding images (which are stored in the db).
The real trick to this process, is configuring multiple connection strings within the web.config. The application then will run checks at the the application start-up and also invoke a timer (both of which are handled within the global.asax), and having application variables retain the status of the main datasource or service. As status changes, the application globally adapts to the change of data sources allowing the application to run in 'full capability mode' or 'maintenance / error mode'. This tracking at such a low level avoids having to place all kinds of conditional logic in the DAL(s), although regular error trapping still needs to occur within the DAL, that can spawn a global re-organization of the application on the fly depending on the error type. Additionally, allows the application to automatically come back online in 'full capability mode' with minimal or no administrative action. The latter is important as this solution is ultimately designed to handle sporadic hick-ups which occur .
The code provided is just to give you an inkling of what you can do. It comes from my application, but since I use custom HTTPHandlers and the likes, posting full working code would be of a bit of an worthless endeavor. If you are using master pages, or other custom methods of handling incoming request, you can still adapt the examples to work for your own needs. The goal here is to demonstrate what is possible and why certain approaches may or may not be a reliable approach.
Lets begin.
First we need to tackle a class that contains some methods that allow us to track and check status.
In this scenario we have two main concerns. The actual config settings and logging. Face it - when your app does go down - the ability to still log events is critical, even in the dev stages.
Create a class file and add the following..
//These are conditional flags to determine status and are settable
//throughout the application....
// used to flag what the main db status is. Useful throughout the app where an action requires the full database to work properly.
public static bool _MAIN_DB_ONLINE_STATUS;
//This is important..as it determines the last time a check was made. By default, declaring the value initializes it with the DateTime.MinValue.
public static DateTime _LAST_STATUS_CHECK ;
// determines if the sql express file is available. This file contains the stored procedures, tables, and data that encapsulate the minimum data required for storing settings.
public static bool _BACKUP_REPOSITORY_ONLINE_STATUS ;
//Another sql express file that allows for logging when the main database is down (assuming you are logging to the main SQL Server
public static bool _LOGGING_REPOSITORY_ONLINE_STATUS ;
public static DateTime LAST_STATUS_CHECK { get { return _LAST_STATUS_CHECK; } }
public static bool MAIN_DB_ONLINE_STATUS { get { return _MAIN_DB_ONLINE_STATUS; } }
public static bool BACKUP_REPOSITORY_ONLINE_STATUS { get { return _BACKUP_REPOSITORY_ONLINE_STATUS; } }
public static bool LOGGING_REPOSITORY_ONLINE_STATUS { get { return _LOGGING_REPOSITORY_ONLINE_STATUS; } }
// This is used to go through the checks to see if the database(s) is online.
public static void PRE_CHECK()
{
if (CommunityGlobals.IsDatabaseOnline(CommunityGlobals.MainConnectionString, "MSSQLSERVER") == false)
{
_MAIN_DB_ONLINE_STATUS = false;
}
else
{
_MAIN_DB_ONLINE_STATUS = true;
}
if (CommunityGlobals.IsDatabaseOnline(CommunityGlobals.ConnectionString_LocalDepository, "MSSQL$SQLEXPRESS") == false)
{
_BACKUP_REPOSITORY_ONLINE_STATUS = false;
}
else
{
_BACKUP_REPOSITORY_ONLINE_STATUS = true;
}
if (MAIN_DB_ONLINE_STATUS == false && CommunityGlobals.IsDatabaseOnline(CommunityGlobals.ConnectionString_Logging, "MSSQL$SQLEXPRESS") == false)
{
_LOGGING_REPOSITORY_ONLINE_STATUS = false;
}
else if (CommunityGlobals.IsDatabaseOnline(CommunityGlobals.ConnectionString_Logging, "MSSQL$SQLEXPRESS") == true && MAIN_DB_ONLINE_STATUS == false)
{
_LOGGING_REPOSITORY_ONLINE_STATUS = true;
}
else if (MAIN_DB_ONLINE_STATUS == true)
{
_LOGGING_REPOSITORY_ONLINE_STATUS = true;
}
else
{
_LOGGING_REPOSITORY_ONLINE_STATUS = false;
}
_LAST_STATUS_CHECK = DateTime.Now;
}
public static string MainConnectionString
{
get
{
NameValueCollection nvc = (NameValueCollection)
ConfigurationManager.GetSection("Cavalia/database");
return nvc["connectionString"];
}
}
public static string ConnectionString
{
get
{
if (BACKUP_REPOSITORY_ONLINE_STATUS == true)
{
return CommunityGlobals.MainConnectionString;
}
else if (MAIN_DB_ONLINE_STATUS == false && BACKUP_REPOSITORY_ONLINE_STATUS == true)
{
return CommunityGlobals.ConnectionString_LocalDepository;
}
else return null;
}
}
public static string MAIN_ConnectionString_Logging
{
get
{
NameValueCollection nvc = (NameValueCollection)
ConfigurationManager.GetSection("Cavalia/database");
return nvc["ConnectionString_Logging"];
}
}
public static string ConnectionString_Logging
{
get
{
if (MAIN_DB_ONLINE_STATUS == true)
{
return CommunityGlobals.MainConnectionString;
}
else if (MAIN_DB_ONLINE_STATUS == false && LOGGING_REPOSITORY_ONLINE_STATUS == true)
{
return CommunityGlobals.MAIN_ConnectionString_Logging;
}
else return null;
}
}
/// <summary>
/// This is used for the local despository and must reside in the APP_DATA directory
/// </summary>
public static string ConnectionString_LocalDepository
{
get
{
NameValueCollection nvc = (NameValueCollection)
ConfigurationManager.GetSection("Cavalia/database");
return nvc["connectionString_LocalDepository"];
}
}
This method is used to pass in the connection string and the type of database...
public static bool IsDatabaseOnline(string CONNECTION_STRING, string DB_INSTANCE_TYPE)
{
bool test;
try
{
test = dalGlobals.IsOnline(CONNECTION_STRING, DB_INSTANCE_TYPE);
}
catch(Exception ex)
{
throw ex;
}
return test;
}
public static bool IsBackUpRepositoryOnline
{
get
{
Cavalia.CommunityGlobals._LOGGING_REPOSITORY_ONLINE_STATUS = dalGlobals.IsOnline(CommunityGlobals.ConnectionString_LocalDepository, "MSSQL$SQLEXPRESS");
return LOGGING_REPOSITORY_ONLINE_STATUS;
}
}
Here is the code that checks the status of the database..
public bool IsOnline(string DB_CONNECTION_STRING, string DB_INSTANCE_TYPE)
{
bool _error;
bool _isOnline = false;
//MSSQLSERVER (for SQL Server) or "MSSQL$SQLEXPRESS" for SQL Express
// alternately - any service name you put in works...
// string myServiceName = "MSSQLSERVER"; //service name of SQL Server Express
string status; //service status (For example, Running or Stopped)
//placed here so that the try catch works properly.
SqlConnection con = new SqlConnection(DB_CONNECTION_STRING);
//display service status: For example, Running, Stopped, or Paused
ServiceController mySC = new ServiceController(DB_INSTANCE_TYPE);
//add any other properties required.
try
{
status = mySC.Status.ToString();
}
catch (Exception ex)
{
Console.WriteLine("Service not found. It is probably not installed. [exception=" + ex.Message + "]");
Console.ReadLine();
return false;
}
if (status.ToLower() == "running")
{
_isOnline = true;
try
{
//this is important as if the connection can not be opened - then it is a no go situation anyways...So, we test for the service existing but still need to at least OPEN the connection!
con.Open();
}
catch (System.InvalidOperationException ex)
{
//typically this would be used to handle issue with invalid parameters..etc..
return false;
}
catch (System.Data.SqlClient.SqlException ex)
{
// this would be used to handle actual Sql exceptions in terms of
return false;
}
catch (System.Exception ex)
{
// these would be critical SQL exceptions....
return false;
}
finally
{
if (con.State == ConnectionState.Open)
{
Console.WriteLine("Finally block closing the connection", "Finally");
//throw new Exception(ex.ToString() + "with connection string:" + DB_CONNECTION_STRING);
con.Close();
}
}
}
//display service status: For example, Running, Stopped, or Paused
//Console.WriteLine("Service status : " + status);
return _isOnline;
}
Now in the global.asax you would have something that would look like this...
namespace mynamespace
{
//*********************************************************************
//
// Global Class
//
// The Global.asax is used to setup the timer for service subscriptions.
//
//*********************************************************************
public class Global : System.Web.HttpApplication
{
public Global()
{
InitializeComponent();
}
//*********************************************************************
//
// CreateTimers Method
//
// The timer fires every 15 minutes (60000 * 15).
//
//*********************************************************************
private void CreateTimers() {
System.Timers.Timer DB_STATUS_TIMER = new System.Timers.Timer();
DB_STATUS_TIMER.Enabled = true;
DB_STATUS_TIMER.Interval = (60000 * 5);
DB_STATUS_TIMER.Elapsed += new System.Timers.ElapsedEventHandler(DB_STATUS_TIMER_Elapsed);
}
//*********************************************************************
//
// WebServiceTimer_Elapsed Method
//
// Every 5 minutes, check for new content from remote communities.
//
//*********************************************************************
protected void DB_STATUS_TIMER_Elapsed(object sender, System.Timers.ElapsedEventArgs e)
{
//here we check the status of the databases...every five minutes
//bool _mainDBstatus = CommunityGlobals.MAIN_DB_ONLINE_STATUS;
CommunityGlobals.PRE_CHECK();
}
//*********************************************************************
//
// Application_Start Method
//
// Create two timers for sending emails and polling Web services.
//
//*********************************************************************
protected void Application_Start(Object sender, EventArgs e)
{
//This is used to check the initial status of all required services and databases...
//by default, until everything has been run at least once these values all should be
//false and therefore work
if (CommunityGlobals.LAST_STATUS_CHECK == DateTime.MinValue)
{
CommunityGlobals.PRE_CHECK();
}
CreateTimers();
}
protected void Session_Start(Object sender, EventArgs e)
{
}
protected void Application_BeginRequest(Object sender, EventArgs e)
{
}
protected void Application_EndRequest(Object sender, EventArgs e)
{
}
protected void Application_AuthenticateRequest(Object sender, EventArgs e)
{
}
protected void Application_Error(Object sender, EventArgs e)
{
}
protected void Session_End(Object sender, EventArgs e)
{
}
protected void Application_End(Object sender, EventArgs e)
{
}
void Application_PostAcquireRequestState(Object sender, EventArgs e)
{
}
#region Web Form Designer generated code
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
#endregion
}
}
And there we have the barebones, skeleton of what is required to handle at least checking the database. The code illustrates how to create timers, how to swap in real time a connection sting without modifying the web.config, and detecting the service condition.
The latter is VERY important as depending on the timeout for SQL - you could be sitting at a position where the page times out before the SQL connection attempt does. The verifying status is the quickest method possible.
Hopefully that little bit of code helps someone else trying to write code to determine if a service is online and how to swap instances when necessary.