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?

• Clockwork-Muse: Saying this is "over a VPN" doesn't really matter - what matters is that you have a remote database. Most RDBMSs can set connections to auto commit/rollback when it's dropped - this is known as an 'implicit' rollback/commit. In other words, the DB should be fine (test it by, say, pulling the cord in the middle of a transaction). Now, the application, on the other hand, will have to be able to deal with this...

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

• Angelo Badellino: My problem is that I have no control on the destination database because I am only use code to deal with it. If the connection fall down I cannot send a Rollbak to the 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.

• Angelo Badellino: I am a programmer JeffO, and probably I haven't the possibility to write procedure on the Oracle desrtination database.