none
SQL Server replication question

    Question

  • Hello, I have a SQL database on which mirroring is already setup to get mirrored on to another server. Now I want to setup transactional replication on the Principal db to a different server, can I set up transactional replication on a Principal db which is already getting mirrored to another server? Thanks. 

    sqldev

    • Moved by Tom Phillips Tuesday, November 12, 2013 7:43 PM Replication question
    Tuesday, November 12, 2013 5:12 PM

Answers

  • Hi,

    Yes. Transactional replication can be set up on the Principal DB to a different server. Here the Principal DB will be the Publisher and the database on the different server will be the subscriber.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 6:39 PM
  • Replication will not break mirroring. As far as performance is concerned, if the mirroring is in asynchronous mode then there won't be any performance impact in ideal conditions but if the mirroring is in synchronous mode then performance may get impacted depending upon how frequently the log reader agent and the distribution agent are running.

    Definately some load will be added up on the network side.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 8:05 PM

All replies

  • Hi,

    Yes. Transactional replication can be set up on the Principal DB to a different server. Here the Principal DB will be the Publisher and the database on the different server will be the subscriber.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 6:39 PM
  • http://technet.microsoft.com/en-us/library/ms151799.aspx

    Thanks, Rama Udaya.K (http://rama38udaya.wordpress.com) ---------------------------------------- Please remember to mark the replies as answers if they help and UN-mark them if they provide no help,Vote if they gives you information.

    Tuesday, November 12, 2013 7:02 PM
  • Thanks for the quick response.

    The Principal db will be the publisher. The distributor and subscriber will be on another server.

    If we set up transactional replication on the Principal database which is already mirrored, will the replication create any issues to the mirroring that's already in place. And also any performance issues? Thanks 


    sqldev

    Tuesday, November 12, 2013 7:06 PM
  • Replication will not break mirroring. As far as performance is concerned, if the mirroring is in asynchronous mode then there won't be any performance impact in ideal conditions but if the mirroring is in synchronous mode then performance may get impacted depending upon how frequently the log reader agent and the distribution agent are running.

    Definately some load will be added up on the network side.


    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 8:05 PM
  • Thanks, I need one more clarification please. Once the transactional replication is set up from the principal db(Publisher) to the subscriber, upon db mirroring failover then that principal db(publisher) will be a mirror db so in that case will be transactional replication will still be working okay?

    sqldev

    Tuesday, December 10, 2013 2:21 PM
  • On failover the original publisher now becomes the mirror. You will need to configure this manually.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Tuesday, December 10, 2013 2:47 PM
  • Once every month db mirroring failover happens, so whenever mirroring failover happens we need to configure replication manually then? Thanks


    sqldev

    Tuesday, December 10, 2013 2:59 PM
  • Any idea please?

    sqldev

    Friday, December 13, 2013 1:47 PM