asked
Interfacing with SQL Server from a Linux box
Hello, I need to find good approach to pushing data into SQL Server (or any other database running on a Windows box) from Linux. I performed a cursory search against google, stackoverflow, superuser, and here. I did find a few items like: SQL Server ODBC Driver for Linux/Unix (
www.easysoft.com) FreeTDS I am wondering what is the best method of interfacing heterogeneous systems with SQL Server? These are the two ideas that I came up with, but not sure if there is a better approached. 1) Using some sort of ODBC Driver for that particular systems. This might provide the best performance, but each system would need a ODBC driver to talk with SQL Server. Also, if I need to talk to a DB2 database then I would need a driver for that database. 2)Implement a Service that runs on the system with the database, expose certain interface, communications is perform through TCP/IP connections. I could see the performance a little slower than the ODBC, though each system would only need to communicate using standard TCP/IP. The Service then could talk with the SQL Server database using Stored Procedures (good idea???). Does anyone have any experience relating to this (I am sure it has been done countless times before) and what approach was taken???? Thanks for any insight, Mark
one option is to create a CSV data source on the Linux box, then create a linked server or use SSIS on the SQL Server windows box to pull the data from the Linux box
+1 Agree with the SSIS solution. If you have the data in some DB running on linux, then yo do not need CSV for that and simply install ODBC drivers for that db on the SSIS machine. For most DB engines there are well functioning drivers for windows.
I can't offer any positive experiences, I'm afraid. I can tell you that I wasn't impressed with easysoft's products. I used [ODBC ODBC bridge][1] on a product once, and it was written to run on Linux. Before the launch of the product, I ported it to run on as a Windows service as well. Come launch time we switched it on, started getting a lot of traffic through and it just fell over constantly. We switched over to the Windows one (bearing in mind it was running on exactly the same source minus some stub O/S functions) and never had a problem with it. So, yeah, not the most positive feedback, but at least you can save yourself some effort by not bothering with the ODBC ODBC bridge. [1]:
http://www.easysoft.com/products/data_access/odbc_odbc_bridge/
If you are using [Python][1] (or willing to use Python as a glue component) you may want to look at PyODBC ( [
http://code.google.com/p/pyodbc/][2] ). I have *not* tested it on Linux, but I have used it extensively on Windows and it is positively fantastic there. The project claims to be fully compatible with Linux. EasySoft, which you mentioned in your question, also has a tutorial on how to use one of their products to connect to SQL Server from Linux. Their tutorial is at [
http://www.easysoft.com/products/data_access/odbc-sql-server-driver/getting-started.html][3] . I know Matt was rather unimpressed when he tried their ODBC-ODBC bridge, and I am certainly not vouching for EasySoft since I have not tested any of their products, but I will point out that this seems to be a different product than the one Matt used and that the tutorial seems up to date so depending on when he tested it they may well have improved it since then. [1]:
http://www.simple-talk.com/sql/database-administration/python-for-the-sql-server-dba/ [2]:
http://code.google.com/p/pyodbc/ [3]:
http://www.easysoft.com/products/data_access/odbc-sql-server-driver/getting-started.html
