Possible early open beta of SQL engine

Red Squirrel · Post by **Red Squirrel** » Thu Apr 30, 2009 4:56 pm

There is still a lot of work to be done on SQL, but I have been thinking of opening it up to an early beta testing phase so I can roll it out faster. Part of the SQL will also be some better account security, account access tracking and other tools that will help us diagnose suspicious activity. For example, if all plans well, I will be able to run detailed real time reports on account access by IP. I also want to reroute traffic through the main firewall and have a SQL script generate rules of blocked ips. Basically, there will be easier to manage security.

With that said, it means i could reopen registration again, to at least see how it goes and if drama can be taken care of easily. However, this also means less internal testing time, and possibility of issues such as lost items. The old plan was to only release SQL with the rest of the revamp, so while we test other stuff, we'd be inderectly testing SQL and pretty much have put it through it's paces for about 6-12 months before it is put live. However if I release it now, it could go live as early as the next few months.

I want to get everyone's opinion on this. Releasing now will also make a smoother transition as it will be one less data conversion phase to go through when we release the revamp. My goal is to perform all transitions with as little downtime as possible, if any, outside of server wars.

The SQL conversion is actually instant from a front end point of view so that part is covered already.

Archived topic from AOV, old topic ID:4620, old post ID:29917

shakyj · Post by **shakyj** » Thu Apr 30, 2009 5:34 pm

Won't item loss happen when the actual roll out happens? If so cant you query the old database to see if people complain something is missing and it isn't in the new database. Assuming all items are also unique ID'd

Archived topic from AOV, old topic ID:4620, old post ID:29918

Death · Post by **Death** » Thu Apr 30, 2009 6:56 pm

Well IMO I think it would be a better bet as this way you will be able to test it to see if it's viable. Then from there, it's easier to plan the other parts of the revamp.

Archived topic from AOV, old topic ID:4620, old post ID:29919

Red Squirrel · Post by **Red Squirrel** » Thu Apr 30, 2009 7:51 pm

shakyj wrote:Won't item loss happen when the actual roll out happens? If so cant you query the old database to see if people complain something is missing and it isn't in the new database. Assuming all items are also unique ID'd

Not quite as by data loss I mean that if there is a malfunction in the save sequence and an item is not marked properly or not processed properly. This would not be trackable as it would be abnormal.

As for normal operation, zero data will be lost, as it will be a straight up conversion. Really the secret is that when the new server loads for the first time, it still loads from file, but the save system is set to SQL, so over the course of a few hours it will save to SQL as if everything in the world was just created. No conversion downtime and no data loss. If shard was to crash in the middle of it all, then it would be more serious, which is one of the reasons I think it may be good to release SQL prematurely so there's no other content that could potentially crash.

Archived topic from AOV, old topic ID:4620, old post ID:29920

shakyj · Post by **shakyj** » Fri May 01, 2009 7:57 am

Can you not introduce the new saves one server wars then apply an instant complete world save straight after that? Even if it means wars takes longer people would feel more comfortable knowing their stuff they already have is saved properly on the new system. Then at least you know your safe if the server crashes and you know the save is working for now.

Then im not sure how easy it would be to create a script that ran through the old DB array and cross check it with the new saved SQL DB to verify that the save was 100% complete and everything was transfered. Again this doesn't work if everything doesnt have a unique ID(not sure how uo backend works). At worst if they dont have unique ID's you could make a script that checks that the same amount of items are in the world ie.xx gods wrath. Not 100% verification but as the save was straight after wars no one would have been on to possibly mess it up.

Archived topic from AOV, old topic ID:4620, old post ID:29926

Red Squirrel · Post by **Red Squirrel** » Fri May 01, 2009 8:43 am

TBH a full blown save actually takes several hours. SQL over a network server is slower then writing directly to a serialized file. Where it's efficiencies kick in is once everything is saved, then only changes save. All this happens in the background too, so no world saves.

On the live server it might actually only take like an hour. Hard to tell.

Archived topic from AOV, old topic ID:4620, old post ID:29927

shakyj · Post by **shakyj** » Fri May 01, 2009 8:55 am

I only ever use SQL for web dev stuff. Takes a few mins max ^^. Is there no way to change the flat files to an array and then import them into the SQL DB? Never used C++ so im kinda thinking of how I would do it in PHP

Archived topic from AOV, old topic ID:4620, old post ID:29929

Red Squirrel · Post by **Red Squirrel** » Fri May 01, 2009 9:49 am

shakyj wrote:I only ever use SQL for web dev stuff. Takes a few mins max ^^. Is there no way to change the flat files to an array and then import them into the SQL DB? Never used C++ so im kinda thinking of how I would do it in PHP

Any kind of import operation would take time. We are talking hundred of thousands of queries. web stuff is max of maybe 10. also these are very large queries, some are as high as 400KB. Due to the nature of RunUO's storage system to avoid totally rewriting it, it's basically each item serialized into a LONGTEXT field. Also, C# is SLOW lol. C++ would probably be faster at doing SQL queries. C# does not deal well with large chunks of memory such as 400KB strings.

Archived topic from AOV, old topic ID:4620, old post ID:29930