There are two PostgreSQL FDWs (currently maintained) I know of for connecting to SQL Server from a Linux/Unix PostgreSQL box. There is the TDS Foreign Data wrapper (tds_fdw driver) which relies on the Free TDS driver. This is a fairly light-weight FDW since it just relies on TDS which is commonly already available on Linux installs or an easy install away. Unfortunately when I tried to use it on windows (compiling my usual mingw64 way), while it compiled and installed, it crashed when I attempted to connect to my SQL Server 2008 R2 box table, so I gave up on it for the time being as a cross-platform solution. One thing I will say about it is that it accepts ad-hoc queries from what I can see, as a data source, which is pretty nice. So we may revisit it in the future to see if we can get it to work on windows. I'm not sure if tds_fdw would support SQL Server spatial geometry columns though would be interesting to try.
The second option, which as you may have noticed, we spent much time talking about is the ogr_fdw foreign data driver. ogr_fdw utilizes UnixODBC on Linux, iODBC on MacOSX and Windows ODBC on windows for connecting to SQL Server. The ogr_fdw big downside is that it has a dependency on GDAL, which is a hefty FOSS swiss-army knife ETL tool that is a staple of all sorts of spatial folks doing both open source and proprietary development. The good thing about ogr_fdw, is that since it is a spatial driver, it knows how to translate SQL Server geometry to it's equivalent PostGIS form in addition to being able to handle most of the other not-so spatial columns.
Although GDAL is big, the good news is that, PostGIS relies on it as well since PostGIS 2.0 to support raster functionality. That was one of my ulterior motives for pushing raster into the PostGIS extension in 2.0: There will come a day when PostgreSQL will need to reach out to vast different kinds of spatial and not-so spatial data and GDAL would be a convenient ring to do so, so lets start planting the roots.. This makes compiling and installing ogr_fdw on Linux pretty trivial if you already have PostGIS with raster support installed and even easier now that PGDG Yum packages it in the repo.
Until recently, I've only used ogr_fdw on Windows and have been very happy with it connecting to all sorts of datasources from open-street map extract, dbase files, excel spreadsheets, MS Access databases, and SQL Server, it's a real gem. That is not to say it couldn't stand for many improvements. For a good chunk of these like OSM and MySQL and SQLite, GDAL doesn't rely on ODBC and uses the native drivers directly. Recently people have been writing me about how they can use it on Linux to connect to SQL Server. Yes, my jaw dropped, Linux people want to connect their PostgreSQL to SQL Server, why the heck would they want to do that. I thought maybe it's a good idea to try this out myself to experience first hand issues people are running into rather than simply relaying the information between people on what they tried that worked and didn't work. So here is my naive attempt to do so and distill the body of information that people have been sending me.
There are two UnixODBC drivers you can use for connecting to SQL Server. There is the TDS based one, and in theory, if you are on CentOS/Red Hat EL (5,6) or SUSE Linux EL 11, you can also use the Microsoft provided via: https://msdn.microsoft.com/en-us/library/hh568454%28v=sql.110%29.aspx which I have not attempted, but may in a future article.
We'll use the more commonly available TDS driver which I think works on pretty much all Linux/Unix and MacOSX systems. This I am doing on a CentOS 7 box.
If you installed ogr_fdw or PostGIS using, yum, then you probably have UnixODBC installed, to verify do this:
sudo yum list installed | grep unixODBC
We get this for output
unixODBC.x86_64 2.3.1-10.el7 @base
I should note, that if GDAL isn't compiled with UnixODBC support, you have a much harder hurdle to jump. Luckily as far as I can tell, I think GDAL is generally compiled with UnixODBC support on Linux and possibly on Mac as well.
Next see what drivers you have installed already
odbcinst -d -q
On our CentOS 7 box, got returned:
[PostgreSQL] [MySQL]
Here are the following steps. Much of this information is gleaned from unixODBC - MS SQL Server. You need to do this if FreeTDS didn't show up as an option when querying odbc manager.
sudo yum install freetdsThis is a RedhatEL/CentOS/Fedora way, on Ubuntu you'd probably need to replace yum install with apt-get sudo yum install freetds
Dependencies Resolved ============================================================================================================================================================================================================== Package Arch Version Repository Size ============================================================================================================================================================================================================== Installing: freetds x86_64 0.91-12.git0a42888.el7 epel 1.1 M Transaction Summary ============================================================================================================================================================================================================== Install 1 Package Total download size: 1.1 M Installed size: 9.8 M Is this ok [y/d/N]: y
tsql -C
Output should look something like
Compile-time settings (established with the "configure" script)
Version: freetds v0.91
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: 4.2
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yesldconfig -p | grep libtdsodbc.so
Should output something like
libtdsodbc.so.0 (libc6,x86-64) => /lib64/libtdsodbc.so.0
Now connect as postgres account. Create a driver template file, doesn't really matter where you put it or call it, since it will be copied by the installer, contents should contain
[FreeTDS] Description = FreeTDS for connecting to Sybase and SQL Server Driver = /lib64/libtdsodbc.so.0
Make sure to replace the driver with whatever path ldconfig gave. We called ours tds.driver.template
su postgres
odbcinst -i -d -f tds.driver.templateShould get an output:
odbcinst: Driver installed. Usage count increased to 1.
Target directory is /etc
odbcinst -d -q
Output should now include FreeTDS
[root@centie source]# odbcinst -d -q [PostgreSQL] [MySQL] [FreeTDS]
To do this we need to create a datasource template file and register that with odbcinst manager similar to what we did with the driver as follows:
Create a file call it tds-testmssql.datasource.template and has contents as follows. Note the [..] is the name you want to refer to your data source and should be different for each database you want to connect to.
[MSSQLTDSTest] Driver = FreeTDS Description = My test database Trace = No Server = 192.168.1.25 Port = 1433 Database = Northwind TDS Version = 7.3
using a server name or fully qualified domain name instead of the ip of the SQL Server works just as well.
Note: From other people's experiences I been informed that TDS Version needs to be 7.3 or above to work with ogr_fdw.Then run:
odbcinst -i -s -f tds-testmssql.datasource.template
isql -v MSSQLTDSTest your_user_name your_password
You should be able to execute queries on console. For a simple exercise try this:
SELECT @@VERSION
Should output something like:
+-------------------------------------------------------------------------------------------------------+
| Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64)
Mar 19 2015 12:32:14
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: )
|
+-------------------------------------------------------------------------------------------------------+
Type: quit
To get out.Connecting to SQL Server using ogr_fdw is more or less the same at this point regardless of if you are using Windows or Unix/Linux.
Install ogr_fdw if you haven't already. Thanks to Devrim, this should be a simple exercise on any platform supported by PGDG Yum repo.
yum install ogr_fdw94;
(if you are running PostgreSQL 9.3 or above. Of course replace the 94 with version you are running.)
su postgres psql CREATE DATABASE test_ogr_fdw; \connect test_ogr_fdw; CREATE EXTENSION ogr_fdw; \q
Use the include ogr_fdw_info commandline tool to figure out the server and fdw for a table. Connecting to ODBC sources with GDAL/OGR is described in more detail here: http://www.gdal.org/drv_odbc.html I'll use my favorite example of information_schema.columns. You should replace with what you want to connect to.
ogr_fdw_info -s "ODBC:your_user_name/your_password@MSSQLTDSTest" -l "information_schema.columns"
This outputs something of the form:
CREATE SERVER myserver
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest',
format 'ODBC' );
CREATE FOREIGN TABLE information_schema_columns (
fid integer,
geom geometry,
table_catalog varchar,
table_schema varchar,
table_name varchar,
column_name varchar,
ordinal_position integer,
column_default varchar,
is_nullable varchar,
data_type varchar,
character_maximum_length integer,
character_octet_length integer,
numeric_precision varchar,
numeric_precision_radix integer,
numeric_scale integer,
datetime_precision integer,
character_set_catalog varchar,
character_set_schema varchar,
character_set_name varchar,
collation_catalog varchar,
collation_schema varchar,
collation_name varchar,
domain_catalog varchar,
domain_schema varchar,
domain_name varchar )
SERVER myserver
OPTIONS ( layer 'INFORMATION_SCHEMA.COLUMNS' );
Note that ogr_fdw always tacks on an fid and sometimes a geometry column. In case of a SQL Server that has no spatial columns, this is just an artifact, and can be changed to bytea and ignored in use.
So revise statements as follows and run in psql.
\connect test_ogr_fdw
CREATE SERVER mssql_tds_test
FOREIGN DATA WRAPPER ogr_fdw
OPTIONS (
datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest',
format 'ODBC' );
CREATE FOREIGN TABLE tds_information_schema_columns (
fid integer,
geom bytea,
table_catalog varchar,
table_schema varchar,
table_name varchar,
column_name varchar,
ordinal_position integer,
column_default varchar,
is_nullable varchar,
data_type varchar,
character_maximum_length integer,
character_octet_length integer,
numeric_precision varchar,
numeric_precision_radix integer,
numeric_scale integer,
datetime_precision integer,
character_set_catalog varchar,
character_set_schema varchar,
character_set_name varchar,
collation_catalog varchar,
collation_schema varchar,
collation_name varchar,
domain_catalog varchar,
domain_schema varchar,
domain_name varchar )
SERVER mssql_tds_test
OPTIONS ( layer 'INFORMATION_SCHEMA.COLUMNS' );Now if you have a lot of tables, the CREATE SERVER process (and selecting from an FDW table) tends to take a while (like 30 seconds), you can winnow down the list a bit to only list tables you would ever want to connect to like so:
ALTER SERVER mssql_tds_test
OPTIONS(SET datasource 'ODBC:your_user_name/your_password@MSSQLTDSTest,information_schema.columns,dbo.ZIPS,someschema.whatever');
Do a test query:
SELECT ordinal_position As ord, table_name, column_name, data_type, character_maximum_length FROM tds_information_schema_columns WHERE table_name = 'ZIPS';
Which for my particular SQL Server db outputs:
ord | table_name | column_name | data_type | character_maximum_length -----+------------+-------------+-----------+-------------------------- 1 | ZIPS | Country | varchar | 4 2 | ZIPS | ZipCode | varchar | 12 3 | ZIPS | ZipType | varchar | 2 4 | ZIPS | CityName | varchar | 80 5 | ZIPS | CityType | varchar | 2 6 | ZIPS | CountyName | varchar | 80 7 | ZIPS | CountyFIPS | varchar | 5 8 | ZIPS | StateName | varchar | 80 9 | ZIPS | StateAbbr | varchar | 40 10 | ZIPS | StateFIPS | varchar | 4 11 | ZIPS | MSACode | varchar | 8 12 | ZIPS | AreaCode | varchar | 12 13 | ZIPS | TimeZone | varchar | 24 14 | ZIPS | UTC | varchar | 12 15 | ZIPS | DST | varchar | 2 16 | ZIPS | Latitude | float | 17 | ZIPS | Longitude | float | 18 | ZIPS | id | int | (18 rows)
Now although SQL Server isn't case sensitive, since PostgreSQL is, the example has to match the case of the SQL Server table.
I chose use of information_schema.columns because from it, it's fairly trivial to create a new foreign table. So if you wanted a new foreign table, you'd do this:
SELECT 'CREATE FOREIGN TABLE ' || table_name || ' (fid int, geom bytea, ' || string_agg(column_name || ' ' || data_type, ',' ORDER BY ordinal_position ) || ') SERVER mssql_tds_test OPTIONS ( layer ''' || table_schema || '.' || table_name || ''' ) ' FROM tds_information_schema_columns WHERE table_name IN('ZIPS') AND table_schema = 'dbo' GROUP BY table_schema, table_name;Warning: This example leaves out logic to handle data types like datetime that need to be mapped to different timestamp in PostgreSQL and other oddities like quote ident.The output of the above query would be this which can then be used to define a new foreign table against the SQL Server
CREATE FOREIGN TABLE ZIPS (fid int, geom bytea, Country varchar,ZipCode varchar,ZipType varchar,CityName varchar,CityType varchar, CountyName varchar,CountyFIPS varchar,StateName varchar,StateAbbr varchar, StateFIPS varchar,MSACode varchar,AreaCode varchar,TimeZone varchar,UTC varchar,DST varchar, Latitude float,Longitude float,id int) SERVER mssql_tds_test OPTIONS ( layer 'dbo.ZIPS' );
SELECT zipcode, cityname from zips WHERE statename ILIKE 'New York' limit 100;Someone mentioned to me that their list of tables that have compound keys didn't show up as options on Linux, but did when using ogr_fdw under windows. I haven't experimented with that to see if I can replicate the issue.