Hello Experts, We need your help to minimize time for data migration from Oracle to SQL. Example, there exist a table in Oracle with 100 Million records and its been three days and session is still executing in SQL server What we have done ? Created a linked server to Oracle Used openquery to insert records to SQL table Kindly advise, what can be the best option to deal with this case. Many thanks in advance ! Regards, Mohsin/Naren

And how does that query look like? Are you trying to insert all those 100 million rows in one statement? When you are trying to copy that much data it is imperative that you do it in batches. So if you want to do it with OPENQUERY, take like five million rows at a time. Define your batches in such a way that there is an index to follow on the Oracle side. Or, well, hm, for SQL Server the best is to use a clustered index, but clustered indexes are not very hip on the Oracle side. But maybe Oracle permits you to read by row id or something like that? In any case, you don't want a scan for each batch. But I would also look into export all to files and import them to SQL Server with BCP or BULK INSERT. Both these permits you to set a batch size, so that there is a commit after each five million rows or whatever.

An option is to use SQL Server Migration Assistant for Oracle (OracleToSQL)

Oracle to SQL data migration is taking too much time | Need Help

Mohsin M Patel 0

Hello Experts,

We need your help to minimize time for data migration from Oracle to SQL.

Example, there exist a table in Oracle with 100 Million records and its been three days and session is still executing in SQL server

What we have done ?

Created a linked server to Oracle
Used openquery to insert records to SQL table

Kindly advise, what can be the best option to deal with this case. Many thanks in advance !

Regards,

Mohsin/Naren

LucyChenMSFT-4874 3,470 Reputation points

2024-07-08T06:20:48.5433333+00:00

HI @Mohsin M Patel ,

Thank you for reaching out and welcome to Microsoft Q&A!

Please refer to the link which Erland provided. If you find it is useful, please remember to mark it as the accepted answer. If you need any additional information, please feel free to share your issue here.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Best regards,

Lucy Chen
LucyChenMSFT-4874 3,470 Reputation points

2024-07-11T02:01:02.5766667+00:00

HI @Mohsin M Patel ,

Is this issue solved now? If you have any questions, please feel free to share with us.

May I humbly suggest that you accept Answer if they helped. It may help others who see the thread in seek of help.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Best regards,

Lucy Chen
LucyChenMSFT-4874 3,470 Reputation points

2024-07-22T06:25:08.45+00:00

HI @Mohsin M Patel ,

I hope the answers below is helpful. If you find any of them useful, please remember to mark them as the accepted answer. If you need any additional information, please feel free to let us know.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Best regards,

Lucy Chen

2 answers

Erland Sommarskog 107.2K Reputation points

2024-07-06T08:31:07.8633333+00:00

And how does that query look like? Are you trying to insert all those 100 million rows in one statement?

When you are trying to copy that much data it is imperative that you do it in batches. So if you want to do it with OPENQUERY, take like five million rows at a time. Define your batches in such a way that there is an index to follow on the Oracle side. Or, well, hm, for SQL Server the best is to use a clustered index, but clustered indexes are not very hip on the Oracle side. But maybe Oracle permits you to read by row id or something like that? In any case, you don't want a scan for each batch.

But I would also look into export all to files and import them to SQL Server with BCP or BULK INSERT. Both these permits you to set a batch size, so that there is a commit after each five million rows or whatever.
Please sign in to rate this answer.
Mohsin M Patel 0 Reputation points

2024-07-06T08:45:00.8433333+00:00

@Erland
Thank for reply, I am using the below to checkpoint every 2hours

EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'max degree of parallelism', 10;

RECONFIGURE;

DECLARE @StartTime DATETIME;

SET @StartTime = GETDATE();

WHILE DATEDIFF(HOUR, @StartTime, GETDATE()) < 2

BEGIN

INSERT INTO dbo.Table1

SELECT * FROM OPENQUERY(AUSTMIG, 'SELECT * FROM Table1')

-- Checkpoint every 2 hour IF DATEPART(MINUTE, GETDATE()) % 120 = 0 BEGIN CHECKPOINT; END

END

Erland Sommarskog 107.2K Reputation points

2024-07-06T08:54:20.7633333+00:00

I will have to assume that this is not the actual code, because the above code is, you will have to excuse me, quite a bit of nonsense. You are copying the same table over and over again into the same table on the SQL Server. And if a INSERT statement would happen to complete on a full hour, you would do a checkpoint - whatever the point with that would be. And then you would stop after two hours. Or, well, that could be after one hour and five minutes, all depending at what time of the hour you started

Then again, you said that the source table has 100 million rows, and this has been running for three days, so then I guess that it has been running that INSERT statement has been running for three days, and that CHECKPOINT statement is never executed. Not that it would really matter, since SQL Server is taking care of that itself.

Mohsin M Patel 0 Reputation points

2024-07-06T13:25:45.4333333+00:00

@Erland
Ths above code is the actual Code we are using with Parallelism. When we used a smaller set like 100,000 it took 2 mins and for 1 million records it took 20mins. And we verified the records were as per the expectation. With that calculation it should take above 30+ hours. But it never completes.
We tried Migration with SSIS Package as well as SSMA but for big tables it failed. Then we opted for Linker Server.
My requirements here is:

