SQL Server Replication

[gayatri235]

Hello Team,

We are planning for real time replication of SQL Server DB.

Can you help me with what should I prefer MS-Replication or MS-CDC ?

Also I want to minimize the load/ impact on the source  during full load .

Any suggestion will be appreciated !!!

[Heinvandenheuvel]

Unfortunately the Replicate User guide only states dull usage facts, and does not provide advice / recommendation.

IMHO MS-CDC should be avoided, but there are use cases (some non-pk table DML).

MS-CDC It uses change-tables on the source which is unnecessary overhead for Replicate.

Replicate can just read the (archived) Tlog to find out what happened.

Perhaps check with your pre-sales team to understand what is best for you situation with lots more background then you provided? One may need to know the number of tables, columns, lobs, volume and more to give the appropriate advice for your situation.

Hein. 

[gayatri235 · Accepted solution]

if non PK table count is more we should go with MS-CDC.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-MS-REPLICATION-vs-MS-CDC/ta-p...

This link suggest we can use a combination of both. How will it work when we implement both MS-Replication and MS-CDC.

[Dana_Baldwin]

Hi @gayatri235 

Each source endpoint can only use MS-Replication or MS-CDC, they are mutually exclusive. Your approach might be to create a source endpoint for MS-CDC and related task for your tables without PK, and an MS-Replication source endpoint and task for your tables with PK.

Thanks,

Dana