Lava Technology - SQL Server 2005 Case Study



|Overview | | |“We held a bake-off against other technology vendors and Microsoft won—on performance. The bonus was |

|Country or Region: United States | | |that it was the lowest cost solution and provided better flexibility as well.” |

|Industry: Financial Services | | |Jeff Hays, Vice President Product Management, Lava Technology, Citi |

| | | | |

|Customer Profile | | | |

|Citi’s Electronic Execution Services offers a| | | |

|suite of Lava solutions that provide | | | |

|real-time aggregated market data across | | | |

|several stock trading systems. | | | |

| | | | |

|Business Situation | | | |

|Citi’s Lava Technology team needed to create | | | |

|a market data storage and retrieval system to| | | |

|provide compliance with the U.S. Securities | | | |

|and Exchange Commission’s Reg NMS | | | |

|requirements that traders provide | | | |

|best-available pricing when executing trades.| | | |

| | | | |

|Solution | | | |

|The Tick Store Reg NMS solution was created | | | |

|using the Microsoft® Application Platform, | | | |

|including Microsoft SQL Server® 2005. Tick | | | |

|Store includes 70 terabytes of historical | | | |

|market data that is pumped into the database | | | |

|to resolve REG NMS queries. | | | |

| | | | |

|Benefits | | | |

|Competitive advantage performance | | | |

|Compliance query responses reduced from hours| | | |

|to sub-second | | | |

|Agility to meet customer needs | | | |

|Scalability to match trading growth | | | |

|Lowest Total Cost of Ownership | | | |

| | | |When Citi’s Lava Technology team needed to create a compliance solution for the U.S. Securities and |

| | | |Exchange Commission’s Regulation NMS (requiring dealers to use best-available pricing when executing|

| | | |securities orders), it thought it would need a Cray supercomputer or other specialized solution. This|

| | | |is because terabytes of data are required to recreate market conditions at the point of an order |

| | | |execution at any point in time. After doing a “bake-off” competitive testing with other solutions, |

| | | |the company, which provides a suite of products to support real-time feeds across multiple equity |

| | | |markets, determined that the Microsoft® Application Platform, including Microsoft SQL Server® 2005, |

| | | |provided the best performance and lowest total cost of ownership. The solution supports peak loads |

| | | |that exceed 200,000 messages per second. |

| | | | |

|[pic] | | |[pic] |

| | | | |

Situation

Citi’s Suite of Lava products, has earned a reputation for market-leading performance and innovation in one of the most demanding computational environments of the world—providing real-time feeds across multiple stock markets (also known as equity pools) to help traders achieve the best execution prices for their customers.

The suite of Lava solutions provide real-time aggregated market data across multiple liquidity pools including the New York Stock Exchange (NYSE), NYSE Arca, NASDAQ, and other major domestic exchanges.

The Lava ColorBook® solution integrates the feeds from multiple liquidity pools for display on a single-screen montage to give clients an integrated direct market access (DMA) solution. Prior to the Lava DMA montage solution, traders accessing multiple markets would have separate, nonintegrated systems sitting on their desk. The Lava ColorMaker® solution builds market-making capabilities on top of Lava ColorBook by integrating price discovery, direct access, and quote management into a single application.

Several years ago, sell-side firms would have separate trading workstations for each stock exchange they worked with. Integration consisted of traders whirling around on their swivel chairs to enter their orders on separate dedicated terminals.

The Lava DMA solution was built using the Microsoft® Application Platform, including Microsoft SQL Server® 2005 database software. A pioneer in creating DMA solutions, the Lava technology team prides itself in having been the first to provide access to all the U.S. market centers.

A survey by Elkins/McSherry, which was published in Institutional Investor, ranked the Lava solutions tops in execution costs for NYSE trading and second for NASDAQ securities in a comprehensive evaluation that looked at commissions, fees, and market impact.

When the Lava development team created its first solutions its technical architects had a wealth of experience building UNIX based solutions for the financial services industry. Mindful of expense, they determined that redundant PCs could provide the same or better reliability as other platforms at a fraction of the cost. The decision was made to build Lava solutions using the Microsoft Application Platform.

Recently they again considered a range of technology choices as the team prepared to create a new solution to help it comply with regulations from the U.S. Securities and Exchange Commission (SEC). Regulation NMS (Reg NMS), a set of rules designed to modernize and strengthen the national market system for equity securities, was published in the Federal Register in June 2005, with a final phase-in required by October 2007.

At the heart of Reg NMS is the Order Protection Rule (Rule 611), which requires trading firms to execute a trade against the best bid or offer in the national market system. Reg NMS also requires that trading firms be able to demonstrate to investors that the best price was given.

Reg NMS compliance requires capture and storage of vast data sets. This is because if an investor comes back to an equity trader 20 minutes—or even several days or weeks—after a trade is executed and says, “I don’t think you gave me the best available price,” the trader must essentially be able to recreate pricing for that given stock across all equity pools at the exact time the trade was executed.

“I thought we would have to deploy a Cray supercomputer, mainframe, or something similar to create our compliance solution because it requires being able to traverse more than 2 billion records to recreate the montage for any point in time,” says Jeff Hays, Vice President Product Management, Lava Technology, Citi. “I didn’t think this could be done with SQL Server.”

Hays wasn’t alone. “To be quite honest, when this project started we really didn't even consider Microsoft because providing Reg NMS compliance is so demanding,” says Rocco Mangino, Senior Vice President Product Development, Lava Technology, Citi. “We were looking at specialized solutions that had been created specifically for tick storage and retrieval, but they were very expensive, and would have required our obtaining new skill sets to deploy and manage.”

Among those who believed the Microsoft Application Platform could support Reg NMS compliance was Ann Neidenbach, Managing Director, Citi, who had spent 20 years at NASDAQ before joining Citi’s Lava Technology group. [To read a Microsoft case study about NASDAQ’s use of SQL Server 2005, please see: .]

“The requirements were extraordinary—processing what has now grown to more than 200,000 updates per second across more than 100 million rows while supporting query responses of less than 2 seconds—that the development team simply didn’t think SQL Server could do it,” says Neidenbach. “But I’d seen how SQL Server had worked for NASDAQ and believed that it could.” A comparison of possible solutions was evaluated to determine which technology platform best met the requirements for creating a Reg NMS solution that could keep up with peak demands that exceed 200,000 messages per second, and that could scale into the future to keep up with the rapid growth in equities trading.

Solution

“We held a bake-off against other technology vendors and Microsoft won—on performance,” says Hays “The bonus was that it was the lowest cost solution and provided better flexibility as well.”

Tick Store

Citi’s Lava Technology group developed its Reg NMS solution, called the Tick Store, using SQL Server 2005 Enterprise Edition (64-bit) running on the Windows Server® 2003 Enterprise Edition for 64-bit Systems operating system. The Tick Store solution includes some 70 terabytes of data stored in zipped flat files. Each file holds one day’s data from one trading system. The solution grows by about 30 gigabytes (GB) per day, adding a terabyte of historical data every 33 days or so. The Tick Store is hosted on an HP ProLiant DL585 server computer with 8 64-bit AMD Opteron processors and 32 GB of RAM.

“We have all the ticks we’ve received since November 2001, as we lost data in 9/11,” says Mangino.

When fielding a Reg NMS query about pricing, the relevant data is imported from the flat files into the SQL Server relational environment of the Tick Store’s database. The Tick Store includes a key that associates the order routing with the market data.

“We can regenerate any past day from the archive, using the order number and time of execution linked to the historic data,” says Doug Leyens, Vice President Product Development at Lava Technology, Citi. “We can recreate the exact market montage for any given point in time.”

Architectural Notes

The Lava Tick Store architecture includes:

■ Tick Collector. The Tick Collector receives information from the Lava Market Data application which provides it with some 35 real-time data feeds. The application processes and normalizes the incoming data to create the order book for each symbol, placing the data into a cache. The Tick Collector and the other major elements of the Tick Store use SQL Server to store configuration data.

■ Tick Injector. The Tick Injector receives normalized data from the Tick Collector cache, and consolidates and optimizes the information for rapid transfer into the relational data store of the Tick Database.

■ Tick Database. SQL Server 2005 hosts the Tick Database, processing up to 200,000 updates per second across more than 1 million rows of data, while at the same time supporting query responses in less than 2 seconds. The database generally holds three days of data, after which the data is stored in flat files. The database also receives historical data pumped in from flat files to recreate a symbol’s order book for any point in time to provide Reg NMS compliance.

