| Downloads |
|---|
| 48476.zip |
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.
First Runner-Up
Gilles Despaties,
Senior Database Administrator
Canadian House of Commons
Extending SQL Server With a KISS
Gilles Despaties, senior DBA for the Canadian House of Commons, is part of a group of software developers. In his DBA job with the Canadian House of Commons, Gilles gets to enjoy problem solving quite a bit. His department supports 3000 end users working with more than 400 databases of various sizes on more than 40 servers. Echoing a scenario that's common in many organizations, his department recently decided that it wanted to keep a tighter rein on database security. The group had followed Microsoft's security recommendation and enabled SQL Server failed login auditing on all their SQL Servers. But the group soon discovered that monitoring alerting on more than 40 servers was time-consuming and impractical. In addition, the group wanted to implement more detailed failed-login tracking than Microsoft's built-in solution allowed.
Specifically, the group needed to track when a high number of failed logins occurred within a certain time frame and find out where they came from. The group had identified a certain level of tolerance of failed user login attempts. But failed login auditing wouldn't let the group automatically execute a procedure when that level of tolerance was exceeded. A firm believer in the KISS principle (Keep It Simple, Stupid) Gilles decided that, in this case, the simpler the solution, the better. He extended the capability of failed login auditing by combining the tool's built-in functionality with a simple T-SQL stored procedure. First, he enabled a failed-login alert that launches a SQL Server Agent job. The job executes the stored procedure that Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 48476) shows. This stored procedure retrieves the alert information and analyzes it according to parameters that define the tolerance level for the specified time frame.
If the analyzed values meet the specified criteria, an email message is sent to the DBA teams, and the DBA on call is paged to take whatever action is needed.
The best part of Gilles's solution is that the stored procedure can be customized for individual servers according to the needs of different groups. Each group that his department serves can define its own parameters for the stored procedure, and monitoring is kept to a minimum. Gilles used T-SQL instead of Visual Basic (VB) or C# so that anyone on his team can modify the T-SQL stored procedure on the fly. Using a stored procedure prevents direct data access, so anyone who needs to work with the code or change its parameters can do so—without compromising security.
In addition, Gilles used SQL Server Profiler to set up a server-side trace that captures the failed logins, thus storing as much information as possible about the source of the failed logins.The complete solution has several direct benefits. Aside from protecting the environment from brute-force attack, the solution tracked down several application login errors at implementation time and identified users who had forgotten their SQL-authentication password or who were trying to connect to a server they weren't authorized to access.
For Gilles, the hardest part of devising the solution was finding the right sequence of events and deciding how he wanted the stored procedure to fire. After he knew what he wanted, writing the procedure took very little time. Gilles encourages other DBAs to try similarly simple approaches to their SQL Server problems. "It's a matter of taking the time to really look at your problem. Your solution doesn't have to be complex to work effectively."
Second Runner-Up
Kevin Terry,
DBA
Problem-Knowledge Coupler (PKC) Corporation
Teaching DTS a New Trick
"There's a certain satisfaction in trying something that you don't know will work," says Innovator Award runner-up Kevin Terry of his winning solution. Kevin's solution uses a common SQL Server tool—DTS—to solve an uncommon problem—moving data from Microsoft Outlook into SQL Server.
The problem arose when Kevin's company, PKC, needed to move data contained in custom Outlook forms to a Web site so that customers could use the data. PKC is a software development company whose main product is medical guidance software called Problem-Knowledge Couplers. Companies that subscribe to the software let their employees access it through the Web or an in-house installation. Employees use the software to search for information about medical problems they might be having. But the results they get aren't generic. Couplers use data that users provide to intelligently match the symptoms with personalized medical information. "The program asks you all sorts of questions about your symptoms," Kevin explains. "Then it cross-references those symptoms with current medical literature. It's designed to give you some idea of what's going on and help you search for treatment options."
Companies can subscribe to all Couplers that PKC offers or select certain Couplers that are of particular interest to their employees. For example, if a company is sponsoring a stop-smoking campaign, it can subscribe specifically to the Coupler containing smoking-cessation information. The SQL Server system that Kevin administers serves as a place to store a list of available Couplers, but the descriptions of the Couplers were stored in Outlook forms. Kevin needed to get the descriptions into SQL Server so that he could put the complete Couplers list—including descriptions—on PKC's Web site so that customers could see their options.
Kevin faced the prospect of manually entering all the descriptions into a SQL Server database. But manual entry opened the possibility of introducing errors into the data. In seeking alternatives to manual entry, Kevin found a driver that he could use to directly access Microsoft Exchange as a linked server, but it worked only when SQL Server was on the same machine as Exchange, which wasn't the case in his situation.
Kevin's search for a better transfer method took him back to SQL Server. He wanted to use SQL Server's own DTS tool if possible because "that's where the data was going to end up anyway." In addition, Kevin really wanted to take advantage of DTS's job-scheduling and tracking capabilities, which would give him more control over job execution and let him watch for problems during the transfer. He'd seen lots of information about using DTS to move data from SQL Server to Outlook, but not much information about moving data from Outlook to SQL Server. Still, he decided to give it a try.
Kevin says he spent a few days "flailing about trying to see if the solution was even possible." With advice from PKC's resident Outlook expert, Chris Weed, about how to use scripting with Outlook, Kevin created an ActiveX task that lets DTS move the descriptions of the Couplers from Outlook forms to a working table, and from there to SQL Server. (To see the scripts that Kevin used for this solution, you can read his article "Coupling Outlook to SQL Server" at http://www.sqlservercentral.com/columnists/kterry/couplingoutlooktosqlserver.asp.)
The completed DTS package requires only three steps, as Web Figure 1 shows. Kevin warns that the more cleansing and manipulation you have to do with your data, the more steps you'll need. However, he says he thinks other DBAs can certainly adapt his solution if they need to move such information as email addresses, email text, contacts, or appointments from Outlook to SQL Server.
Kevin learned from creating this solution not to underestimate SQL Server's capabilities."SQL Server comes with a lot of powerful stuff," he says. "Before you look outside it, see if what you need can be done with what comes in SQL Server."
Dawn Cyr ([email protected]) is a senior editor for SQL Server Magazine.
Anne Grubb ([email protected]) is a senior editor for Windows IT Pro. She has more than 20 years of experience as a writer and editor of articles, books, and other materials in the computer, business, and legal fields.
