Database Migration Nets Manufacturer 100% Uptime and ROI in Six Months

As Western Digital prepared to produce new lines of hard drives at its Malaysia facility, the company knew the Enterprise class database used to manage production there couldn’t handle the increased load. The database already had caused production errors because of slow response times, and some failures resulted in significant production loss. To prevent further problems, the Malaysia facility switched to Microsoft Windows 2000 Advanced Server and SQL Server 2000 Enterprise Edition running on two Compaq ProLiant 8500 servers. The mission-critical database now handles 80 million peak transactions a day with ease. The company achieved a return on investment (ROI) in June 2003—six months after implementation. The increased level of performance and scalability has turned profit loss to profit gain.

Situation
Western Digital—founded in 1970 and based in Lake Forest, California—designs and manufacturers hard drives for PCs, Macintosh computers, gaming consoles, personal video recorders, audio/video jukeboxes, and set-top boxes for satellite and cable systems. The company also designs and manufactures external storage devices.

With 10,000 employees worldwide and U.S.$2.5 billion in annual sales, Western Digital maintains sales and support offices around the globe and manufacturing facilities in Malaysia and Thailand. The company’s Malaysia facility manufactures 70 percent of its product line, churning out more than 100,000 hard drives, PCBs and HeadStacks a day. This level of production represents about U.S.$3 million in daily revenue.

With increasing competition and tougher international market conditions plaguing the high-tech manufacturing sector, Western Digital must be ever vigilant about finding ways to improve production processes, set higher standards of product quality, and reduce operating costs. With its manufacturing facilities producing around the clock, the availability of its mission-critical operations cannot be compromised. Slow response times from the MITECS manufacturing execution system (MES) and its supporting database mean product errors. An outage or failure, even for one hour, equates to millions of dollars in profit loss.

Developed internally, supports about 6,000 scanning devices and more than 100 application host machines that manage and monitor the production-line process and flow at Western Digital’s Malaysia facility. During full production, the database accessed by MITECS must handle about 800 to 900 connections and more than 280 transactions per second. Simultaneously, the database must also feed information to an online, Online DNS-based reporting system—developed internally by Western Digital’s IT group and used to provide daily production reports for managers at the Malaysia facility—with minimal latency and impact on the overall production process. This is WesternDigital’s unique Web-based Digital-Nervous-System (WDNS ) which are sets of Real-time Dashboards used as Balanced-ScoreCards for daily management usage.

SQL Versus enterprise class database % Improvement Diagram

Manufacturing Process Leaves Ample Room for Error
As hard drives move through Western Digital’s MES, they are sent to the appropriate workstations (where assembly and testing take place) based on their serial numbers. The serial numbers equate to certain types of hard drives. For example, hard drives being manufactured for Microsoft® game consoles are sent to one set of workstations while those being manufactured for Macintosh computers go to another set of workstations.
Throughout this process, the MITECS application and supporting database scan the hard drives to ensure that they are at the right set of workstations. With each scan, the machines installing the necessary components wait for the database to confirm that the correct action is being taken before moving the hard drives to the next step of the process.
Because these hard drives move through the production line by the thousands simultaneously, these scans and responses must occur within seconds of one another.
According to Sin Chee Pang, Western Digital’s Senior IT Director for Asia, there is plenty of room for error within this process. “We are turning out hundreds of thousands of hard drives,a day, so the possibility of a mix-up can be quite high. However, no matter what’s the potential for errors, we can’t tolerate them. Any slowdown in the response time, any error, means we take a hit to our bottom line.”

MES Fails to Meet Business and Technical Performance Requirements
As Western Digital planned to add new lines of hard drives to its portfolio in early 2002, the company’s manufacturing facility in Malaysia had serious doubts about the ability of its existing database—an enterprise class database running on a UNIX platform—to bear the ramped-up production requirements. The enterprise class database, which the Malaysia facility had been using for five years, already showed signs of slowing. At times, CP says, the database took 9 to 10 seconds before responding to a single scan, which is about 7 to 8 seconds too long. Repeated attempts to isolate the cause and correct this lag time proved fruitless.

Waning technical support. Whenever Western Digital’s IT group contacted the enterprise class database’s help desk to report slow response times or other problems with its databases, including the two-day outage, it typically took a day or more to receive a response.

Manual database maintenance. Western Digital’s IT group found the enterprise class databases increasingly difficult to maintain, with few default settings or automated maintenance and monitoring commands available. Time spent conducting routine maintenance manually meant higher operating costs.

