Post reply

Migration of Data from one Database to Multiple Databases

  • May 17, 2022 at 12:53 pm

    #4034271

    Hello,

    I have a requirement to migrate the data from existing AWS RDS DB Server (A) to new AWS RDS DB server (B).

    Example:

    DB Server (A) is having one database with 30 tables (All tables are inter linked with foreign key constraints). Requirement is to create 5-6 databases in new DB server (B) and include 30 tables into specific databases (Audit related tables should be considered and included into one database, Payments related tables in another database Etc.,) and then migrate the data from (A) to (B). Please suggest how to migrate the data with foreign key constraints in source.

    Thank You.

     

    • This topic was modified 2 years, 4 months ago by  vamshi.sql.

  • May 17, 2022 at 4:33 pm

    #4034427

    Instead of migrating, I'd just restore the database 6 times under 6 different names, then drop what isn't needed from each of them.

    If you have foreign key constraints between tables that will exist in different databases after the move, then you'll just have to drop them.

     

    Eddie Wuerch
    MCM: SQL

  • May 18, 2022 at 3:36 pm

    #4035064

    Presuming the AWS RDS chosen engine is SQL Server, foreign keys do not work between databases.  To satisfy the stated requirement the foreign keys will need to be replaced with triggers.

    I would suggest to post the full situation in a new post, describing the issue that is forcing the requirement to break up the database.  The community may come up with a better solution.

     

    --Will

  • May 18, 2022 at 5:26 pm

    #4035130

    FK's to other databases also don't work in "normal" on-prem servers.  And, we don't need a separate post on this. 😉  We do need a bit of a better description, as suggested, though.  In this case, a graphic may replace a thousand words and questions.

    You can create synonyms in one database that point to tables in another and they will work just fine as if the table were in the local database EXCEPT for things like FK's.  Like Will stated, if you need to keep DRI going across databases, you'll need to enforce that with triggers.  You can possibly do some of the checking with CHECK constraints but not everything.  And, you need to make sure that your CHECK constraints don't reference scalar UDF's or all code will be relegated being single threaded and possibly worse.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply