Gulf Coast Seal

Global Distributor Avoids Cost, Risk While Upgrading Mission-Critical Data Warehouse

Gulf Coast Seal manufactures and globally distributes high-performance industrial sealing products. An upgrade of the company’s enterprise resource planning (ERP) software required significant changes to the data warehouse used to access ERP data. To reduce impact to the business, the changes had to be performed and deployed with minimal downtime. Two programmers upgraded the data warehouse in only eight weeks by relying on Microsoft® Visual Studio® 2005 Team Edition for Database Professionals. Because deployment required merely eight hours, the impact on operations was minimal. Now, database developers are even more productive, collaborative, and firmly in control of database change. With better control of the database development life cycle and source control, Gulf Coast Seal continues to reduce risk and cost.

Situation

Gulf Coast Seal is a leading worldwide manufacturer and distributor of high-performance sealing products and solutions. With headquarters in Houston, Texas, and offices in the United Kingdom, Gulf Coast Seal markets more than 50,000 seal and related products and is a leading distributor of Parker Seal Group products. The majority of Gulf Coast Seal customers are in the energy, oil, and gas industry.

The ability to work in Visual Studio almost exclusively instead of switching among … separate programs was probably the single biggest factor in our ability to meet the extremely tight deadline. Jeff Lynch, IT Manager, Gulf Coast Seal

Responsiveness to customers is the number one way that inside salespeople and other Gulf Coast Seal employees can provide superior service, encourage repeat business, and help the company stay competitive in the high-volume, low-margin distribution market.

Gulf Coast Seal enhanced employee responsiveness and customer service by developing a reporting data warehouse that enables employees to quickly and efficiently access and analyze the vast amounts of data stored in the legacy enterprise resource planning (ERP) system. Employees access data by using standard reports. Gulf Coast Seal also uses the data warehouse to automatically send weekly sales and inventory reports to major customers and suppliers. The company’s data warehouse relies on Microsoft® SQL Server™ 2005, SQL Server Integration Services (SSIS), and SQL Server Reporting Services.

Both the ERP system and the data warehouse are mission-critical for Gulf Coast Seal. Jeff Lynch, IT Manager at Gulf Coast Seal, states, “We serve customers in all time zones, and they contact us frequently to make changes to their orders—item, quantity, or delivery date. Our ERP system and data warehouse need to operate flawlessly and continuously.”

Gulf Coast Seal wanted to upgrade its ERP system to a newer version—a step that also required extensive changes to the extract, transform, and load (ETL) process, the reporting data warehouse structure, and all reports. The Gulf Coast Seal database development team needed to change approximately 450 of its 500 database and related objects, including tables, indexes, functions, stored procedures, SSIS packages, and report definitions. “All five hundred objects needed to be reviewed and tested before we deployed our revised data warehouse,” remarks Lynch.

Gulf Coast Seal needed to implement the data warehouse changes with minimal interruption or downtime. With limited IT resources, it didn’t appear that Gulf Coast Seal would be able to accomplish the needed changes in the eight weeks allotted.

Solution

Gulf Coast Seal found its solution in Microsoft Visual Studio® 2005 Team Edition for Database Professionals. Designed to manage database changes and improve software quality, Team Edition for Database Professionals brings the benefits of Microsoft Visual Studio Team System and life-cycle development to the database professional. Two in-house database programmers created the new reporting data warehouse in the scheduled eight weeks. The upgraded data warehouse consists of two new databases, scores of user functions and stored procedures, new SSIS packages, and new reports.

Development with Control
Visual Studio Team Edition for Database Professionals helped Gulf Coast Seal database developers overcome many challenges, the first of which was to find a way to develop new database schemas while maintaining the existing production data warehouse.

Database programmers used the Schema Import capability in Team Edition for Database Professionals to make an offline copy of the existing staging and reporting databases. This copy allowed them to establish an offline “sandbox” area in which to develop and test changes.

Team Edition for Database Professionals is revolutionary in that it enables database programmers to create an offline data store, thereby “decoupling” the programming

Figure 1. The Schema Compare Function

environment from the production or staging environments.

“Before Team Edition for Database Professionals, there really wasn’t a good way of working offline,” explains Lynch. “This is a huge step for database professionals. The sandbox is offline, but we can treat it like a real project because it behaves like a production environment.”

Programmers renamed objects in the database by using the built-in Rename Refactoring tool. The tool automatically renames references to the changed object and enables developers to preview the impact of changes before accepting them.

To script updates and to compare and synchronize the schema of two databases, programmers relied on the Schema Compare functionality, as shown in Figure 1. Programmers had the option to author tests in either Transact-SQL (T-SQL) or managed code. Team Edition for Database Professionals supports Gulf Coast Seal database developers with extensible unit tests and a new test editor targeted at database testing.

Testing and Deployment
The database developers used Team Edition for Database Professionals to review and test all of the database objects. Working almost exclusively within the Visual Studio 2005 development environment, the team reviewed, revised, tested, and deployed all the SSIS packages and SQL Server Reporting Services reports. They relied on SQL Server Management Studio Express to run specific SQL Server jobs and to set up SQL Server maintenance plans.

“Whenever we did a build, Team Edition for Database Professionals helped us ensure that all of our objects, tables, and the like, were correct,” comments Lynch. “Talk about a time-saver. It parsed everything just as if we were building a C# project. It told us if we had made any mistakes.”

