SQL Server professionals are a creative bunch. Daily, DBAs and developers wrestle with a multitude of challenges as they strive to keep data clean and transactions humming, construct queries that give users exactly the data they need, or write applications to improve business processes. We know that the database pros who are our readers have devised innovative solutions to meet such challenges. That's why SQL Server Magazine is recognizing the creators of exceptional SQL Server solutions in our fourth annual SQL Server Magazine Innovator Awards. This year, the judges awarded one grand prize and two runners-up awards to the creators of the most outstanding solutions among the entries received. The winning DBAs and developers all have one thing in common: They use SQL Server technology in creative ways to reap tangible benefits for their organizations. We hope their winning solutions will inspire your own SQL Server innovations!
Grand Prize
Kevin Manley
Lead Software Developer
Arts Alliance Media
Open-Source & SQL Server
A WINNING MIX
Cost is a hurdle for any application development project. A business-identifies an application need, but translating that need into working code costs money. When Kevin Manley signed on to develop a complex set of SQL Server 2000-based applications for City Screen, the largest independent cinema chain in the United Kingdom, he knew he'd need to find a way to enable his three-person team of developers to build the applications as efficiently and economically as possible. "The cinema business is a low-margin business, and cost is always a sensitive issue," Kevin says.
Defining Requirements
City Screen partnered with Kevin's employer, London-based Arts Alliance Media, to develop a two-part application. The first part, the back-office component, would run on a central server at City Screen's main office, providing management with up-to-the-minute data, such as ticket and concession sales at individual cinemas, information on which films each cinema booked, film distributors' booking rates and current rental agreements. The second part, a point of sale (POS) system, would run on over 120 touch-screen terminals at City Screen's 20 cinemas throughout the UK.
As architect and lead developer for the project, Kevin worked closely with City Screen staff for 6 months to nail down application requirements. The most crucial requirement was that the application had to run 24 × 7 with no downtime. "The system had to be very robust against failure," Kevin says. "You can't have a situation where you're unable to sell tickets or concessions at individual cinemas." In addition, City Screen wanted the application to centralize management of individual cinemas and feed relevant information (e.g., movie schedule changes) to specific cinemas.
Choosing a Language
The heart of the application would be its secure and foolproof data-replication component for moving data between the cinemas and the central office, and Kevin knew that coding this component would be tricky. He investigated using SQL Server's native replication capability but ultimately chose to develop the replication code—and the rest of the application—from scratch, using the open-source Python language. "Basically, I've always been a Microsoft and C++ developer. I'd never used Python before," Kevin says. "But I knew that I needed a high-productivity language because I had a small team, and we already had a huge amount of work." Kevin says that he chose Python because "it seemed to be the most mature of the open-source choices. In Python, it's so easy to express complicated logic in a small amount of code, and Python code is easy to maintain and read. Doing a lot with little code appealed to me."
Kevin had a secondary motive for selecting an open-source language to develop a SQL Server database application: portability. "Although SQL Server is a great database, we wanted to have the freedom to switch to another database later if we had to," especially a less-expensive, nonproprietary alternative, Kevin says. "I don't think there's an open-source database good enough to replace SQL Server right now, though."
Coding Tricks
Two-way replication between the central-office server, which houses SQL Server databases, and individual cinema clients is the core of the application. Replication from the central office to the individual cinemas happens once a day or on demand, whereas replication of transaction data from the cinemas back to the central office occurs about once a minute.
Kevin and his team built the back-office component first, over a period of about a year. This component is a multithreaded Web-server application that communicates with a SQL Server 2000 Enterprise Edition database and has a Web-browser front end. Before writing any actual code, Kevin decided to create an XML file to contain schema specifications for the application (e.g., tables, columns, indexes), instead of coding the Data Definition Language (DDL) by using SQL Server syntax. "We wrote Python code that reads the XML description and spits out all the DDL for SQL Server," Kevin says.
Storing the database schema in an XML file gave the developers greater flexibility in coding. "Having a file that expresses all the relationships between all the tables let us do some interesting things, such as topological sorting, which is really important in replication," he says. "For example, if you're replicating two tables that are related to each other, perhaps through a foreign-key relationship, you need to make sure that you replicate the table that's referenced before you replicate the one referencing it," he explains. "You don't really get this deep understanding of how data is related to other data when you use stock SQL Server with stock replication. Because we've got this system that really understands the data, we've been able to develop replication that does things in the right order and somewhat automatically." After completing the back-office application, Kevin and staff tackled the second part, which took 6 months to develop and roll out.This component is a multithreaded XML-remote procedure call (RPC) server that runs on a system at each cinema and communicates with an instance of Microsoft SQL Server Desktop Engine (MSDE). A front-end GUI, also written in Python using the wxPython windowing toolkit, communicates with the XML-RPC server and provides a POS touch-screen UI.The cinema servers can function even if the central server is down. "We used a robust application-level replication protocol to keep the individual cinema databases synchronized with the central SQL Server database securely over the Internet," Kevin says.
Big Benefits
Kevin reports that City Screen management is pleased with the system. "They think it's pretty incredible." The before-and-after picture dramatically shows how the new system has benefited City Screen. Previously, cinema managers had no way of knowing how their cinema was performing compared with others. And the central office had to manually enter totals from individual cinemas' weekly financial reports into an accounting package, then generate company sales reports from that data.
Now cinema managers get timely information about movie schedules and sales figures via an easy-to-use Web UI. City Screen executives can see detailed statistics for the entire business as well as each individual cinema. In addition, Kevin says, the system lets City Screen's booking agents see what prices other agents have negotiated with distributors and use that to get the best terms.
Kevin's solution highlights his willingness to use tools and development methods that some SQL Server developers might consider nontraditional. Sometimes, though, stretching boundaries is a hallmark of innovation. By giving priority to meeting City Screen's needs over being wedded to certain tools and methodologies, Kevin developed an application that's innovative because of its coding techniques and its complex, yet reliable, replication capability, creating a solution that's state-of-the-art in the UK cinema industry.
See Associated Figure