Azure SQL server with PlayFab. How and what to store?
:[ really didn’t want to go this path but with msft having huge cloud infrastructure obviously they would want users to go there.
question: since playfab stores most vital information for us, what should I store on sql? Should I duplicate as much playfab info on the player on sql as well?
goal: my goal for making an sql is to do things like store trade history for players, query all players trade history by anyone. Implement a tax for guilds, a tax for lands etc... all of which I can’t work around with existing playfab resources unless I am missing something. Thanks.
I also don’t want to add too many external APIs, because 200ms adds up...
side question: do you think I should use firebase cloud functions instead? I’m debating whether to use a nosql because multiplayer games are relational heavy.
Best Answer
Double-pumping API calls to different endpoints isn't recommended, since a) you're inevitably going to have sync issues between the data sources, and b) it's not the most cost-effective route.
What would be better would be to use the event export service to get all your data into Azure Blob storage, and then use it from there using PowerBI (or similar). That way, you could do things like run a script that queries for player trades for the last 24 hours and so generate your daily tax.
But then, this might be even simpler for some of what you have in mind, depending on your requirements. If what you really want for the tax concept, for example, is to apply a tax to any trade transaction, you could just set up a Rule that fires an Azure Functions Cloud Script Action which does the calculation and applies the tax where you need it.
Event exporter is not what I’m looking for. The data I need to track is constantly changing dependent on playfab. Like I need to read tax Rate data field constantly and playfab says I the shared data shouldn’t be read too much. Same thing for storing and reading trading history for all users.
If you mean shared group data, that's correct - that data type is really just user data with no user owner, so it shouldn't be used for cases where you have a lot of players trying to use it (for read or write). But Group data (Entity Objects and Files) is designed for guild/team/clan use, so you could use those if the number of players using the specific data is only 1K or so. Also, if the tax rates are global, you might consider using Title Data, since that can be used to serve data to all players in the game (though if the tax rate is dynamically changing, obviously this wouldn't be useful).
But it sounds like what you're saying is that you want to aggregate data in real-time across all players and make dynamic changes to global parameters. If so, the frequency of data changes really means you will need an in-memory data system, rather than disk-based. If you want to use SQL, you should check out In-Memory OLTP. Otherwise, I'd recommend Redis or similar.
I'd still recommend looking at the export route, since that should be a lower cost solution overall. The trade-off is that you'd be limited to aggregating and updating your globals once an hour (the max frequency for export).
