Ok, I think you may have noticed that occasionally the site hangs for 5 minutes (or even more) before it comes back again. It's been doing this randomly for a while now, and it's pretty frustrating because I can't pinpoint exactly what the problem is. I've searched everywhere on the problem, and all I can find is that it may be due to recordsets not being closed, but I'm certain they all are. But yeh, for some reason, the database is getting locked up.
Now, what I'm going to tell you is probably going to make you laugh your ass off, but Sumea uses Access for it's database. Access should stay as a desktop database, and not on the net where there's lots of concurrent users.
Anyway, I've installed MySQL, converted the database over from Access, and I'm currently going through every page on Sumea and making the appropriate adjustments and changes so that everything works as it supposed to. It won't be as bad as going through 700 or so Sumea comment threads to make sure that the XML is properly formed, but it's still a pain. I should've used MySQL from the beginning, but I just didn't know any better [;)]
Ok, scrap that, I can only choose between MySQL or SQL server with my host, and for some reason I set up SQL server ages ago [:o].. so SQL server it is. MySQL is really nice though. I highly recommend it, and for god's sakes, don't use Access. [:D]
It's handling quite well most of the time, which is what is most surprising about it all.
Anyway, earlier I said I was able to get MySQL going, the database converted, and the site running in no time. The site just needed some adjustments with SQL queries, but I've had some of the important pages changed already.
Getting to the same stage with SQL Server has been an absolute nightmare. The main problem is that I can't successfully convert the database because Access's database upsizer doesn't like something, and the only error message is "Table not converted" or something like that. So I read some hefty whitepaper on why my database won't upsize because that's the only thing I can find on the problem, and it's not even helpful. I can't find a gui front end to see what tables I have successfully coverted because, well, Micosoft wants you to buy their server software which has one, and so everyone else wants to sell you theirs. There's a free web based gui like PHPAdmin, but the site for it is down.
So anyway, I check out the server software, and it looks like Microsoft released SQL Server express edition which is a limited free edition of their SQL server software. Unfortunately, it was released not long ago, and the tools it has are pretty skimp feature-wise and obviously still in testing stage (they call it "community technical preview"), and documentation isn't in an abundance.
Anyway, I'm not a database guy, not even remotely interested in them, but the moral of the story is.. Access -> MySQL = easy. The tools are free and plentiful, and it's all easy. Access -> SQL Server, holy hell on a stick. You'd think Microsoft would make it easier to move up from their own software.
I think I just might ask tech support if they will just remove my SQL server option and put in MySQL instead if I can't get any progress on this.
I "heard someone tell me once" that if you were to find the SQLServer 2000 Evaluation Version then once the SQLServer database component expires you can keep using all the administrative tools (Enterprise Manager/Query Analyzer).
Of course Microsoft seems to have deleted all access to that download, but if you were to google +"sqleval.exe", or to PM or email someone that had a copy it might help...
...if you're not using a more recent SQLServer version that is :)
You poor b*stard - how many years is it supporting sumea based on ACCESS???
*boggle* :)