Linked Server : Syncing two tables on two SQL Servers Suppose these database structure :

--On main server(server)
TicketRequest (id, date, count, start, end/*,and some other columns*/)
Ticket (id, reqId, blockedDate, usedDate, userId/*,and some other columns*/)
--and some other tables

--On linked server(gate)
--Just this table
Ticket (id, userId, blockedDate, usedDate)


A Ticket may use on gate, so on the gate just its usedDate may change.

At server, a Ticket may blocked, deleted; or after importing gate's data, set its usedDate.

I want to sync them online.

I wrote sp_server_export , sp_gate_import for transport data from server and  sp_gate_export , sp_server_import for transport data from gate, to sync them via a file(a server_export file goes to the gate, and server_import file come back to the server).

I used MERGE and its performance is really good! Now when I'm trying to find a solution for syncing server with gate, after some searches UNFORTUNATELY found Microsoft says : MERGE not working on remote table as target!

So, how I can sync with a transaction from server?!

NOTE : As our plan, syncing must only execute from server! Gate operator cannot change DB at all!

IMPORTANT NOTE : Consider that concurrency is my first concern! I wanna sync while a user may delete a Ticket! Or a Ticket may block at the same time! Or at the gate a Ticket may use!

UPDATE :

Our syncing logic is :

1 - delete old tickets(which returned with a file syncing and have returnDate and have modificationDate &gt; one month later timestamp to ensure delete used tickets from gate)

2 - update gate.ticket where usedDate is null set blockDate = server.blockDate;

3 - insert new rows from server

4 - set transferDate for Tickets transfered to gate ( i mean new/updated tickets in step 2 and 3 )

5 - update server.Ticket.usedDate from gate.Ticket where gate.Ticket.usedDate is not null. ( this will send used tickets from gate back to server )

6 - insert a transferLog ( in transferLog table on server )
Have you considered Merge Replication?  This would seem to be a great solution to me.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/merge/merge-replication