Auto switch to MS Access if SQL Server goes down

[stevenutt]

I run a web application that relies on the database being up. Under normal conditions my ASP.net app connects to my SQL Server database, however, my web host occasionally takes the database server down for maintenance leaving my main app without a database. I have created a duplicate MS Access database so that it can run things temporarily if the SQL Server is down. Does anyone else do this, or something sililar in order to achieve "poor man's database redundancy" ? If so - at what stage do you check for availability of the SQL Server ?

http://www.youralarm.com - written in ASP.net & SQL Server

[N a v a n e e t h]

stevenutt wrote:

my web host occasionally takes the database server down for maintenance leaving my main app without a database.

It's time to look for a better provider. :)

stevenutt wrote:

Does anyone else do this, or something sililar in order to achieve "poor man's database redundancy" ?

I have never done this as my hosting provider is good. All I can suggest is, try to connect to SQL server, if it throws error, catch it and try with the access DB. BTW, a better solution is to find a provider who can provide more server up time.

Navaneeth How to use google | Ask smart questions

[stevenutt]

This has nothing to do with the web provider. I have been with the provider for years and the database has never failed in all that time. Is it not reasonable to expect that they do service pack upgrades on the database as they become available ? No provider can gaurantee 100% uptime, but unfortunately in the business that I'm in - I have to. So, even though I have never had a database crash, that doesn't mean that there won't be one tomorrow. As my MS Access database is stored on the web server and not the database server, it seems like a good way to keep things going in case the database server is unavailable.

http://www.youralarm.com - written in ASP.net & SQL Server

[goodideadave]

If your SQL Server database goes down, and you start writing to the Access database, then do you have to copy the transactions to the SS database when it comes back up? What about reading from the Access database when SS is down? What are you storing in the SS database?

My other signature is witty and insightful.

[stevenutt]

Yes, there are a small number of tables that would have new rows written into them but it is acceptable and we can do a manual transfer of the data some time after the recovery.

http://www.youralarm.com - written in ASP.net & SQL Server

[goodideadave]

Have you considered working with your hosting provider to mirror your database on a different server? Assuming the hosting provider gives you advance notification of downtime, you can manually switch over to the mirror, then switch back when the primary comes back up. But if you really want to implement Access as a fallback, I would put the Access code in classes that you could insert when a SQLException is thrown. If you can't connect, go to plan B.

My other signature is witty and insightful.