MS SQL Replication - Make Subscriber dataset Read Only?

krinpit

I have set up a test-bed which performs full transactional replication from the publisher to the host. The tools in SQL Server 2005 make this incredibly easy to achieve.

However....

The data on the subscriber is editable, even though I have not set it up as "Updatable Subscriptions for Transactional Replication".

How can I enforce the subscription to be Read-Only, so that the data is always guaranteed to be in sync, no matter who has access to the subscriber?

Thanks in advance

Ginger

Lost the original reply but here we go again

In your security on the subscriber database, just have the users connect with the datareader permission only.

Other than that do you want one way replication?? Publisher to subscriber only not the other way round?

seamus

You should be able to make the database on the subscriber read-only for all users?

I haven't messed much with replication, but I can't see why this wouldn't be possible.

krinpit

Yes, I just want one way replication - Pulisher to subscriber.

I will now follow up with seeing how I can make users connect with Read Only permission.

Thanks for your help.

Ginger

Is your data time sensitive? As in can it be replicated once a day for example?

Maybe look at snapshot replication..

For your security thing..

Use the datareader permission which allows you only to read data rather than write.

If you use SQL Authentication just assign it to the that user.. if its Windows auth, just do the same thing

krinpit

Yes, it's time sensitive - it needs the very latest data - I'm just splitting away the reporting database from the production database (intraday trading). This is why I'm using transactional replicaction rather than snapshot/merge.

Thanks again for the permissions tip.

Ginger

No worries, you could also use a view if you want to make the data easier to aggregate for your analysts

krinpit

To make the subscriber database read-only:

1. Right Click on the Database in SQL Server Management Studio
2. Select Properties
3. Select Options
4. Set "Database Read-Only" to True

Ginger

Then the db is readonly regardless of security, so even admins cant write to it...

Be careful with it.. its handy if you are doing certain things, otherwise its too restrictive

krinpit

Hmmm, this stops replication from working

seamus

krinpit wrote: »

Hmmm, this stops replication from working

That's what I was going to post after. I imagine that the SQL Server Agent carries out the updates to the subscriber database. So if you make the database read-only, then nobody can update the database - including the SQL Server Agent which needs update permissions in order to replicate.

Giving only dbo, the process under which the SQL replication agent runs and yourself change access and everyone else read access, should sort it.

Ginger

Yup.. database is now fully readonly to all things.. hence the security option is better..

If you think about it, you have made the file readonly tho you still want to write to it...

What you need to do is allow your normal users to connect using the datareader permission and then from there, set up the security so that your replication account can write to the database as required...