Persistent Characters and MMORPG Network Architectures

#121 I want to use MS SQL for many reasons, but a few would be: built in encryption, stored procedures (yes, mysql now has them, but you can't do NEARLY as much in the sprocs with mysql's query language as you can with TSQL), C# calls from db directly (web services, etc), EXTREMELY easy to use and robust management studio, more data types, etc. etc.

Even if MySQL was an enterprise solution (i'm not saying it is, I'm not saying it isn't, I'm merely saying), it doesn't support half the stuff MS SQL does. Why not move to Oracle then? MS SQL is 25% the cost of Oracle.

I wouldn't compare anything Horizons did period. That game blew chunks and lagged horribly the entire time I was in it. Half of the things didn't even work. I doubt they knew what the hell they were doing when they built it. On top of that, just because you build a game doesn't mean you know squat about building an efficient and optimized db layer.

#122 That's cool Jonathon:

I wish you luck with MS SQL. I just wanted to make sure you had a reason to use MS SQL. I dont agree with MSSQL having any features that MySQL doesnt have but again I'm not a spokesman for MySQL I just wanted to point out what others before you have found before you invested a lot of time into your project ;)

Matt:

It's cool that you've had such extensive experiences with MS SQL, I would like to point out however that I am nto refuting MS SQL's ability as a database =). MS SQL has been a database solution used by banks, investment companies, enterprise networks all around the world for the last number of years... I don't argue that.. I am a little hard pressed though to equate the efficiency of a gambling DB to the hard core demands of an MMO gaming platform.. I also would like to mention the costs involved in setting up a casino network infrustructure and server farm is in the millions... They have world experts, some of the best talent in the business configure their server systems.. At rates of $1500 a day I dont think most people will be able to design their MS SQL configuration or querry structures in quite that efficient of a maner ;)

Again though guys it's cool to bring up examples like the casinos because there is always a good tool for the job. It's good for people to hear about all the implementations of a DB before they proceed.

PS: I will admit I am a little sour on MS products, both server platforms AND DBs because of my work experience. I am a professional network engineer and have been designing and maintaining enterprise networks for over 15 years. MS Products are clunky in my experience. Yes they work and if configured very well they work fairly well. In the long run though they are hands down more overhead and more maintenance to operate than any other alternative available. I mention this so that peopel will understand my posts are definitely a bit biased. I apologize if that is a negative thing ;)

#123 MySQL doesn't have any of the features I listed in my reasons for taking it, that's why they were listed as reasons. If MySQL doesn't have those, then how can you not agree with MS SQL having features that MySQL doesn't?

#124 Jonathon:

I think it has been said enough times in this thread already that there is a right tool for every job. I am not going to get into the finer points of MS SQL vs MySQL. I am not a DB genius nor am I a master of everything MMO. As I stated if you feel that MS SQL is the right choice for you then go for it.

As for your "reasons" for taking MS SQL, I fail to see exactly what you were saying there..

-STARTQUOTE:
I want to use MS SQL for many reasons, but a few would be: built in encryption, stored procedures (yes, mysql now has them, but you can't do NEARLY as much in the sprocs with mysql's query language as you can with TSQL), C# calls from db directly (web services, etc), EXTREMELY easy to use and robust management studio, more data types, etc. etc.
-ENDQUOTE:

What do you mean built in encryption? Do you mean the querries are encrypted? How so? The querries are handled internally by the server and MS SQL does not encrypt and then decrypt it's own querries internally it doesnt make any sense. If you want to use a sequre tunnel for MS SQL to recieve qurries then do so. That same encrypted tunnel can be used on MySQL etc etc

Stored procedures? MySQL can do anything that SQL can. It's the same SQL structure.

Call DB directly.. again.. I think you are misinformed about MySQL.

Jon, use the right tool for the job. If MSSQL works for you please use it. Your above "reasons" however are not correct. You need to be careful about posting things as fact if you are unsure about them. People read this thread as a reasource for heavy load integration DBs into the Torque dev environment. If you don't know enough about MySQL to know that in can be accessed at least equally as well as MS SQL if not better and more efficiently please dont make statements like that =)

If you disagree with me that is fine by the MySQL website backs me up. If you wish to discuss the specifics of MySQL with me I am coding today and can spare some time to answer an email or even chat on MSN if you want some real time interaction. Not here to argue just think that you are unclear about MySQL and MSSQL as far as load handling.

If you think MySQL can't handle it how do you propose Mythic Entertainment is currently running the Triple A MMO "Dark Age of Camelot" exclusively on MySQL DB servers? ;)

#125 SQL Server 2005 supports encryption capabilities within the database itself, fully integrated with a key management infrastructure. By default, client/server communications are encrypted. To centralize security assurance, server policy can be defined to reject unencrypted communications. Data within the tables can be encrypted as well using this system.

The next few are pretty much apart of the same answer, so I'll give one reply that handles all the SPROC related ones. In SQL Server 2005, database programmers can now take full advantage of the Microsoft .NET Framework class library and modern programming languages to implement functionality within the server. Using CLR integration, you can code your stored procedures, functions, and triggers in the .NET Framework language of your choice. Microsoft Visual Basic .NET and the C# programming language both offer object-oriented constructs, structured exception handling, arrays, namespaces, and classes. Additionally, the .NET Framework provides thousands of classes and methods that have extensive built-in capabilities that you can easily use on the server side. Many tasks that were awkward or difficult to perform in Transact-SQL can be better accomplished by using managed code; additionally, two new types of database objects-aggregates and user-defined types-are available. You can now better use the knowledge and skills that you have already acquired to write in-process code. In short, SQL Server 2005 enables you to extend the database server to more easily perform appropriate computation and operations on the back end.

With the new sproc structure, I can call web services and C# modules directly from the sproc as well. I didn't say that the code calls the db directly, I said that the db calls the CODE directly. You misread my statement.

So I guess I don't see how my statements "are not correct"? All my information is from www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx where you can see many other features that MySQL doesn't support.

I'm also not here to argue. I don't flame or state guesses, I make points and show proof. I don't know if MySQL can outperform or even perform as well as MS SQL, but I do know many other features don't exist just based on their own documentation.

I, again, didn't say anything even remotely in the range of the fact that MySQL can't handle an MMO, I said for ME, I want the extra features that MS SQL offers.

EDIT: Almost forgot too that I use visual studio.net for my development, and MS SQL is fully integrated with vs.net. And T-SQL has been improved to include error handling directly in the sproc itself. *wink*

#126 Jonathon:

Thanks for clearing that up. I can see what MS SQL is offering you is a much more "turn key" system than MySQL. All of the features that you want (Aside from the built in integration with .NET and other MS appys which I personally wouldnt touch with a ten foot pole anyways heh) can be used with MySQL but certainly they dont come pre-configured or as part of the DB installation.

One thing MS has going for it hands down is the "ease of use" factor that it integrates with most other MS products and services without much tinkering. My issue with that is that you end up using a bunch of "generic" features that are not properly designed for a specific application. I will say that most of the features that you want are able to be used with MySQL but certainly not as an out of the box solution. I differ to your point that MS has many more "turn key" options avail.

I apologize if I tend to be so down on MS products, fact is I develop on MS platforms and my servers are MS servers BUT.. doesnt mean I have to like it ;)

I built my own secure communications layer for my client/server DB interaction. Wasnt a big deal to me and I got to optimize it for the traffic load that I am attempting to scale for.

Thanks for being such a champ about your stance on MS SQL =) Always happy to trade differing viewpoints with someone who may know something I dont.

Good luck in your developments.

#127 No need for an apology. I hate MS as much as the next guy, I just am a realist and not your typical "this technology is the greatest no matter WHAT!" standpoint that you typically find. It's age old with the 'linux dev' who thinks no matter what ms does, linux will always be better and the apple guy who says the same about apple versus windows.

Only reason I know so much about MS SQL is I've one of the top SQL DB chicks in the world consulting at a company I was consulting with a few months ago. She came in and tore the crap out of our database and revamped it literally cutting minutes off of application execution times (that had to do with the DBs in question). She is the one that MS actually pays to travel the world giving classes and stuff on MS SQL.

She showed me tons of REALLY neat tricks you can do in MS SQL to really optimize the crap out of it.

It just goes to show, between MySQL, MS SQL and Oracle, it's really more a question of the developer's abilities, not the DB's.

#128 Wow the discussion turned into a bash on sqlserver lol, well here are some thoughts, SQLServer(lets not refer to it as MS SQL as it sort of confusing to some), works very well. Although Horizons
is a bad example, there are some others that had much more success.

For example (cause I know you just love those), City of Heros uses SQLServer, runs what 200k subs
its minimal hardware (i think something along the lines of dual processor machines) fair amount of data.

and we cant forget Lineage I and Lineage II with what something like 4million and at one time around 8 to 10million subs back in the day. All runs on SQLServer.

To be honest, I think any respectible RDBMS will work, its the design, architecture, usage, and domain knowledge that will make you successful with any one you choose.

but bad design, lack of database functional expertise, poor implementation will trash any hopes even with the "best" RDBMS money can buy.

