I have to develop a batch procedure with VB.NET. This simple procedure is going to read some data from a MS SQL database, execute some elaborations and the write the results on another Oracle SQL database located to a remote network reached via VPN. The VPN is "always on", we do not need a client to manage the connection because it is created firewall-by-firewall.
The data that I have to write are atomic, so I have to use a transaction.
Can you tell me what happen if the VPN connection fall down when a transaction is open and the COMMIT was not executed? Do you have some suggestion/best practice to follow to avoid this scenario?