• Costly server maintenance and licensing fees. The servers used to support the MITECS application were IBM Sequent Numa-Q servers, which Western Digital found costly to maintain. Also, CP says, Western Digital’s cost to license the enterprise class database was 50 percent to 60 percent above some competing products including Microsoft® SQL Server™ 2000 Enterprise Edition.

Despite the slow response times, costly downtime, and other points of contention with the existing database environment, Western Digital remained a bit leery of moving away from an IBM environment and took a serious look at the capabilities of DB2. “Even though we had major problems, we were familiar with the environment,” CP says. “Historically, Western Digital had been an IBM house, so migrating to something different represented a stretch for us philosophically and technically.”

Performance in Thailand Puts Microsoft in the Lead
In 2001, Western Digital’s manufacturing facility in Thailand, which handles the remaining 25-30 percent the company’s hard drive production, switched from a similar MES infrastructure to the Microsoft Windows® 2000 Advanced Server operating system and SQL Server 2000 Enterprise Edition running on two Compaq ProLiant 8500 servers.

According to CP, the performance reports coming out of Thailand—40 million transactions handled daily without a single glitch—put Microsoft on the Malaysia facility’s radar screen. After thoroughly analyzing the efficiency gains in Thailand—40 percent to 70 percent over the old database environment—Western Digital decided in August 2002 to pursue a Microsoft-based solution in Malaysia.

Solution
Western Digital’s Malaysia facility needed a database solution that provided stability, speed, ease of use, and low maintenance costs. With new product lines coming out of development and scheduled for release, the Malaysia facility’s output would increase steadily and significantly over the next few years. Thus, the Microsoft-based solution needed to meet Western Digital’s current business targets as well as support plans for future growth.

This diagram provides an overview of Western Digital’s Windows and SQL Server solution implemented to redesign and enhance the facility’s MITECS manufacturing execution system.

Project Scope and Expectations
The Malaysia facility’s project scope involved redesigning the existing MITECS MES and its supporting database environment. As discussed earlier, MITECS supports about 6,000 scanning devices and more than 100 application host machines, which manage and monitor the production-line process and flow at Western Digital’s Malaysia facility.

During full production, the database accessed by MITECS must handle 800 to 900 connections and more than 280 transactions per second. The peak transaction rate for database connections is about 12,000 per minute. The database houses about 150 tables, with the largest table containing about 60 million rows. Simultaneously, the database must also feed information to the Web-based reporting system, which generates daily production reports for the facility’s managers.

The Malaysia facility’s managers expected the redesigned MITECS and its supporting database environment to handle these heavy transaction rates with zero lag time. At times, the facility’s enterprise class database took 9 to 10 seconds to respond to a single query from MITECS. With new production burdens bearing down, the new MES needed to reduce that time to no more than two seconds.

Other expectations for the project scope and resulting solution included:

• Compressed implementation time frame. From planning and testing to implementation, the Malaysia facility’s management expected the project to take only a few months. Further, because of the profit impact involved with halting production, Western Digital would authorize only 48 hours for moving the new database environment into production.
  
  
• Customized solution. Western Digital wanted a database solution designed specifically for its manufacturing execution system. According to CP, the facility’s IT group had always found the UNIX-supported enterprise class database environment a bulky fit, requiring manual and after-implementation customization, including the removal of large default tables and archives.

In terms of the nuts and bolts of its Microsoft-based database solution, Western Digital’s Malaysia facility decided to follow Thailand’s lead and use two Compaq ProLiant 8500 servers—each with eight Intel Pentium Xeon 733-MHz processors and 8 gigabytes (GB) of memory. The Compaq servers run the Microsoft Windows 2000 Advanced Server operating system and SQL Server 2000 Enterprise Edition. The solution also incorporates a Compaq EMA storage area network (SAN) with 400 GB of total storage capacity to bolster fault tolerance.

Western Digital uses both Compaq ProLiant 8500 servers for production, with each running two instances of SQL Server. The servers store data collected at all phases of the Malaysia facility’s production process, with quality checks and specification verification occurring at each stage.

An Active-Passive Database strategy was employed with two instances of the transactional Databases and two instances of the Business-Rules databases .
The MITECS application writes to both servers, ensuring data synchronicity. However, it only reads from one of the servers designated as the primary server. If one database fails, the MITECS application can be configured on the fly through the use of customized scripts written for Western Digital by Microsoft Consulting Services (MCS) to switch to the server still in operation. The redesigned MITECS application also uses built-in logic written in Microsoft Visual Basic® development system version 6.0 for detecting connection failures to either of the databases.

