- Logshipping in Standby Mode: But this need killing connection every time t-log will be restored and atleast I have to give 10mins latency.
- Mirroring: Except creating snapshot on mirror I can’t do anything and problem is latency of data. i.e. no real time data.
- Replication: This should be 1st option to use transaction replication but my all tables doesn’t have primary key and it’s not feasible to add PK in all tables of very old application.
- Merge Replication: So now you will question its Bidirectional. No , not always. We can also set one sided merge replication i.e. from subscriber no one can update data. Also called as Merge replication with download only.
Merger Replication with Download only: This feature was introduced in SQL 2005. The idea behind this feature is to make sure static tables can only be modified at publisher and for all subscribers it should be Read-only. You can set an article , group of articles or all articles property “synchronization direction” to “download only to subscriber, prohibits subscriber changes”.
Now By clicking next it will automatically add unique identifier to all tables to use concept of primary key.
Next , I have to select subscriber server , connect , and select database for subscription. If you want you can create new database as well. For me database is “Replication_Test_Downloadonly”
Next , I will get option to give security for publisher and subscriber so be careful while picking login for authentication.
In connect to Subscriber option please put sql authentication login at subscriber server.
Select Initialization "immediately"
Select Subscription type as Client other wise this will start working as publisher and it will no more remain "Download only" for more understanding(Click here)
Initialization is completed
Now we will Verify if my replication is working fine?
Now you cant test to insert some values to Subscriber and it will fail , Bcoz we st it as Undirectional.