As second largest container ship line in the world, Mediterranean Shipping Company (MSC) relies on its 5-terabyte database that tracks more than 15 billion transactions a year. With annual growth of 30 percent and an ever-more data requests from its worldwide customers, MSC needed an enterprise solution that would scale with its growth, simplify database administration, and help ensure high availability. The company upgraded its database to Microsoft® SQL Server™ 2005 Service Pack 1 running on Microsoft Windows Server™ 2003 Enterprise Edition on a Unisys ES7000 platform. MSC has found that the move from SQL Server 2000 to SQL Server 2005 has increased its agility to swiftly respond to customer needs, simplified database administration, provided faster query responses, and given it an even better development platform. SQL Server 2005 Database Mirroring provides high availability.
Situation
Mediterranean Shipping Company is the second largest container ship line in the world, with offices or agents in more than 200 countries, and with some 40,000 employees worldwide. The company’s fleet of 270 ships calls on some 250 ports, moving more than seven million full containers each year.
The company’s customer list reads like a guide to international commerce and features many Fortune 100 companies.
 |
SQL Server Integration Services is a very important enhancement for SQL Server 2005. It enables us to program in a much more natural way to move data in and out of the system.” |
 |
|
Bob Erickson, Executive Vice President for Software Development, Interlink (a fully owned subsidiary of Mediterranean Shipping Company) |
|
|
Based in Geneva, Switzerland, Mediterranean Shipping Company (MSC) has three main data centers—in Geneva, Singapore, and Warren, New Jersey, in the United States. Company-wide, MSC manages more than 5 terabytes of data.
Several years ago, the company moved from an IBM DB2 database to Microsoft® SQL Server™ version 4.2.1, as it launched its internally developed application, MSCLink, to track all aspects of its shipping business. Over the years the company has steadily upgraded its SQL Server database and was using SQL Server 2000 running on the Microsoft Windows Server™ 2003 Enterprise Edition operating system. (Windows Server 2003 and SQL Server are part of Microsoft Windows Server System™ integrated server software.) SQL Server now stores more than 90 percent of the company’s data.
The company has an ever-growing demand for information technology to keep up with its rapid growth, as well as with the increasing demands of customers who seek end-to-end tracking of shipped goods.
“For Mediterranean Shipping Company, and all other carriers, the global demands of shipping are always pressing,” says Bob Erickson, Executive Vice President for Software Development at Interlink, a fully owned subsidiary of Mediterranean Shipping Company. “In the United States alone, we move 1,200 inland containers per day. Even though our business is based on ocean shipping, we increasingly provide door-to-door service to meet customer needs. Looking ahead to growth in the number of containers moved and the need for more exacting data for customers, we know that our information systems have to scale up, for us to remain competitive.”
System scalability is especially important to MSC, because it has enjoyed annual growth of about 30 percent in recent years and expects the growth to continue into the future. “With 30 percent annual growth, our biggest challenge is to ensure that we can continue to provide services to our customers that are superior to that of our competitors,” observes Fabio Catassi, Chief Technical Officer at Mediterranean Shipping Company. “We have been very successful in doing so, and need to retain and build upon this tradition.”
MSC executives were eager to upgrade to SQL Server 2005, the Microsoft next-generation data management and analysis platform, to meet MSC's needs for a database solution that provided:
- Capabilities to support rapid changes within the industry.
- Ease of data management.
- Enterprise-class scalability to support the company’s rapid growth.
- An efficient development platform.
Solution
In 2005, Mediterranean Shipping Company upgraded its database infrastructure supporting its MSCLink application to SQL Server 2005, and in 2006 installed SQL Server 2005 Service Pack 1, in part to take advantage of SQL Server 2005 Database Mirroring. The company, working with its partner Interlink also used SQL Server 2005 to launch a new Web-based portal, called VWA (Vendors Web Access), for creating a direct link to its main US vendors. MSCLink and vwa.msc.us are based on a multitier architecture that includes:
- Web Portal Tier. VWA provides a browser-based version of the MSCLink application and connectivity to the SQL Server 2005 database so that vendors can insert tracking data about shipments over the Internet, instead of relying on fax machines or the telephone. VWA was created using the Microsoft Visual Studio® 2005 development system and the Microsoft ASP.NET application framework, part of the Microsoft .NET Framework version 2.0.
- Client Tier. Client software was developed with the Microsoft Visual C++® development system and upgraded to Microsoft Visual Studio 2005. The client software employs Microsoft Message Queuing, a technology in the Microsoft Windows Server 2003 operating system that supports asynchronous message queues. Message Queuing communicates with the application tier. When the client tier receives a request for a booking, for example, it passes the request to the application tier for execution, and returns back the result. The client software is deployed in more than 70 cities, covering every continent of the world.
 |