■ Tick Extractor. The Tick Extractor, using business logic held in SQL Server stored procedures, moves historical data from flat files into and out of the Tick Database in response to order book queries.

■ Storage. Flat files totaling some 70 terabytes, and growing daily, are stored on an EMC storage area network (SAN). Historical data is pumped from the SAN into the Tick Database to resolve Reg NMS queries.

■ Reporting. Reporting tools were created for generating fixed reports and supporting ad hoc queries.

The solution, which supports more than 16,000 symbols, or equities, was created using Microsoft Visual Studio® 2005 development system, with C++ used for generating the most performance-critical code. The Tick Store was created within 3 months, by a team of three contributing time equating to about two full-time equivalents.

All data is deployed using active/active two-node clusters, and synchronized across two geographically dispersed data centers.

Benefits

Building the Tick Store using the Microsoft Application Platform has enhanced performance so well that the Lava technology team values it as a competitive advantage. With Tick Store support for pumping historical data, as needed, into the Tick Database relational environment, compliance query response times have gone from hours to less than a second. The solution also provides the agility to meet internal and customer needs, scalability to match trading growth, and the lowest total cost of ownership of any solution it looked at.

Competitive Advantage Performance

Neidenbach notes that the high performance SQL Server provides is especially important because of the “burstiness” of market activity. “A year ago we were seeing peaks of about 60,000 messages per second,” says Neidenbach. “Now we are seeing peaks of 200,000 messages per second, and we expect that to go even higher because of the growth in algorithmic trading. Supporting heavy loads like this requires a sophisticated and fast database, because even when a trading algorithm behaves erratically and generates what appears to be questionable market data, every tick must be captured.”

Looking on the positive side, Hays says that the heavier the trading becomes, the more the Lava solutions will stand out for their ability to keep up with the ever faster pace.

“Until recently we were seeing 800 million updates in a day,” says Hays. “We’re now sometimes seeing nearly 2 billion messages a day. We’re gaining a competitive advantage because we have the technological foundation that gives us the performance to keep up with the growing loads.”

Compliance Query Responses Reduced from Hours to Sub-Second

Prior to deployment of its Tick Store, Reg NMS queries could take four or more hours to complete, as historic data was assembled into an order book to recreate the montage for the order being asked about.

“By pumping the archive data into SQL Server we’ve reduced montage creation time for historic data from four or more hours to less than a second,” says Mangino.

The company reports processing speeds of 1.6 billion rows per second during montage creation.

During the bake-off, Mangino says, the performance of SQL Server was so fast that the Lava team asked for the test to be repeated to make sure nothing was left out.

“For the bake-off we created a pilot program that provided an accurate representation of what we wanted to test,” says Mangino. “We submitted the pilot to vendors, and the Microsoft results were surprisingly fast. We were especially impressed with the speed of query responses even as the database was processing more than 120,000 updates per second, which was the rate we used for testing.”

Agility to Meet Internal and Customer Needs

Citi values the agility that enables it to easily change its Lava Tick Store solution to meet internal and customer needs. Updating the application is easy for the company because its internal developers created the solution themselves using the Microsoft Application Platform.

“Other products we looked at would have required us to go through a product change request process, in which we were asking a third party to change their product to better meet our needs,” says Neidenbach. “We don’t have time for that. We need the ability to fine-tune our own solution whenever we like, and that is what we are able to do with SQL Server 2005.”

The team knows that the need for change isn’t just theoretical. “The industry changes constantly,” says Neidenbach. “We get notices every day of changes in the data we’re receiving. They may be adding new fields, or new order types, and we need to be able to modify and accommodate those changes as they happen. Because this is our own product we can make those changes as they are needed.”

The agility of SQL Server and the rest of the Microsoft Application Platform makes it easy for the Lava Technology team to use the information it handles to gain new value, including using SQL Server 2005 Analysis Services to do analytics.

“If we were nailed down to a fixed third-party product we would be limited to the existing functionality,” says Hays. “Instead we are in a position where we can do whatever we like. For example, although we haven’t yet made use of Analysis Services, we could use it to open up new possibilities in what we do with our vast amount of data.”

Customers are intrigued by running analytics against terabytes of historical data to see what value might emerge. Hays notes, “Our customers are very interested in what they might learn from this data about how their algorithmic trading programs are working or anything else that yields insight into the market.”

Scalability to Match Trading Growth

