Proper way of copying larger data from another Azure database?

Question

Being the 3rd party that is allowed to read from the Azure SQL (in cloud), we need to simulate access to the data as in the older version of the ERP that used plain old MS-SQL server. Only the subset of data is to be "mirrored" to our application database (on the same Azure SQL server). Some of the things were solved; however, the custom mirroring is affected by all the things. So, some introduction to situation...

The source tables through source views: The new version of the ERP adds the custom fields as contained in the extra tables. To simulate the older structure, we were allowed to create views that join the base and extra tables. The view was given the name that was used on the plain old SQL server by the older version of the ERP.

For example, the older table was named [Sales Invoice Header], the new implementation uses the [Sales Invoice Header$base] and the [Sales Invoice Header$extension]. We have created the view named [Sales Invoice Header XXX] (the XXX is the suffix to mark it as part of our solution; so, the developers of their ERP are not surprised where it came from).

The external tables: In Azure SQL, to access the data from another database, one have to create the external table (and some other things) to create a "key hole" to see the data in the other database. So, we have created the external tables with the same name as our views in the other database.

For example, we have created the external table (in our database) named the [Sales Invoice Header XXX].

The helper views: In our database, we further create the helper view that wrap the external tables. They are given the same name as were the tables in the older ERP, and they are renaming some of the fields to be closer to the older ERP. The reason is that we had a lot of SQL code for getting the data from the older ERP. Sometimes, the fields are renamed to be closer to the older solution.

For example, we now have the [Sales Invoice Header] view.

Trying to separate the solutions: The access through the above "key hole" is slow. We also want to separate the ERP from our application to minimize the performance impact to the ERP. Because of that, we copy the needed data from the ERP to the tables in our database. So, we use the "data pumps" (user stored procedures) called by the separate application (called through the Windows scheduler).

Copying the data to our tables: There is basically no problem with small tables like code-lists. However, we need to copy also larger tables that can even be changed in time. Think about the [Sales Invoice Header] and even bigger [Sales Invoice Lines]. In the case, the dedicated data pumps are scheduled to be executed in the night. Only the recent history (like two years) is copied; anyway, it is still a lot of data, and we would like to avoid blocking the ERP -- even in the night hours.

Splitting the number of rows to batches: Learning on "how to do that properly", I have found various solutions that basically use the WHILE loop to copy the rows in portions (unlocking the source of the date after the portion to give the chance to others). We did something similar even with the plain-old-SQL solution (it was much easier).

Currently, I have studied the Copying Large Tables Efficiently In SQL Server, Last Updated on May 16, 2022 by Erik Darling. However, the Azure SQL adds more questions.

No access to the Azure SQL tools... As our access rights to the Azure SQL database is limited, we have no access to the tools. For example, we cannot see the execution plans... Moreover, the Azure SQL requires other (new) skills that we do not have. Because of that, I am asking here...

Any articles on proper ways of copying between Azure SQL databases? Can you point me to some? Or can you summarize some of your own experience?

Some of the observed problems: When trying to use the classic approach to the situation, we can observe, for example:

• The original table has the clustered index. The extension table also has the clustered index -- the same PRIMARY KEY. However, the index is "lost" when you JOIN the tables in the view. I am aware that the classic views can have an index. Anyway, I have no personal experience with that, and I do not know how it would work through another external table (the "key hole") and another view (to rename the fields).
• The ORDER BY seems to be very slow (no surprise) when applied in the context of the target database.
• Probably because of that, some of the "tricks" do not work well.

Answer 1

Use SQL Data Sync and copy the table [Sales Invoice Header] to another database.

Now you can query that table without stalking the main ERP.