Customer Success Story

Georgia Municipal Association

• Industry: Non-profit
• Products: VShell® for Windows and SecureFX®
• Summary: Using VShell triggers to automate site synchronization, and Virtual Directories and Access Controls for simple configuration, this Georgia non-profit provides its member municipalities with a flexible, low maintenance e-commerce solution.

Organization

Based in Atlanta, GA, the Georgia Municipal Association (GMA) is a voluntary, non-profit organization that provides legislative advocacy and educational, employee benefit, and technical consulting services to its members.

GMA's membership includes over 485 municipal governments, accounting for more than 99 percent of the state's municipal population.

Challenge

GMA's mission is to provide leadership, tools, and services to assist Georgia's local governments in becoming more innovative, effective, and responsive. With municipal governments under increasing pressure to provide or improve online services, GMA saw an opportunity to provide its members with a secure, affordable, and easy-to-manage e-commerce solution.

"We leveraged the trigger feature in VShell to load our import engine to upload files in real time into our production system. I've written a shell script that works great in conjunction with VShell's triggers. Because of the triggers I'm able to update our SQL database in almost real time after a city updates their data." – Jeramie Mercker, Software Architect/Developer

A GMA-hosted e-commerce platform would allow members to offer their residents a variety of online services, from viewing property tax information and paying their utility and tax bills to purchasing tickets to community events and entertainment.

GMA's requirements included:

• Strong, industry-standard encryption
• Authentication to Windows 2000 using usernames and passwords
• Cost effectiveness (not at the expense of reliability or support)
• Able to be programmed to do real-time updates (with the ability to get between GMA code and the vendors)

GMA was able to resolve the issue of handling municipal financial transactions, but needed a secure way to synchronize orders and changes with the municipalities' main databases. GMA also wanted a solution that could automate certain routine functions, reducing the time required for basic system administration.

Solution

GMA selected VanDyke's VShell server for Windows and SecureFX (or other Secure Shell file transfer applications) on the client side. "We looked at other Secure Shell solutions, but none of them seemed to provide the programmability that VShell does with triggers," said Jeramie Mercker, a GMA software architect/developer.

"As part of the solution, we developed a custom import engine written in Visual Basic and implemented as a DLL hosted in COM+," said Mercker. "File specification mappings are stored in a SQLServer 2000 database and give us the capability to be very flexible in the file layouts/types that we can accept from our customers."

VShell is used to synchronize the cities' databases with the e-commerce solution's MS SQL databases. "We leveraged the trigger feature in VShell to load our import engine to upload files in real time into our production system," said Mercker. "I've written a shell script that works great in conjunction with VShell's triggers. Because of the triggers I'm able to update our SQL database in almost real time after a city updates their data."

"I didn't want to be polling the directory every half hour or so," said Mercker. "When a file is uploaded, the "[VShell] triggers basically run one Visual Basic script. The script then passes the parameters of the trigger on to a specialized COM DLL that puts everything through Microsoft's Text Driver for ODBC to parse the data. The data is mapped to the table format necessary, imported, and then archived. It then sends an e-mail to the sender to let them know the import has taken place, how long the import took, and how many records of each data type were imported."

"In some cases, like Duluth, GA, they only need to update one way, since the residents only use the solution to view their property tax information. Other cities provide the ability to both view and pay utility bills and property taxes, and apply for licenses or permits, such as alcohol licenses. In these cases, [they] get data from two different databases, import it into their SQL database, then export it back to the source for updating nightly. In the case of alcohol licenses, that data gets shipped to yet another database. Nightly scheduled batch jobs at our customers' sites upload and download required files without user intervention."

Ease of use and the need for system administration resources was also a factor in selecting VShell. "We looked at a variety of open source and some high-end commercial solutions," said Mercker. "Some were out of our price range (starting at $15,000 for 50 users, and we could possibly have up to 500 cities), and also required PKI. In other words, I would have to install a digital certificate on each [city] site. Unless the person at the city is really technically savvy, it would almost guarantee a site visit and training. This way, we buy VShell (unlimited connections), and we require that the city buying the solution pick up the cost of the client (we recommend SecureFX). We provide sample batch files using SFXCL to do the automated transfers. Any modifications that are necessary can be done over the phone. It's easy for us, easy for them, and no site visit is necessary.

"It's been extremely reliable and I haven't had a single bit of down time," said Mercker. "I downloaded VShell and set it up within a day back in April [2004]. Because of the flexible way you handle groups in both VShell's Virtual Directory structure and its Access Controls, I haven't had to touch the configuration ever since. Your pre-sales staff was extremely helpful in walking me through a few things, but [VShell] really just worked. And now it is simple for me to add a new city when one comes on board — it only takes me about five minutes to set them up."

When GMA began planning the e-commerce project, they evaluated what level of security was required. Their decision was driven not only by regulatory requirements, but also by internal best practices. A review of the "Open Records Act" revealed that the information they were presenting was not technically private data. But addressing security up front fit into GMA's proactive approach.

"Even if the data wasn't 'private'," said Mercker, "we couldn't afford to risk compromising data integrity while the data was in transit during synchronization to and from the cities' and municipalities' databases." GMA also decided to be proactive and anticipate that privacy requirements could change. "Rather than having to go back and re-architect the whole system in the future, we decided to go with a secure system up front," said Mercker. "This organization is really good about security and contingency planning. We've not only got contingency planning for our IT, but also for phones and any business-critical function that we provide." VShell and SecureFX provided the encryption and data integrity GMA needed for their e-commerce platform. Physical security and contingency systems were handled by setting up duplicates of in-house machines and internet-facing machines at a co-location site.

"The combination of VanDyke's VShell and SecureFX was, by far, the most economically priced solution that we found on the market. The feature set of VanDyke products has enabled us to provide exactly the functionality we needed for our customers. The fact that VanDyke has a great pre-sales support team and that you can evaluate VanDyke's software not just free, but hassle free, was a big win," said Mercker.

"With VanDyke Software, I've always been able to speak with the right person and my problems always get solved," said Mercker. Most software vendors' tech support lines seem tasked with getting the customer off the phone instead of solving their problem. On numerous occasions, I have had exactly the opposite experience with VanDyke staff. They've all been genuinely interested in not only helping me solve my problem, but also in helping me have a better understanding of how the products work so that I can better manage them. I've never been rushed off the phone and I've never been given instructions to do something "just because". I'm also not forced to waste my time being quizzed by various levels of tech support just to get to the person that can solve my problem.

With VanDyke Software VShell server and SecureFX, GMA now provides its members with an e-commerce platform that is simple to set up and use. VShell triggers automate synchronization and ensure nightly updates; VShell's Virtual Directory structure and Access Controls simplify adding new member; and Secure Shell's strong encryption ensures security of municipality and resident data.

"We measure the success of this implementation using the same key factors that led us to select Vandyke Software: quality, reliability, and value for our investment. All have exceeded our expectations and have led to a very successful implementation," said Mercker.

Back to Top