FSQL to Database Connection Problem Alerts

Hello Everyone,

How would one go about setting up alerts for when FSQL has problems connecting to a given database? Does FSQL have any built-in functionality which does this, or do you have to roll your own solution?

Thanks in advance for any help.

–HandledException

After browsing through things more (still getting used to everything) I stumbled upon the solution to my problem in the expressions library:IsDBConnectionAvailable("<data connection name>")Now I am running into a new problem though: I can’t seem to get any alerts to fire off of these new Action Items (Although I do have other alerts which do work).

Current Setup:
[ul][li]Standard Group[/li]
[li]Action Item for each database connection to monitor
[list][]Digital Alert[/li]
[li]If value is zero[/li]
[li]Severity: High[/li]
[li]Ack Mode: Auto[/li]
[li]Send Alarm Clear[/li]
[li]Delay for 1 min[/li]
[li]**See attached screenshot[/li][/ul][/
:m]
[li]Simple distribution list emailing anything above severity 0[/li][/list:u]

I am trying to test it out by throwing in a bogus connection name and starting up the group. The values update correctly, but any that are false do not send off alerts. Is there any reason why this shouldn’t work?

Thanks for any and all help.

–HandledException


Sorry for the slow reply, but I just got around to setting everything up and giving it a shot…

So, for me everything seemed to work fine (always works like that, huh? :smiley:). HOWEVER, I did run across one small item that might be coming into play: by default the “send alerts for initial states” settings is OFF in the Alert settings section, meaning that if you’re testing by simply starting and stopping the group, you won’t get the emails.

Is there any chance this is what you’re running into, or is the database connection actually good and you’re stopping/starting the db or pulling the connection? If you’re not doing this, you might give it a try, otherwise try enabling the “send for initial states” setting.

Also, to reduce complexity a bit in order to narrow it down, you might try using the static email list for now instead of distribution groups.

Hope this helps,

No problem with the delay Colby, I have plenty to keep me busy. :wink:

What you posted makes sense. If it always failed (like my test would have) it never would have sent out any emails. Going by some of your advice, I have two more questions/problems though…

First off, I keep getting this error message when I try to test this out.Cannot execute query. An error occured while loading the data connection, and data caching is not enabled. at FactorySQL.LocalDBOperationProvider.LoadConnection(DBConnectionID ConnectionID, Boolean SecondTry) at FactorySQL.LocalDBOperationProvider.ExecuteQuery(DBConnectionID ConnectionID, String Query) at FactorySQL.Alerting.Distribution.AlertDistributionManager.RefreshObjects(Boolean AllowCache, Boolean ParseAfterLoad) System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Shared Memory Provider, error: 40 - Could not open a connection to SQL Server) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at FactorySQL.LocalDBOperationProvider.LoadConnection(DBConnectionID ConnectionID, Boolean SecondTry) at FactorySQL.LocalDBOperationProvider.LoadConnection(DBConnectionID ConnectionID, Boolean SecondTry) at FactorySQL.LocalDBOperationProvider.ExecuteQuery(DBConnectionID ConnectionID, String Query) at FactorySQL.Alerting.Distribution.AlertDistributionManager.RefreshObjects(Boolean AllowCache, Boolean ParseAfterLoad)It’s saying that I don’t have data caching enabled, but I do! In the service settings I have it set to default size and location, and when I go to the system status it says that everything is ok. Any ideas on what I could have wrong there?

Side question, if FSQL loses it connection to the database storing the email distribution lists, how can it send out the email that it lost it’s connection? Does it cache it somehow, or is this probably what is causing the aforementioned problem?

Secondly, when I stop/pause the sql server service, only one of my databases becomes unavailable, even though they are all on the same db service. Any ideas on what could cause that? My working theory is that when the first exception is thrown in an Action Item, the transaction dumps out and skips all of the other ones. Plausible?

Thanks for you help once again. I’m really starting to like your company’s product, and the support is great as well. :thumb_right:

–HandledException

Actually, the first part of that message is misleading. It has nothing to do with data caching. The real problem is contained in what follows:

Basically just saying to DB is unavailable, which is correct.

DING (that's the sound of me realizing something I should have long ago): The distribution groups are stored in the database. If the database isn't available, it can't get them, and thus can't send the email.

The emails will be sent irregardless of the database state. Obviously the alert log entry will have to be cached, but the email should still work. However, it needs to be able to get the list of addresses to send to.

At this point, there are a few options/things to consider:

  1. Specifying one or more email addresses in the static list. This is what I was testing with earlier, and worked fine. However, these addresses will receive ALL of the alerts, so don't put everyone in there.
  2. In real life, you're probably trying to set this up in order to monitor a remote connection. If you have a second database locally, and the distribution groups are stored there, it shouldn't go down and everything should work fine. My point is that you may be creating artificial problems since you're constrained to working with just the local db right now.

Finally, why do some look OK and others not: I think that it's something along the lines of what you thought- DB connections are only detected to be faulted when something tries to use them and fails. Thus, you can have multiple connections, but if you're only actually using one of them, that will likely be the only one to go bad.

Regards,

Yep, you’re right on all accounts!

The database only went unavailable if there was an active group trying to access it. Good to know.

I was thinking the same thing about moving the distribution list table to the local machine. I don’t think it would be that bad of an idea to have all of the FSQL-related tables on the local machine. That way when we lose our connection for whatever reason, FSQL would be fine.

Thanks for helping me sort this out. One of our client’s networks is less than optimal, so we have lost our db connection a few times. This solution should help mitigate that by informing us immediately if/when it happens.

On a related note, is there a way to sent out alerts on the data cache’s status?

Thanks again.

–HandledException

[quote=“HandledException”]On a related note, is there a way to sent out alerts on the data cache’s status?[/quote]Nevermind. Right after posting I looked in the expressions library and it slapped me in the face.

–HandledException