4 ms·

Potentially dumb question: I've done front-end (iOS) my entire career. If I wanted to go from 0 to being able to do a SQL DB migration in production without dis

by xenihn 7y ago

Potentially dumb question: I've done front-end (iOS) my entire career. If I wanted to go from 0 to being able to do a SQL DB migration in production without disaster, what should I do? I saw https://www.masterywithsql.com/ https://www.masterywithsql.com/ posted on HN a few months ago, and I'm planning to finally start it over break. But what do I use after that?

hrgiger 7y ago
Probably someone will throw a better answer for DB migration, but the problem is not DB migration here, TBH db part seems to me the easiest part but what they did is migrating entire stack not by part by part, without pilots and using poor testing. Migrating whole stack at one click is nuclear I believe poor planning had a great role to force them to get it done.
tzmudzin 7y ago
That‘s a good start, but may even be an overkill for your goal. The trick is not in the application of highly complex SQL. The trick is in the PROCESS — making it robust, testable, traceable, and reversible for every object despite the complex dependencies. And then indeed validating every step went well, with the validation depth depending on the objects criticality (OK return code vs check of record counts vs check of totals on fields vs check of totals per category etc) If you‘re really into it, consider the use of automation and tracing IDs for individual loads. If you‘re worried about the time it takes despite automation: work on the bulk of the data, but use DB triggers to record delta from your snapshot, then treat that „sidecar“ accordingly. Then: do at least one end-to-end test. Learn from the mistakes at every single stage, and act on what you‘ve learned.
readonly 7y ago
- pick a relational database (ie: mysql or postgres) - learn how to backup the database (I suggest via the official documentation) - restore that backup (on a different environment) - validate the restore is complete (compare the data) - if the backup and restore are good, now you can start learning how to migrate without the stress that you'll lose historical data
- tzmudzin 7y ago
  You‘re absolutely right, but we all can be talking about different subjects here: - database restore - database migration (your case) - data migration to a different platform and presumably different data model (the bank‘s case, or my answer here) Not sure which case the question was reffering to, though...
  - xenihn 7y ago
    How about all 3 :)
- interblag 7y ago
  This is 100% the way to learn this, and a great answer to the original question. Should also emphasize though that for a migration of the scale discussed in this article doing a SQL backup + restore is almost certainly not the right approach. At this kind of scale + uptime requirement, you'd want to do a migration like this gradually - hydrating the new system with data and keeping it up-to-date with changes for a period of weeks/months while doing testing + validation (and ideally also doing a gradual cutover, although that might not be realistic given banking infrastructure/application design). Probably the relevant approaches to look into after reviewing basic backup/restore are things like log-shipping or change-data-capture, although choosing the right approach would be highly dependent on the underlying technology, architecture, and requirements...

by xenihn 7y ago

hrgiger 7y ago

Probably someone will throw a better answer for DB migration, but the problem is not DB migration here, TBH db part seems to me the easiest part but what they did is migrating entire stack not by part by part, without pilots and using poor testing. Migrating whole stack at one click is nuclear I believe poor planning had a great role to force them to get it done.

tzmudzin 7y ago

That‘s a good start, but may even be an overkill for your goal. The trick is not in the application of highly complex SQL. The trick is in the PROCESS — making it robust, testable, traceable, and reversible for every object despite the complex dependencies. And then indeed validating every step went well, with the validation depth depending on the objects criticality (OK return code vs check of record counts vs check of totals on fields vs check of totals per category etc) If you‘re really into it, consider the use of automation and tracing IDs for individual loads. If you‘re worried about the time it takes despite automation: work on the bulk of the data, but use DB triggers to record delta from your snapshot, then treat that „sidecar“ accordingly. Then: do at least one end-to-end test. Learn from the mistakes at every single stage, and act on what you‘ve learned.

readonly 7y ago

- pick a relational database (ie: mysql or postgres) - learn how to backup the database (I suggest via the official documentation) - restore that backup (on a different environment) - validate the restore is complete (compare the data) - if the backup and restore are good, now you can start learning how to migrate without the stress that you'll lose historical data

tzmudzin 7y ago
You‘re absolutely right, but we all can be talking about different subjects here: - database restore - database migration (your case) - data migration to a different platform and presumably different data model (the bank‘s case, or my answer here) Not sure which case the question was reffering to, though...
- xenihn 7y ago
  How about all 3 :)
interblag 7y ago
This is 100% the way to learn this, and a great answer to the original question. Should also emphasize though that for a migration of the scale discussed in this article doing a SQL backup + restore is almost certainly not the right approach. At this kind of scale + uptime requirement, you'd want to do a migration like this gradually - hydrating the new system with data and keeping it up-to-date with changes for a period of weeks/months while doing testing + validation (and ideally also doing a gradual cutover, although that might not be realistic given banking infrastructure/application design). Probably the relevant approaches to look into after reviewing basic backup/restore are things like log-shipping or change-data-capture, although choosing the right approach would be highly dependent on the underlying technology, architecture, and requirements...