This is a retrospective covering some of the learnings from ‘Project Aurora’. A nine-month project to migrate 60 Data Analyst<\/a> users and all their objects from a legacy SQL Server database<\/a> to a fit for purpose, scalable AWS Aurora database<\/a>.<\/p>\n\n\n\n
What I hope you take away from this post is not only that it’s very easy to get started with AWS<\/a>. But that migration is a team sport<\/a>. This project would not have been a success without the buy-in and support from the Data Analyst users.<\/p>\n\n\n\n
Growing Pains<\/a>
Community and Communication<\/a>
Design Challenges<\/a>
Migration<\/a>
Testing and Go Live<\/a>
The Future<\/a><\/strong><\/p><\/blockquote>\n\n\n\n
\n\n\n\nPart One: Growing Pains<\/h2>\n\n\n\n
Analytics has been growing incredibly quickly at Xero<\/a>. There are now over 60 Analysts using the platforms that my team, Data Services, build and maintain. But it hasn’t always been this way.<\/p>\n\n\n\n
Back in 2015, there were four Marketing<\/a> Analysts in Wellington and another four in the regional offices in London, San Francisco and Melbourne. Data Science<\/a> wasn\u2019t a hot job yet, and the Data Analysts role was primarily to support marketing automation by using the Data Services platform to pull lists for email campaigns.<\/p>\n\n\n\n
\n\n\n\nLife with SQL Server<\/h3>\n\n\n\n
The SQL Server deployed as a sandpit environment was designed for a much smaller workload, wasn\u2019t intended to be a production space and became much harder to monitor, debug and deliver a quality service.<\/p>\n\n\n\n
Data Services had moved to Amazon Redshift for our Data Warehousing so made the call 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
<\/figure><\/div>\n\n\n\n We had reports of:<\/p>\n\n\n\n
Tables being empty and not enough visibility of when the 12-hour process to update the SQL Server environment would complete.<\/p><\/li>
Regional Analysts losing work time as the update window cut into their workday.<\/p><\/li>
Database locking as multiple queries hit the same tables during busy times<\/p><\/li><\/ul>\n\n\n\n
\n\n\n\nDeciding on Aurora<\/h3>\n\n\n\n
Aurora<\/a> provided the efficiencies we were looking for:<\/p>\n\n\n\n
It\u2019s a fully managed service at 25% of the cost<\/a> of SQL Server licensing and disc.<\/p><\/li>
And can scale with us as we grow. Scaling up<\/a> with AWS is as simple as pressing a button.<\/p><\/li><\/ul>\n\n\n\n
\n\n\n\nPart Two: Community and Communication<\/h2>\n\n\n\n
Before we could start solutioning a new environment, we wanted to make sure there was a shared understanding between the Analyst community and Data Services.<\/p>\n\n\n\n
Migrations within our own team had been tricky enough but this one had added complexity. We have users in four different timezones which makes it a challenge to schedule meetings and keep communication flowing<\/a>.<\/p>\n\n\n\n
Due to Xero’s growth, there are new Analysts starting each month. Some Analysts have only Excel<\/a> experience, others are used to crunching through big data sets.<\/p>\n\n\n\n
To get everyone on board we have held monthly workshops with key users, created a dedicated Slack channel<\/a> and focused on making the environment as accessible as possible.<\/p>\n\n\n\n
\n\n\n\nPart Three: Design Challenges<\/h2>\n\n\n\n
In mid-2018, with the community on board, we launched a Proof-of-Concept Aurora database and took stock of the SQL Server environment we were dealing with.<\/p>\n\n\n\n
\n\n\n\nRationalisation<\/h3>\n\n\n\n
The first thing challenge was to see how many tables we would be tasked with migrating to Aurora and hopefully reduce that to the bare minimum.<\/p>\n\n\n\n
Because the SQL Server environment was designed as a sandbox there were legacy tables, temp tables and schemas of objects no one seemed to own.<\/p>\n\n\n\n
Through a huge amount of effort and a little bit of pestering from me, we managed to clear out 2500 tables and freed up 2TB of disc.<\/p>\n\n\n\n
\n\n\n\nMoving Data Around<\/h3>\n\n\n\n
Challenge number two was to replace the mechanism used to move the data from our Data Warehouse to the Analyst Database.<\/p>\n\n\n\n
Some tables are billions of rows deep, and others are hundreds of columns wide which the SSIS process wasn\u2019t coping with.<\/p>\n\n\n\n
Having to translate all that data from a Postgres Datawarehouse to a SQL Server database reduced efficiencies and updating the SSIS packages that moved the data around was tedious and fiddly.<\/p>\n\n\n\n
<\/figure><\/div>\n\n\n\n By using DB Link<\/a> and Apache Airflow<\/a> as our scheduler we\u2019ve removed the complexity in transferring between the Data Warehouse and Aurora. The copy time has gone from over 12 hours to just two.<\/p>\n\n\n\n
\n\n\n\nSearching for a Scheduler<\/h3>\n\n\n\n
Our third challenge was to find a scheduling tool that the Analysts could use to schedule their stored procedures.<\/p>\n\n\n\n
Aurora does not come with a built-in job agent so we needed to work through other possibilities.<\/p>\n\n\n\n
We considered CRON jobs but not all the Analysts are familiar with the command line, we then considered standing up an Airflow<\/a> server for the Analysts, but with each new job scheduled, the server needs to be restarted. This could have been a disaster with Analysts in four timezones potentially interrupting running jobs to schedule their own.<\/p>\n\n\n\n
In the end, we decided on using what was already there. Windows task scheduler<\/a>, as it provided little disruption and was a tool the Analysts were already familiar with.<\/p>\n\n\n\n
\n\n\n\nSeparation and Security<\/h3>\n\n\n\n
The last major consideration we made was around security<\/a> and separating functional groups.<\/p>\n\n\n\n
\n\n\n\nPart Four: Migration<\/h2>\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
But just like a traditional migration not everything went according to plan.<\/p>\n\n\n\n
\n\n\n\nSchema Conversion Tool and Database Migration Service<\/h3>\n\n\n\n
The Schema Conversion Tool<\/a> works hand in hand with the Database Migration Service<\/a> but has presented the biggest challenges through the whole migration.<\/p>\n\n\n\n
This resulted in multiple tables landing that needed to be cleaned up.<\/p>\n\n\n\n
\n\n\n\nPart Five: Testing & Go Live<\/h2>\n\n\n\n
\u201cGreat job migrating all the data. Not an easy task. Thank you for all the support!\u201d<\/p><\/blockquote>\n\n\n\n
Despite some challenges in designing the right environment and moving the data around, we went from gathering requirements to User Testing in six months. But the challenges don\u2019t end here.<\/p>\n\n\n\n
Building an environment in isolation is very different from letting over 60 users lose. There\u2019s the new syntax, less technical users needing more help and queries hitting the database all at once. It\u2019s still early days but the feedback has been great.<\/p>\n\n\n\n
\u201cWooo hooooo! Aurora is excellent. Glad we are using Postgres now\u201d<\/p><\/blockquote>\n\n\n\n
We\u2019ve had feedback on how smoothly everything has gone. Even though there were bumps in the road to get to where we are now.<\/p>\n\n\n\n
The Analysts have taken to the new system and are getting to grips with the quirks that go along with a new database.<\/p>\n\n\n\n
Copy times have reduced from 12 tedious hours to just over two. Analyst queries are much quicker to return and ownership is a lot clearer between Data Services and the Analysts.<\/p>\n\n\n\n
\u201cIt feels so much faster!
I\u2019m sure we will encounter a few challenges but that\u2019s part of every new system\u201d<\/p><\/blockquote>\n\n\n\nWe\u2019re documenting everything we can in a user guide that gets added to by both Data Services and the Community. The Slack channel will also stay open for the Analysts to help each other.<\/p>\n\n\n\n
As one of the first teams to make the move to Aurora 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
\n\n\n\nPart Six: The Future<\/h2>\n\n\n\n
If you would like to get started with AWS<\/a> the first step is to check out the free tier<\/a>. AWS offers one year of services to explore what they offer. Aurora comes in MySQL and PostgreSQL flavours and Serverless is also on the roadmap. After that, it’s a matter of clicking a few buttons to get going.<\/p>\n\n\n\n
But it\u2019s not just about tooling, as migration is a team sport.<\/p>\n\n\n\n
\n\n\n\nCover photo by Pixabay from Pexels<\/p>\n","protected":false},"excerpt":{"rendered":"
This is a retrospective covering some of the learnings of ‘Project Aurora’. A nine-month project to migrate 60 users from SQL Server to AWS Aurora.<\/p>\n","protected":false},"author":1,"featured_media":3088,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"Layout":""},"categories":[11,147],"tags":[171,173,177,175],"yoast_head":"\n
The Great Migration: From SQL Server to AWS Aurora | Helen Anderson<\/title>\n<meta name=\"description\" content=\"This is a retrospective covering some of the learnings of 'Project Aurora'. A nine-month project to migrate 60 users from SQL Server to AWS Aurora.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"The Great Migration: From SQL Server to AWS Aurora | Helen Anderson\" \/>\n<meta property=\"og:description\" content=\"This is a retrospective covering some of the learnings of 'Project Aurora'. A nine-month project to migrate 60 users from SQL Server to AWS Aurora.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/\" \/>\n<meta property=\"og:site_name\" content=\"Helen Anderson\" \/>\n<meta property=\"article:published_time\" content=\"2019-04-11T06:48:40+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-11-19T09:42:36+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/04\/aws-migration.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1280\" \/>\n\t<meta property=\"og:image:height\" content=\"853\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:site\" content=\"@helenanders26\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\">\n\t<meta name=\"twitter:data1\" content=\"8 minutes\">\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebSite\",\"@id\":\"https:\/\/13.237.200.153\/#website\",\"url\":\"https:\/\/13.237.200.153\/\",\"name\":\"Helen Anderson\",\"description\":\"Data Analyst | Technical Writer\",\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/13.237.200.153\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-NZ\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#primaryimage\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/04\/aws-migration.jpg\",\"width\":1280,\"height\":853,\"caption\":\"aws migration\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#webpage\",\"url\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/\",\"name\":\"The Great Migration: From SQL Server to AWS Aurora | Helen Anderson\",\"isPartOf\":{\"@id\":\"https:\/\/13.237.200.153\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#primaryimage\"},\"datePublished\":\"2019-04-11T06:48:40+00:00\",\"dateModified\":\"2020-11-19T09:42:36+00:00\",\"description\":\"This is a retrospective covering some of the learnings of 'Project Aurora'. A nine-month project to migrate 60 users from SQL Server to AWS Aurora.\",\"inLanguage\":\"en-NZ\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/\"]}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#webpage\"},\"author\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"headline\":\"The Great Migration: From SQL Server to AWS Aurora\",\"datePublished\":\"2019-04-11T06:48:40+00:00\",\"dateModified\":\"2020-11-19T09:42:36+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#webpage\"},\"publisher\":{\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\"},\"image\":{\"@id\":\"https:\/\/helenanderson.co.nz\/aws-aurora-migration\/#primaryimage\"},\"keywords\":\"aurora,aws,project,redshift\",\"articleSection\":\"AWS,Data Technology\",\"inLanguage\":\"en-NZ\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/13.237.200.153\/#\/schema\/person\/4677a271385757403307fb29bd14d7bf\",\"name\":\"Helen Anderson\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/13.237.200.153\/#personlogo\",\"inLanguage\":\"en-NZ\",\"url\":\"https:\/\/helenanderson.co.nz\/wp-content\/uploads\/2019\/11\/helen-anderson-profile-selects-FA-1000.jpg\",\"width\":1000,\"height\":1000,\"caption\":\"Helen Anderson\"},\"logo\":{\"@id\":\"https:\/\/13.237.200.153\/#personlogo\"},\"description\":\"Hi, I'm Helen. I'm a data analyst, technical writer, and AWS Data Hero. I interpret the story behind the numbers, build data applications, and grow analyst and developer communities - currently at Kiwibank.\",\"sameAs\":[\"http:\/\/www.helenanderson.co.nz\/\",\"https:\/\/www.instagram.com\/helenanders26\/\",\"https:\/\/www.linkedin.com\/in\/helenanders26\/\",\"https:\/\/twitter.com\/helenanders26\",\"https:\/\/www.youtube.com\/channel\/UCttVhJizwkhgmMlDBMUE0wQ\"]}]}<\/script>\n","_links":{"self":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/1083"}],"collection":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/comments?post=1083"}],"version-history":[{"count":29,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/1083\/revisions"}],"predecessor-version":[{"id":3564,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/posts\/1083\/revisions\/3564"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media\/3088"}],"wp:attachment":[{"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/media?parent=1083"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/categories?post=1083"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/helenanderson.co.nz\/wp-json\/wp\/v2\/tags?post=1083"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}