Migration of Data

Question

Migration of Data

SVA 121

Hello,

What is is the best and most efficient (performance wise) method to migrate of full data from large database to small multiple database (microservices) during cutover.

Thanks

Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-26T20:49:09.1833333+00:00

You need to describe in a lot more detail what you want to do. But just a warning: the answer is still likely to be it depends. There are so many things that can tilt the answer in different directions.
Olaf Helper 47,581 Reputation points

2025-06-27T05:37:05.3033333+00:00

Are there Primary-Foreign-Key constrains between tables, as it's best practice? Then it will be a challange to split it over several databases.
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-27T20:58:31.09+00:00

Are there Primary-Foreign-Key constrains between tables, as it's best practice? Then it will be a challange to split it over several databases.

If you want to keep the constraints enforced, yes. But we know very little what SVA want to do. Do they want to partition all tables of over small databases. Or do they want to put some tables in database A, others tables in database B and so on?
SVA 121 Reputation points

2025-06-29T09:27:09.5266667+00:00

There is Primary-Foreign-Key constrains between tables and table without that also. Need to move data from same table in one DB to another DB or some case need to move some columns of table to another tables in different DBs
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-29T21:07:06.18+00:00

There is still a lot more that I need to know before I can give answer with confidence.

You mentioned something about a cutover. During this cutover, how long can you afford to have downtime for a longer period, for instance over the weekend?

And how much data are we talking about?
SVA 121 Reputation points

2025-06-30T06:47:24.1466667+00:00

DB of size 2 TB and need to reduce the final cutover downtime time by doing sync between the old structure and new DB structure
Singam shetty Sri ganesh 80 Reputation points Microsoft External Staff Moderator

2025-12-01T04:41:23.2466667+00:00

Hi @SVA,

Thank you for posting your query, and thanks to @Erland Sommarskog for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Singam shetty Sri ganesh 80 Reputation points Microsoft External Staff Moderator

2025-12-04T10:15:09.4833333+00:00

Hi @SVA,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

2 answers

Your answer

Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-26T20:49:09.1833333+00:00

You need to describe in a lot more detail what you want to do. But just a warning: the answer is still likely to be it depends. There are so many things that can tilt the answer in different directions.
Olaf Helper 47,581 Reputation points

2025-06-27T05:37:05.3033333+00:00

Are there Primary-Foreign-Key constrains between tables, as it's best practice? Then it will be a challange to split it over several databases.
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-27T20:58:31.09+00:00

Are there Primary-Foreign-Key constrains between tables, as it's best practice? Then it will be a challange to split it over several databases.

If you want to keep the constraints enforced, yes. But we know very little what SVA want to do. Do they want to partition all tables of over small databases. Or do they want to put some tables in database A, others tables in database B and so on?
SVA 121 Reputation points

2025-06-29T09:27:09.5266667+00:00

There is Primary-Foreign-Key constrains between tables and table without that also. Need to move data from same table in one DB to another DB or some case need to move some columns of table to another tables in different DBs
Erland Sommarskog 128.6K Reputation points MVP Volunteer Moderator

2025-06-29T21:07:06.18+00:00

There is still a lot more that I need to know before I can give answer with confidence.

You mentioned something about a cutover. During this cutover, how long can you afford to have downtime for a longer period, for instance over the weekend?

And how much data are we talking about?
SVA 121 Reputation points

2025-06-30T06:47:24.1466667+00:00

DB of size 2 TB and need to reduce the final cutover downtime time by doing sync between the old structure and new DB structure
Singam shetty Sri ganesh 80 Reputation points Microsoft External Staff Moderator

2025-12-01T04:41:23.2466667+00:00

Hi @SVA,

Thank you for posting your query, and thanks to @Erland Sommarskog for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.
Singam shetty Sri ganesh 80 Reputation points Microsoft External Staff Moderator

2025-12-04T10:15:09.4833333+00:00

Hi @SVA,

Hope everything’s going great with you. Just checking in has the issue been resolved or are you still running into problems? Sharing an update can really help others facing the same thing.

Thank you.

Answer 1

Since you are not keen on sharing information, I will only answer by listing some points to consider:

To reduce time, run things in parallel. This means that you need to identify operations that run independently without blocking each other and put these in separate scripts.
Run all copying with non-clustered indexes and foreign keys disabled. Re-enable them in the final step.
For larger amounts of data, run things in batches. It's important that your batching conditions follow the clustered index.

It goes without saying that you need to rehearse and test the scripts you develop.

I am assuming here that the new databases are on the same server as the source database.

Answer 2

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Migration of Data

2 answers

Your answer