Oracle to Microsoft SQL Server and Sharepoint Migration for Wolters Kluwer SFS

Details

Due to the large volume of returns processed during the tax season, it was imperative that the new system be able to process tens of thousands of messages per minute to keep the report system in synch with submitted returns. The proof-of-concept demonstrated the potential speed for the solution, which was architected to include:

A SharePoint 2010 farm to serve both as a service bus to provide scale-out capabilities for processing incoming XML-based messages and report requests, as well as the control panel to monitor message exceptions and resubmit messages as necessary.

Entity Framework 3.5 to publish and update messages to the SQL Server 2008 database engine.

A group of SQL Server 2008 database servers that used replication and data partitioning to distribute the report workload across customers.

Multiple SQL Server Reporting Services (SSRS) nodes to process and deliver reports in real time.

The system was designed to provide scale-out capabilities at the processing (SharePoint farm) and reporting (SSRS nodes level) to allow SFS to add nodes as needed to accommodate growing demands during the tax reason.

Why Atmosera?

Microsoft needed a partner that was expert in architecting an enterprise grade solution involving SharePoint 2010, SQL Server 2008 and SSRS capable of high concurrency and designed to scale out effectively. Atmosera’s deep experience with designing, developing, and implementing large scale solutions using the Microsoft technology stack was the deciding factor. Atmosera is a recognized leader in software architecture and implementation on the Microsoft platform.

Strategy

Atmosera began by providing a proof-of-concept project to process sample messages. The proposed solution would receive XML messages from the back-end system through a service end point hosted by the SharePoint 2010 farm. SharePoint would pass these messages to handlers written in C# that validated the messages against schemas (XSD), and then used the Entity Framework to publish and/or update the messages to the master database. The master database would then partition the data and replicate it to the SSRS nodes for report delivery.

Conclusion

The proof of concept was successfully implemented over several weeks and demonstrated a throughput many times in excess of the requirements, so the full production system was scheduled to move forward. The production application was built over the course of a quarter to be fully tested and implemented in time for the 2011 tax season. The SharePoint 2010 farm provides the ability to scale out as needed to handle messages. A unique handler architecture developed by Atmosera provided the means to build distinct handlers for messages that were registered with SharePoint and used to process incoming messages. To handle new messages, the team simply writes a new handler and registers it with the SharePoint system and it is ready to begin processing.

Nearly 100 reports were converted from the legacy Oracle system to SSRS. These reports are hosted on SSRS nodes. The partitioned SQL Server 2008 architecture allows SFS to specify new nodes as needed based on demand. If a new or existing customer starts requesting a high load of reports, the team can specify a new node and replicate data to that node for dedicated processing of reports. The customers are able to request a report online, which is routed to the correct node through the SharePoint 2010 server and then delivered in CSV or PDF format to the end customer.

The original system processed 20 million returns a year. It peaked at 110,000 returns per hour and reports were 24 hours old. The converted system achieved up to 360,000 returns per hour and all reports are now generated near real-time.

Atmosera and Microsoft were able to demonstrate the power and scalability of the SharePoint 2010 and SQL Server 2008 technologies. The developer tooling enabled the team to write the entire system over a single quarter in time for the customer to face the 2011 tax season. The use of the Entity Framework not only accelerated development by providing a seamless integration with the database backend, but also demonstrated the maturity of the technology through the high performance and throughput it was able to achieve. The architecture will allow SFS to meet the increasing demands for tax returns in subsequent years by providing multiple tiers to scale out. The final result was over 300% improvement throughput of message processing and the elimination of the old batch-based 24-hour reporting system in favor of the new on-demand reports that are generated in real-time.