Optimize database interaction through VPN

Question

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?

Answer 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:

1. 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.

2. 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

Answer 2

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.

1. Do as much work on the SQL Server data, and place the results in some set of SQL_Staging table(s).
2. Transfer the data from SQL_Staging to Oracle_Staging. Any breakdown occurs, this can be repeated without doing all calcs over again.
3. 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.