A Modern Marvel of Direct Data Consumption
Service Integration with WCF and CLR
The Challenge:
Our healthcare client had a system used to calculate expected reimbursement of managed care claims so they have a better idea of their true expected earnings (Accounts Receivable). The system used SQL Server for data management. The client needed to integrate 3M components into its calculation routine, which resided entirely within a SQL Server database. (3M Healthcare makes software for computing certain clinical reimbursement models, and provides Java and 32-bit COM APIs.)
The Cimetre
Solution
We applied a Service Oriented Architecture (SOA) model to capture the reimbursement data from the Java-based 3M application. With a goal of having the 3M processing occur on a separate server, we exposed the necessary methods through Windows Communication Foundation (WCF) end points, and consumed them directly from SQL Server via CLR procedures.
The Challenge: Transactional Replication Delay
This client used transactional replication to populate a read-only data replica with millions of records daily. During peak data loading periods, the replication backlog grew to several hours, even though the replica's hardware was similar to the master — and both systems resided in the same data center.
The Cimetre
Solution
By analyzing the data traffic on the replica using SQL Profiler, we were able to isolate the problem: poorly-performing select statements related to replication. Judicious application of indexes to some of the replication system tables completely eliminated the delay in replication.