The importance of database modernization: Xero’s SQL Server Migration 

By Helen Anderson, AWS Data Hero and Business Intelligence Consultant

Technology is moving fast, and it’s important to keep up. This isn’t just to chase the next ‘shiny thing,’ but to take advantage of improvements to security, tooling, and performance. In this post, I show you what database modernization is and why you should modernize your database. I detail how and why my team migrated 60 Data Analyst users from a legacy database to a purpose-built, scalable Amazon Aurora database.

When thinking about modernization, it’s important to think about what a legacy system is. A legacy system isn’t just the servers locked away in a cupboard behind the IT department.  Legacy systems are systems, which are no longer supported or fit for purpose. This could be infrastructure that got partway through a cloud migration before priorities changed. It could also be systems that are working fine, but could benefit from the latest technology and security offered by the cloud. These systems could trundle along just fine, but there are benefits from moving Database systems to the cloud.  

Why should you modernize your database?

Moving databases to the cloud reduces operational costs and frees up your team to add more value during their day. Rather than spending their time patching, securing, and scaling databases, your team can focus on innovating. Other benefits include: 

• Resources can be spun up at the click of a button from the console
• No capital expenditure and lower operating costs
• Flexibility to scale down or switch off resources with Serverless
• Confidence that the service is reliable with uptime SLAs

Why we modernized at Xero 

Analytics grew quickly at Xero, meaning that the SQL Server database we deployed years ago could not keep up with our Data Analysts and Scientists. We had moved our core platform to AWS, which “provides improved levels of security, availability and performance that will support massive future growth.” This change in infrastructure also gave our team the opportunity to embrace the benefits of the cloud.

The database technology our Data Analysts relied on for insights was built for only a handful of users. With more data to load, we experienced slower copy times from our Amazon Redshift Data Warehouse. Additionally, with more Data Analysts competing for resources, there was a demand for faster processing, a significant increase in demand for storage space as multiple sets of analytics were performed.

“State of Emergency” to “State of the Art”: Life before Xero’s migration

The SQL Server database we deployed for our Data Analysts was intended as a sandpit environment with a small workload. With more users, more demanding workloads, and more data, it became much harder to monitor, debug, and deliver a quality service. Our DBA and Data Engineers were applying fixes and responding to help tickets, rather than adding value and innovating.

We had moved to Amazon Redshift for our Data Warehousing so we could take advantage of massively parallel processing (MPP).  So we decided to use another AWS option to take the Analysts database from ‘state of emergency’ to ‘state of the art.’

As a fully managed service that uses the same Postgres syntax as our Amazon Redshift warehouse, Aurora provided the efficiencies we needed. This sped up the time to copy data across from the Data Warehouse from 12 hours to four as they speak the same language.  Storage costs were reduced and there are no license costs to pay for with Aurora. The roadmap for Aurora looked very promising to meet Xero’s growing analyst demands with Serverless giving us the tools to improve the environment even more.

Using Migration Tooling from AWS

Unlike a traditional database migration, we had some tools from AWS to make things faster and easier.

Schema Conversion Tool

The AWS Schema Conversion Tool (SCT) is designed to seamlessly convert the entire schema with all its keys, constraints, and indexes. Once configured with a set of transformation rules, the Database Migration Service converts the data to automatically land it in the new Aurora database. 

The SCT was easy to use and a massive time saver in our migration. We spent more time upskilling users and less time tinkering with scripts and schemas.

Database Migration Service

The AWS Database Migration Service (DMS) is a web service that allows you to migrate data from one database to another.  You can move database objects from the same kind of database engine or a different database engine. The only requirement is that one of them must be an AWS service.

To start, you must set up a replication instance. There are some decisions to make based on your use case that you can read about in the documentation. Thankfully, the console allows for a straightforward process.

Then, create the endpoints. The source and target data stores can be on an Amazon Elastic Compute Cloud (EC2) instance, an Amazon RDS instance, or an on-premises database.

Running tasks

The good thing about using this service is that you can use the UI to run your tasks. This part requires you to specify the database endpoints and a description. Create these tables one at a time, since you can’t clone multiple tables to repeat the process.

Using transformation rules

The next step is to use Transformation rules to make sure that the data arrives as expected. You can change at the Schema, Table, and Column level.

Make sure to use transformation rules of the same type. If you’re using two Table transformation rules, it can pick one of them and sometimes both. The resulting table must be cleaned up. This isn’t great when you need database permissions and must sheepishly ask your DBA to script a fix.

Go live

Despite some challenges in moving data around, Xero went from gathering requirements to User Testing in just six months. 

You can build an environment, but there is no way to test how it is going to react with analysts or how easy it is for users to adopt without them actually using it. This is due to the new syntax, fewer technical users needing more help, and queries hitting the database all at once. It’s still a work in progress, but the feedback has been great.

We’ve had feedback on how smoothly everything has gone. The Analysts have taken well to the new system and are getting to grips with the quirks that go along with a new database. We’ve gotten comments about how much faster the database feels, and how people are happy to be using Postgres. 

Copy times have reduced from over 12 tedious hours to just over four. Analyst queries are much quicker to return, and we’re sharing all our learnings as we go.  As one of the first teams to make the move to Aurora at Xero, we’ve found the process fairly smooth. Having such positive feedback from the users has also been a huge win. 

How to start your modernization journey

If you would like to get started with AWS, the first step is to check out the free tier. AWS offers one year of services to explore what they offer. After that, it’s a matter of clicking a few buttons to get going.

Another helpful AWS resource is the Compute Savings Plans. Xero also uses Compute Savings Plans for SQL Server clusters, as they provide higher savings and flexibility for Xero’s environment.

 But it’s not just about tooling—migration is a team sport.  By listening and taking a slow and steady approach, we have tackled the project in just nine months. We were able to build the Aurora platform further for the Analysts we support and contribute to their success.

How can AWS help with modernization?

At this point, you may be thinking what the best way is to get started with AWS down this modernization journey. Join all the customers who trust AWS to run their most important applications in the best cloud by letting AWS help you with an assessment. Email us at WhatsYourModernizationPlan@amazon.com, and please consider joining the conversation using the #WhatsYourModernizationPlan hashtag.

To learn more about modernizing your applications, please visit Windows on AWS.

Helen Anderson is an AWS Data Hero and Business Intelligence Consultant from Wellington, New Zealand. Helen leads on projects with customers like Xero that empower users and make businesses more efficient by utilizing AWS products. Xero provides accounting software for small business, that works anywhere you do. Xero’s online accounting software means you can automate everyday business tasks, get up-to-date financials and run your business – from wherever you are.