DMaaS — Database Migrations as a Service
A holistic approach to the Database Migrations beyond just Data Transfer.
Finally, after over a decade of consuming web content, I am glad to be a contributor :)!
Preface
One of the best aspects of working with an enterprise like Walmart is always thinking about a massive scale. Just like database migrations!
We have seen an exponential rise in Database Migrations in the last few years, mainly due to cloud adoptions. The upward trend will continue with the ever-evolving tech stack and multi-cloud adoptions.
The developer communities are getting smarter by having possible database migration considerations in the development phase than an afterthought. This allows developers to be more agile to changing tech-stacks or adapt to cutting-edge database solutions very conveniently. However, the Database Migration activities remain a challenge.
We have dozens of data transfer tools available (Migration Tools) that perform homogeneous (e.g., MS SQL to MS SQL) or heterogeneous (e.g., MSSQL to Oracle) Database Migrations.
These migration tools handle one-time or even continuous migrations using change data capture really well. This ensures no data loss and minimizes the downtime/business impact during migrations significantly.
The problem is that most of The Database Migration tools available currently only cover the data transfer activities. But, the actual database migration activity is much more complex and involves multiple activities which need to be performed before & after the migration. These pre & post-activities and interacting with the existing migration tools need some level of DBA expertise.
This makes the whole Database Migration process quite cumbersome and time-consuming.
Here’s an illustration of the manual migration flow using the Attunity replicate server.
Let’s zoom in to the Problem Statement a bit more.
Problem Statement
As per the flow above, the current process involves application teams working with the database migration consultants (Heterogeneous migrations) or the DBA teams (Homogeneous migrations) to migrate their databases.
The migration consultants or the DBAs then work with the Attunity replicate tool manually to perform the migrations.
The pre-migration activities include but are not limited to,
- Connectivity testing.
- Query the source database tables metadata (row count, table size) to decide which tables to include in the scope of one migration task. (Parallelism to reduce migration duration/exclude tables which are no longer required)
- Check if the target database tables have records (to avoid accidental overwrites).
- Disabling constraints (if any) from the target database to avoid constraint-related errors during the migration.
Initiating the actual database migration.
The post-migration activities include but are not limited to
- Checking the status of the migration
- Generating a status report of the migration to see if all the tables are successfully migrated.
- In case of failures, go through the error logs of the migration tool to find the exact cause of the failure.
- Retry the migration task in case of failures.
- Truncating the target database tables before the retry & disabling the constraints (if any).
- In case of a successful migration, enable the constraints.
The above list of tasks makes the overall migration process very complex and results in a lot of manual efforts. This creates a bottleneck for migration projects as the ratio of the database migration requests in the pipeline to the Migration Consultant/DBA team resources will never match.
Eventually, this creates an indirect business impact by either depriving the end-users of some new cool features or draining revenue by supporting legacy systems for a longer duration.
Enter the “DMaaS — The Database Migration as a Service”!
“DMaaS” to the rescue!
We looked at the above problem statement to see how we can help ease such high-touch database-related operational activity and develop an automation framework to reduce manual efforts.
The “DMaaS — The Database Migration as a Service” tool helped us solve this problem.
A tool that looks at the Database Migration holistically performs both pre & post-migration activities automatically along with the actual data migration. This makes the entire Database Migration process available over a few clicks without any DBA involvement/knowledge.
We worked with the Database Migration team to understand the end-to-end “Runbook” for the typical Database Migration request. This runbook helped us understand the full life cycle of the migration operations and thus define the scope of the automation.
The initial idea was to see which activities can be automated to reduce Migration Consultant/DBA dependency during trivial migrations (dev/stage, low volume prod, etc.). However, while deciding on the MVP, we identified a simple pattern like “pre-activities,” “main-task,” & “post-activities.”
This allowed us to develop the “DMaaS — Database Migration Automation” tool to completely remove Migration Consultant/DBA dependency, at least for the trivial migrations* (dev/stage, low volume prod, etc.)
This allows the application teams to execute their trivial migrations independently!
(* dissecting the non-trivial migrations can be a separate blog itself!)
Tools & Technologies
- Attunity Enterprise Manager, which exposes REST APIs to interact programmatically with the Attunity Replicate server.
- Concord server for hosting the UI and orchestrating workflows (Could be an individual app server as well).
- HTML, Javascript for the UI.
- Ansible for templating capabilities & access to Attunity servers. This helps us create a JSON object that captures all the information required by the Attunity Replicate to handle the data migration.
- PL/SQL — SQL queries for getting the list of tables, Stored Procedures (Dynamically fetching the list of constraints and enabling/disabling them).
- Python scripting to handle string manipulations (God bless python! :) ).
- Email, Service-Now & Jira Integration for administrative activities like notifications, change process validation and creating an automated ticket in case of failures.
Salient Features of the “DMaaS”
- One-stop-shop for all the migration-related activities over a single link.
- Source and Target DB connectivity check before migration.
- No to minimum DBA expertise is required for at least the trivial migrations.
- ServiceNow change validation (Validate approved CRQ for production database migrations).
- Table selection with sorting (based on row count & size) and searching capabilities (Allows partial migrations)
- Pre-migration validation tasks for the target database, such as disabling constraints & row count checks, are embedded in the flow.
- Initiating the actual migration and sending periodic updates of the migration status.
- Post-migration tasks such as to enable constraints from the target databases.
- Publish the exact errors by scrapping the Attunity logs for smooth remediation in case of errors. (For both windows or Linux platforms) *
(* This is a key feature, as multiple flows are orchestrating the whole migration process, viz. query the source DB, interact with the Attunity replicate, etc. If errors are not handled well, then it can potentially create chaos for Migration Consultants/DBAs to understand the exact point of failure and the reason)
Along with all of the aforementioned features, the Modular design of this automation allows this framework to extend to different types of databases, database migration tools & can add/remove various pre/post activities. The Modular design also ensures that the individual flows can be executed independently. e.g., check target database row counts before initiating migration. (This is embedded in End-to-End flow as well)
Here’s a sneak-peak into some of the UIs,
Conclusion
The “DMaaS — Database Migration as a Service” is a framework that orchestrates all the pre & post-migration activities, including the data transfer itself. This framework is not limited by the number of activities, database technologies, or even the data transfer tool used underneath. With this tool, application teams can run their Database Migration show on their own to a great extent and involve the skilled Migration Consultants/DBAs only for complex requests.
Thank you very much for reading through the “DMaaS journey.” I hope this was helpful.
Wishing you all “Happy Database Migrations” :)!
Please feel free to share your views or any other suggestions.
Credits: Rajesh Pilwari, Jaydeep Godhani, Vishnuram Mohan, & Senthil RV
