I have a server that is replicating a large part of our database to secondary servers across the globe. This is done via transactional replication with the goal of supplying our offices with read-only access to our data.
I am currently looking at the best way of allowing our in-house client software to access these secondary servers, but only allowing read-only access to the data. The best way being, to change the client software to check what server it connects to and then not allow write operations - this would require too much recoding (time constraints apply as usual!).
We use application and database roles in most of the software, so applying a DENY to those would stop about 85% of the possibilities of writing to the database. However, the remaining 15% (mostly old programs that are being phased out) could still slip through and write to the secondary systems.
I have considered the following:
The writes that can be made against the secondary systems are not terrible, it would only really be updates and deletes, as the inserts would not work (we work with number ranging for ID generation that would not work on the secondary servers). So the worst someone could do, is change a status that would then be overwritten by the primary system / never be seen anywhere except for where the mistake was made.
Does anyone have any ideas/thoughts on this? Have I missed a really obvious solution? Are any of the possibilities I mentioned a good idea?
All systems are running SQL Server 2008 Enterprise Edition / Windows 2008R2 Enterprise Edition, so all possible features are at my disposal :)
Would love to hear any input
Thanks in advance.
asked May 24, 2011 at 01:05 AM in Default
Some thoughts, in no order:
Can you use roles to group your replication accounts and your other accounts? you could then use deny on your majority and exclude the critical ones.
Are you using the replication in one direction? Cant the replication simply over-write the changes at the remote locations? Users (I know from experience) slowly pick up on the fact that details get obliterated every 10 minutes and give up.
How do these users connect to the data? If its via ODBC can you not specify in there that its read only in nature?
If you used Log Shipping the secondary databases could be Read-Only thus avoiding any issues like this.
answered May 24, 2011 at 01:20 AM