July 1, 2020 at 2:38 pm
Hi,
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..
July 2, 2020 at 2:35 pm
I got answer for first two. We can find it through below script
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
But How can I find out, if my current replication pattern is any of following or not? I am using SQL2008R2.
1)Peer to peer replication
2)Bidirectional transactional replication
July 18, 2020 at 11:30 pm
My question would be, why do you want to mix replication and AlwaysOn Availability Groups? What problem are you trying to solve by doing that? Mixing them is going to complicate manageability significantly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply