I have a production database which is being used by my primary application. Another department has created a new application for which I need to provide subset of my data from my database. The new application will have its own database server and just for few tasks they need my data (which will keep on updating through primary application). I tried creating a Transaction Replication and made the subscriber as read only mode but it failed as replication cannot be done on read only database. What options do I have in order to give them the data but also to ensure the data is not tampered at their end and database stays in read-only mode.

  • Welcome to DBA.SE and thanks for your participation. You have a slight misunderstanding in your logic. A Transactional Replication is a feature where-by a subscriber's database is updated (<== Hint) from a distributor's database. It can't be read-only, because it will be updated. You could try and remove the DML grants (Update/Insert/Delete) from the users accessing the subscription database. – John aka hot2use Dec 23 '19 at 9:42
  • 1
    @Johnakahot2use Thanks for clarifying. Since, I am not DBA for the secondary server, I'll have to ask them to do these changes and assume they will follow it. – Kartikeya Dec 23 '19 at 10:00
  • In every version and edition there is LOG SHIPPING. You schedule tran log backups on your primary server and send them to secondary where leave the database in standby mode between restores. Your secondary will be readonly between restores – sepupic Dec 23 '19 at 11:22
  • 2
    Don't know if it will work with replication, but an old trick is to create a view and grant user permission only in that view – jean Dec 23 '19 at 11:50

Your Answer

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

Browse other questions tagged or ask your own question.