From “Days to Seconds”: Inside Jobvite’s SQL Server to Amazon Aurora Migration

How would it be to move your infrastructure from a source of constant care to “seamless,” something that “you don’t have to think about anymore”? For Jobvite, a provider of recruiting software that helps thousands of companies source, hire, and onboard top talent, this is exactly what they experienced upon migrating from self-managed Microsoft SQL Server to fully managed Amazon Aurora MySQL. Tasks like setting up replicas took all day in a self-managed environment but took just minutes in Amazon Aurora, and ultimately moving to AWS yielded a 40 percent increase in responsiveness for each application/tier, according to Chaitanya Konduri, DevOps manager at Jobvite. “Some particular functions saw 100 percent or more improvement,” Konduri says.

Additionally, he says that moving to AWS eliminated a great deal of database maintenance, resulting in a significant freeing of time so Jobvite engineers can instead focus on building innovative applications for customers.

“Used to take days, now easily doable in a few seconds”

If you’ve considered moving from your traditional on-premises database to a cloud-based database, you’re not alone. According to Donald Feinberg, a Vice President and Distinguished Analyst in the Gartner ITL Data and Analytics group, “Organizations are developing and deploying new applications in the cloud and moving existing assets at an increasing rate, and we believe this will continue to increase.” Indeed, nearly all of the growth in the database market has come from cloud-based database services, Gartner has found.

This is different from simply running a database in the cloud. Jobvite, for example, had been running Microsoft SQL Server on Amazon Elastic Compute Cloud (Amazon EC2), using Amazon Elastic Block Store (Amazon EBS) for storage. This gave the company the benefit of not having to buy their own servers, but still left them doing the heavy lifting associated with managing the database. “Building recruiting applications is our core competency,” says Pranav Bhargava, Jobvite Director of Engineering. He says they rely on Amazon to take care of managing the database infrastructure.

Upon moving to Amazon Aurora, managing the database immediately became easier. In self-managed Microsoft SQL Server, replicas could take up to a day to bring online; in Amazon Aurora, it takes just minutes. This relates to scalability, where Master had been Jobvite’s primary bottleneck. Bhargava says this would keep his team awake at night, but since moving to Aurora, they’ve had instances in which their Master just switched over, they had a failover, and that’s it. “It was transferred to our application,” he says. “Something that used to take days is now easily doable in a few seconds.”

“Security is something we get out of the box,” Bhargava says, and it’s not something they have to invest in separately. Encryption at rest, automatic backups, and so on — all of these come “out of the box” for Jobvite since moving to Amazon Aurora.

When Jobvite set out to modernize, cost wasn’t the only consideration. Even so, the company saved money by moving to AWS, Bhargava says. One reason? Simple provisioning math. When you’re managing your own database servers, as Jobvite was, you’ll tend to over-provision your storage to ensure sufficient capacity as your database grows to support a growing list of customers. In other words, you pay more simply to have peace of mind and ensure the best quality of customer service. But with Amazon Aurora, you just pay as you use – the database service provisions according to your needs.

That’s the outcome of modernizing, but how did Jobvite go about migrating from Microsoft SQL Server to Amazon Aurora MySQL?

An iterative process

Jobvite approached database migration in a classic way: separating schema migration and data migration into two different phases, followed by performance testing and application migration (as well as a rollback plan to plan for the unexpected). Jobvite’s main datastore contained sizable data on jobs, candidates, and more. Given the size of the database, as well as the nature of the data, migration didn’t promise to be simple.

Migrating the right data

To successfully migrate their data, they needed to know whether they were migrating the right data. Over the years, Jobvite had been storing their data in such a way as to make it easier to work with, for example BLOB columns in a database. This made migration a frustrating endeavor.

Once Jobvite pulled in the AWS Database Migration Service (AWS DMS) team, however, they were able to see there were tables in which they were replicating full LOBs (large objects), meaning they were scanning across entire rows and replicating them. Once the AWS DMS team had helped them to scan all rows to find the maximum length (e.g., select max(len(Description))from Table_Name) and then set migration to a limited LOB with a defined, smaller size (breaking down tables into smaller sizes and grouping them into tasks), the data replication task shrunk from days to hours.

As Konduri and team worked with AWS DMS, he says that particularly dealing with BLOB columns and special characters required extra care. “It took us multiple iterations to identify the right set of tables for each migration task and collation settings,” he says. “We learned that planning data/tables is critical to a successful datastore and is critical to consider as part of any development.”

This proved to be a key lesson for the Jobvite team.

Validation and testing

Among other lessons, they discovered the importance of having a validation tool to facilitate schema migration by giving them the means to check which tables, columns, and so on had been successfully migrated. After every schema migration, Jobvite dumped source and destination data types of each tables in .txt format (T1_mssql.txt and T1_mysql.txt) and would apply a bootstrap script to compare each column. If the columns mismatched, then this would show up in their report. They’d then keep adding special conditions with multiple iterations (1/ varchar(max) in mssql is equal to logtext,  2/ Bit to Boolean, 3/ Getdate to datetime(3)) so as to be sure that every column was migrated with the right data type. “This was an iterative process,” Konduri says.

Jobvite invested four months doing performance testing to make sure everything would work. During this time they captured production traffic during their busiest time of the day and replayed it against their production-scale testing environment at an accelerated rate, to the point that some tiers were experiencing 10x the traffic they would normally get to support a growing list of customers. This helped ensure that other aspects of the application were the bottlenecks, if any, according to Konduri. By performance benchmarking and simulating production loads, they were able to capture most of the issues they could have encountered during migration.

By migrating from self-managed Microsoft SQL Server to Amazon Aurora, Jobvite has been able to embrace the latest technologies including “cloud-scale design,” Konduri says. He says that the separation of the scaling of the query engine from the data layer, along with Aurora’s serverless approach, gives Jobvite completely dynamic load handling that helps drive customer satisfaction.

More to come

Please continue to join me as I regularly highlight companies on their modernization journeys onto AWS. As you do, I hope you’ll also ask the question, “What’s your plan for moving off self-managed SQL Server?” Or whatever technology keeping you from modernizing to better care for your customers?

Let AWS help you assess how your company can get the most out of cloud. Join all the AWS customers that trust us to run their most important applications in the best cloud. To have us create an assessment for your Oracle applications or all your applications, email us at WhatsYourModernizationPlan@amazon.com, and please consider joining the conversation using the #WhatsYourModernizationPlan hashtag.

To learn more about modernizing your Microsoft SQL Server database, visit our Migrate from Microsoft SQL Server to Amazon Aurora page.