Case Study: Payvision Migrates to SQL Server 2008 with Only 4 Seconds of Database Downtime - News

Case Study: Payvision Migrates to SQL Server 2008 with Only 4 Seconds of Database Downtime 1/11/2010

When Payvision, a provider of international payment solutions, recently needed to migrate to SQL Server 2008 with the minimum amount of downtime, it set up a comprehensive testing environment and turned to Microsoft Gold Certified Partner Solid Quality Mentors.

Payvision specializes in multicurrency processing for all types of card-not-present electronic transactions, efficiently and securely processing exchange transactions in more than 150 currencies for all credit card companies, including VISA, MasterCard, American Express, Discover, Diner's Club, Carte Bleue, and JCB. Headquartered in Amsterdam with offices in New York, the UK, Singapore, and Madrid, Payvision also offers credit, debit, gift, loyalty and stored-value card processing, MO/TO, secure billing, reporting and underwriting, CNP chargeback remediation, and uninterrupted customer service.

Migration Challenges

The Payvision payment platform offers flexible solutions that not only simplify and improve its customers’ experience but also provide powerful tools for organizations to streamline operations and effectively manage transactions. The company’s payment infrastructure is based entirely on Microsoft technologies, from the IIS and .NET-based Web front end through which customers can see detailed transaction status to the SQL Server-based transaction manager.

The firm had been researching SQL Server 2008’s functionality, especially the high availability enhancements, data and backup compression, and table-partitioning features. With customers in virtually all time zones and guaranteeing the high service levels required by online payment systems, Payvision wanted to take advantage of the best SQL Server – Windows HA solutions available. However, the company’s biggest technical challenge in migrating from SQL Server 2005 to SQL Server 2008 was also the need to support its customers 24 hours a day.

Payvision's service-quality policy requires it to inform all customers of any service interruption weeks in advance. The firm's clients can't afford even 5 minutes of downtime. "[If we couldn't assure such a level of service], customers would simply forgo our services and seek other providers with SLAs that meet their needs," said Jose Fco Bonnin, Payvision CTO.

Payvision needed a safe, reliable, and rapid migration plan with the lowest possible impact on its system, solutions, and customers. For that, Bonnin contacted Solid Quality Mentors, authors of the authoritative SQL Server 2008 Upgrade Technical Reference Guide and a trusted, global provider of advanced solutions and education for the Microsoft Data Platform.

"Although we have significant expertise in SQL Server, what we sought was a supplier that would be responsible for issues related to migration of the database engine and that would work independently without interfering too much in our day-to-day operations before performing the actual migration," Bonnin explained. Bonnin received good references from Solid Quality Mentors, which features more than 100 of the world's top technical experts specializing in assuring client success using integrated Microsoft technologies. To help ensure the smoothest migration, Payvision provided Solid Quality Mentors a testing platform to iteratively simulate the migration process and find the most efficient path to SQL Server 2008.

Old and New Database Architecture

Payvision’s SQL Server 2005 transaction management system used database mirroring in synchronous mode with automatic failover configured for the Web farm. The company also had separate reporting servers to support customer reporting and analysis.

"Our customers require that transactions never fail, and the transactional server must fulfill that requirement by being online 24x7x365,” Bonnin noted. “However, for the analytics server, although customers want it to be available to the highest availability standards possible, they agree that this environment can be more flexible."

Payvision needed to migrate to a new SQL Server 2008 configuration that included the following:

Database servers configured in a Microsoft Windows Clustering implementation
Reporting servers configured as subscribers of transactional replication from the OLTP servers
Warm stand-by servers configured in asynchronous database mirroring mode from OLTP servers

Migration Project Phases

The Payvision SQL Server 2008 migration project—spanning a total of 7 weeks—began with the HA solution design, moved through system and application performance testing and configuration, and concluded with the migration itself and some key post-migration tasks. The side-by-side migration project was developed primarily as iterative processes dependent upon each other. The team revisited a few processes after refining the storage system configuration; otherwise, the project flow unfolded as planned.

The key project phases were:

1.      Measurement and optimization of the existing system. To begin, the Solid Quality Mentors team conducted a compatibility analysis and validation of application performance of Payvision’s existing SQL Server 2005 system to ensure that after the migration, the system and applications would perform at least at the same levels as in the past. The team then analyzed the compatibility of applications with SQL Server 2008 and investigated Database Engine configuration changes that would be necessary for SQL Server 2008.

