(originally posted on microsoft.com/casestudies – http://bit.ly/Ltlz2M)
TradeStation provides historical stock-market data so that traders can test investment strategies. To support a massive planned increase in data volumes for an important customer offering, TradeStation needed to update a data management solution that required excessive maintenance work and lacked sufficient scalability. Based on extensive experience, the brokerage deployed a Microsoft solution, which further strengthens customer relationships and cuts manual workloads and processing overhead by half.
TradeStation offers traders analysis tools that include providing historical data against which strategies can be tested. It offers about 30 years’ worth of data for all major stock exchanges, and customers have access to a development interface where they can build and test computer-based buy and sell strategies.
“The SQL Server 2012 solution has reduced the IT department’s manual workloads by at least half” – Jose Ruiz, Quality Assurance Director, TradeStation
To support growth, further strengthen customer relationships, and expand into a new line of business, TradeStation needed to update the solution it was using to manage its historical trading data. One problem with the existing solution was that it did not support having one centralized, authoritative version of the data, so customers had to access copies on different machines. Occasionally, such as after power interruptions or operating system failures, these duplicated files diverged.
Updating these files was also a challenge. “Adding each new day’s data required rebuilding our files every night, which took anywhere from one to six hours,” says Joe Messina, a Senior Engineer for TradeStation. This high processing overhead reduced the company’s ability to deliver data promptly to customers.
In addition to correcting these shortcomings, TradeStation had another goal. “Currently, our system stores only a stock’s best asking price, best bid, and any actual trades,” says Messina. “Our plan is to begin offering access to market depth data, or every single asking price and bid for each stock. That’s going to expand our data volume by about a hundredfold, and it just wasn’t going to be possible with our old, homegrown system.”
TradeStation wanted a solution that could more easily manage binary large objects (BLOBs) and deliver data to customers from one central database. The solution needed to be scalable, easy to update, and simple to maintain.
TradeStation considered KDB and Vertica solutions but ultimately decided to deploy Microsoft SQL Server 2012 data management software. “We already use a lot of Microsoft technology elsewhere in our IT environment, and we’re very comfortable with it,” says Andrew James, a Product Manager for TradeStation. “This solution is going to be the foundation for the long-term growth of our company, so we wanted to go with a technology that has large teams of people expending all of their effort and brainpower making it even faster and better. That’s the reason we chose Microsoft.”
With SQL Server 2012, TradeStation can use the FILESTREAM feature to store BLOBs in its SQL Server database but still be able to access them from Windows applications as if they were stored in the file system. The database holds 4 terabytes of market data, and TradeStation receives about 175 gigabytes of new data per day. The new solution is deployed on HP ProLiant DL360 G7 server computers.
To simplify maintenance, speed data loading, and optimize query performance, TradeStation is using dynamic partitioning logic in SQL Server 2012 to create a new partition every 100,000 new files. SQL Server 2012 supports as many as 15,000 partitions to support large sliding-window scenarios that can streamline maintenance of extremely large data sets.
In the next phase of the deployment project, TradeStation plans to implement SQL Server 2012 Remote Blob Store (RBS) to further reduce strains on its storage and processing resources. With RBS, the company has the option to use commodity hardware for dedicated storage solutions and make use of a scalable architecture to increase throughput and grow to accommodate thousands of terabytes.
The Microsoft solution cut manual workloads for IT and other employees, improved the company’s relationship with its customers, and reduced processing overhead.
Reduces Manual Workloads by 50 Percent
The new solution so simplified the data management infrastructure that it sharply reduced the time that TradeStation IT workers needed to spend on maintenance. “With SQL Server 2012, we’ve reduced the number of physical servers from 50 to 4, or 46 fewer machines that we need to worry about upgrading, managing, and tracking,” says Jose Ruiz, the Quality Assurance Director for TradeStation. “And because we now have one centralized set of data, we’ve cut out all the time we used to spend duplicating data sets from one machine to another. When you combine these improvements, the SQL Server 2012 solution has reduced the IT department’s manual workloads by at least half.”
The new solution is saving time for other departments, too. James says, “The SQL Server 2012 solution is reducing the number of trouble calls to our data-integrity department.” Keith Black, Vice President of Product Development at TradeStation, agrees: “SQL Server 2012 has removed a lot of pain and wasted time. That’s a big win for our employees.”
Further Strengthens Customers’ Trust
The solution’s centralized, authoritative data eliminates the inconsistencies that customers sometimes encountered previously. “Before, when our data was duplicated across so many machines, sometimes customers noticed differences between data sets that should have been identical,” says Ruiz. “By centralizing our data with SQL Server 2012, every customer is going to access the same data, no matter what server they log on to and get served from. That improved consistency is a huge gain for us and for our customers.”
Cuts Processing Overhead
By centralizing data storage, the new solution drastically reduces the amount of processing time needed to update or correct data. “By moving all of our data to SQL Server 2012, we no longer need to create multiple copies and rebuild the files each night, so we’ve dramatically reduced the length of time needed to process our end-of-day files or make corrections,” says Messina. “SQL Server 2012 is also helping us serve requested data to customers more promptly, which makes them happier.”