\$\begingroup\$

Both approaches are used with MMORPGs. Keeping everything in memory and periodically check pointing it to disk seems to be the most popular option, at least for older games. It has the advantage of being fairly simple to implement and scaling fairly well, but making it reliable is completely up the to the developer. SQL databases provide ACID properties that make reliability easier, but are overall more complicated to implement well and can have problems with scaling.

EVE Online is an example of an MMO where everything is stored in an SQL database. I think it peaked at something around 60,000 simultaneous users, and has had to dedicate some expensive hardware to the database servers over the years in order keep up with the load. However EVE stores a lot more data per user than than most MMORPGs and has a lot more frequent and varied transactions. The database's ACID properties allows the entire massive and complicated database to always be kept in a consistent state.

For example, consider the case when someone gives an item to another player. EVE's database guarantees that even in the event of a crash or power failure that the item ends up in only one player's inventory. That is, the database transaction that removes the the item from one player's inventory and adds it to the other player's is atomic. The transaction either fully completes or doesn't happen at all. You can't end up in a state were the item exists in neither players' inventory or in both.

An MMORPG that keeps player data in memory and periodically checkpoints it has to implement this atomicity itself. One way to do this would be checkpoint every player's data at the same time, ensuring that new checkpoint is fully committed to disk before its considered the most recent checkpoint. The game would also have to ensure that player data can't change while it's being checkpointed. With a large amount of active players, the challenge becomes doing all this without causing a delay long enough that the players would notice.

Don't underestimate how important consistency is. When you're developing your game its going to crash a lot. You don't want have to track down why items are disappearing and/or duplicating, not when the problem is an unrelated bug is causing the game to crash at a bad time. Moreover when your game goes live, it'll crash way more than you expect it to. Your server that was operating perfectly under testing, will suddenly expose numerous bugs under full load and players doings thing you didn't expect them to.

Note that most MMORPGs use SQL databases for account related information even if they don't for actual game data. Even more important than keeping the game state in consistent state is keeping the billing state consistent. Worst case for the game database getting corrupt is that you have to restore the database from a daily backup. Worst case for account database getting corrupt is that you go bankrupt because of all the chargebacks.

For your project you can probably go either way. Having 1000 simultaneous users probably won't push the limits of what an SQL server can handle on a commodity PC these days, but a lot will depend on the nature of the transactions. If you're familiar with SQL and relational database design then this can work for you. You'll want to minimize the transactions as much possible, for something like player current hitpoints you might only want to periodically save them to the database since stats like these don't necessarily need to be consistent. (In PvP game they might though...) Don't store things like monster HP in the database at all, in most games only player related data is persistent.

On the other hand if you're not an SQL wizard you might find keeping all data in memory much simpler to get working reliably. SQL databases make consistency and reliability easier, but its not automatic. A badly designed database can perform poorly and lead to inconsistencies. Transactions won't be atomic unless you make them so. If you don't use parameterized statements religiously you'll open yourself to SQL injection attacks.