I am assessing the migration from SQL 2008R2 to SQL 2017 AlwaysOn where we have replication (publisher, subscriber, distributor) setup on SQL2008R2.

There are various restrictions if one wants to setup Replication on top of AlwaysOn..For e.g below  

  1. Merge replication is not supported.

  2. Transactional replication with immediate or queued updating subscriber is not supported.

  3. Peer to peer replication is not supported.

  4. Bidirectional transactional replication is not supported.   Does anybody have any simple select script which can confirm that my replication setup on SQL 2008R2 is anything out of the above type? 

I tried checking "mspublication" table but that isn't providing any guidance to confirm whether my replication setup is out of the above 4 types.

I don't have access on client's SQL instances so want to provide them simple select statement.


1 Answer 1


This answer has been copied from the accepted answer on this question of Stack Ovweflow:

How to determine the SQL Server replication type by TSQL?

Run the following query on the distribution database:

    MSPublications P
    MSSubscriptions S
    ON P.Publication_ID = S.Publication_ID;


  • 0 = Transactional
  • 1 = Snapshot
  • 2 = Merge


  • 0 = Read only
  • 1 = Immediate update
  • 2 = Queued update with message queue
  • 3 = Immediate update with queued update as fail-over using message queue
  • 4 = Queued update with SQL Server queue
  • 5 = Immediate update with queued update as fail-over using SQL Server queue
  • Thank you this does answer the first two types of Replication.. however I am still looking for a query to identify following two types 3.peer to peer replication 4.Bidirectional transactional replication
    – U J
    Commented Jul 2, 2020 at 3:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.