Linked Server : Syncing two tables on two SQL Servers

Question

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 > 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 )

Answer 1

If you want to use MERGE to sync the table on the remote server, but you can't because it's on the remote server...

From MERGE (Transact-SQL):
target_table cannot be a remote table.

...you should still be able to use separate UPDATE and INSERT statements, for the same effect.

Using the same basic criteria as in the MERGE, you should be able to:

1. UPDATE all rows that already exist; and
2. INSERT any rows that don't already exist.

Doing it in that order avoids updating rows you've just inserted. MERGE operations do the INSERT and UPDATE steps separately in reality, so concurrency issues should be no worse than with MERGE.

_{Community wiki answer}

Answer 2

Have you considered Merge Replication? This would seem to be a great solution to me.

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