Citi’s Lava Technology team has witnessed exceptional growth in trading activity over the years, and anticipates the growth to continue. This means it must have a highly scalable solution, which is exactly what it has created using the Microsoft Application Platform.

“To get a perspective on the rate of growth, consider that when we first started less than 10 years ago we were processing about 1,000 messages a second during peak loads,” says Mangino. “We’ve now seen that grow to 200,000 messages a second, and we have every reason to believe that trading volume will continue to climb, especially with increased use of algorithmic trading.”

“SQL Server has continued to scale to meet our needs,” says Neidenbach. “And we see no end in sight for that scaling.”

Lowest Total Cost of Ownership

Citi’s Lava Technology group decided to build its Reg NMS solution using the Microsoft Application Platform because its testing found that SQL Server 2005 provided the best performance. So it was an added bonus that using the Microsoft Application Platform also provided the lowest total cost of ownership (TCO) of all the alternatives it looked at.

The lower TCO was based upon licensing costs, hardware costs, and ongoing operational costs, including administrative resources.

“Without getting into names specifically, one of the reasons I kept pushing for SQL Server is the fact that so many of the alternatives had high licensing costs as well as difficult to use proprietary interfaces and cryptic codes that would have required our training or hiring application specialists,” says Neidenbach. “Some solutions were so specialized they would have been more like integrating an outsourced project, and we didn’t want to lose our ability to do our own fine-tuning.”

Mangino estimates that the cost of using an alternative solution would have been higher by approximately U.S.$1 million on the initial purchase cost “and higher by an additional cost of several hundred thousand dollars a year for ongoing licensing and administrative services.”

In summary, Citi’s Lava Technology group was able to quickly develop and deploy Tick Store as a high-performance and scalable Reg NMS compliance solution using the Microsoft Application Platform. Their “bake-off” testing against other potential solutions showed that SQL Server and the Microsoft Application Platform provided better performance as well as a lower TCO.

Microsoft Server Product Portfolio

For more information about the Microsoft server product portfolio, go to:

servers/default.mspx

Microsoft SQL Server 2005

Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage. And, because it’s part of the Microsoft server product portfolio, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.

For more information about SQL Server 2005, go to:

sqlserver

-----------------------

| |Software and Services

■ Microsoft Servers

− Windows Server 2003 Enterprise Edition for 64 Bit Systems

− Microsoft SQL Server 2005 Enterprise Edition (64-bit)

■ Microsoft Visual Studio 2005

■ Technologies

− SQL Server 2005 Analysis Services

|Hardware

■ HP ProLiant DL585 64-bit server computers with 8 AMD Opteron processors and 32 GB of RAM

Hardware

■ AMD | |

“SQL Server has continued to scale to meet our needs. And we see no end in sight for that scaling.”

Ann Neidenbach, Managing Director, Citi

| |

Data Flow - Integration of CDTC (Tick Collector), CD TI (Tick Injector) and CDTS (Tick Storage

“By pumping the archive data into SQL Server we’ve reduced montage creation time for historic data from four or more hours to less than a second.”

Rocco Mangino, Senior Vice President Product Development, Lava Technology, Citi

| |

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published January 2008 | | |

For More Information

For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:

For more information about AMD products and services, visit the Web site at:

For more information about Citi’s suite of Lava products and services, call (212) 609-0100 or visit the Web site at:

[pic]

Fast Facts | |Speed of montage build processing |1.6 billion rows per second | |Peak loads on SQL Server 2005 Tick Store relational database |200,000 updates per second | |Size of largest Tick Store database table |100 million+ rows | |Average daily Tick Store update messages |2 billion | |Total size of Tick Store flat file records |70 terabytes and growing | |Current growth rate of Tick Store records |30 GB per day | |Tick Store development team |2 fulltime equivalents | |Tick Store development time |3 months | |Application Platform Capabilities |Development, Data Management, Business Intelligence | |

“We can regenerate any past day from the archive, using the order number and time of execution linked to the historic data. We can recreate the exact market montage for any given point in time.”

Doug Leyens, Vice President Product Development, Lava Technology, Citi

| |

“I thought we would have to deploy a Cray supercomputer, mainframe, or something similar to create our compliance solution because it requires being able to traverse more than 2 billion records to recreate the montage for any point in time.”

Jeff Hays, Vice President Product Management, Lava Technology, Citi

| |

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download