Member Avatar for get2tk

Hi,

I was told to build a application that will encrypt data and migrate data from one database platform to another platform.

Please i do not hae a clue where to start from as i am a newbie to database.

Any info will be well appreciated

  • 3 Contributors
  • 3 Replies
  • 111 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

For a first design pass: treat encryption as a separate, testable layer and keep migration logic focused on safe, restartable movement and verification. needs a clear decision on whether the app must protect data in transit, at rest, or both — and whether encrypted values still need to be searchable. Those choices drive the rest of the design.

Key design points that are often missed in short posts by others here (for example and ): use envelope encryption (a short-lived data key per batch/row, encrypted with a master key) and AEAD ciphers (AES-GCM or ChaCha20-Poly1305) so tampering is detectable. Never hard-code keys. Use a KMS or HSM for master-key storage and record the key-id and IV/nonce alongside each encrypted record so recovery is possible. Prefer TLS for the transport layer so nothing travels in the clear between source and your app.

Make the migration restartable and accountable. Process in small, idempotent batches, store a digest (SHA-256) per batch or row, and write progress metadata to a staging table. Keep logs of failures and retries. For very large fields, stream and encrypt in chunks rather than buffering full values in memory.

Small workflow example (pseudocode):

while source.hasMore():
  rows = source.fetchBatch(N)
  for r in rows:
    dek = randomKey()
    iv = randNonce()
    r.enc = AEAD_encrypt(dek, iv, r.sensitive)
    meta = encryptWithKMS(masterKey, dek)
  target.batchInsert(rows with r.enc and meta.keyId, meta.encryptedDEK, iv, checksum)
  commit()

Validate thoroughly before cutover: compare row counts, sample decrypted values, and verify stored checksums. Test recovery: rotate keys, restore a backup, and decrypt a sample. Plan a rollback window and test on a copy of production before touching live data.

Recommended Answers

All 3 Replies

Member Avatar for debasisdas

First of all you need to understand the complete architecture of both the source and target database.

Member Avatar for AdeelM

get2tk, you first need to identify what tables you want to migrate from Microsoft's SQL Server to Oracle. Once you have identified these tables, create these tables on the Oracle table exactly like they are on SQL Server (on SQL server, explore the attributes and attribute type of each table, and make an exact equivalent one on Oracle). Then for each one of these tables, "dump" (export) the data from SQL Server. I recommend exporting it as a CSV file. Then on Oracle, once you have created the tables, use a script of some sort to import the CSV file into each table. The data will not be encrypted in the CSV files but perhaps you can use a compression program for the CSVs that encrypts the files. Here is a helpful link: http://msdn.microsoft.com/en-us/library/ms141209.aspx. Good luck.

Member Avatar for debasisdas

You need to do a lot of other work before starting actual data migration.

1. Field mapping.
2. checking for data type compatibility
3. how to handle LOB columns

etc...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.