Migration of Data from one Database to Multiple Databases

Question

Migration of Data from one Database to Multiple Databases

vamshi.sql

SSCommitted

Points: 1765
More actions
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, 5 months ago by vamshi.sql.

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

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

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

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

WHug Ten Centuries Points: 1248 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 3

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)