|
|
 |
 |  | | | | |  | |||
 | ||||||
          |  |  | ||||
|
Relational
Database In-Game Logging One of the more compelling uses for a database with an MMP to record all important player actions and chat text so that there is a detailed record of what transpired in game. The logs are invaluable because they:
The following should be considered when creating a logging system:
Security Modern databases have robust security features that may be used to secure and partition access to the database schema by user. If desired, security can be as specific as allowing a user to only view the values of individual columns in a table! Having to manage security during development when objects are being rapidly added and modified can become a cumbersome task that is difficult to keep up with. In practice, it may be easier to create 2 security profiles. The first would be a user that is granted the ability to do any development action desired: create and drop tables, add stored procedures, etc. The second profile would be the "read only" user. This user has access to view everything in the database, but is prevented making from any modifications. These two types of users should be sufficient during development. Once the game is ready to go live, it becomes important to implement a more robust security policy. Live data must be protected because of the expense of accidental modification or even malicious tampering of player data. Restoring from backups can help resolve issues, but preventing the problem from ever happening is always better, right? Perform a careful analysis of the type of access needed by various people in the organization. Keep data opaque to those people without a need for access to perform their duties. At a minimum, provide individual logins for those that do require access so that there will be an audit trail of every modification to the database originating from outside the game. This is extremely important, and with the security mechanisms available, there is little excuse for not keeping people accountable for modifications made to valuable company data. The Cost Factor While
there are some significant challenges to implementing a database on an
MMP game, potentially the biggest hurdle to overcome is the cost factor.
This topic is presented so that you are not blind sided by all the potential
costs that may crop up when using a database. It is worth the time to be studied up on your selected vendor's licensing; perhaps the architecture that you plan to implement does not require the enterprise level offering that can be so expensive. While a monolithic, 32-CPU centralized database server may be desirable because of its simplicity, what is the impact to the service if that machine has problems? Having a redundant fail over just sitting around will be costly, and would need to be accounted for in the overall architecture. Alternatively, would it be preferable to run a single, much smaller (and less expensive) database server per game world instance? How would the architecture handle data that is shared across game world instances, such as player login? All these considerations require a delicate balancing act between cost and ease of implementation. Figuring out an appropriate licensing strategy will be a challenge. If you have any doubts, then check out the extensive online FAQ sheet and White Paper that Microsoft has devoted to the subject. One thing is always true. You will do yourself a favor by treating the database as valuable resource that needs to be used utilized efficiently and creatively, as opposed to something that gets thrown onto bigger hardware every time there is a performance problem. If you are part of an organization that is large enough to have a dedicated IT department, you should immediately investigate what they are using for their database software and leverage that knowledge. If they already have a suitably robust solution, you will want to jump on that bandwagon because of the experience and knowledge that they have developed in house. The opportunity for savings in terms of acquiring expertise, understanding licensing issues, and so forth is worth its weight in gold, especially if your own experience with databases is light. The next cost related item to consider is performance versus ease of use. The leading database vendor, Oracle, has a reputation for achieving the highest performance, but at the expense of ease of use. It provides the ability to tweak and configure every nuance of performance, but doing so is enough of a black art that Oracle expertise commands top dollar in the open market. It may be worth sacrificing some performance for a solution that is easier for mere mortals to administer, such as MS SQL Server. You
may need to account for software costs related to working with your chosen
database software. Does the vendor provide competent tools to administer
and utilize the product, or is it necessary to procure third party offerings
to avoid misery? It is not a given that a product will be easy to use
out of the box, or even that tools are provided gratis by the vendor.
While vendors have come a long way in developing friendlier tools with
graphical user interfaces, there remains a thriving market for third party
database software. You may be surprised if you presume to have no expense
in this area. Will you want a tool that provides you the ability to maintain the entire database schema for the game within an easy to use graphical interface? More than likely, you will have to pay for the convenience of such a tool. Fortunately, using MS SQL Server, we benefit in that the Enterprise Manager application that comes bundled with the database provides us this functionality in a competent manner. How do you plan to interface the rest of the game with the database? Are you going to utilize the vendor provided libraries, or do you utilize a language (such as Python or Java) that a third party excels at supporting? If the latter, you will need to account for licensing the third party libraries. On my project, we have licensed mxODBC, which is a very simple-to-use multi platform Python database library built on top of ODBC, an industry standard database independent C based API. This has given us the flexibility to develop our database server on Windows, but later migrate to running on Linux. To talk to MS SQL Server from Linux, we licensed the Easysoft ODBC Bridge software. This allows us to continue leveraging our company investment in MS SQL Server, while running servers on Linux, where the vast majority of my team's server expertise resides. The last significant area of expense in software is that of data entry or query tools. It is great to have the game built on a database, but without tools to enter data and generate reports, a significant piece of the puzzle is missing. For small tables, one can likely get by with the standard tools from the vendor to perform data entry and querying. That quickly becomes impractical when working with tables with large amounts of data. On the Windows platform, products such as MS Access and Visual Basic can be used to create custom forms that make it easy for designers to perform data entry. Of course, resources have to be allocated on the project to build these custom forms and reports. Database software has to run on hardware of course and the more robust the better. The database will perform better on machines with fast and large hard drives - think SCSI and RAID here. The software will require enterprise quality operating system software; you'll be running some version of Windows NT Server or variant of Unix. The software performs better with multiple processors on board, with each processor having as much on board cache as possible, and the software will need a very fast network connection as close to its clients as possible, with potentially multiple network cards to maximize bandwidth. In other words, procure the fastest, biggest, and fattest that the budget allows. No bare bones $1500 PC allowed here! Finally, you should account for personnel related costs. Development budget must be allocated to administer the database, design and create forms and reports, and implement the database server architecture and stored procedures needed to support the game. In addition, if there is no one with database expertise in house, you will need to provide training to your staff, or bring in a consultant/contractor. Having in house expertise is very valuable and worth the extra hit on the budget to acquire or develop, especially when problems crop up. If you are a developer with an outside publisher, look into whether they have some database expertise to offer. Most experienced MMP publishers already understand the benefits of a database and will likely be very willing to help you get started in the right direction. Also don't forget your internal IT department, if your company has one. You may be surprised with who you find lurking there. And no doubt working on a database for a game will be quite an interesting draw to them! Hopefully all this cost related discussion hasn't caused you to question your decision to go with a database. As expensive as it can all turn out to be, it will likely not match up to the cost of not using a database on your MMP game. Architecture Currently, most MMOGs run multiple game server instances, in which each instance is a unique world for players. Even if that is how you plan to proceed, it is not immediately obvious how the database should be factored in. You could have a large monolithic database box that supports all of the game instances from a single point. In a global marketplace, this is likely to be a regional or country server, since you'll want the game servers and database to be in close proximity for the best connection possible. If proceeding with a monolithic database box, how does the database get partitioned so that it looks unique to each game world instance? Should there be a single database instance, or a database instance for each world instance? This will require some extra strategizing, as will planning for the impact of outage to the hardware. To ensure that all worlds are not all down at once, the game servers will have to be prepared to switch over to a backup at a moment's notice. Achieving this without interruption to gameplay will undoubtedly be quite challenging. Implementing a separate database server for each world instance may seem like the easiest and logical solution. But it can get complicated if there are requirements for data to be shared across game world instance, such as a player reputation, or account name and password. How does one keep this data unique across servers? And with separate servers, there is the cost of consolidating data when it is desirable, such as for data mining. In addition to the overall architecture, one has to consider the internal architecture for how the game talks to the database. Should each game server have a separate thread for performing database actions, or should they communicate to another process that interacts with the database? If you implement a separate database server process you'll have to consider where that process is going to reside. Does it reside on the database server machine itself, or does it co-exist with the other game servers? Should data be cached? Caching will definitely help overall performance and reduce bottlenecks due to constant database access. But if data is cached, an unexpected interruption in service will cause any activity that hasn't been written to the database to be lost. Is this something the game can afford? How upset will this make players? On the other hand, failure to cache data likely leads to a high volume of activity in the database. Will the database be able to stay responsive when the load gets very heavy? A database can easily be overload if one is lax in applying good judgment in table design and building queries. If a game world is partitioned and there are definite boundaries between these partitions, deploying a database server process per partition becomes a viable option. Since players cannot be in two partitions at once, transactions are guaranteed to be atomic, and the overall load is divided among multiple database server processes. Backup and Restore One of the most useful features of a relational database is the ability to perform a backup without service interruption, also known as a 'hot backup'. Yes, there is likely to be some performance impact during a hot backup, but it can be kept to a minimum by selecting the appropriate backup device(s). For instance, make backups to a fast SCSI based RAID device as opposed to tape. Once the backup is created, it can always be independently moved to tape for archival purposes. MS SQL Server also supports the concept of snapshot backups for databases requiring high availability. These are services that used in conjunction with third party offerings that can perform backups of large databases in seconds with little or no impact on the database server. This is a more expensive option than the standard backup mechanism, but may prove desirable for your situation. It may be obvious, but is worth stating anyways: bad things can and will go wrong. The more recent a backup you have of the game state, the less unhappy your player base is likely to be when you have to restore from a backup. Perform a hot backup as often as you can get away with without overly degrading game responsiveness. Players don't like to have to put up with any kind of loss, but the smaller the amount of "time warp" a player has to experience when a restore is necessary, the more palatable it will be. Sometimes, circumstances make a "time warp" unavoidable. In those instances, you may choose to refer to the game logs and return items or other in game earnings lost by players because of the restoration of a backup. Fortunately, doing this is relatively simple using the power of queries in the database. It is not feasible to restore the database while the game is running; instead the game will have to be taken off line. Most of the time, the need to restore is forced on you by some catastrophic event, so it's not likely you "scheduled" this down time. Instead, scheduled maintenance down times will be reserved for modifications to bring new content online or fixes for exploits or cheats that are hurting the game. Other Departments and the Database The operations staff will of course be primarily handling database backup, restore and maintenance activity. They will very much appreciate your decision to rely on a database for the game. It makes this part of their jobs a well understood quantity and there are a lot of external resources available for help should the need come about. The database allows customer support to easily correct issues that are reported by players or uncovered through internal investigation. The only requirement of the player is that they not be in the game when fixes are applied. The player then sees any corrections made on their behalf the next time they login. This is a certainly a powerful capability, and has the potential for abuse. However, support employees are a lot less likely to give in to temptation when they know that the database is recording every transaction that occurs, and that it is extremely easy to track down the source of suspicious or illegal activity. Having the support staff utilize the database to manage the game also potentially frees development from having to create a "super client" or other proprietary mechanisms for administering the game when goes live. If desired, a suite of simple web based reports and forms can be built on top of the database to remove the need for the majority of the support staff to be conversant in SQL. I strongly suggest that you cannot have too many people in house with database skills, and that you should encourage those that are interested in learning. Customer support can also perform data analysis to discover exploits and cheats. While referential integrity will help cut down on bugs that are exploited, what is 'correct' data in the database can in fact be problems when someone asks the right questions. Triggers or queries should be written to report when data is legal yet falls out of 'normal ranges' so someone can take a look into why the variances exist. For example, the database can reveal if players are accumulating a rare item at a rate that is statistically out of bounds. It can also identify that players are accumulating in game currency at significantly faster rates than expected. Not every anomaly reveals a cheat or exploit, but with a database it is trivial to casually analyze the integrity of the game without any player impact. One side note: a lot of analysis activity involves running complex queries against live data. However the queries need not run in the current live database. Instead, to minimize any impact to the running game, such analysis should be performed on a very recent backup restored to a separate database instance. Marketing will want to perform data analysis to aid their efforts. More than likely, they will want summary reports. These reports can take a long time to run, and could potentially impact overall game performance. However, just as you would for support data analysis, provide Marketing with read access to a recent backup from the live game. When developing the game schema, you may want to consider that Marketing will likely want to generate reports for the entire game universe; as opposed to generating these per game world instance should that be your chosen architecture. The community team will be able to leverage in game data to provide additional player content that does not require the player to be connected in the game. For example, one of my employer's products, Lineage, offers web pages that allow players to see the items they have in their inventory and to make it publicly viewable if they desire. The web content also reports who currently owns the castles in various regions of the game world, and their current taxation rate. Having information accessible from a database makes providing such value added features embarrassingly simple. Of course, it should go without saying that interacting with these various departments while the game is still in development affords you the best opportunity to meet any specific needs they have. They know where their challenges occur, and with the database you have a robust mechanism to aid in alleviating these. Schema Maintenance During development, adding new objects to the database is quite straightforward. However as a schema grows over time, changes to the game design or bug fixing will call for modifications to tables and stored procedure that are already utilized by code. Applying the desired modifications will potentially break this code, causing delays for the team as it waits for the code fixes to occur. Both because it is a beneficial activity, and keeps your colleagues happier, make every effort to minimize the impact of schema and stored procedure modification. Build code that is resistant to change by understanding the types of changes that can typically occur. It will be common to add and remove column(s) on tables, or add or remove columns from a query result set. The data types of columns may change, as can the names of columns, tables or stored procedures. With this in mind, determine a strategy for how the code will handle these types of modifications. If it is not possible for the code to continue to run, at least structure it so that it can be easily modified to accommodate changes as the need arises. Finally, if an operational interruption is unavoidable, use email to announce it ahead of time to the team. This will help everyone maintain their sanity! Have QA perform testing on a separate snapshot of the database and code. This isolates development from QA and vice versa, and both parties will be better off with this scenario. When QA has tested everything, they can 'bless' the build, approving both the code and the compatible version of the database. Maintenance issues get magnified in scope and importance on a game that is live. The good news is that there will be regularly scheduled service windows when the game is offline to perform database maintenance. The bad news is that any maintenance has to be applied in such a way as to retain all of the players' progress in the game up to that point. Performing this task can be daunting, especially if the maintenance is a new content publish. It's almost a given that something will go wrong if a human being has to make the changes manually. Fortunately, there are third party tools available that will perform both a structural and data level comparison between database instances and generate the scripts necessary to make one database look like the other. Trust me; this is money that is well worth spending. Use such a tool to create the scripts and verify they perform correctly against the most recent backup of the live database. Then during the service window, apply the same scripts to the live database and you should be free of any surprises. Don't forget to make a backup of the database at the beginning of the service window after players are forced to exit the game. That way, if the planned modifications fail for some reason, you are able to quickly restore service using the backup without any loss to players. This buys time to resolve the issue independent of the pressure to get the game back on line. Conclusion If you are serious about the overall integrity of your game, and you want to provide the highest level of service to your players, you really have little choice but to utilize a database solution. Consider this as well: If you do forge ahead with a database, you will have a significant competitive advantage over those in the industry that decide against it. And perhaps more importantly, you won't be at a significant disadvantage compared to those in the industry that are already on board. Without a doubt, there are many issues to consider when choosing to implement your MMP game on top of a relational database. But with careful consideration to the information presented here, I am confident you can avoid many of the pitfalls in the process. In the end, the benefits reaped from a robust database implementation will far outweigh the negatives, especially when the game goes live.
|
|||||||||||||||||||||||||||||||||||||||||||||||||
|
|
Resource
Guide 