By relying on a deployment feature in Team Edition for Database Professionals, the Gulf Coast Seal team completed the entire reporting data warehouse deployment and verification in less than eight hours, which kept data warehouse downtime to less than a day.

Lynch remarks, “When a few issues arose, we resolved them quickly using Team Edition for Database Professionals to compare our production databases with our sandbox databases. Problem solved.”

Benefits

By using Visual Studio Team Edition for Database Professionals, the database development team at Gulf Coast Seal has greater control of the database development life cycle. Programmers improve productivity, enhance collaboration, and help Gulf Coast Seal avoid or reduce risk and cost.

Better Control of Database Change
“Visual Studio Team Edition for Database Professionals gives us a truly revolutionary way to do database development, start to finish,” remarks Lynch.

Team Edition for Database Professionals enabled Gulf Coast Seal to support the business by keeping the existing data warehouse running while programmers worked with an offline version, which they managed as a source-controlled project. In this way, the team reduced risk for Gulf Coast Seal.
 
Tools in Visual Studio Team Edition for Database Professionals enabled database programmers to:

• Automatically cascade changes throughout the database schema in a controlled and consistent manner.
• Reduce the risk involved with changing database schema by comparing the source-controlled version against test and production systems and automating the creation of change scripts.
• Rename any object in the database and be assured that all references to that object would be renamed to correspond to the change.
• Create a build, test for bugs, and deploy the new data warehouse.

“There’s no better way to say it: The tools are phenomenal,” remarks Lynch. “Team Edition for Database Professionals gives database developers and administrators the power to control and manage database change.”

Lynch and his colleague worked with an offline copy of their production data warehouse—something their peers at publicly held companies could not have done due to the Sarbanes-Oxley Act of 2002. Fortunately, Team Edition for Database Professionals also provides a data-generation tool that lets database programmers quickly and easily generate meaningful data that can be used to test the database schema without compromising the security and privacy of production data.

Improved Collaboration, Communication, and Productivity
“In the past, our database development was done ad hoc using two or more software tools, and without the benefit of source control, unit testing, or any proper build procedure,” says Lynch.

As we … revise this system in the future, Team Foundation Server gives us the security and confidence of knowing exactly what we deployed … and allows for controlled, managed changes. Jeff Lynch, IT Manager, Gulf Coast Seal

Database developers appreciated the fact that Visual Studio Team Edition for Database Professionals gave them full access to the benefits of Microsoft Visual Studio 2005 Team Foundation Server. Team Foundation Server can be used in conjunction with Team Edition for Database Professionals. A shared team portal, integrated change management, common version control, and a shared data repository in Team Foundation Server helped Gulf Coast Seal database developers communicate important changes and collaborate by using work-item updates and event-based notifications.

While developing this data warehouse upgrade, programmers were highly productive because they developed and tested all databases, integration packages, and reports in Visual Studio.

“The ability to work in Visual Studio almost exclusively, instead of switching among two or three separate programs, was probably the single biggest factor in our ability to meet the extremely tight deadline,” says Lynch. “What’s more, we hit the ground running because we already had experience with Visual Studio 2005.”

Support for the Database Development Life Cycle
Team Foundation Server helped Gulf Coast Seal developers manage versions, track work items, and perform reporting using a single, secure collaboration platform.

“In the past, I would have hundreds of script files stored on a file system, generally not under source control,” comments Lynch. “Now, we have a secure, managed way to store the source code for our entire reporting data warehouse, including all database schemas, integration packages, report definitions, and deployment scripts and utilities. As we grow and revise this system in the future, Team Foundation Server gives us the security and confidence of knowing exactly what we deployed during this upgrade and allows for controlled, managed changes.”

Lynch also liked the way that Microsoft released Team Edition for Database Professionals. Through its Community Technology Preview, Microsoft sent out a new release almost monthly. “This was really a customer-driven release. Microsoft implemented almost every one of our suggestions into the product,” remarks Lynch.

Costs Avoided
“Visual Studio Team Edition for Database Professionals helped us avoid several days of downtime,” says Lynch. “That meant we could keep customers supplied and revenues coming in.”

The same database team expects to accomplish a similar feat in far less time for a separate data warehouse at the company’s Scotland facility. In the past, the team would have traveled to Scotland, but for the planned upgrade, they expect to use a virtual private network connection. “We figure that deploying the upgrade will take less than an hour,” enthuses Lynch.

Whether or not the team sets a record, they’ll certainly save Gulf Coast Seal money and avoid considerable downtime. Lynch explains, “For the Scotland upgrade, I expect that we’ll slash downtime from days to under an hour and avoid travel expenses altogether.”

Microsoft Visual Studio 2005
Microsoft Visual Studio 2005 is the world’s most popular development environment for designing, developing, and testing next-generation Windows®-based solutions and Web applications and services. By improving the development experience for Windows, the Web, mobile devices, and Microsoft Office, Visual Studio 2005 helps organizations deliver a variety of solutions more productively than ever before. Visual Studio Team System expands the product line with new software tools that enable greater communication and collaboration throughout the development life cycle. With Visual Studio 2005, businesses can deliver modern service-oriented solutions more efficiently.

 

For more information about Visual Studio 2005, go to:
msdn.microsoft.com/vstudio

 

For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:
www.microsoft.com

 

For more information about Gulf Coast Seal products and services, call (713) 910-7700 or visit the Web site at:
www.gulfcoastseal.com

© 2007 Microsoft Corporation. This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY