I am trying to setup Tran replication between SQL Server and Postgresql however microsoft officially supports only Oracle and IBM as Non-SQL Server Subscribers.
I have created a linked server between MSSQL and Postgresql which is working fine, so is there a way we can setup replication between the two DB Servers using linked server?
- Edited by Asif_DBA Thursday, March 23, 2017 3:45 AM
You will need to set it up as a heterogeneous subscriber. You will need to configure an odbc connection to your postgres server and replicated to that as an ODBC subscriber. I did try this with SQL 2000 and there were some issues. I see that others have been able to get this to work. You will need to map your datatypes from SQL to postgres.
The last time I did this was with SQl 2008 and mysql. I have been struggling to get it to work with SQl 2012, 2014 and 2016. Microsoft did say this was going to be deprecated, but I did not see anything saying that it had/has been. I will struggle a little more.
There are two pieces to this.
1) sp_addsubscriber to your odbc data source which will create a linked server with the name of your system dsn.
2) sp_addpushsubscription_agent where you pass info about your ole-db provider.
Can you tell me what version of postgres you are running and which driver you are using?
What error message are you getting? I am getting the cannot connect to subsriber error message.