Switch between databases, use two databases simultaneously

Question

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'.

Answer 1

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.

Answer 2

If both your database are in synch (which is an obvious requirement for what you are trying to do), the best solution is to rely on a loadbalancer but if you can't, I guess you goal is to run the query against both database in the same time and returns the first result otherwise you will have to wait for the timeout to run the request against the second server.

SO what you need is asynchronous sql command right ?

Answer 3

ist of all find the reasons of the Timeout if it is in your code than rectify the code by optimizing query etc.

i think what you need is a Failover Server , where you can switch if the one server is down.

Alternatively

you can maintain two connection string in web.config and can switch to other server if one is down.

in both the method , you need to devise an strategey to sync the servers.

Answer 4

Not really.

Data consistency and integrity:

• How do you decide what data or what call to make at what time?
• What happens on write?

Firewalls, remote server etc:

• If you use another hosting company, how will you connect?

Misconception:

• Two databases on one server = absolutely no advantage.
• The server is probably overloaded, a 2nd db will make it worse

A database timeout could be code related of course and it may not help you to have 2 databases with the same poor code or design...

Not a nice answer, but if your host is providing poor service then your options are limited