Technology is moving fast, and it\u2019s important to keep up. This isn\u2019t just to chase the next \u2018shiny thing,\u2019 but to take advantage of improvements to security, tooling, and performance. In this post, I show you what database modernisation is and why you should modernise 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<\/a> database.<\/p>\n\n\n\n
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: <\/p>\n\n\n\n
Analytics grew quickly at Xero<\/a>, 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<\/a>, which \u201cprovides improved levels of security, availability and performance that will support massive future growth.\u201d This change in infrastructure also gave our team the opportunity to embrace the benefits of the cloud.<\/p>\n\n\n\n
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<\/a> 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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
We had moved to Amazon Redshift<\/a> for our Data Warehousing so we could take advantage of massively parallel processing (MPP)<\/a>. So we decided to use another AWS option to take the Analysts database from \u2018state of emergency\u2019 to \u2018state of the art.\u2019<\/p>\n\n\n\n
As a fully managed service that uses the same Postgres syntax as our Amazon Redshift<\/a> 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\u2019s growing analyst demands with Serverless<\/a> giving us the tools to improve the environment even more.<\/p>\n\n\n\n
Unlike a traditional database migration, we had some tools from AWS to make things faster and easier.<\/p>\n\n\n\n
The AWS Schema Conversion Tool (SCT)<\/a> 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. <\/p>\n\n\n\n
The AWS Database Migration Service (DMS)<\/a> 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.<\/p>\n\n\n\n
To start, you must set up a replication instance<\/a>. There are some decisions to make based on your use case that you can read about in the documentation<\/a>. Thankfully, the console allows for a straightforward process.<\/p>\n\n\n\n
Then, create the endpoints<\/a>. The source and target data stores can be on an Amazon Elastic Compute Cloud (EC2)<\/a> instance, an Amazon RDS<\/a> instance, or an on-premises database.<\/p>\n\n\n\n
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\u2019t clone multiple tables to repeat the process.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Make sure to use transformation rules of the same type. If you\u2019re using two Table transformation rules, it can pick one of them and sometimes both. The resulting table must be cleaned up. This isn\u2019t great when you need database permissions and must sheepishly ask your DBA to script a fix.<\/p>\n\n\n\n
Despite some challenges in moving data around, Xero went from gathering requirements to User Testing in just six months. <\/p>\n\n\n\n
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\u2019s still a work in progress, but the feedback has been great.<\/p>\n\n\n\n
We\u2019ve 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\u2019ve gotten comments about how much faster the database feels, and how people are happy to be using Postgres. <\/p>\n\n\n\n
Copy times have reduced from over 12 tedious hours to just over four. Analyst queries are much quicker to return, and we\u2019re sharing all our learnings as we go. As one of the first teams to make the move to Aurora at Xero, we\u2019ve found the process fairly smooth. Having such positive feedback from the users has also been a huge win. <\/p>\n\n\n\n
If you would like to get started with AWS, the first step is to check out the free tier<\/a>. AWS offers one year of services to explore what they offer. After that, it\u2019s a matter of clicking a few buttons to get going.<\/p>\n\n\n\n
Another helpful AWS resource is the Compute Savings Plans<\/a>. Xero also uses Compute Savings Plans for SQL Server clusters, as they provide higher savings and flexibility for Xero\u2019s environment.<\/p>\n\n\n\n
Photo by Alexas Fotos<\/a><\/strong> from Pexels<\/a><\/strong><\/p>\n\n\n\n
This post first appeared on Modernising with AWS<\/a><\/em><\/p>\n","protected":false},"excerpt":{"rendered":"