I have a web site which uses one SQL database but the hosting company is very slow sometimes and I got database timeout, login and similar errors. Can I implement my code to use two databases simultaneously? I have stored procedures and the data is updated periodically.
EDIT:
Simply: When dbDefault is down and inaccessible I need to use dbSecondary so the web app keeps running. Ant these two databases must be always same.
EDIT:
Some errors:
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)
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)
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Cannot open database "db" requested by the login. The login failed. Login failed for user 'root'.
解决方案
Load balancing and/or fail-over clustering database servers typically involves a lot of work.
You will need to make sure ALL data is merge replicated between the two database servers. Hosting providers rarely provide this option unless you have a dedicated server.
Allowing for merge replication might involve redesigning parts of your database; which may not be feasible.
Unless you are willing to invest a lot of time and money, you are much better off just switching hosting providers to one that has better db support. Considering there are literally thousands upon thousands of such companies out there this is an easy fix.
UPDATE
Almost of all the errors you identified in your edit are generally attributable to failing to properly dispose of connections, commands, and readers. You might want to go through your code to make sure you are accessing the sql server correctly. Every connection, command, and reader should be wrapped in a using clause in order to make sure they are properly released back to the connection pool.
If you provide a data access code sample (new question please) we can help you rewrite it.