This article was written by Anand Chavan, Founder and CEO, GuardX Inc.
There is a database that has been in use for 10, 15 years or more, sturdy .. keeps working silently. What started out as a simple set of tables soon has data validations added to it. Slowly stored procedures start to show up and a whole lot of functionality is now built in as SQL Queries, Triggers and stored procedures. Soon no one can actually track or explain all the stored procedures, SQL queries scattered everywhere in the codebase … total management nightmare. A system kept alive with old hardware and a prayer. The original team long gone!
One day someone a few pay grades above decides that it is time to refresh the tech stack and revisit the working but problematic database. You are the chosen sacrificial lamb being offered by the powers that be to “Lead” the migration effort. Prevent the impending disaster. Be a hero!
This article speaks about a few successful strategies and a few disasters I have seen and been part of. Hopefully this will help you navigate the pain you are about to experience.
Strategy 1: Run!
Let us state the easiest and probably most effective strategy first. Quit. Run. Find another job internally or change your company. Thank everyone for the amazing opportunities, the growth you have experienced, the lifelong friends you have made (80% of whom you will never connect to ever again) and move on to your next chapter. Announce on LinkedIn. Collect some likes.
Migrations are thankless jobs. Best case you won’t get fired. If you don’t follow anything else in this article .. just follow this. There is no great outcome for you in a migration. No one got an MD promotion, a 7 figure bonus for running a migration.
Fine you won’t cut your losses. Maybe the market is terrible, family reasons, maybe you got hired to do this job and couldn’t find anything better. Whatever. Let us look at the next two strategies.
Strategy 2: Defend by Committee
Find out every system that touches the database. Then find every other system that touch those systems. By now you should have a large pool of “blame sharers” — your migration committee. Start including them in meetings for the large migration project. Hire a diligent project manager a.k.a your pit bull. Start putting together an extremely detailed list of tasks that move business logic from the database to their systems. Most of the deliverables should be owned by other stake holders. Put together a master plan that is owned by the committee. Make sure that few if any items that actually come under your responsibility are on the critical path. If you do need to do a large body of work, hire an army of consultants. Use their skills to create a long stretching project plan with business impacting deliveries starting in year 2 at least.
For the next 1 year you have two main jobs. First, Sending status report to C- suite every week showing mostly Green and an occasional Orange on the tracker, all smiles and roses. Second, networking and finding another job.
Eventually the progress will be endemic, lots of issues and side-effects will show up, and somebody will get canned. Try to have low visibility and assume the role of project advocate in the mid to late stage of the project .. abdicating responsibilities to the aggressive, clueless folks early on usually helps. Who knows, you might be able to “transition” to a better role based on early successes and dodge that bullet.
Strategy 3: The Smart Solution
I experienced this solution while working with Tasos in a few places. I eventually wrote this for a project that I picked up around 2016. We have now baked this into the standard migration approach in Foreseer as part of migrating clients to our platform. Foreseer is our data extraction and enrichment platform targeted towards semi-structured data like company filings, Twitter feeds, LinkedIn Feeds and more. More details at: https://www.foreseer.ai.
This strategy is implemented with far more rigor as part of a validation / transformation / aggregation general purpose solution in Terahelix — http:// www.terahelix.io.
The solution is simple and elegant:
Map out all database objects and their foreign key relationships into an object model in the language of your choice.
Take any ANSI SQL compatible Parser and build parse trees on the fly for incoming SQL statements.
Generate Python or Java or whatever code based on the query structures you are seeing.
Write a Database driver (or use Aspect Oriented Programming to intercept the calls) and switch out the database query to a call to your generated code.
Peer review the generated code, clean it up, make it more efficient etc.
Track the usage of the generated code in a prod parallel environment (forward all queries to the fake database) and ensure that the outcomes are consistently the same.
Start cutting over query invocations by using a Facade to redirect based on the incoming query to either the actual database or your fake database layer.
Watch it like a hawk! Things will break .. make sure you always update the original datastore as new data comes and and you can revert query by query without needing to restart any system.
Terahelix is a state of the art platform for implementing a robust, cloud-native data management platform and family of applications. Be it data validation or financial applications like Risk Based P&L or Regulatory reporting — I haven’t seen a better platform than Terahelix.
If you want to get more details on this solution or need our help in building this out reach out to either firstname.lastname@example.org or to email@example.com. If I get enough interest I might put together a working prototype on this and release it to the wild as a demo. So share, like and comment.