If a connection failure does occur, the MITECS application recovers any lost data through the use of the Compaq EMA SAN and replays all database transactions including document retrieval, insertion, and deletion.

Migrating from an Enterprise class Data Warehouse to SQL Server 2000
For reporting purposes, the Compaq ProLiant 8500 servers feed data continuously to a Web-based reporting data warehouse. The data warehouse contains 1 terabyte of raw data mirrored (RAID 0+1), which includes a variety of fixed and ad hoc reports related to Western Digital’s manufacturing processes, production dashboards, and production workstations.

There are 811 registered users, with about 150 concurrent users from both the Intranet and sites round the world that required such access. These concurrent users send queries to the data warehouse 24 hours a day, seven days a week, affecting more than 60 percent of the data within the warehouse simultaneously. Users access the data warehouse from across the enterprise. To populate the enterprise class data warehouse, Western Digital’s database solution uses the bulk copy program—a utility within SQL Server 2000 Enterprise Edition that enables and speeds the import or export of data between SQL Server and text file formats.

The Malaysia facility plans to move its enterprise class data warehouse to SQL Server 2000 Enterprise Edition by the end of 2003. This migration will allow the facility to conduct data mining, which will help Western Digital better manage information that affects its manufacturing processes as well as its customer service and relationship management functions.

Solution Implementation
From planning and testing to rollout, Western Digital’s database migration project took three months. Western Digital moved the new database environment into production during a one-day holiday on December 5, 2002. The next day thousands and thousands of hard drives moved through the Malaysia facility’s new manufacturing execution system and its Windows-based database environment with newfound speed and agility.

With only a 48-hour migration window approved by the Malaysia facility’s management, planning and testing proved crucial to the implementation’s success. The following section discusses the testing strategy used and the goals set by Western Digital and Microsoft Consulting Services.

POC Testing Strategy, Goals, and Results
After its initial assessment of the Malaysia facility’s existing MES as well as the facility’s business and production goals, Microsoft Consulting Services presented its concept of what the new database environment would potentially look like. In September 2002, two MCS consultants and one representative from the Malaysia facility’s IT group traveled to Microsoft headquarters in Redmond, Washington for a one-week proof-of-concept (POC) session.

The POC test team hoped to prove that Windows 2000 Advanced Server and SQL Server 2000 Enterprise Edition could handle the expected load of Western Digital’s production requirements while still providing fast application response times. Thus, the team focused on two metrics: system response times and system capacity.

To test these metrics, Western Digital and MCS set up a test lab, which mirrored as closely as possible the database environment they had envisioned for the Malaysia facility. One of the few exceptions: The lab used two four-way Dell servers with 8 GB of memory each instead of the Compaq ProLiant 8500 servers eventually used at the Malaysia facility.

In terms of system response times, the Western Digital and MCS POC test team set its goal at two seconds for each business process. After some fine-tuning to better match the actual queries performed by the MITECS application and supporting database, the system response times met or surpassed that goal.

In terms of system capacity, the lab’s server setup handled about three times the Malaysia facility’s current load. Based on testing scripts, the server used in the lab easily handled all database operations required for the production of 300,000 hard drives per day.

“If we had any doubts before the POC session, we were sold during the testing,” Chee Pang says. “As we moved toward migration, we now had a clearer picture of the benefits we could expect from a database solution built around Windows 2000 Advanced Server and SQL Server 2000 Enterprise Edition.”

Team Organization and Responsibilities
Once back in Malaysia, Western Digital and MCS set up a similar testing facility and organized teams to finalize the architecture design, plan the actual migration, and conduct database tuning and query optimization.

The main team’s composition and responsibilities included the following:

• Three MCS consultants charged with overall project management, database tuning, testing, and migration planning
  
  
• Two database administrators from Western Digital involved with project management, database tuning, and migration planning
  
  
• One system administrator from Western Digital’s IT group who assessed the facility’s hardware and overall infrastructure needs
  
  
• Two managers from Western Digital who provided the overall project supervision and approved various plans of action along the way.
  

A subteam of about 8 to 10 developers from Western Digital monitored and made any changes necessary to the MITECS application throughout testing and migration, ensuring seamless integration with Windows 2000 Advanced Server and SQL Server 2000 Enterprise Edition.

Implementation Challenges
Although Western Digital and MCS took great pains to plan each and every step of the Malaysia facility’s solution implementation, challenges always arise. Those faced by Western Digital’s solution implementation team centered on testing, the short migration time frame set by Western Digital’s management, and SQL Server database performance tuning.

