Recover from a DB down

[Regan]

I am having trouble recovering from DB errors.  I am opening the database with Simple.Data.Database.Opener.OpenConnection(connectionString, "System.Data.SqlClient").

I start my program when the DB server is down.  I then open the connection and attempt to perform a query.  I correctly receive an error 

Exception Type=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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I then start up the DB server and try the query again.  Again, I receive the same exception.  I can't seem to get rid of it, until I restart my application.  I need a way to get Simple.Data to start fresh.

I have tried calling SqlConnection.ClearAllPools(), but it doesn't seem to make a difference.

I have also tried Simple.Data.Database.ClearAdapterCache(); but that also does nothing.

Can you help me get past this issue?

I am using version 0.18.3.1.

Thanks,

Regan

[Mark Rendle]

Hi,

Can you verify that the same scenario (DB down then brought up again while application is running) does not occur with plain ADO.NET calls? I have had similar problems in the past without Simple.Data being part of the equation, due to connection pool behaviour.

Thanks,

Mark

--
You received this message because you are subscribed to the Google Groups "Simple.Data" group.
To unsubscribe from this group and stop receiving emails from it, send an email to simpledata+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Regan]

Yes.  I will test it and post the result.

[Regan]

I tested it out and direct ADO recovers fine.  It did take two tries, but the second time worked fine.  In my tests with Simple.Data, the problem remained after multiple tries.  Is there some additional caching going on?

[Jens Doose]

Hi everybody,

I ran into the same problem, with the same circumstances.

DB down on program startup - Simple.Data will never be able to connect to the DB after the DB start.
DB up on program startup - Simple.Data will be able to reconnect to DB even after the DB was down and up again.

I was not able to find a solution, tried with ClearAllPools and ClearAdapterCache as well.
I am connecting to an SqlServer, using version 0.19 of SimpleData.

Same circumstances works fine with the Ado-Classes.

Sounds like the same problem ;)

Has there ever been a resolution?

I can provide a test visual studio solution to reproduce the scenario, just let me know.

Thanks in advance

Jens

[Jens Doose]

Solution: Call DatabaseSchema.ClearCache(); to clear the schema cache which is saved although the schema information could not be retrieved

[Michael Draper]

Should I be making this call whenever I open the DB connection? Seems like that would counter the purpose of the cache :\

[Jens Doose]

Michael, I wouldn't do it everytime.
We are clearing the cache everytime a certain exception occurs.

Basically we wrap every call to the DB with a try/catch-Block and in the catch-Handler we call HandleSqlException.
This will clear the cache in case of Db Connection errors.

        /// <summary>
        /// Handles the SQL exception described by e.
        /// </summary>
        /// <param name="e" type="Exception">The Exception to process.</param>
        internal void HandleSqlException(Exception e)
        {
            if (e is SqlException)
            {
                SqlException sqlException = e as SqlException;

                if (sqlException != null)
                {
                    // Only reset adapter, if server or database is not reachable
                    if (sqlException.Number == -1 || sqlException.Number == 4060)
                    {
                        Logger.Log(EventLogEntryType.Error, e, "Determined a database login error. Resetting the adapter.");

                        this.ResetAdapter();
                    }
                }
            }
        }

        /// <summary>
        /// Resets the database adapter.
        /// </summary>
        internal void ResetAdapter()
        {
            try
            {
                DatabaseSchema.ClearCache();

                SqlConnection.ClearAllPools();

                Database.ClearAdapterCache();
            }
            catch (Exception ex)
            {
                Logger.Log(EventLogEntryType.Error, ex);
                Debug.Fail(MethodBase.GetCurrentMethod().Name + "\r\nException!\r\n\r\n" + ex);
            }
        }

Jens

[Rachel Lim]

Hi Jens,

I am having the same situation here. what should I add to reference in  order to use DatabaseSchema? or could you please guide me on how should I make the DatabaseSchema syntax work in my c# code? Thanks!

[Jens Doose]

Hi Rachel,

sorry for the delay, I missed the notification.

use the class DatabaseSchema from Simple.Data.Ado.Schema namespace.
Add Simple.Data.Ado as reference.

Jens

[wayne hiller]

This should really be handled within Simple.Data itself, it does look like a lot of work tho. Maybe we can make sure that a fix is in place in version 2.0 if the issue occurs there as well.

[Jon Scolamiero]

Maybe a little off topic, but does anyone know the status of the next Simple.Data version?

[wayne hiller]

I am curious about this too. I would like to get involved in the project, I use Simple.Data in all my projects and it is very important for me.