0

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.

0

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
| improve this answer | |
  • 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 Jul 2 at 3:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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