C/ODBC over VPN (ETL - Business Warehouse)

limonti

Hello!

We have over 30 subsidiaries using Navision. They are all conected in one VPN-network. We would like to have a central BW-solution based on the SQL Server 2005. Our concept was to install C/ODBC centrally and to get the data from subsidiaries using DTS over VPN. The problem we have now is the perfomance of this solution. As we trying to get the data from subs it takes 15 minutes to get meta data (table/fields information) only. The transfer of the real data is not managable.
Now to the possible solutions:
1. Install SQL-server locally in the subs. DTS with SQL-driver works perfectly - very perfromant. This is a bit too expensive for us.
2. Use C/ODBC - ODBC bridge. Does somebody know anything about this solution? Would it help? :?:

In generall: what is the standard process/product for such solution? :?:

Thank you very much for your answers.

lubost

Can you be more precise?
1. Amount of transferred data
2. If you transfer data from subsidiaries, why do you need C/ODBC ?
3. Try use simple ftp transfer of files and read transferred files

limonti

Thank you for your reply.
1. The amount of the transferd data will be very high, probably 1 gb per subsudiary (* 30) per day.
2. We would like to read directly from databases and avoid file transfer. That's why we think about C/ODBC
3. The question is: is it possible with C/ODBC? Of course we could transfer the files, but this solution we don't prefer due to many steps in the process. We need a central solution to read data directly from the databases.

I will try to use Easysoft's odbc-odbc-bridge on monday, but probably it will not bring something, as ODBC-functionality causes too much traffic???

bbrown

1 GB a day? 260 GB a year? Seems like a rather large amount of data for Navision.

jekingcom

One way you can do this, is to create an external application that uses NAS and MSMQ to pull the data out of Navision (realtime or at different checkpoints) and ship the data to the central database in 2005 using MSMQ on the other side. Problem with this is that you don't want to do this once a day with 1 GB of data, but during the day close to realtime, this would keep the transfer sizes easier to manage.

Feel free to email me at (justin.king at brulant.com) with questions. We have done this a number of times in different situations, but same concept.

lubost

bbrown is right. When your database increases by 260 GB per year, you will have have a trouble in performance. So I think tha are that not all data are needed by Navision.
So we have experience with file transfer (about 15 MB a day). We use wget as ftp client and one permanently logged in user named "SYNC", which only scans input folder for transferred files.