@broke, yeah I am not aware of any real generic turn key middletier solutions that are free that would work.
Its a key piece that is typically implented using your own core server framework technology.

essentially a new process, with the memory caching specific to your needs, database handling specific to your needs, network message handling specific to your protocol, etc. it can be a fairly custom process, but can be easily written on top of your core tech (server framework, threading, data structures, database communication, etc).

you could of course start with something already sort of there, like an existing server framework(for quickly implementing new server processes) based on something already out there.
but it is a process your going to want to be able to gather metrics from, to tune, optimize etc which
makes it more specific to what you application is trying to do.

if your database activity is really lite and amoutn of data low you might not even need that extra process, but if its heavy, and a lot of data youll probably need something a bit more custom to work, and so you may need to implement some of the finer details yourself.

turn keys solutions are rare for this stuff, one example is bigworld but it can be as much as 3million for the serverframework(yeah even big companies go woah).

there are a few others, fairly expensive, if anyone has found a free or near free cheap one they should post which one :)

#129 @Flybynight Studios

Quote:
What do you mean built in encryption? Do you mean the querries are encrypted? How so? The querries are handled internally by the server and MS SQL does not encrypt and then decrypt it's own querries internally it doesnt make any sense. If you want to use a sequre tunnel for MS SQL to recieve qurries then do so. That same encrypted tunnel can be used on MySQL etc etc

It is possible to encrypt stored procedures in SQL Server using the "WITH ENCRYPTION" option in the proc definition. All this really does is encrypt the procedure itself so that anyone trying to view it cannot see the actual SQL code. Its actually a pain in the ass and I don't recommend using it, especially if your database is not part of a product being sold to outside customers. For an inhouse MMO type setup there is not much reason to use this. Properly configured user accounts can help prevent internal people who should not have access from seeing anything.

Also, using an encrypted communication channel or encrypting non-sensitive data (for example, encrypting a character's stats) would add alot of overhead with all of the encrypting / decrypting going on. The SQL server probably should not have internet access anyway so an encrypted LAN connection is probably a bit overkill as well.

Quote:
Stored procedures? MySQL can do anything that SQL can. It's the same SQL structure.

This isn't true no matter how you look at it. The SQL querying language used between the two is different (they are both based on the ANSI SQL standard but none of the db vendors have a 100% ANSI compliant implementation of the core features, and all of them have several additional features that make up their specific language. Microsoft uses T-SQL for example, while Oracle uses PL/SQL), the way that data is actually physically stored is different (SQL Server uses a data file and transaction log file, MySQL breaks each table out into a separate physical file and the actual organization of data within these files is different), the way indexes are used is different (I don't know if MySQL even has the concept of a "clustered" and "non-clustered" index whereas the distinction in SQL Server is very important). They each support different datatypes (some that are not compatible with each other).

While its true that MySQL can do most things that SQL Server can (ableit support for these things is very new -- within the past year or so), there are some things that MySQL cannot yet do (for example, XML support) and the best way to do things between both systems is different. You can't expect to be a master of SQL Server optimization and try to apply that knowledge to MySQL (or vice versa).

Quote:
Jon, use the right tool for the job. If MSSQL works for you please use it. Your above "reasons" however are not correct. You need to be careful about posting things as fact if you are unsure about them. People read this thread as a reasource for heavy load integration DBs into the Torque dev environment. If you don't know enough about MySQL to know that in can be accessed at least equally as well as MS SQL if not better and more efficiently please dont make statements like that =)

Agreed.

Quote:
If you disagree with me that is fine by the MySQL website backs me up.

Probably the worst place to get information about the performance of a database is the vendor's website. ;P

That really applies to most things though. If you want to know about something talk to someone who owns / uses that thing, not the marketing department of the company trying to sell it.

#130

Quote:It is possible to encrypt stored procedures in SQL Server using the "WITH ENCRYPTION" option in the proc definition. Also, using an encrypted communication channel or encrypting non-sensitive data (for example, encrypting a character's stats) would add alot of overhead with all of the encrypting / decrypting going on.

Not exactly what I meant by encryption. I'm talking about a key based encryption system that you can apply specifically to FIELDS within the database to encrypt DATA stored inside the field inside the table. This wouldn't be used for character data necessarily, but more for account data such as the player's credit card number, address, etc.

Encrypting this information inside the db is much faster than encrypting it in code, and data at rest should always be encrypted when it containts credit card numbers, no matter how well the db is designed. Someone internal ALWAYS has access to the data, and NO ONE ever should without having to decrypt it (which leaves a trail).

Quote:That really applies to most things though. If you want to know about something talk to someone who owns / uses that thing, not the marketing department of the company trying to sell it.

Amen to that. If you went off what the selling company says, MS stuff would be the best stuff on the planet for ANY use ;)

