Moving data between Azure SQL databases

Question

Moving data between Azure SQL databases

King Java 790

I am trying to come up with a best way to move data between two/three databases within same Azure SQL.

I am talking about inserting new data to about 20+ tables daily.

Basically, it is using Insert statement from one or two databases to other database.

Is is possible just running Stored Procedure to push data?

Or do I have to use Azure Data Factory to do this?

Accepted answer

2 additional answers

Your answer

Answer 1

Hey,

There are multiple ways to sync data between tables between 2 Azure SQL databases:

Create external tables( Elastic queries) to read data from another database and use SP to copy from 1 database to another. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview?view=azuresql ANyhow you would need an orchestrator to trigger the SP and also the performance is not great w.r.t elastic query with large amount of data
You can use ADF/SYnapse pipelines/Fabric pipelines to sync data between 2 tables
You can use data sync : https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql

Answer 2

Olaf Helper 47,436

Azure SQL Database don't support cross-database-access, so you can't solve it in plain SQL. You have to use ADF = Azure Data Factory. https://learn.microsoft.com/en-us/azure/data-factory/

King Java 790 Reputation points

2024-02-14T21:29:11.39+00:00

@Olaf Helper Thank you for your feedback.

I have set up one ADF like that and I was using External Table to do this. Do I have to create External Tables for each and all pipelines?
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2024-02-15T03:29:42.3966667+00:00

In case if you are using ADF, no need to create external tables. you can use the table as is as source
King Java 790 Reputation points

2024-02-15T06:25:08.93+00:00

@Nandan Hegde If I use ADF (to insert data with a condition or some logic), I ended up using Stored Procedure. That means, I have to create External Tables, no? How do insert data with some logic just between one table to another table?
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2024-02-15T06:29:02.8+00:00

Sample : https://www.modern-dataengineering.com/post/how-to-copy-multiple-tables-in-azure-data-factory You can use Query in source settings. No need to create external tables you can directly copy data from source to sink using COpy activity
King Java 790 Reputation points

2024-02-15T06:38:39.6666667+00:00

@Nandan Hegde If I use insert statement (not copy), I could still use the example on the top? Thanks.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2024-02-15T06:44:49.83+00:00

you cannot use insert statements with cross database unless you use extrenal tables. The use of ADF is to avoid extrenal tables. Rather than insert statement, use the Select statement wuthin the source that you would have used within the insert statement
King Java 790 Reputation points

2024-02-15T17:48:34.7833333+00:00

@Nandan Hegde I am sorry. Can you give me some example of using "Select statement within the source that you would have used within the insert statement"? I am trying to get a better picture where Select statement should be located.
Are we talking about creating a SQL View first (using condition) at destination table and just pull that data straightforward? I guess that is what you are talking about: Just select some range and copy data, correct?

Thanks.
Nandan Hegde 36,146 Reputation points MVP Volunteer Moderator

2024-02-16T03:38:04.3133333+00:00

Correct, the source dataset should have query (query equivalent to the data that you intend to copy into the sink ; no need to create a view ; the above image select statement is sufficient) and the sink should be the table in the destination database

Answer 3

Alberto Morillo 34,671 MVP Volunteer Moderator

You can do that using Elastic Queries, with them you can query an Azure SQL Database from another Azure SQL Database. However, with Azure Data Factory (ADF) you may have a better performance. You can do incremental loading of data using ADF.

Share via

Moving data between Azure SQL databases

2 additional answers

Your answer