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