Reputation: 13171
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:
Upvotes: 1
Views: 2462
Reputation: 88092
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.
Upvotes: 4
Reputation: 9113
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 ?
Upvotes: 1
Reputation: 17566
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.
Upvotes: 1
Reputation: 432722
Not really.
Data consistency and integrity:
Firewalls, remote server etc:
Misconception:
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
Upvotes: 1