Sign in to follow this  
SamCook

MMORPG With SQL Server backend

Recommended Posts

The next game I work on I'm thinking about using the following idea for, but i wanted some other people to read it and point out any potential flaws I may have missed. The back end (server) of the game will be a regular style server program, which interacts with the client through TCP (and potentially UDP for some things), and stores all live data in memory. The database for the game will be a sql database hosted on the same network or computer as the server program. In addition to the client receiving live data from the server program, it will be able to make a direct read only connection to the sql database to access certain non-live data, like guild statistics, or other initial data that is sent when the client first connects to the game (world data). The idea behind this is to make the programming of both client and server simpler, and reduce stress on the server program. Thoughts?

Share this post


Link to post
Share on other sites
Absolutely, definitely NO! Never, ever let ANY client access your database unless it is THEIR database. The server making a query and then dishing it out to the client is still probably going to be more efficient than the remote client gathering the information. But if the remote client can access the database directly, you have very little control over what information they can see, how frequently they can query the database, and what kind of queries they can make.

What kind of load are you trying to reduce? Is making queries to your database actually a problem? If so, you might want to double-check your database schema and code used to access the information. If that still isn't enough, build an internal cache into your server for the information you are querying.

If the actual processing is what is bottlenecking things, you might need to either re-work your code, get better hardware, or start thinking about a distributed server.

Share this post


Link to post
Share on other sites
Quote:
In addition to the client receiving live data from the server program, it will be able to make a direct read only connection to the sql database to access certain non-live data


Badness! Never let a user connect directly to a database or file system of your servers -- it's simply impossible to totally tighten up that, from a security point of view.

If you want data that is truly read-only and static, then serve it through Apache. If you want data that is read-only but dynamically generated, then you should go through your application server (which, in your proposed system, is your game server).

Share this post


Link to post
Share on other sites
I dont know, I could make the data the client had access to limited to what I wanted it limited to, i'm pretty good at database security. The idea is to reduce the stress on the application server from dishing out a lot of small packets, at least for data that doesn't need to be verified by the server since it is only going to be used by the client for reading and displaying purposes. If they did manage to query things they weren't supposed to, it would be of little consequence in read-only mode.

Share this post


Link to post
Share on other sites
Quote:
Original post by SamCook
The idea is to reduce the stress on the application server from dishing out a lot of small packets, at least for data that doesn't need to be verified by the server since it is only going to be used by the client for reading and displaying purposes.


So you want to reduce the stress on your application server just to increase the stress on your database server? [wink] Sounds like that would really be worse than having it served through the application server, ignoring all the security issues.

If you were really worried about the 'stress' on the server program, just make a second server application to act as the database interface so you can:
1. not worry about nearly as many security issues
2. optimize requests via a limited command set
3. cache results and only update every so often to further reduce load (this is slightly different than the caching the DB would do)

You could even make it UDP driven since the client would just send a command and it would keep sending it at an interval until it had the data it needed. That would reduce any extra resources required for TCP connections and if it's read only data that everyone can access, then you don't have much authentication to do since you can validate the commands sent.

A simple example would be like:

1. Client sends command 0001 to get a list of all guilds and their access index
2. Server sends the information preceded with the command (0001) so the client knows the request was fulfilled.
3. If the client doesn't get data that begins with the command, it will go back to #1. If it gets the data, now the client has a list of guilds with their access index. It will mark that command as no longer being needed so any duplicate packets it will simply ignore.
4. Client sends command 0002 with a guild access index of 123 (guild #123) to get the guild's Master.
5a. Server gets command 0002 with an index of 123 and sees that there is no guild 123. It sends back an error code. Client handles accordingly.
5b. Server gets the command 0002 with an index of 123 and the information is valid so it responds with the necessary information.

I think an approach like that would work out a lot better than your current proposed idea of database access.

Share this post


Link to post
Share on other sites
I doubt the load on the application server would be measurable, if all it does is pass-through to somewhere else. Meanwhile, if someone talks directly to the database, they can DOS you pretty easily by just issuing a dozen self-join queries over all available data in parallel.

Share this post


Link to post
Share on other sites
Quote:
Original post by Drew_BentonSo you want to reduce the stress on your application server just to increase the stress on your database server? [wink]


Well, the database server is only accessed by the application server when something needs to be saved or loaded, which is rather infrequent -- pretty much when a character joins or leaves, or when the server is shut down or loaded. Therefore, the database is under very little stress from the application server and it makes sense to put more stress on it rather than the server program because the server program is already dealing with tons of packets a minute.

I agree that there are some issues with my original plan, but it is so much easier to query a database directly rather than going through a second separate server application, if the data i need changes, i just have to configure the client to query an additional column in the original plan, it would be much more complicated to go through a third (additional server) application, and would be a bigger pain to change if the data i wanted changed... .net has a great system for receiving sql data from a query, much easier than receiving tcp data and parsing it out... unless there is a way to send sql results over tcp easily and then format them back into a table on the other side that i'm unaware of.

Share this post


Link to post
Share on other sites
Quote:
Original post by SamCook
because the server program is already dealing with tons of packets a minute.


Tons per minute? For metric people among us - how much is that in furlongs per forthnight?

For reference: 10,000 packets per second is fairly trivial to handle even without any advanced networking concepts.

Also, for WoW-like model, 500 concurrent users will not peg even a single core of current generation of CPUs, unless something is very wrong, for example, if all 500 of them bunch up and start fighting with AoEs inside a corridor of stairs with full collision detection.

Share this post


Link to post
Share on other sites
Even IF you could protect yourself against all kinds of SQL-related abuse (such as the DOS queries hplus mentioned), you still have to consider the fact that you are exposing a publicly available software (and one that serves a highly sensitive purpose indeed!) to every kid with an internet connection. Given how often code execution exploits appear in commercial software packages such as SQL Server these days, how can you possibly feel safe? When you run custom server software you will at least be protected by the fact that nobody but you has access to the source code, rendering many potential security holes useless. Blind exploitation of buffer overflows is not really possible in practice, for example.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this