Some of the projects I have created / performed include:
|Near real-time operational rules engine||Ran on top of an OLTP system for real time operational reporting. Made use of CDC (Change Data Capture) to determine the incremental changes. Once changes were determined a subset of rules would run in parallel against changed data to revalidate the rule’s status. This design allowed for quick turn around time of rule status and can scale to up to 1000 rules. Two modes of operation were incorporated, a full and an incremental. The incremental ran intra-day and provided a one minute turn around time. The full refresh ran all the rules against all the data for consistency checks.
This application has been running in production for 2.5 years with approximately a 99.9% uptime.
|Near real-time XML based rules engine with dynamic PDF form population||Created XML Based rules easily manipulated by the front end. This abstracted the front-end from having to create SQL on the fly and allowed rules to be created quickly and easily by re-using existing logic. The XML rules contained attributes that defined parameters. These attributes then referenced SQL modules on the back end, which were combined to output a final result set. Additional instructions could be sent with the rule, in order to dynamically populate a PDF dynamically with data. This project returned a recurring 8 million per year.|
|Three tier reporting Services setup using web service driven front-end and integrated authentication using Kerberos delegation.||Both a normalized Data Store and cubes were used for reporting purposes. Input Parameters were cascading and dynamically constructed. XML based entry point stored procedure allowed for dynamic parameters.|
|Datawarehouses||I have created two data warehouses. One utilized a Historical Normalized Data Store and configurable parallel data imports with 2 refresh modes, full and incremental. Three tier package design facilitated logging at job level, package level, and detail level. Global variables were parameterized and could be changed at run-time. End of day history was kept for 1 year, then history was pruned to one month.|
|Performance Tuning / Slowdown troubleshooting||
I have performed many of these jobs. The majority of the time, I can diagnose these with only the use of perfmon. Other times, the relation between the bottlenecks may not be as apparent. In these cases, you can use SQL Nexus & SQL Diag to help diagnose the issue.
Also, understanding the interrelation between the middle tiers and the back end is important. A slow front-end may be caused by an issue on the database, however often times it is not apparent from the database side.
|SQL 2000 to SQL 2005+ Upgrade||
I’ve performed three of these. Some important key factors include :
My main methodologies include object oriented / modular design, creating minimal points of failure, implementing debug modes and logging. I firmly believe in creating proof-of-concepts using the most critical path. This ensures scalability and stability by mitigating issues in the beginning of the project rather than in the end when it’s too late.