I think you'll might help me or at best point me towards the direction. All things have been designed in c#, but client programs could be in whatever language, simply need to know tcp protocol employed for interacting with servers.
I designed a server application, including a lobby and a game title server. You are able to mount as numerous game server since you need (really, I've three, only for test, each with another type of game).
The customer application connects to lobby for authentication, nd other small things, and ask for for a game title ... following this it's rerouted towards the appropiate game server.
All the details processed throughout the overall game, statistics, chats, ... is held in a PostgreSQL database (you are able to configure this to be used MySQL, MS SQL).
Now, the gamers want to have queries to obtain past information on themselves, or stats, or whatever ... my real question is:
¿Should I keep gamers query directly the database, I am talking about have the is a result of the database server (delivering the particular sotre procedure command to obtain the results)?
or (because of they keep an energetic alive reference to lobby server by socket (and anothers with game server))
¿Do I get the request and send the outcomes of every query via lobby server while using permanent active connection socket?
¿Which one is easily the most performance as well as secure? or ¿Will you recommend another suggestion?
It is good to express that 5.000 - 10.000 concurrent connections or maybe more are required on all game servers.
.- I didn't remember to say that some queries can has large results, I am talking about, 500/2000 records (rows) with a few posts. .- Didn't remember to express that I believed to create the queries via socket, processing and packaging the query within the server and delivering the end result to player(s) zipped (compressed).
Thanks ahead of time
You need to absolutely do not have the gamers clients directly posting queries for your database servers your game servers must have their very own protocol for queries the clients submit, and also the 'game query servers' should sanity-check individuals queries before building the particular SQL.
On no account for anyone who is building queries from user-posted info, though.
Most dependable is ovbiously the answer in which the user doesn't be aware of SQL-Server, that's in which the lobby server handles all connections and communication (presuming no exploitable bugs on either server ofc). You also convey more treatments for the implementation of security, getting one user (the lobby server), probably within the same domain, who will get read and execute permissions for exactly just the databases he will need individuals permissions for. Performance < Security, and so the question regarding performance really is not important much, however performance is actually also rather dependent on your implementation anyway.