Low-Cost High Availability: Simple Database Monitoring in a Windows Environment : Page 3

Adding Database Connection Strings

Add a connectionStrings section to the app.config file at the same level as appSettings. The following template shows both SQL Server and Oracle connection strings:

<connectionStrings>
  <clear/>
  <add name="Main Office SQL Server"  
    connectionString="Server=SQLMAIN\YourNamedInstance;database=YourDatabase;Integrated Security=SSPI;" 
    providerName="System.Data.SqlClient" />
  <add name="Branch Office SQL Server"    
    connectionString="Server=SQLBRANCH;database=YourOtherDatabase;User ID=LmtdUser;Password=Secret;" 
    providerName="System.Data.SqlClient" />
  <add name="Main Office Oracle Server" 
   connectionString="user id=OraUser;password=OraPwd" 
   providerName="Oracle.DataAccess.Client"/>
 </connectionStrings>

Notice the use of the clear element to block inheritance from any other config files, such as the machine.config file. Make sure that only the connection strings specified in your monitoring application's app.config file are used. Also note the providerName elements used in the connectionStrings section. To keep things simple, these are the names of the DbFactory invariants.

Using the template as a guide, add a connectionStrings section to your app.config file. If any of your connection strings contain passwords, you must take proper care to secure the connection strings.

Database Factories

To make the database-monitoring program as robust as possible, use DbFactory classes to allow the use of generic Database Access Layer (DAL) code. DbFactory classes work with DbProviderFactories, which are uniquely identified by their invariant names. Although you could let your application inherit DbProviderFactories from the machine.config, use the clear element once again to block inheritance. This is so that the monitoring application is controlled only by its app.config file. The DbProviderFactories are contained in System.Data, which is at the same level as appSettings.

Using the following XML as a guide, add a DbProviderFactories section to your app.config file:

<system.data>
  <DbProviderFactories>
  <clear/>
    <add name="Oracle Data Provider for .NET" invariant="Oracle.DataAccess.Client" 
      description="Oracle Data Provider for .NET" 
      type="Oracle.DataAccess.Client.OracleClientFactory, Oracle.DataAccess, 
      Version=10.2.0.100, Culture=neutral, PublicKeyToken=89b483f429c47342" />
    <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" 
      description=".Net Framework Data Provider for SqlServer" 
      type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, 
      Culture=neutral, PublicKeyToken=b77a5c561934e089" />
  </DbProviderFactories>
</system.data>

The machine hosting your monitoring application must have the client connectivity software installed. You can find the exact syntax for the DbProviderFactories by referring to the machine.config on your machine.

Using a Generic List with DbFactoryClasses

You can simplify the code by creating a generic list to store all of the database connection strings and status information. Storing status information is necessary to prevent a flood of duplicate messages when a database server is down for a prolonged period of time. Since your database status polling internal is likely to be shorter than it takes to bring a database application back to full working status, you must persist the time of a database connection failure. By comparing the time of the last failure to the RepeatNotificationMinutes, you can suppress redundant error emails:

