Ok, so I wanted to start a thread, because I was chatting with a friend, and we were discussing what would be the best way to protect user stats in case of a failure, while your(or my) application is trying to connect to the database.

What is the best solution, imo:
Whenever I think about it, I seem to come back to my original solution: Just notify the users that the database can not be accessed and that their stats for their current visit, might not be saved.

What are the other possibilities:

Well, for one thing, as my mate suggested - you could have a flat database file(the ones that I hate most, because the SQL syntax there is just so damn limited), and save all the users there too. Then there's the thing that you will have to compare the 2 databases, and wonder which one has the newer data now.

The second option could be temporary text files. I dislike the whole idea around having files, the encoding of which you can never be sure of. And also there's the human factor here - modifying is easy, and I don't know what they can put in the file - basically, I don't want the whole application crashing, just because of a faulty txt file. And there are far too many things that can happen to them.

The third option we discussed, was to dump the SQL code in a txt file again. But for the same reasons, as the second option I didn't like that option either.

The fourth option was almost the same as the third. But dump the SQL queries in a flat database file this time. Thinking about it now, each query could have it's own row. But on the other hand: There's still the human factor there. Someone could accidentally move or delete the file(and one of the people I'm afraid of is myself). Then, there's one more reason. By the time this query is executed the person, that it corresponds to could have rejoined the server a couple of times, which would make the data incredibly irrelevant(that also goes for the third option too).

So, that's all the solutions and combinations of solutions we could think of.
What do you do when you can't access your database for one or another reason? For all the reasons specified above, I keep it simple: Just notify the users in the server, that the database is not currently accessible, and let them know that the stats they build up might, and most probably will be lost when they leave the server.

After all, I prefer a day of unsaved stats, instead of messing up the accounts of everyone that has joined the server during that day. I could easily give some extra to everyone who's account stats have not been saved today. But how would I make it up to people that have played forever, and their stats have been totally messed up?

But if you have any better solutions than mine, please feel free(no.. I demand you :razz to share them. I'd really like to have a better and user-friendly way to go about this so rare, but also so-annoying-when-it-happens issue.

Well Saving the SQL queries do suck, but they'd allow you to effectively update your DB once it's restored to the current state. (You can just batch them)

Flat files and such suck because then you haffto manually reintegrate the data, which is pretty irritating.

Having not done anything with databases in a while. (PRISM does not have any db support right now.) And I'm on a whole HTML5 Web 3.0 kick, because of the Google Chrome Cr-48, I would say save the data to the client side database within your web application. The client will log it's own querys, so once it loses connection it keeps track of all of the things it has done but have not been saved. Once it get's the connection back, it posts the changes to the master db server and both things become in sync again.

Quote from Dygear :

The client will log it's own querys, so once it loses connection it keeps track of all of the things it has done but have not been saved. Once it get's the connection back, it posts the changes to the master db server and both things become in sync again.

That sounds massively open to exploitation. Even if you encrypted the queries they could still be destroyed in a closed source application or manipulated in an open source application.

Personally the OP's viewpoint (notify the user their stats won't be saved for the session) is the best one. People may lose a day's worth of stats but it's better than the alternatives.

Quote from amp88 :

That sounds massively open to exploitation. Even if you encrypted the queries they could still be destroyed in a closed source application or manipulated in an open source application.

Personally the OP's viewpoint (notify the user their stats won't be saved for the session) is the best one. People may lose a day's worth of stats but it's better than the alternatives.

Use signed keys to keep the query's secure. But yes, if your app has to potenial to lose data, you should warn the user about it.

How long are you expecting your database to be down for? And why wouldn't you just get a more reliable server/RDBMS instead of wasting resources planning for when it's going to be down?

Because I like to take every possible failure into consideration. Connection losses are not something that happens often, but also not something that never happens. I don't like the idea of my app crashing, because of it's inability to work offline. And after I'm at it, why not just add 5mins of work more to inform the users about the potential loss, they may experience?

Anyway, I think that I thought of a better approach for my situation. I'm going to save the connections in a list anyway(C# app), so I could just keep the entry until connection to the db is restored. I will still inform the users if I take that approach, because in case of a long down time.. or.. something, I am going to clear some of the app's memory(by clearing some records from the connections list, obviously) ..probably. I'm maybe thinking too huge scale, but I like to waste a few processes more, instead of having a crashed app, or even worse - an overloaded server, in case the app eats too much memory.