The City of Charlotte, North Carolina, wanted a quick, easy, and accurate way to update employee data across multiple software systems. It created a master employee database for this purpose and is upgrading that database to Microsoft SQL Server
2012. As a result, the City of Charlotte has been able to update applications five times faster—in one day instead of five—and linking them has become easier. The Microsoft solution has also tripled the overall database’s performance and has accelerated encryption
activity by a factor of 60.
Charlotte is the largest city in North Carolina, with a population of 731,424. It is the 17th largest city in the United States based on population and the second largest banking center in the country after New York City.
||With SQL Server 2012, we can keep data across all our systems synchronized and up-to-date, which makes it easier for the City of Charlotte to operate smoothly.
| Larry Charlton
SQL Server Database Administrator
City of Charlotte
Charlotte wanted to create a master database to track its 18,000 city associates, which include both employees and contractors. It not only wanted a comprehensive list of associate staff but a quick and easy way to update all of the city’s IT systems that contained
associate information. The process for making even minor changes was onerous and took a week or longer to update all of the city organization’s information systems.
For example, if associates got married and changed their last names, they had to make changes in a human resources application, an accounting application, the IT department’s Active Directory system, and so forth. It was a burden on the associates initiating
the changes and on the various departmental employees that had to implement the changes.
Additionally, “Because we didn’t have a central associate database, the data in all our systems was out of sync,” says Larry Charlton, SQL Server Database Administrator for the City of Charlotte. “Each system had to be reconciled independently. We wanted
to create a master version of the truth.”
In April 2011, the City of Charlotte used Master Data Services in the Microsoft SQL Server 2008 R2 data management software to create a central associate database. It already had about 1,200 SQL Server databases; using SQL Server 2008 R2 Master Data Services
provided a familiar technology and an easy way to create a central data source and to keep employee data synchronized across applications. However, because of issues with server-side architecture and the user interface, the central associate database never
performed and functioned as desired.
The City of Charlotte wanted to deliver on the promise of this database and salvage its investment, by reengineering it for faster performance. “We weren’t giving up on our goal of delivering real-time changes from source to destination systems,” Charlton
In October 2011, Charlton and his team learned of Microsoft SQL Server 2012, which provided a new version of Master Data Services, faster web services, and the new Microsoft Silverlight 5 browser plug-in, all of which would help with the performance issues.
After installing SQL Server 2012, the database team tested its associate database with the software. “The database performed substantially better on SQL Server 2012, and the user experience was also vastly improved, thanks to the new Silverlight interface
to Master Data Services,” Charlton says.
The City of Charlotte currently runs SQL Server 2012 Enterprise on a single virtual machine but will be moving it to an HP ProLiant BL460c G6 quad-core server computer with 8 gigabytes (GB) of RAM running the Windows Server 2008 R2 Enterprise operating system.
The associate database contains 18,000 records, or roughly 1.5 GB of data, and receives about 250 changes a day.
The city’s IT staff is looking to take further advantage of SQL Server 2012 to enhance the performance of other databases. For example, it has tested the SQL Server 2012 transparent data encryption feature with one database that previously used column-level
By upgrading to SQL Server 2012, the City of Charlotte will be able to keep all of its IT systems synchronized by updating them five times faster. Overall database performance is three times faster for the associate database and much higher for other applications
Updates Applications Five Times Faster and Keeps Systems Synchronized
It previously took a week to update all the organization’s IT systems, because the central associate database did not work properly. After upgrading to SQL Server 2012, the database works as it should and the user experience is much improved. With these
improvements, employees can implement changes to the associate database by the next business day.
“With SQL Server 2012, we can keep data across all our systems synchronized and up-to-date, which makes it easier for the City of Charlotte to operate smoothly,” Charlton says. “Once we get faster hardware, our goal is to implement real-time changes from
the source system to destination systems.”
Triples Database Performance
Charlotte has seen dramatic performance improvements with SQL Server 2012. Processing the 18,000 records of the associate database previously took 4.5 minutes. Now, with SQL Server 2012, they are processed in 1.5 minutes.
Data encryption has also greatly improved. With an earlier version of SQL Server using column-level encryption, encrypting 680,000 records required 18 hours. The same record load ran in just 18 minutes with SQL Server 2012 transparent data encryption—60
Screen changes also process much faster with the new Silverlight 5 interface for Master Data Services. “With SQL Server 2012, we should see a speedup across all of our operations,” Charlton says.
Links Applications More Easily
With the city’s systems synchronized, employees can easily produce multisystem reports—something that was not previously possible without a great deal of manual data integration. “All of our data is correct and available in all systems at all times,” Charlton
says. “We’re bringing in a new financial system soon and can now link this system with all our other systems, which is critical to the business.”
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.