1. Within the DbMonitorWrapper project, add a class library file and name it Database.cs or Database.vb.
2. Add C# using or VB Imports statements for System.Data and System.Data.Common to both class files (DbMonitor and Database).
3. Add C# using or VB Imports statements for System.Threading to the DbMonitor file.
4. Add the necessary code as shown here:

   
   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Data;
   using System.Data.Common;
   
   namespace DbMonitorWrapper
   {
       class Database
       {
           string _FriendlyName;
   
           public string FriendlyName
           {
               get { return _FriendlyName; }
               set { _FriendlyName = value; }
           }
           string _Invariant;
   
           public string Invariant
           {
               get { return _Invariant; }
               set { _Invariant = value; }
           }
           DbProviderFactory _Factory;
   
           public DbProviderFactory Factory
           {
               get { return _Factory; }
               set { _Factory = value; }
           }
           DbConnection _Connection;
   
           public DbConnection Connection
           {
               get { return _Connection; }
               set { _Connection = value; }
           }
           DateTime _LastFailure;
   
           public DateTime LastFailure
           {
               get { return _LastFailure; }
               set { _LastFailure = value; }
           }
   
           public Database() { }
           public Database(string FriendlyName, string Invariant, DbProviderFactory Factory, 
               DbConnection Connection, string ConnectionString)
           {
               this.FriendlyName = FriendlyName;
               this.Invariant = Invariant;
               this.Factory = Factory;
               this.Connection = Connection;
               this.Connection.ConnectionString = ConnectionString;
           }
           public Database(string FriendlyName, string Invariant, DbProviderFactory Factory, 
               DbConnection Connection, string ConnectionString, DateTime LastFailure)
           {
               this.FriendlyName = FriendlyName;
               this.Invariant = Invariant;
               this.Factory = Factory;
               this.Connection = Connection;
               this.Connection.ConnectionString = ConnectionString;
               this.LastFailure = LastFailure;
           }
       }
   }
   

5. Add two new functions to DbMonitor.cs (or .vb) to create a generic list, and use DbFactory classes to test the connections:

   
   public class DbMonitor
   {
       static string SMTPServer =            ConfigurationManager.AppSettings["SMTPServer"];
       static int SMTPPort =          Convert.ToInt32(ConfigurationManager.AppSettings["SMTPPort"]);
       static string MailFrom =          ConfigurationManager.AppSettings["MailFrom"];
       static string MailTo = ConfigurationManager.AppSettings["MailTo"];
       public static int IntervalMsec =    Convert.ToInt32(ConfigurationManager.AppSettings["IntervalMsec"]);
       static int RepeatNotificationMinutes =
   Convert.ToInt32(ConfigurationManager.AppSettings["RepeatNotificationMinutes"]);
       static List<Database> Databases = new List<Database>();
   
       public static void InitializeList()
       {
           DateTime lastFailure = 
               System.DateTime.Now.AddMinutes(-RepeatNotificationMinutes);
   
           foreach (ConnectionStringSettings conString in ConfigurationManager.ConnectionStrings)
           {
               Console.WriteLine(conString.ConnectionString);
               Console.WriteLine(conString.Name);
               Console.WriteLine(conString.ProviderName);
   
               DbProviderFactory factory =
                   DbProviderFactories.GetFactory(conString.ProviderName);
   
               DbConnection connection = factory.CreateConnection();
   
               Databases.Add(new Database (conString.Name, conString.ProviderName
                   , factory, connection, conString.ConnectionString, lastFailure));
           }
       }
   
       public static void TestConnections()
       {
           for (int i = 0; i < Databases.Count; i++)
           {
               Database db = Databases[i];
               try
               {
                   db.Connection.Open();
               }
               catch (Exception ex)
               {
                   DateTime rightNow = System.DateTime.Now;
                   TimeSpan sinceLastError = rightNow.Subtract(db.LastFailure);
                   if (sinceLastError.Minutes >= RepeatNotificationMinutes)
                   {
                       db.LastFailure = rightNow;
                       SendMail(db.FriendlyName);
                       Console.WriteLine("ERROR " + db.FriendlyName);
                   }
               }
               finally  //
               {
                   db.Connection.Close();
               }
           }
       }
       public static void SendMail(string FriendlyName)
   

   Notice how the DbFactory classes are provider independent.

6. Build the DbMonitorWrapper project and resolve any errors.
7. Modify the TestMonitor project to call TestConnections instead of SendMail:

   
   private void monitorTimer_Elapsed(object sender, ElapsedEventArgs e)
   {
       DbMonitor.TestConnections();
   }
   

8. Build the TestMonitor project and resolve any errors.
9. Run the TestMonitor project and confirm it is working as intended.
10. Before proceeding to the next step, consider changing the IntervalMsec and RepeatNotificationMinutes values to longer time intervals that are more suitable for actual monitoring than debugging.

Previous Page

Next Page