I have a use case where my SQL Server is installed on a VM on Azure (or we may end up using Azure SQL Server). We want to copy few tables from the DB to multiple on premised databases. The schema of the on premise databases are guaranteed to be same as the source.
I have tried, SQL Data Sync, Azure dataflow etc. For SQL Data Sync we need to install a Sync Agent on the on premises database servers which is not ideal. For Azure dataflow, I tried testing it with a new database table, the job succeeds but the data is not copied.
The basic diagram looks like below:
Can someone please help me with a solution that I can use?
You should look into MSSQL - Linked Server. It doesn't require any agents or CLI to set up and works with sub-second linking. This isn't ideal for a live backup service, or a replication.
https://learn.microsoft.com/en-us/sql/relational-databases/linked-servers/create-linked-servers-sql-server-database-engine?view=sql-server-ver16
For replication, I would look at data pools. There's a starting point with Big Data pools for MSSQL 2019, but it will be deprecated in 2025.This gives you a year to migrate though.
https://learn.microsoft.com/en-us/sql/big-data-cluster/concept-data-pool?view=sql-server-ver15