1

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'.
HasanG
  • 12,734
  • 29
  • 100
  • 154
  • 7
    Switch to a better webhost. – SLaks Dec 28 '10 at 15:22
  • What would it even _mean_ to use two databases simultaneously? – SLaks Dec 28 '10 at 15:22
  • 2
    Have you ascertained that your database is not the one causing the slowness? – Raj More Dec 28 '10 at 15:27
  • 1
    @marc_s: clustering is "high availability", not "load balancing". It isn't correct for this – gbn Dec 28 '10 at 15:33
  • 1
    Is it related to your adhoc [XML backups](http://stackoverflow.com/questions/4535536/timeout-expired-with-sqlbulkcopy)? – gbn Dec 28 '10 at 15:37
  • @gbn:No it's not. @SLaks: I have limited budget :), I have other database options but outside country so it will slow my web app. @Raj More: No, I have seen other customers complaints same thing. – HasanG Dec 28 '10 at 15:48

4 Answers4

4

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.

NotMe
  • 87,343
  • 27
  • 171
  • 245
  • Although, I don't think I have mistake in my code I've posted a question here: http://stackoverflow.com/questions/4550206/right-code-to-retrieve-data-from-sql-server-database Thank you. – HasanG Dec 28 '10 at 23:01
1

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

gbn
  • 422,506
  • 82
  • 585
  • 676
1

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.

TalentTuner
  • 17,262
  • 5
  • 38
  • 63
  • that's true , think of Hot/Cold Replication – TalentTuner Dec 28 '10 at 15:33
  • 2
    -1 A failover server just won't help. If the server is overloaded, what do you think a synch task will do? If synch is automatic, then it's a high availability solution (mirror, cluster) which still means one server running at any time. This is not load balancing – gbn Dec 28 '10 at 15:34
  • 1
    i am telling about the db server switching as in most of cases your deployment strategey include DR servers so you can switch to the DR server in case of failure , what do you suggest in a real time scenarion , have you not include DR or Failover server as part of your Deployment Strategey – TalentTuner Dec 28 '10 at 15:38
  • Question asks "use two databases simultaneously". Your answer is nothing to do with this. It's a good answer for a different question. A timeout <> server failure. More likely poor code. – gbn Dec 28 '10 at 15:40
  • :) , okie , i need a break then , i just read what i want to read. – TalentTuner Dec 28 '10 at 15:44
  • Hey, maybe I cannot express myself good but this is an answer. I need something like failover. When dbDefault is down and inaccessible I need to use dbSecondary so the web app keeps running. – HasanG Dec 28 '10 at 15:55
1

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 ?

VdesmedT
  • 9,037
  • 3
  • 34
  • 50
  • Yes, probably this could be a solution. Waiting for timeout error won't be pretty. – HasanG Dec 28 '10 at 16:04
  • I guess you know that "pretty" is definitely not matching any solution where to avoid the problem of a failed server, we try to patch the client instead of fixing the server... – VdesmedT Dec 28 '10 at 16:16
  • @HasanGursoy : client is used in the context of client/server, the server being you database server while the client is the calling application (which is a webapp in this case) !! – VdesmedT Dec 28 '10 at 16:29