| Solution architecture for the MSCLink application. |
- Application Tier. The application tier, written with Visual Studio C++ and SQL Server 2005 stored procedures, is a set of modules including: bookings, contracts, equipment control and trucking management, import operations, billing, electronic data interchange, reporting, e-commerce through VWA, and auditing. The MSCLink application includes some 1,000,000 lines of SQL Server stored procedure code. The Microsoft ADO.NET application framework from the Microsoft .NET Framework provides for database connectivity. The application tier makes use of Microsoft Network Distributed Transaction Coordinator. For load balancing, four servers at the Warren, New Jersey, data center host the application tier. Additional middle-tier servers are used throughout the rest of MSC’s global operations.
- Data Tier. MSC has some 5 terabytes of information stored on multiple instances of SQL Server across its global operations. The largest is a single 0.8-terabyte instance at the Warren data center. The data tier has 1,934 tables and 4,959 stored procedures. The system supports more than 50 million database transactions per day. The data tier is hosted on 18 Unisys ES7000/520 servers, each with eight Intel Xeon processors and 8 gigabytes (GB) of RAM and 2 Unisys ES7001/600 with 8 Intel Dual Core 64-bit CPUs and 32 GB of RAM. The servers are configured as two-node active/passive clusters. SQL Server 2005 handles more than 15 billion transactions per year. Copies of the core data synchronize between the ES7000 server clusters at the global data centers.
MSC synchronizes its databases across its major data centers with Microsoft Message Queuing technology, which enables applications running at different times to communicate across heterogeneous networks and systems, providing reliable message delivery, efficient routing, security, and priority-based messaging.
- Mirrored Database. MSC uses the Database Mirroring feature of SQL Server 2005 to provide high availability of data. Database mirroring transfers transaction log records directly from one server to another and can quickly fail over to the standby server, with no loss of committed data.
- Reporting Tier. MSC management plans to deploy SQL Server 2005 Reporting Services to streamline reporting.
- Analysis Tier. MSC IT staff is building a data warehouse, set for deployment at the end of 2006. SQL Server 2005 Analysis Services supports multidimensional reporting in the MSC solution.
Benefits
Upgrading to SQL Server 2005 has provided Mediterranean Shipping Company with a number of benefits, including the agility to swiftly respond to customer needs, easy database administration, enterprise-class performance, high availability, and an integrated development platform.
Agility to Swiftly Respond to Customer Needs
MSC management values the agility that SQL Server 2005 provides the company as MSC builds upon its reputation of meeting customer needs. “MSC is a global company, and more and more of our customers are becoming global in their operations,” says
|
Our users are seeing better response times with SQL Server 2005. They are cheering about the enhanced speed, even in cases where we haven’t altered the code. We immediately saw increased performance, from the first moment SQL Server 2005 was installed. |
|
|
Brian LaManna,
Vice President of Software Development, Interlink |
|
|
Catassi. “With SQL Server 2005 supporting our MSCLink and VWA, our customers and vendors have a single point of access—no matter in which geographical market they operate—for getting in contact with us, doing business with us, and taking advantage of our unified worldwide services. SQL Server 2005 gives us the flexibility to respond to any of their requests. We’ve never had a situation when a customer asked for something that we were unable to deliver.”
The agility that MSC gains from SQL Server 2005 is especially important because things change quickly in the global shipping business. The agility, which is part of the company’s corporate culture, is enabled in part by SQL Server 2005 and from Microsoft Visual Studio 2005, which the company uses for its application development environment.
“The biggest challenge we face is trying to stay ahead of all the business requirements that are changing in the global shipping area,” says Erickson. “We constantly get new requests that have to be implemented very rapidly. We try to respond very quickly, giving our customers and our people the tools that they need to be more efficient and more effective. We work together globally to come up with solutions. There’s an attitude that we can make things happen and we can accomplish our work quickly.”
Erickson points to an example of how quickly the company, using SQL Server and Visual Studio, can adapt to the need for rapid change. “A few years ago, the United States Department of Homeland Security changed the regulations of how imports were coming into the country,” Erickson recalls. “The new regulations impacted shipping worldwide, and we had only a few months to comply. We were able to do this because of the great employees we have throughout our company, and because of the flexibility that we enjoy in our IT infrastructure through SQL Server and Microsoft development tools.”
The company has gained IT agility by using Microsoft operating systems and products throughout its organization. “We have been a Microsoft shop for years,” says Catassi. “Basically, we bet that Microsoft could help us to meet our business needs by providing such a complete suite of integrated products. In addition to SQL Server, and Microsoft development tools, we use the Microsoft Exchange Server [messaging and collaboration server] to support e-mail, and Microsoft Office [Professional Enterprise Edition 2003] running on the [Microsoft] Windows® XP operating system for our desktop computers. The entire company is run on Microsoft solutions. We believe that this has made it easier for us to respond whenever there is an urgent need for a change."
Easy Database Administration
MSC IT staff appreciates the easy database administration experienced in working with SQL Server 2005. Moreover, the SQL Server 2005 Management Studio—a new integrated suite of management tools used for developing, deploying, and troubleshooting SQL Server databases—provides MSC developers a significant boost in their productivity.
“We feel that SQL Server has long held an edge over competing products in terms of being very easy to deploy, monitor, and customize,” says Erickson. “SQL Server 2005 builds upon this tradition and provides an even more powerful set of management tools.”
Some of the new SQL Server 2005 features that MSC development staff found to be especially helpful include:
- Monitoring Tools. “Dynamic Management View and the other monitoring tools that are built into SQL Server 2005, with its combination of PerfMon plus SQL Trace, gets us very quickly to wherever there is a performance problem,” says Erickson. “The ability to have SQL Trace running with PerfMon helps us to see exactly what’s going on, to see where the bottlenecks are, and solve the problem. We can now locate a problem within minutes that in the past would have taken us hours or days to identify.”
- Query Optimizer. “We’re extremely happy with SQL Server 2005 Query Optimizer performance,” says Erickson. “We have close to 1,000,000 lines of stored procedure code. We’ve found that the Query Optimizer in SQL Server 2005 does a much better job of creating query plans and provides better performance than the previous version.”
- Profiler. “The SQL Server Profiler is a great audit tool. It's especially valuable to us because we can use it to perform a side-by-side profile to compare what is happening on SQL Server with what is happening on a second computer,” reports Brian LaManna, Vice President of Software Development at Interlink. “This lets us see, for any point in time, exactly what was happening on our Web box, for example, and what was happening with SQL Server on the back end.”
- SQL Server Integration Services. The company has found database management to be easier with SQL Server Integration Services (SSIS). “SQL Server Integration Services is a very important enhancement for SQL Server 2005,” says Erickson. “It enables us to program in a much more natural way to move data in and out of the system.”
Enterprise-Class Performance
With MSC’s rapid growth and global operations, the company needed a database that was enterprise-ready. This is exactly what MSC has found with SQL Server 2005: the robustness and scalability that it required.
|
SQL Server 2005 gives us the flexibility to respond to any of their requests. We’ve never had a situation when a customer asked for something that we were unable to deliver. |
|
|
Fabio Catassi,
Chief Technical Officer, Mediterranean Shipping Company |
|
|
“MSCLink is an application that must be available without fail—24 hours-a-day, seven days each week, and 365 days a year,” says Catassi. “With SQL Server 2005 we’ve enjoyed 99.999 percent availability because of its faultless performance and our ability to reduce planned downtime.”*
High availability is important to MSC because MSCLink is so central to its operations. “We have in fact more than 20,000 users—between MSCLink and the other systems like the container tracking system—that are based on SQL Server,” says Catassi. “That’s the nature of our operations. Our ships never stop. Therefore, there must be a system that is able to support them around the clock. Every time a container is loaded anywhere around the world, for example, if it is coming to the United States, we have to send an electronic message to the Department of Homeland Security in the United States. Our compliance with this requirement is assured through constant access to our major data centers running SQL Server 2005.”
In addition to the rapid growth the company has enjoyed, it has also seen a growth in the data that its customers need. “Our customers are demanding more and more direct access to the data, at the same time the volume of data has been growing exponentially with the growth of the business,” says Catassi. “Every day, we have hundreds of thousands of container events that are received electronically by the system and made available to our customers for tracking and tracing information, as well as to our internal equipment control department for managing and tracking our assets. With SQL Server 2005 we are able to maintain a quality of service that is unsurpassed in our industry.”
Even as database workloads are growing, the company has found a noticeable boost in performance with the move to SQL Server 2005. “Our users are seeing better response times with SQL Server 2005,” points out LaManna. “They are cheering about the enhanced speed, even in cases where we haven’t altered the code. We immediately saw increased performance, from the first moment SQL Server 2005 was installed.”
MSC values SQL Server 2005 support for 64-bit architectures, and has deployed its first 64-bit two-node cluster on ES7001/600 servers with eight dual core processors and 32 GB of memory. “The support for 64-bit processors is something that we have been looking at,” advises Catassi. “We are upgrading our hardware by deploying Unisys ES7001/600 64-bit servers. The combination of 64-bit SQL Server 2005 and 64-bit hardware will help us meet our future growth needs. We see no limit to SQL Server 2005.”
High Availability with SQL Server 2005 Database Mirroring
The Database Mirroring feature released with SQL Server 2005 Service Pack 1 has helped MSC to deploy a high availability solution—without the cost and complexity associated with other hardware-based and other third-party solutions.
Initially MSC is using Database Mirroring to provide a synchronous geographically remote solution between its production database in its Warren, New Jersey data center and a second MSC data center in New York. The company plans to expand its use of Database Mirroring across its other key operations.
With SQL Server 2005 Database Mirroring, an originating SQL Server 2005 instance (referred to as the principal database) continuously sends a database's transaction log records to a copy of the database on another standby SQL Server instance (referred to as the mirror database).
|
SQL Server 2005 and Visual Studio 2005 are a perfect combination. |
|
|
Fabio Catassi,
Chief Technical Officer, Mediterranean Shipping Company |
|
|
In all SQL Server databases, data changes are recorded in the transaction log before any changes to actual data pages are made. The transaction log records are placed first in a database's log buffer in memory, and then flushed to disk (or 'hardened') as quickly as possible. In Database Mirroring, as the principal server writes the principal database's log buffer to disk, it simultaneously sends that block of log records to the mirror instance.
When the mirror server receives a block of log records, it places the log records first into the mirror database's log buffer and then hardens them to disk as quickly as possible. Those transaction log records are later replayed on the mirror. Because the mirror database replays the principal's transaction log records, it duplicates the database changes on the principal database.
The synchronous transfer between principal and mirror database instances guarantees that all transactions in the mirror database's transaction log will be synchronized with the principal database's transaction log, and so the transactions are considered safely transferred.
“SQL Server 2005 Database Mirroring has given us a powerful high availability solution,” Catassi says. “It is easy to deploy, easy to manage, and provides a significantly lower total cost of ownership than hardware mirroring solutions. We also plan to use it to support reporting and help desk activities without affecting the production environment.”
Integrated Development Platform
MSC requires a solid development environment because it performs so much of its own application development. One of the new features that the company’s internal developers appreciate is that SQL Server 2005 hosts the common language runtime (CLR) feature of the Microsoft .NET Framework version 2.0. The CLR makes it possible for developers to incorporate significant business logic into stored procedures, and new functionality in Transact SQL and XML. This powerful business logic expands the range of data manipulation and storage functions that are available to the developer.
“SQL Server 2005 and Visual Studio 2005 are a perfect combination,” concludes Catassi. “An especially important benefit is the integration of the CLR. Working with SQL Server and Visual Studio, we're confident that we can create any solution we may need.”
* Results not typical. Availability is dependent on many factors, including hardware and software technologies, mission-critical operational processes, and professional services.
Microsoft Windows Server System
Microsoft Windows Server System is a line of integrated and manageable server software designed to reduce the complexity and cost of IT. Windows Server System enables you to spend less time and budget on managing your systems so that you can focus your resources on other priorities for you and your business.
For more information about Windows Server System, go to:
www.microsoft.com/windowsserversystem
Microsoft SQL Server 2005
Microsoft SQL Server 2005 is comprehensive, integrated data management and analysis software that enables organizations to reliably manage mission-critical information and confidently run today’s increasingly complex business applications. By providing high availability, security enhancements, and embedded reporting and data analysis tools, SQL Server 2005 helps companies gain greater insight from their business information and achieve faster results for a competitive advantage. And, because it’s part of Windows Server System, SQL Server 2005 is designed to integrate seamlessly with your other server infrastructure investments.
For more information about SQL Server 2005, go to:
www.microsoft.com/sqlserver
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 Mediterranean Shipping Company products and services, visit the Web site at:
www.mscgva.ch
For more information about Interlink and the MSCLink application, visit the Web site at:
www.interlink.bz
For more information about Unisys products and services, visit the Web site at:
www.unisys.com
© 2005 Microsoft Corporation. All rights reserved.
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Microsoft, Visual C++, Visual Studio, Windows, the Windows logo, Windows Server, and Windows Server System are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. All other trademarks are property of their respective owners.