Process to do the Migration from Oracle to SQL Server

Is there a Quicker process

Erland Sommarskog 107.2K Reputation points

2024-07-06T21:33:26.6833333+00:00

Ths above code is the actual Code we are using with Parallelism.

Again, the code above is a piece of nonsense. You loop for one to three hours copying the same table over and over again.

And, no, you are not using parallelism. I see that you set the configuration option max degrees of parallelism, but an INSERT SELECT OPENQUERY will not use parallelism. At least not on the SQL Server side.

Copying 100.000 rows is a walk in the park. So is one million. Copying 100 million rows is not. It is an advanced operation that requires careful planning and understanding. And in this particular case, it requires good knowledge of both SQL Server and Oracle. And if you wish to use SSIS, you do of course need to know SSIS. Before I move on, I should say that myself I only qualify for the first point. I have not worked with neither Oracle nor SSIS.

As I mentioned you need to split up the work in batches. If you try to copy 100 million rows in a single statement, you are likely to saturate resources in Oracle, in SQL Server as well on the network.

As it happens, I have a talk about splitting work in chunks, which you can view here: https://www.youtube.com/watch?v=fCme4dTgCsc If you watch it, you will learn a few basic things for your problem, but you will still have a long way to go.

When it comes to parallelism, you could identify a small number of partitions in the source table, say six. Then you have one loop like that for each partition, and then you run these loops in parallel. Again, this is not a trivial operation, but an advanced strategy. Thus, it requires that you have the skills to implement it. (Given the orchestration needed, using SSIS to drive this is probably a good idea.)

LucyChenMSFT-4874 3,470 Reputation points

2024-07-23T02:24:26.2833333+00:00

HI @Mohsin M Patel ,

I hope the answers below is helpful. If you find any of them useful, please remember to mark them as the accepted answer. If you need any additional information, please feel free to let us know.

Your action would be helpful to other users who encounter the same issue and read this thread. Thank you for your understanding! :)

Best regards,

Lucy Chen
Sign in to comment
Olaf Helper 43,331 Reputation points

2024-07-08T06:58:15.52+00:00

An option is to use SQL Server Migration Assistant for Oracle (OracleToSQL)
Please sign in to rate this answer.
Naren 25 Reputation points

2024-07-08T09:01:28.5+00:00

We tried this but it doesn't seems to be effective for large tables.

Mohsin M Patel 0 Reputation points

2024-07-08T14:21:30.8866667+00:00

@Olaf
We used the SSMA with 10 parallelism and we are successful with tables that are not that big, here the question if to migrate tables that are huge.

Mohsin M Patel 0 Reputation points

2024-07-08T14:26:36.98+00:00

@Erland Svingen
Below is the Actual Script i am using for achieving this in chunks(1 million at a time), But is still running from last more than 4 hours. Although, when I ran for 1 million it took 20mins. but this below is still running.

DECLARE @start INT = 1;

DECLARE @end INT = 1000000;

DECLARE @chunkSize INT = 1000000;

DECLARE @maxRowNum INT;

DECLARE @sql NVARCHAR(MAX);

-- Determine the maximum row number in the source table

SELECT @maxRowNum = MAX(rownum) FROM OPENQUERY(<LinkedServer>, 'SELECT rownum FROM Table1');

WHILE @start <= @maxRowNum

BEGIN

BEGIN TRANSACTION; -- Form the dynamic SQL command SET @sql = ' INSERT INTO dbo.Table1 SELECT * FROM OPENQUERY(<LinkedServer>, ''SELECT * FROM Table1 WHERE rownum BETWEEN ' + CAST(@start AS VARCHAR) + ' AND ' + CAST(@end AS VARCHAR) + ''')'; -- Execute the dynamic SQL command EXEC sp_executesql @sql; -- Commit the transaction COMMIT TRANSACTION; -- Update the start and end for the next chunk SET @start = @end + 1; SET @end = @end + @chunkSize; -- Adjust the end if it exceeds the maximum row number IF @end > @maxRowNum BEGIN SET @end = @maxRowNum; END

END

Erland Sommarskog 107.2K Reputation points

2024-07-08T20:37:28.73+00:00

First of all, did you run the above logic with a smaller table, so that you know that the logic is correct? (The logic looks good to me, but I have been wrong before.)

Next, it's a great it idea to add something like:

RAISERROR ('Inserted %d rows and time is %s', 10, 1, @totrowcount, @currentimeasastring) WITH NOWAIT

If you don't have WITH NOWAIT, the output will be buffered, so you will not see the progress. (Alas, SSMS itself will start buffering after some time.)

But you can also run

SELECT SUM(rows) FROM sys.partitions WHERE object_id = object_id('YourTable') AND index_id IN (0,1)

to get the current rowcount in the target table.

Finally, what query plan do you have on the Oracle side. This rownum is this a column in the table, or is some internal Oracle thing? What you want is a sequential read of the exact rows without any key lookup. What you absolutely do not want is a full scan on the source table.

Also, what indexes do you have on the target table? You should only have the clustered index, and maybe only if it aligns with rownum.
Sign in to comment

Share via

Oracle to SQL data migration is taking too much time | Need Help

2 answers