#131 @Flybynight Studios

Quote:
Matt:

It's cool that you've had such extensive experiences with MS SQL, I would like to point out however that I am nto refuting MS SQL's ability as a database =). MS SQL has been a database solution used by banks, investment companies, enterprise networks all around the world for the last number of years... I don't argue that.. I am a little hard pressed though to equate the efficiency of a gambling DB to the hard core demands of an MMO gaming platform..

I'm not sure that I understand this correctly. Is your last statement made in jest? Microsoft studies the gaming industry (that is, the "casino" industry as most people here would probably think of) as it is one of the most highly transactional industries in the world (and the most regulated industry in the world -- more regulated than the banking industry and medical industry). Microsoft has done case studies on the products my department produces because there are few other industries (or companies) that deal with such massive amounts of data and high volume of transactions. Members of my engineering department have spoken at MS engineering conferences about a variety of topics.

Also, a casino is alot more than just a "gambling database". The entire casino is one big product. A slot machine is not some simple mechanical device; every individual EGM (electronic gaming machine) is a cluster of several computer systems built into one chasis. A casino is really a gigantic computer network. Even table games (blackjack, craps, etc) collect information about you (either computer generated information or manually entered information from a pit boss). The restaurant infastructure is connected to the hotel infastructure which is connected to the gaming floor, bar, shows and performances, and everything else.

Many times these systems are interconnected across multiple physical properties. Complimentaries (aka "comps") aren't just given out randomly. There is tons of real time and historical data that is being used all the time. On top of all of this is regulatory infastructure (reporting and collecting specific data in ways that regulatory agencies want), accounting, stuff dealing with employees among others. On top of all of that, potentially dozens of third party systems are interfacing, sending and requesting data to and from various databases (for example, a patron may want to use a "comp" at a gift shop that a third party "point of sales" system needs to be able to handle).

All of this information needs to be handled in real time as well (you don't want to wait 5 minutes for a coupon to authorize your discount or a slot machine to print out you cash voucher).

What is it that makes a MMO so "hardcore" exactly (compared to any other enterprise use of SQL)?

Quote:
I also would like to mention the costs involved in setting up a casino network infrustructure and server farm is in the millions... They have world experts, some of the best talent in the business configure their server systems.. At rates of $1500 a day I dont think most people will be able to design their MS SQL configuration or querry structures in quite that efficient of a maner ;)

You'd be surprised about the type of people in the IT departments of alot of these places... You'd also be surprised about some of the hardware. Some really understand the value of technology and will buy the cutting edge stuff. Others are still running Windows 2000 on older hardware.

That being said having a fast and efficient database implementation is not out of reach of people who don't have a multi million dollar budget. Educating yourself (and I do NOT mean taking courses at some college!) will go a long ways. Learning how to write basic SQL queries over a weekend and thinking that you can now develop a database for use by thousands of simultaneous users will get you in trouble in the long run -- especially if you don't continue to learn what you need to know to develop an efficient database system. You have to start somewhere, but if you are running a system that relies heavily on a database... guess what? You need to become increasingly more competent with using and optimizing that database! If you are writing an application that needs all the latest shaders and uber cool graphical effects you need to become increasingly more competent with using graphics APIs!

If you wrote a bunch of graphics code that was netting a max of 5fps on a high end machine would you consider using it for a MMO? Using it for any other multiplayer game where speed is a factor? No? Why do the same with a database? (Which, by the way, if you need a database, you really need to invest (knowledge wise) in the technology. If your database is hosed, so are you.)

Its just like any other technology really.

#132 @Jonathan Stevens

Quote:
Not exactly what I meant by encryption. I'm talking about a key based encryption system that you can apply specifically to FIELDS within the database to encrypt DATA stored inside the field inside the table. This wouldn't be used for character data necessarily, but more for account data such as the player's credit card number, address, etc.

Encrypting this information inside the db is much faster than encrypting it in code, and data at rest should always be encrypted when it containts credit card numbers, no matter how well the db is designed. Someone internal ALWAYS has access to the data, and NO ONE ever should without having to decrypt it (which leaves a trail).

You are absolutely right about sensitive data needing to be encrypted (yet many places -- like banks, hospitals [the military...] do NOT always encrypt sensitive data). I'm really not so sure about encryption being faster in SQL Server than from an independant application. It depends on your hardware. From a SQL perspective, encrypting the data somewhere else will ALWAYS be faster than encrypting it in SQL since SQL doesn't have to deal with it. Obviously doing something like SELECT DECRYPT(CreditCardNumber) FROM CreditCardTable; will be much slower for SQL than simply SELECT CreditCardNumber FROM CreditCardTable;

