by Kathleen Wheatley

Huge amounts of information and frequent database changes are a way of life at Total Information Services Inc. (TISI), a leading pre-employment screening company.

Each day, customers log on to the TISI website to obtain credit reports, criminal records, and other background information on potential employees. And each day TISI database programmers must verify that the half-terabyte live production database is synchronized with the testing database.

These database changes used to take a couple of days of manual scripting and comparing code. Now they take about an hour.

"Previously, when we rolled from beta to production, I would spend a day or two making sure all of the structures were accurate and in place," says Philip Freeman, a development database programmer at TISI. "Now that we use SQL Compare, I can register and capture the databases in an hour or so."

SQL Compare is a simple database comparison tool from Red Gate Software that graphically displays differences in database structures. If the software finds a discrepancy between two environments, it can automatically generate a script or TISI developers can write and plug in their own.

An enormous amount of data

Having the ability to quickly compare environments and accurately incorporate changes is especially important to TISI because of the large number of unique datasets stored on the company's 17 SQL database servers. In addition to retaining all user login identification and product order information, TISI's website connects to separate, stand-alone programs and applications that retrieve specific information requested by an employer.

After obtaining security clearance, a customer can log in to the TISI website and choose an option from the product catalogue, such as verification of a potential employee's insurance information or social security number. In most cases, data is retrieved by a stand-alone program, but TISI also works in conjunction with government and other agencies to maintain on-site databases of personal information. Regardless of where the data is stored, TISI works to make sure customers can receive a full report on the entered request within a matter of seconds.

TISI's databases allow customers to keep complete records of exactly what information they have looked up and when. These search request reports can be listed by a number of factors, including the first or last name of a potential employee, a social security number, or the original entry date.

"The volume of data we deal with on a daily basis is enormous. It's not uncommon for us to have 20 million rows in a table," says Freeman. "SQL Compare graphically shows what is and is not there in our two environments. This clear comparison enables us to add just what is needed without dropping or re-creating entire tables when perhaps only a column is missing."

Maintaining accessible, accurate data

Efficiently making up-to-date information available in a timely manner is critical to TISI's business success, especially since, at any given moment, up to 2,000 customers could be trying to access the databases online.

"At TISI, we are replicating databases and continually moving information to production on a daily basis," he says. "In this constantly changing environment, our customers are relying on the accessibility and accuracy of the data, so it is imperative that both the testing and live environments are exactly alike. Because our databases are the central component and the key to our business, maintaining the integrity of the information and the data structures is our number one goal."

Here are some screenshots of Red Gate's tools in action at TISI.

1. Screenshot 1
2. Screenshot 2
3. Screenshot 3
4. Screenshot 4
5. Screenshot 5
6. Screenshot 6

Kathleen Wheatley (cramco@cramco.com) is a freelance writer specializing in IT and other technology topics. She works for Cramblitt & Company in Cary, N.C.