24 August 2017

Bringing DevOps to the database – Moody’s Analytics and SQL Clone

Moody's Analytics had a constant need to provision database copies, particularly for the Test Engineers who needed to run multiple daily database integration and acceptance tests. SQL Clone reduced the time taken to deploy a database copy to minutes, instead of hours.

Developers need copies of databases to successfully create new code, test programs and fix issues, but provisioning copies is time-consuming, and each one can take up large amounts of storage space – up to a terabyte and more in many cases. This challenge can potentially slow down extending DevOps to the database and ultimately hit the speed at which updates can be created, tested and deployed.

Innovative, data-driven IT

That’s exactly the situation that Moody’s Analytics found itself in. A subsidiary of Moody’s Corporation, it helps capital markets and risk management professionals worldwide respond to an evolving marketplace with confidence. The company offers unique tools and best practices for measuring and managing risk through expertise and experience in credit analysis, economic research and financial risk management.

Moody's Analytics Moody’s Analytics provides insurers with industry-leading modeling and content delivered through high-performance, modular and configurable software. Its Economic Scenario Generator (ESG) is an award-winning software product that provides Monte Carlo simulation paths for the joint behavior of financial market risk factors and economic variables.

The ESG is constantly updated with the latest market and calibration data, and the responsibility for keeping the update process running smoothly falls on the shoulders of the Service Delivery Infrastructure (SDI) team.

The architect, two testers and three software engineers in the team have already adopted agile software development practices and Principal Software Engineer, Daryl Griffiths, is constantly looking for ways to innovate yet further.

They had a constant need to provision database copies, for example, particularly for the Test Engineers looking to run multiple daily database integration and acceptance tests. While Daryl had developed an easy-to-use approach to provisioning, it still took over an hour to perform each restore, limiting the number of copies that could be created each day.

SQL Clone – provisioning copies in seconds, not hours

An existing Redgate Software customer, the Moody’s Analytics team already uses SQL Source Control, SQL Compare and SQL Data Compare as part of its daily work. So when the beta version of SQL Clone was announced, Daryl leapt at the chance to try it.

Moody's Analytics 2 SQL Clone creates full copies of SQL Server databases and backups in seconds, yet uses only a tiny fraction of disk space. By doing so, it allows companies to provision copies for development, testing and diagnostics quickly and easily.

The copies, or clones, which are only around 40MB in size for a 1TB database, work just like normal databases and can be connected to and edited using any program. By saving time and disk space, it enables teams to work locally on up-to-date, isolated copies of the database and speed up development, accurately test code using realistic data, and fix issues faster.

After testing SQL Clone in his own development environment, Daryl saw immediate results. “Our process before was quite slick, but in my tests with SQL Clone, I reduced the time to provision copies of all six of our databases from nearly two hours to ten minutes.”

Following further tests, Daryl shared the tool with the team’s Test Engineers. They also saw the benefits and reported that there was no difference performing tests against a clone, rather than a full copy of the database. While the developers in the team were aware that SQL Clone was coming, as a final check, they were not informed of the exact timing when remaining provisioning was switched to SQL Clone. The result? They didn’t notice any differences, and there was no impact on performance or how they carried out their work.

Increasing efficiency and supporting continuous deployment

Now that SQL Clone is a standard part of the SDI team’s development process, anyone can self-serve a database copy in minutes, rather than needing to wait hours as before. This frees up time and means development and testing is faster and more efficient.

This new freedom is bringing wider benefits too. Working with clones removes the fear that changing data in development databases might lead to problems that take a long time to fix, encouraging the team to be more innovative. For example, they can try two different ways of achieving something, each on one clone, and compare the results to work out the best approach.

It is also supporting Moody’s Analytics move to embrace DevOps, streamlining the testing process and ensuring that the database is a central part of development. As Daryl concludes, “If we want to deliver new functionality more frequently, then we need to adapt and improve our development processes, and ensure that the testers can make the most effective use of their testing time. We’re now moving towards continuous deployment, and SQL Clone is a big part of that.”

To find out more about how Moody’s Analytics introduced SQL Clone, read the case study.