Hi,
What to achieve (it is complicated to explain) :
Set up a local DB on a SQL 2016 which contains information (tables) from Salesforce DB. Salesforce DB is added as a linked server.
SalesForce WILL be the PRIMARY source of everything.
Local DB will be for updating certain tables back to Salesforce (Otherwise Salesforce will be the primary in everything) (Reason for this is we need a log and transaction whenever a card is scanned at certain points. EX. card A is scanned at reader 5, deduct $2 from their account. Calculate and send to salesforce) When this is registered in salesforce it should replicate the change every 5 min (more often if possible) down to the local DB so it is as close to updated as possible.
Tried: Pinging salesforce with a query from the reader 5 and deduct $2 from their "account". Took 6-7 seconds before reply OK. This is WAY too long. I need this to as close as possible to instant. That's when the local database was thought to handle this kinda transaction.
As you might guess English is not my first language. If someone has input on how to achieve this please feel free to comment.
Hoping for some comments and suggestions on how this can be achieved 🙂 All feedback is very welcomed. I am not an SQL guy, more of the infrastructure guy