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?
1
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?
Two general results may come out:
-
The roll-back procedure in your sql code ( or sproc, if you use one) will catch the un-committed transaction and prevent the corruption of data.
-
Or you will end up with corrupted data base, with orphan records.
Long story short, the application does need a good transaction management to roll back un-committed set of transactions.
The roll-back procedure should be in place to handle issues like locking, lost connection, etc. These procedures will prevent a corrupted data entry to the database system, and preserve data integrity of the entire database.
There are number of Oracle books that provide examples on how to roll-back transactions and log the issues. Just verify the version of database that you are using and find specific guidelines for that database.
References from Oracle:
- 4 Transaction Management
- 7 Transaction Handling
- 7.1 Troubleshooting Oracle Microsoft Transaction Server
1
SQL Server and Oracle can handle rolling back transaction. The VPN connection is going to be an issue. Break up this process to rely on it as little as possible.
- Do as much work on the SQL Server data, and place the results in some set of SQL_Staging table(s).
- Transfer the data from SQL_Staging to Oracle_Staging. Any breakdown occurs, this can be repeated without doing all calcs over again.
- Use local code to import Oracle_Staging data to all the necessary tables with the ability to rollback. This does not require the VPN at all. Trouble-shooting is easier since you won’t have to retransfer all the data from SQL Server. Really beneficial if that’s not the source of the problem.
Ideally, you’d have a great always on VPN connection and could let both RDBMS handle it, but as a programmer (or are you a DBA?) you probably want a little more control.
1