Copy data from a database to another

Question

I am new to Azure so I will be posting a lot here.

I have 2 databases DBA and DBB

I created the same table Employee, suppose it has 2 columns (EmpID int, FullName nvarchar(255) ).

I want to copy the data from DBA to DBB (daily)

I checked this solution: https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-getting-started-vertical?view=azuresql

So I thought about creating a view. When I run simple select * query without any conditions, it works fine, but whenever I add a condition like EmpID > 9999, I get a timeout.

What is wrong with my approach ?

Answer 1

Even if Azure SQL databases aren't primarily designed for cross-database operations, the elastic query feature bridges this gap, with remote access to different database tables.

In reality what happens with your WHERE condition is that Azure SQL will push it to the remote table so it is important that in the remote tables the indexes are enable and are optimizing the queries.

3 tips to put in mind when using Azure SQL tables :

• Stay clear of varchar(max) and varbinary(max) columns.
• Refrain from accessing remote tables without specific filters.
• Recognize that there is a substantial overhead bridging databases.
• The more local data you use in JOIN operations, the worse the performance becomes.

In your case you can use a stored procedure in the remote database that provides only the needed result set:

select @cmd = N'sp_AnStoredProcedureName ' + @subid_list

EXEC sp_execute_remote @data_source_name = N'YourElasticDBQueryDataSrc', @stmt = @cmd;

Here are some links to help you :

https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/

https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?view=azuresql

https://learn.microsoft.com/en-us/azure/data-factory/tutorial-incremental-copy-overview?wt.mc_id=DP-MVP-4015656