The more "business logic" you off load to the SQL server, the more bogged down it will get. SQL Server isn't magic :). Using alot of the features you have listed (like CLR in the database, built in encryption etc) is a trade off of where you want the load to take place. If your SQL database is going to have a generally light load, then loading it up with a bunch of additional stuff would probably be fine. If your SQL database is going to be under heavy load and storing alot of data then the more stuff you make it do, the slower its overall responsiveness will be. Its entirely plausible to run multiple database machines (accounts on one server, characters on another physical server, game data on another server) and so on based on your budget and infastructure. SQL Server is great at dealing with sets of data and data manipulation. It CAN do a bunch of other stuff but its not necessarily ideal to do so. If you have a team full of SQL guys and are short on C++ programmers, loading up more business logic would make more sense as its a better use of your existing resources. Basically it all depends on your individual hardware / team / expectations. There's no "right" answer, just pros and cons to try and balance.

Also, decrypting that data won't necessarily leave any more of a trail than what SQL Server already keeps track of. SQL Server can be setup (and does most of this by default anyway) to log every action by every user (NT User account or SQL account, date/time, what happened etc). If someone knows the encryption algorithm used and can get the key, they can easily write their own application to decrypt that data (and the database log would only show that they did a query that gave them the data, but since they decrypted it outside of SQL it would have no record of that).

Ultimately you can restrict people from accessing tables / stored procedures / databases as needed using account permissions. Encrypting data so that the guy who develops and maintains the credit card / account storage and processing system can't get it is not going to work. Obviously the guy who wrote that is going to need to implement the encryption in the first place. Having people design sensitive areas that you don't trust to actually be able to see the data is probably not a good idea either. In any case, not everyone should have access to every table or database. You should definitely encrypt data that you need to, but properly setting up your SQL / NT accounts should be a top priority as well.

#133

Quote:SELECT DECRYPT(CreditCardNumber) FROM CreditCardTable; will be much slower for SQL than simply SELECT CreditCardNumber FROM CreditCardTable;

The real question isn't can SQL Server handle this faster. The real question, when looking at the entire application, is which of the following scenerios is faster:

Send plain text over the network, encrypt inside sql, store in table

or

encrypt inside program, send ENCRYPTED DATA over the network, store in table

Obviously, plain text version is much, much faster than sending binary encrypted data over the network, so where do you take the hit? The DB or the bandwidth?

#134 @Jonathon

That's the kind of determination you need to make based on your infastructure. Is sending plain text credit card numbers across the network going to be a security issue for you? Maybe, maybe not.

There are always many factors to consider. Internal network traffic is a consideration. Database performance is a consideration. Application performance is a consideration.

No one can determine the best way to establish your infastructure for your needs but you and your team.

#135 Preloaded parameters anyone? ;)

#136 Also I should add that there will obviously be different considerations for different systems. How frequently is the billing system going to be updated / used? Probably not as often as a system that is dealing with a live game. It depends on how your product is developed of course (maybe people are buying stuff on your website all the time and their billing information is frequently being changed or updated ... or maybe not). Most likely people will enter their credit card information maybe once or twice a year? That is very infrequent, and even with thousands of players, the number of hits to the database would be fairly minimal.

The worst that can happen (well, ok maybe not THE worst) is that you implement doing things one way, over time it becomes problematic and you have to change it.

#137 The issue of which platform to use is one of scalability. The Windows has gotten beat up is not because it cannot act as a good server. The issue is it cannot serve more than one application reliably. Windows systems should only contain one server application because the operating system cannot isolate applications between each other. The design of the system allows for interaction between the server processes. Unix systems have a better process separation and have rules for acting as a super user. Most Unix server apps are reduced privilege while most Windows server apps are super users. The windows apps can do what they want including walking over other processes. So the result is a Windows server computer is generally only loaded to 10-20% of capacity while a Unix server can be loaded closer to %100. Now this is for data centers at corporations. Now if you are only running a SQL server with associated scripting environments to serve data then that is 1 application. Just don't mix applications. With a MMO I am guessing you can load Windows closer to 100% with the single application.

There are other ways to run more than 1 server app on a machine regardless of OS. It is a virtualization technology that allows a sub OS to control mutiple OS installations to be on one computer. It also allows for moving an entire OS image between multiple servers for load sharing. Some have specs in the mS range for switching seamlessly. The computer can run any flavor of OSes at the same time. This allows for near 100% hardware usage without the issues of application cross contamination on any OS.