2.       Measurement and optimization of the new system. Using Payvision’s valuable testing environment, Solid Quality Mentors reproduced simulated activity on the new system and analyzed the time required for system migration, reproducing consistent, reliable activity against the SQL Server 2008 solution.

3.       The migration. The critical phase of this step was to migrate from SQL Server 2005 to SQL Server 2008 as quickly as possible. The migration process included reproduction, measurement, and implementation in production systems of scripts designed during the previous phase.

4.       Post-migration tasks. After the successful migration, the team performed further validation of system activity and specified data replication settings to other servers for processing customer reports. Solid Quality Mentors also configured a warm stand-by center that would be available for processing after the unlikely event of database server failure.

"The project has been a technical challenge: On one hand, you need to minimize downtime, and on the other hand, you have to properly configure systems for optimal performance because the speed of an answer directly relates to money for customers,” said Eladio Rincón, Director of Database Technology for Solid Quality Mentors Iberoamericana. “The database transactions are mainly charges on credit cards, and under no circumstance could we have the servers not working properly in any stage of putting the migrated system into production."

The team accomplished the side-by-side migration in the following stages:

1.       Undo the database mirroring session between the existing SQL Server 2005 database servers.

2.       Create synchronous database mirroring session between the existing SQL Server 2005 and new SQL Server 2008 database servers.

3.       Force role changes from SQL Server 2005 to SQL Server 2008, making the SQL Server 2008 servers the primary servers in the database mirroring session.

4.       Undo the mirroring session.

5.       Migrate from SQL Server 2005 servers to SQL Server 2008 servers.

6.       Create an asynchronous mirroring session between the new SQL Server 2008 servers.

"The critical phase was changing database mirroring roles between SQL Server 2005 and SQL Server 2008 servers,” said Solid Quality Mentors’ Ruben Garrigós. “By using different threads to fail over multiple databases in parallel, we were able to migrate large databases in just 4 seconds."

As for the Web farm, "the total time between when we stopped managing transactions in SQL Server 2005 and began managing them with SQL Server 2008 was only 7 seconds, which was a totally justifiable time for our customers," said Payvision’s Bonnin.

The migrated system currently manages a peak of 600 database transactions per second (Trans/sec), which is far below the levels supported in the test environment, which consistently reproduced averages of 5,100 Trans/sec and peaks of up to 9,800 database Trans/sec. Payvision’s environment is configured with four 2.27GHz QuadCore processors, with an average use of about 10% Processor Time and an average of 3,800 queries processed per second as measured by the Batch Requests/sec performance counter.

Benefits of Migrating to SQL Server 2008

The benefits of Payvision’s migration to SQL Server 2008 are significant, including the following:

IT can more predictably manage server resources using Resource Governor for index maintenance and more efficiently perform and manage backup processes.
New transaction log compression mechanisms in database mirroring sessions, transparent to the application, make more efficient use of the network.
The compressibility of backups, reduced in ratios of 1:5, yields the shortest backup times possible, requires less storage, and gives the company faster restorability.
SQL Server 2008 offers new possibilities for data partitioning combined with partition level compression.
Compression of row-level data that SQL Server uses for numeric data types yields better performance and takes less storage space.
The company enjoys the ease of Windows 2008 Cluster management.

As Payvision’s successful migration shows, with a proper plan, comprehensive testing, and the right combination of technologies, you can upgrade to SQL Server 2008 safely, reliably, and with a minimum of downtime.

About Solid Quality Mentors

Since its founding in 2002, Solid Quality Mentors has become a trusted global provider of advanced solutions and education for the Microsoft Data Ecosystem™. Our mentors are located around the globe and include more than 40 Microsoft Most Valuable Professionals (MVPs) and seven Microsoft Regional Directors (RDs). Our experts work daily with the constantly evolving Microsoft SQL Server, Business Intelligence, and .NET Development platforms to help you take advantage of the latest features for database and application deployments. Learn more about Solid Quality Mentors at http://www.solidq.com/; for assistance, you can reach us at 1-800-757-6543 or Info@SolidQ.com.