Windows Azure SQL Database Marketplace
Editor’s Note: Today’s post, written by Bill Davidheiser, Chief Architect and Co-founder of iVoteSports, describes how the company uses Windows Azure to power its iVoteSports MLB-focused mobile game.
We started iVoteSports.com (sold on the Apple, Android and Amazon app markets) with a fairly simple idea that most sports, like baseball, can be broken down into games within the game. For example every baseball inning has many at-bats and each individual batter has a number of potential outcomes for his at-bat such as a walk, strike and home run.
The basic theme of the game is to let users to predict the outcome of each event and win points for guessing correctly. The winnable points are based on a number of factors such as the probability of the event outcome.
Determining the winner of a live sporting event is challenging due the volume and frequency of events. Using baseball as an example: Each game has 9 or more innings with 6 at-bats per inning, and each at-bat has at least 3 events (strike, ball, etc). This, coupled with a volume of 162 games per team per regular season, creates a tremendous volume of events that cannot be managed by a human umpire – at least not within the practical resource constraints of our game. To address this, we created a way to programmatically determine outcomes using crowd sourcing concepts.
WINDOWS AZURE DESIGN AND FLUCTUATING TRAFFIC
As mentioned, the nature of live sporting events is that a lot of people get together in relatively short time period (around 3 hours) and then they rapidly disperse. Since the iVoteSports application is played during the live game, it needs to support these dramatic usage spikes.
This type of fluctuating demand is perfect for a cloud application. In our idle state we maintain two small (single-core) Windows Azure web role instances. However, as many players come on board, we can quickly add web instances, scaling out as demand ramps up. The trigger point for adding additional instances intra-day is based mostly on processor utilization: if we are consistently exceeding 80% we will add additional instances.
In future versions we will programmatically add instances via the management API, taking advantage of the Microsoft Enterprise Library’s Autoscaling Application Block (WASABi), but for now scaling is performed manually. For days that have enough popular games to require increased capacity, we can proactively increase the instance counts.
The actual build-out of iVoteSports turned out to more closely resemble a multi-tier enterprise application than a mobile game. We have distinct concepts of presentation, application and data layers.
Since the database is multi-tenant, user id’s are associated with all tables that deal with activities such as keeping score, making a prediction, or asserting an outcome. Our views, UDF and stored procedure development was pretty much exactly like what would be created for a typical .Net application. Since only user prediction and outcome information is kept from day to day (stats and schedule data is archived) the 150GB Windows Azure SQL Database limit is not an issue.
Our initial decision to use SQL Database was driven mostly by the desire to create optimized TSQL that could be changed on the fly without need to redeploy code. For our next generation of the game, we will likely migrate some, if not all, of our data structures to the more cost effective Windows Azure Table Storage.
As a side note, a mobile app that has a lot of the logic in the server has nice benefits. Web developers get spoiled in the ability to push out a hot-fix in near real time to correct bugs. Unfortunately when a bug is found in mobile code there is nothing real-time about the fix. In the case of Apple, it could take as much as a week to get the change approved.
Our presentation tier is the mobile device. We started by creating a lightweight API that was really optimized for unpredictable traffic between a wide range of mobile devices – including the really old phones unable to make rapid network calls. The test case was an antique 1st generation Droid that had such an overburdened processor it would take an Angry Bird around 5 seconds to cross the screen.
While security is not a major issue with the game, we implemented authentication enforced by coupling the email address with the physical device ID. If needed in the future, we can derive a security token used for role based authorization of certain functions.
All traffic originating from the mobile devices are stateless and effectively synchronous. Each mobile device will perform a lightweight sever poll every few seconds to check if there are messages waiting. If messages are waiting, a more expensive data exchange call is made.
Probabilities are central to our game. The probability of a play outcome is driven off a handful of key influences such as historical event outcomes (fly-outs are more common than walks), player match-ups (batter-X does well against pitcher-Y) and player aptitude (batter hits .240). There are of course other factors such as stadium, injury and weather that play a part in the probability, but on average these factors are minimal and mostly just applicable to a local sports bookie.
Mashing key influences together produces a combined probability that can be converted to winnable points for the predictor of the play outcome. We call that prediction an iVote. For example: a home run against Casey Jones batting against Joe Throwhard will earn 30 points for a correct iVote while a home run predication for a weaker batter than Casey may give the opportunity for 100 points. Las Vegas people commonly refer to this as a “point spread.”
Taking it one step further, when many people are involved with the same play, point motivations can be created to encourage less popular predictions to be made and keep a more even distribution of iVotes. This concept is roughly patterned after a what is often known to as “Spread Betting.”
It is important to note that even though iVoteSports.com deals with a lot of gambling concepts, we are in no way a gambling application. There are only points involved – never actual money.
Adding current stats allows up to make the game quite a bit more interesting by preloading batting line-ups, showing player bios and trivia, and of course use the most current stats so that our probability calculations are using good data.
We get two types of data from Stats.com: daily and pre-game. The daily data includes schedule, roster and player statistics and are loaded into our SQL Database at 4am Pacific time each morning. The pre-game data is loaded about 15 minutes before each baseball game starts and contains the starting batting line-up for each team along with the starting pitchers.
Programmatic determination of an event’s outcome is quite difficult. This is not an issue when a trusted official is recording each event, but as mentioned earlier using a human official was not a scalable answer for our mobile app where we can have dozens of games occurring at once at many different times of the day.
Crowdsourcing design patterns turned out to be an excellent answer for us. As documented by many excellent articles and practical examples such as the Truthsquad Experiment, if you get enough people saying that something is true then it probably is. Of course there are caveats to “collective truth” such as ensuring against collaborators and having less than a critical mass of people. However these challenges can be mitigated and on the whole crowdsourcing is a mathematically proven approach and quite effective for our purpose.
As shown in the graph ‘Relation of assertions to accuracy,’ when we reach the critical mass of players in a given event asserting an outcome we gain have confidence the assertion accurately represents the outcome. For example, if we have 30% users from the total population that say a specific event occurred (such as a player has struck out) we have a confidence exceeding 50% that we have a confirmed, actual outcome.
Confidence greater than 60% allows us to not only reward the people that predicted correctly but penalize those that appeared to have cheated by saying they iVoted right when they really didn’t.
We feel very comfortable with our overall design; specifically by running a large component of our game in Windows Azure, we will be able to rapidly develop to new presentation platforms.
We found the Windows Azure SQL Database query optimization process to be more challenging than with regular SQL server. We used the now discontinued RedGate backup utility to create a local copy of the DB and then ran the SQL Profiler, feeding the results into the SQL DB Tuning Advisor. We manually applied the Tuning Advisor suggested indexes to our SQL Database via SQL Management Studio. Hopefully Microsoft will give tools to improve this process in the near future. The main Windows Azure lesson we learned during the development process had to do with deployment. We initially used the web role’s web deploy option without realizing that the VM would be reset on a periodic basis. The non-persistent nature of web deploy will cause the deployment to be reverted back to its original state when the web role VM is re-imaged. When re-imaging occurs, Microsoft does not send notification – at least no notification that we were aware of. This caused confusion when our application kept reverting back to older behavior.
However overall we had an excellent experience with Windows Azure. With unpredictable and rapid demand variations, Windows Azure proved to be an ideal operational platform for our game. In addition the development tools gave us good productivity with short learning curves.
While technical in nature, we hope the end result is an easy to use and entertaining sports game. To check out the results of our work, please visit us at www.ivotesports.com.