how hard is it to run your own SQL server?

run your own SQL server for dynamic web pages

[Andy_Kos]

I have an ecommerce website, which is working very well for me, however Im frequently getting problems with the MySQL server failing, therefore rendering my site useless.

Im thinking (probably incorrectly) that it cant be that hard to set up your own PC to act as the SQL server, thereby having complete control of this part yourself, rather than letting some hosting company mess it up for you?

I need a solution, but Im reluctant to just switch to another hosting company, as I have no way of knowing if their reliability is any better than my current supplier.

Any help/comments appreciated

[txbakers]

Hi Andy, welcome to the Webmaster World! We're glad to have your first post.

Before you think about switching hosts, or technologies, or getting involved in setting up a server for you to manage at home, have you determined what the cause of the mySQL failure is?

It could be something in your scripts that can be fixed, or it could be some configuration settings on the database itself.

Setting up a database server is vairly easy - on the surface - but you also have to worry about redundant drives for failover, backups, uptime, connectivity, and configuration issues.

[Andy_Kos]

I dont think it's my scripts, I think it's the hosting company cant cope, the problems occur quite regularly at the weekend usually on a saturday night, and I think it's just that they can cope with demand, Im guessing my site is on a server with about 30 other sites or so, and it cant cope with the load. Strangely enough, at start of business on a monday morning the sql server gets back up and running - I guess that's when the tech staff start work and give it a kick.

At one point I got an error message 'too many connections' or something like that, but most of the time I just cant get a connection to the mysql server.

As a temporary solution I have set up a mirror site with a backup database, and my php script auto redirect in case of main database failure, but this is a pain trying to keep both databases up to date.

I'm using easyspace, and they dont seem to bother to reply to questions, and they have a 25p per minute helpline which is no use whatsoever. All in all Im getting very frustrated with them.

I need a more reliable solution, and preferably something I have a little more control over.

[txbakers]

For full control over a server you will want a dedicated box, but be prepared to pay for it.

I run a dedicated server at my host for $199/month. I am responsible for all the patches and server maintenance. They provide the box and bandwidth and live tech support if I need it.

I still think, based on what you described, that the problem could be in your or the other users's scripts. Something is causing the SQL Server to hang, and it's usually connection strings that are left open. "Too many connections" is an indicator that people are not destroyng their connection objects. Too many people do that, server crashes.

[Birdman]

I would simply move on to another host. If this happens every weekend and they can't bring it under control, then they are a terrible host.

They should be able to monitor the connections and find the culprit fairly easily, I would think.

Oh yeah, welcome to WW!

Birdman

[syber]

Andy,

You may be at the limit to what mySQL can handle.

There are several hosting companies that provide Microsoft SQL Server. I would try one of them before trying to host my own SQL Server. You are right in that it is not difficult, but why put up with the hassle?

Art

[Andy_Kos]

Thanks for the comments, I will contact my hosting company again to see if they can resolve it. Unfortunately as there are other people user the same server, I cant control their scripts.

This problem has occurred every weekend for the last 4 weekends, before that I had nearly 2 years hassle free.

I dont want to move hosting companies unless I have to, but the downtime recently is unacceptable and I need a result. I had a long term plan of setting up a PC here as a basic server, which I can keep running and monitor myself, and also integrate with our sales software, at the moment we're uploading and downloading databases all the time, which is a pain.

Any suggestions for a good reliable hosting company? Primary website currently has a data transfer of about 350 Meg per day for all the images and HTML.

Im using mainly PHP and Mysql to implement the website and it's on a LINUX based server.

The Mysql part is relatively simple, it simply looks up product descriptions and prices in order for people to place orders online, but if it doesnt work I lose money!

[Andy_Kos]

I dont mind paying a 'reasonable' price to get the right service, I have my own main site, but as a sideline I look after 3 or 4 other ecommerce sites which rely on the same server....

So when it goes down... it really goes down

[mattb]

We had problems with server crashes before with MySQL. Our host moved us to another server that had less traffic. It was a 10 minute move and we've been fine since. You might want to ask them about this.