none
Heterogeneous Replication Via Linked Server

    Question

  • 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?


    aa


    • Edited by Asif_DBA Thursday, March 23, 2017 3:45 AM
    Monday, March 20, 2017 5:35 AM

Answers

All replies

  • Hello,

    No, it's not possible, replication don't work with linked Server.

    You e.g. could use SSIS packages to transfer data between the different SQL Server, but of course not in a transactional way.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, March 20, 2017 8:10 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.
    Monday, March 20, 2017 11:43 AM
    Moderator
  • Is there any links or hints that you can provide me?

    aa

    Tuesday, March 21, 2017 3:40 AM
  • 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.

    Wednesday, March 22, 2017 1:45 PM
    Moderator
  • Once more thing - does your linked server to postgres work? What version of SQL are you running?
    Wednesday, March 22, 2017 1:45 PM
    Moderator
  • Yes, it does work and I am using SQL Server 2014 SP2.

    aa

    Thursday, March 23, 2017 3:42 AM
  • Tested in SQL 2014 ENT edition and it's working fine..

    aa

    • Marked as answer by Asif_DBA Sunday, August 13, 2017 7:09 AM
    Sunday, August 13, 2017 7:09 AM