I need to update the Azure SQL Server table if another SQL Server tables is updated.
For example: I have one Azure SQL table dbo.Azuretable and I also have a SQL Server table dbo.sqlservertable. Both these tables are part of two different SQL Server instances, but the table schema is exactly the same.
What happens is dbo.sqlservertable updates on daily basis, let's say every day one row inserts in dbo.sqlservertable, I want the same row to be inserted into dbo.Azuretable as soon as dbo.sqlservertable gets updated. I want to do it Azure if possible or I am open to any other method as well.
What I have done so far:
I created one copy activity which replicates the changes to dbo.Azuretable what ever happened in dbo.sqlservertable. But I need to manually trigger it because I don't know when my dbo.sqlservertable gets updated. I want to automate this process.
There are multiple ways to sync the data :
use data sync in azure sql database to sync from sql server to Azure SQL database https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database?view=azuresql
You can create a trigger on the table in SQL server 2017 on insert,update,delete
and via trigger, call a stored procedure which would export data into blob via polybase. leverage blob trigger in adf pipeline to sync data from sql server to Azure SQL database
Else you can also trigger a SQL server job wherein you can have a powershell logic to generate a file in blob or trigger ADF REST API for pipeline trigger
sample reference my blog: https://datasharkx.wordpress.com/2022/10/01/event-trigger-data-sync-from-sql-server-to-synapse-via-azure-data-factory-synapse-pipeline/