Robert Coleridge
MSDN Content Development Group
Recently, one of our readers wrote to express concern that two of the articles in our Helpdesk series, "Designing the HelpDesk Transaction-Processing Object" and "Task Distribution in a Customer Service System," (published in the last issue of Developer Network News) offer conflicting data-access models. In fact, they are actually in complete harmony. It was a good question, though, so we decided to take the time to reply here, in case other readers had the same impression.
Fred Pace's article, "Designing the HelpDesk Transaction-Processing Object," which shows how the object model for the HelpDesk sample was designed, gives an excellent example of encapsulating data access through a defined API. Why would Fred do this? The answer is quite simple: In a distributed environment, all client/server designs should be based on an n-tier paradigm.
Fred goes to great lengths to explain the design model for the HelpDesk. There are several reasons why this design is advantageous: The application is enterprise scalable, the application is easier to maintain, the application's clients tend to be thinner, and last but not least—the actual data is not exposed to the user, only a working copy of it. By encapsulating access to the data with an API set, there is a safety net of validation, security, workflow, and business logic. All of this ensures a much more scalable and robust application.
Steve Kirk's "Task Distribution in a Customer Service System" is a great article on how to do load balancing with Transact-SQL (T-SQL). Through the power of T-SQL, Steve shows how the HelpDesk sample uses SQL tasks to automate the assignment process for incoming requests. In order to gain the highest performance-to-maintainability ratio, Steve wrote a "distribution optimizer" in T-SQL. By doing this he gains direct access to the data and encapsulates the data access to a few stored procedures.
Since Steve's optimizer is written in T-SQL and directly accesses the data, it might appear that he violates the n-tier paradigm that MSDN advocates. However, because the optimizer is a scheduled SQL task, it runs at the lowest level—in the same tier as SQL Server itself.
So, here we have two models: Fred's encapsulates data access through an API set, and Steve's directly manipulates the data via T-SQL. The question is, "Are they in conflict?"
My answer: The model that Fred espouses deliberately exposes the data to a user, but only through an API set, while Steve's model does not expose the data at all. While Fred's API is for external exposure, Steve's stored procedures are for tasks internal to SQL Server and would never be invoked by an end user. So they are not in conflict, merely fulfilling different tasks on different tiers.