0

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.
2
  • 1
    Why on earth do you need order by when copying data?
    – mustaccio
    Dec 20, 2023 at 17:49
  • @mustaccio: The data is not to be copied at once. Instead, something like 10000 records is to be copied using one INSERT-SELECT to prevent long locking of tables. One of the techniques relies on copying in the order (of the cluster index). Then the next loop can get the last copied key. This is the reason of my last two points.
    – pepr
    Dec 21, 2023 at 8:57

1 Answer 1

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.

4
  • Thanks, Francesco for the suggestion. I was considering that option in the past. However, we do not have rights to set the mechanism. Secondly, we do not want to synchronize all the data -- rather the subset (limited space of the target database). Moreover, we need to pre-process the source data (like joining the tables and renaming) without using too much space.
    – pepr
    Dec 21, 2023 at 9:34
  • Can you connect to the database through SSMS? can you Select? What privilege your user has? Dec 21, 2023 at 9:49
  • Yes, I can connect through SSMS. I can also create some objects in the source database (like the views). Anyway, I want to be not invasive to the source database (not to cause problems). I am the owner of the target Azure SQL (cloud) database; so, I am able to create the external tables (with the things necessary to connect them). Basically, I want to be restricted to SELECT from the source database, but the source database also have timestamps (binary(8)) and systemID's of the records.
    – pepr
    Dec 21, 2023 at 9:54
  • Are both databases on Azure? Is the vendor aware of you doing this? Because I don't understand why you don't have full privileges Dec 21, 2023 at 20:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.