What are possible solutions to do per-request or on schedule 1-way sync of one SQL Server database to the other in Azure? Both DBs are configured to allow access only via private endpoints.
I've just started exploring options, appreciate expert's opinion on the question.
1-way replication, incrementally data sync and on schedule configuration -- Azure Data Factory is the most suitable service to achieve the following requirements.
Using ADF, you can incrementally load data from multiple tables in a SQL Server Database to one or more databases in another or same SQL Server by creating pipeline using Copy activity. You can also schedule the pipeline trigger based on your requirement.
This official tutorial from Microsoft on Incrementally load data from multiple tables in SQL Server to a database in Azure SQL Database using the Azure portal will help you to create the ADF environment using Linked Service, Datasets, and copy Activity to accomplish your requirement (skip setting Self hosted Integration Runtime as it is required when one of your database is in on-premises).
Once your pipeline has been created, you can schedule it by creating New Trigger. Follow Create a trigger that runs a pipeline on a schedule to create new trigger.
