Introduction
Three years ago, the South Carolina Department of Juvenile Justice (DJJ) made a critical decision: Faced with the limitations of its 18-year-old mainframe system hard-wired to 43 county offices, the DJJ began using SQL Server tools to develop a web-based system for tracking juvenile offenders.
Armed with a three-year, $1.8 million grant from the Federal government, the DJJ first shifted responsibility for computer technology from the Division of the State Chief Information Officer to a newly formed IT group.
Once its new IT operation was up and running, DJJ took a closer look at the old MIS mainframe and its application for tracking and managing juvenile cases and decided to bring those in house as well. The agency plans to retire the mainframe and convert the old application to a more intuitive system that can be easily and quickly accessed by the state's county offices over the Internet. That's proving to be a multi-year undertaking, but one that will have an enormous payoff for users.
"The new JJMS [Juvenile Justice Management System] will not only be faster than MIS, it will allow users to do things they would never be able to do before," says Frederick Volking, senior architect and project leader of the team responsible for the conversion from the mainframe. "Probably the most important improvement for the user is that it is no longer necessary to remember the thousands of codes required by the mainframe."
Four-digit codes used by the mainframe represent specific juvenile charges. One code might indicate that a child was charged with breaking and entering; another might signify breaking and entering with assault. Currently, these codes must be memorized or looked up in a large book, a time-consuming process.
With the new system, users will select from pull-down menus or use look-up tables. They will also be able to search for charges using a more complex set of parameters, such as "assault in which drugs are involved." This will increase data quality.
Migrating to SQL Server
Volking oversees eight developers, five of whom work at any given time. Two work full-time for the DJJ and six are independent contractors. The developers use Visual Studio .NET, SQL Server 2000, and Red Gate Software's SQL Bundle, which includes SQL Compare, SQL Data Compare and DTS Compare. These software tools enable the developers to work on their own machines at the component level and then migrate their code to the servers.
Volking's team manages 11 servers networked to desktop PCs. The first two, a SQL Server and a web server, handle development integration. The second group consists of one SQL Server and two web servers used for QA testing and user acceptance. The third grouping, for production, has two SQL Servers and two web servers. The final two servers are domain controllers.
Despite this new hardware arsenal, the MIS mainframe is still in the picture, at least until the conversion is completed. So each night the mainframe FTPs data files to the production, QA and integration servers.
"We have thousands and thousands of lines of Transact-SQL that are executed every evening," says Volking.
Error-free comparisons
The agency updates its databases every night and does a full refresh on Saturday using Red Gate's DTS Compare software. DTS Compare enables the team to quickly see the differences in configuration between the integration servers and the QA servers. The team can then create a script to promote the information to QA. The script continues running on QA until it is tested and the team decides it's time to promote it to production.
"It gets really involved," says Volking. "We're talking thousands and thousands of lines of code and probably a nesting of about 35 or 40 little DTS packages that do different things. Attempting that manually would probably be close to impossible. It would be so prone to errors that it would be crazy."
The development team relies on Red Gate's SQL Compare to verify that the schema of database objects looks exactly the same in integration and in QA.
"Do we have the same number of fields? Are they the same types of fields? Do we have exactly the same indexes? Foreign keys? Same constraints? These are the types of things we look for with SQL Compare," says Volking.
After the software compares the schema of the two databases, it indicates where, say, an index needs to be added. This enables DJJ's developers to operate in a totally independent, stand-alone environment and use SQL Compare and Visual Studio to do the integration.
"Sometimes you move all this schema up to the next promotion level and then something stops working and you can't figure out what happened," says Volking. "Why did the screen take 45 seconds to come up when it should take only two seconds?
"Without SQL Compare, you hunt and compare and it costs you hundreds of hours to identify where the problem is," says Volking. " I have done development for lots and lots of companies, and the ability to make sure that you don't forget to move a flag or forget to move an index is just gargantuan in payoff."
Third piece of the puzzle
Another big part of the conversion is the data itself. Not only does the agency want to preserve the information it has, it wants flexibility in the types of data that can be gathered in the future. To that end, the development team has already received numerous requests for changes from some specially selected county employees participating in the conversion as QA testers.
These testers are generally long-time employees who understand both the MIS system and the business processes behind it. The QA testers are uniquely suited to helping Volking and his team to develop JJMS so it will accommodate current needs as well as future improvements.
At the request of the QA testers, for example, the team created a table of "people types." This lookup table includes such data as the juvenile's relatives, counselor, parole officer, and the like. If during development the team is asked to add another people type called, say, "guardian but not legal," they add it at the desktop development level. But they must then ensure that this content migrates up to all of the servers.
That is easily accomplished using the third module in the SQL Bundle, called SQL Data Compare. The software lets the development team create a project that contains rules for comparing and migrating data up through the server hierarchy. One project the team created specifies that whenever two databases are compared, SQL Data Compare must compare both the data tables and the data within the lookup tables.
"SQL Bundle has now helped us address all three primary issues necessary to move development from a PC all the way up to production and guarantee reproducibility," Volking says.
Volking estimates that SQL Bundle has decreased his total manpower needs by about 20 percent, admitting that if people were perfect that might not be the case.
"A single programmer mistake can cost literally hundreds of hours to try to uncover and repair," he says.
Put to the test
The development team will put SQL Bundle to the test again after the county offices are up and running with JJMS. Later phases of the project call for integrating the solicitors' offices (known as district attorneys in other states), the judges' offices, the probation and parole offices, the jails, detention centers, and on down the line of county services.
These offices never had access to MIS, so many developed their own tracking databases. The multi-year JJMS plan calls for integrating these small databases, enabling all JJMS users to instantly see and understand the relationship between, say, a fairly serious charge and the reason the judge only recommended probation. It could even show that two juvenile offenders who have fought in the past are scheduled for the same detention center.
In Volking's vision, JJMS will become an interactive tool rather than simply a tracking system. It will allow everyone in the juvenile justice system to determine if a child requires treatment, punishment or probation, depending on his or her unique circumstances and prior history.
"MIS has always been a necessary evil of doing our business," says Volking. "JJMS will, for the first time, become a tool for doing our business. This difference is huge."
To find out how well the SQL Bundle will work for you, download a fully-functional, free trial from Red Gate's website.
Redgate makes ingeniously simple software used by 804,745 IT professionals and counting, and is the leading Microsoft SQL Server tools vendor. Our philosophy is to design highly usable, reliable tools which elegantly solve the problems developers and DBAs face every day, and help them adopt database DevOps. As a result, more than 100,000 companies use products in the Redgate SQL Toolbelt, including 91% of those in the Fortune 100.
Submit an article or tip
