Sql-server – SQL server replication (publisher, subscriber, distributor) on top of AlwaysOn

availability-groupsreplicationsql serversql-server-2008-r2

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.

Best Answer

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:

SELECT 
     P.Publication
    ,P.Publication_type
    ,S.Subscriber_ID
    ,S.Update_Mode
FROM 
    MSPublications P
INNER JOIN 
    MSSubscriptions S
    ON P.Publication_ID = S.Publication_ID;

Publication_type:

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

Update_mode

  • 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
Related Question