Author: Jeff Marino

SQL Server Index Optimization Software

In 2011, EmpireCLS needed a way to automate index rebuild and re-organizations in SQL Server. This was a direct result of a small outage experienced on a weekend.

We diagnosed that under certain circumstances certain indexes in the database would not perform well. In some cases it didn’t matter what the percentage of fragmentation was, queries would just be slow.

We built an automated process that would automate the rebuilding and reorganization of indexes based up fragmentation levels. This job would scan all indexes in the database on a daily basis and queue up the SQL operations in off hours.

On a weekly basis certain indexes, those that would degrade performance regardless of fragmentation level, were automatically rebuilt.

We also had historical tracking of when the indexes were rebuild and what fragmentation level they were at when rebuild job was queued up.

Customer Notification Software

In 2011, EmpireCLS needed a notification system to send out email and text notifications to customers for whom the chauffeur when a they were in a location waiting for the customer. Special notifications were used for VIP customers and high security customers so that their managers and handlers could receive full background data on the chauffeurs for security purposes.

Upon landing at an airport a customer would know that their chauffeur was there waiting for them, who the chauffeur was, and the chauffeurs cell number in case they had to contact them.

Technologies… SQL Server, C#

Be Transported Product

In 2011, EmpireCLS wanted to unite their 500+ affiliate network and build a marketplace for customers.  This was a brand new business entity and was released as betransported.com.

We worked extensively with the EmpireCLS executive team to help define the business model.  We were then able to implement the entire application in around 9 months; website, accounting, mobile app, and the on boarding of 400+ affiliate partners.

The project was a success as far as the partner relationships were concerned.  There were many challenges with getting customer adoption so it was put on hold indefinitely.

System highlights… web UX for creating rating structures, extensible rating API, cross platform mobile app with code reuse from back end, extensible excel import framework for bulk loading rating structures.

Technologies… C#, SQL Server, asp.net MVC, asp.net Web API, Xamarin.

Flight Tracking Software

In 2010, EmpireCLS needed automate the tracking of flights to better control the dispatching of their drivers. By having the most accurate view of a flights estimated arrival time EmpireCLS would be able to optimize the dispatching of vehicles and cut down on vehicles being idle at airports.

Technologies… c#, SQL Server, Flightview API

SQL Server Table Change Tracking Software

In 2009, EmpireCLS needed the ability to audit all table changes to see which fields were changed by which user or system at any point in time.

We analyzed the out-of-box SQL Server change tracking framework but it fell short in several areas, the most important of which was not being easy to maintain when adding new columns to a table that was being tracked.

We designed and implemented a custom generic solution for EmpireCLS that easily handled what was needed in this environment. We used a services of triggers in SQL, a back-end C# service, and a separate reporting database with a common set of SSRS reports to view the changes.

The SQL triggers and temporary tables could be generated via a single stored procedure that would interrogate the system tables for column names and types. This stored procedure would also do full diffs of the source tables so that column additions were automatically handled. Bringing a new table or an additional column could be done in seconds with no risk or change to the backend service or reporting infrastructure.

The backend service was generic and would infer the list of tables via a separate schema in SQL Server. The service would then handle batches of before and after data records, do diffs, and create the appropriate header / detail history tables in the reporting database. Only changed columns would end up being saved, which each column change equating to a detail row with before and after values, all details of the change where bound to a single header containing the change time and user.

Technologies… SQL Server, SSRS, C#, ado.net

Email and SMS Software

In 2009, EmpireCLS needed to migrate their customer email notification service off of Linux. We built an extensible framework that allowed EmpireCLS to easily add custom email and SMS notifications easily and with little risk.

A Plug-in architecture that could easily add SMS and Email specific formatting logic without changing common infrastructure code for sending and queue processing.

Technologies… C#, SQL Server, Exchange

Cache Software

In 2008, EmpireCLS needed to have a real-time display of current reservation data for it’s new dispatch system. Because of the number of client applications and the frequency of reservation data changes the dispatch application could NOT poll the SQL Server database directly.

This service monitored the relevant reservation data and cached up to the minute changes for access by all dispatch applications. The decreased the load on SQL Server and allowed the dispatch application to operate as fast as possible by just getting diffs of the datasets that they were working with.

Technologies… C#, SQL Server

Dispatch Software

In 2008, EmpireCLS needed to replace their existing Linux dispatch application. The application was a console app that was extremely difficult to maintain. The goal was to rewrite as a GUI fat client on Windows.

This application allowed the dispatchers to see all chauffeurs in real-time throughout the day. If the chauffeur was picking up, dropping off, or just waiting for instructions, it was all visible within this application. Real-time shift visibility was also a requirement, this allowed dispatchers to cut down on overtime and increase safety by constantly adjusting the workforce.

By using this application EmpireCLS was able to achieve a significant cost reduction in their overall operational costs and increase customer satisfaction.

This application was to integrate directly with SQL Server but was to be used in parallel with the existing Linux reservation and accounting system.

Technologies… C#, WPF, SQL Server

Database Replication Software

In 2008, EmpireCLS started the migration of their legacy Linux data to Microsoft SQL Server. A custom real-time data replication service was created to facilitate this first step in porting the legacy Linux C code to C# / .NET on windows.

A code generator was created that would parse C header files and create an XML model representing all of the data structures that were used by the existing ISAM database. This XML model was then used by Code Smith templates to generate SQL Server tables, procs, and triggers that would handle both the storage and access. We also generated a complete C# data access layer with Code Smith that would be used by the various services to interact with the database. The C# data access layer was to be the marshaling layer between the ISAM and SQL Server databases.

Technologies… C#, XML, Code Smith.