Specifically in the area of testing, the Western Digital team found it challenging to formulate and develop a testing strategy and tool that could accurately simulate the Malaysia facility’s production rate of more than 100,000 hard drives per day. MCS worked with Western Digital’s IT group to develop comprehensive and customized testing scripts—written with Visual Basic—to do just that. These scripts were used throughout the development phase to identify problem areas and iron them out before migration, as opposed to moving the new database environment into production and then testing it for errors.

The compressed migration time also posed a significant challenge: developing a migration plan that was foolproof from start to finish. Data had to move from the enterprise class database within a tight window, giving the teams time to make any necessary corrections before the plant reopened. Western Digital’s IT group and MCS thoroughly tested several migration scenarios and approaches before developing the final migration strategy and plan. The teams rehearsed that plan over and over again to ensure a smooth switch.

Primarily trained on the enterprise class database development systems, Western Digital’s IT group had limited experience with and knowledge of SQL Server. Using a combination of the tuning wizard and query analyzer within SQL Server 2000 Enterprise Edition, MCS helped Western Digital’s IT group fine-tune its database queries and enhance the manufacturing execution system’s overall performance. MCS also hosted a variety of training sessions to cover best practices for writing stored procedures and applications in SQL Server, and reviewed Western Digital’s stored procedure codes and recommended various code changes designed to enhance the new database environment’s efficiency.

Benefits
Since rolling out its new Windows-based database environment in early December, Chee Pang says, Western Digital’s Malaysia facility hasn’t reported one glitch. Overall response times have, on average, improved 40 percent to 70 percent, with individual query interactions between the MITECS application and the SQL Server 2000 Enterprise Edition database taking only one to two seconds—even at peak production.

Based on these significant performance gains and the other benefits discussed below, Pang says Western Digital plans to replace the enterprise class database server running the Malaysia facility’s reporting system, switching to Microsoft Windows ServerTM 2003 Enterprise Edition for 64-bit Itanium-based systems and SQL Server 2000 Enterprise Edition (64-bit). This replacement should occur before the end of 2003.

Rapid ROI
To implement its new Microsoft-based manufacturing execution system, Western Digital made a software and licensing investment of about $380,000. In terms of hardware, it used two existing Compaq ProLiant 8500 servers and paid about $92,000 for the storage area network used to boost fault tolerance. Chee Pang says the company achieved a complete return on investment (ROI) in June—just six months after implementing the Microsoft-based database solution.

In terms of other cost savings, which also affect this rapid ROI, Pang says the facility’s switch from the more cumbersome and expensive IBM Sequent Numa-Q2000 servers to the two Compaq servers reduced its hardware maintenance by 80 percent. And, licensing fees for SQL Server 2000 Enterprise Edition are 50 percent to 60 percent lower than those for the enterprise class database.

Increased and Unmatched Scalability
Since ramping up production to meet the requirements of Western Digital’s new product line, the Malaysia facility’s database transactions can climb as high as 80 million per day, representing the most database transactions managed to date by SQL Server 2000 Enterprise Edition.

Despite the increased transaction capacity requirements, the Windows 2000 Advanced Server operating system, which supports the database environment, ensures that response times keep pace.

On average, the Malaysia facility reports a response time improvement of 40 percent to 70 percent. However, Chee Pang says, on those workstations that proved especially problematic for the UNIX platform and the enterprise class database because they required more queries than others, the Malaysia facility reports a 200 percent to 400 percent improvement.

Enhanced Maintenance Capabilities
During solution implementation, MCS helped Western Digital’s IT group fine-tune its database queries and enhance the manufacturing execution system’s overall performance.

As stated earlier, MCS used a combination of the tuning wizard and query analyzer within SQL Server 2000 Enterprise Edition. These and other tools within SQL Server also can be used to easily monitor and make configuration changes as the facility’s needs change moving forward. This knowledge now can be used to ease much of the maintenance burden experienced with the facility’s old MES environment.

Increased Profits
Without the profit loss associated with slow response times and system failure, Pang says Western Digital’s Malaysia and Thailand facilities are producing at all-time record highs. High levels of production without product errors means the company has turned profit loss into profit gain.

“We shipped nearly 10 million hard drives last quarter,” Chee Pang says. “Our new database solution built around Microsoft Windows 2000 Advanced Server and SQL Server 2000 moved those drives through production faster, with little waiting and no errors. That equates to tremendous business value.”