Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I need to create a copy of my SQL 2008 R2 Enterprise database and have it located on the same server as the original. I will be using this second copy of the database as the target of a mostly read-only website. I understand that if I create this copy of the database using snapshot replication that all data changes in the subscriber database will be overwriten in the event of the next replication. The web application will try to write to this database to record login attempts, etc and will fail if its source database is read-only. In my case I do not need to keep these auditing records and they can therefore be overwriten each time a new snapshot is applied.

My question is whether SQL Server forces the subcriber database to be read-only and is there any way around this?

Thank you,

Nate

share|improve this question
add comment

2 Answers

Likely you are aware that you can created updateable subscription using transactional replication: http://technet.microsoft.com/en-us/library/ms152769(SQL.90).aspx

share|improve this answer
    
I am aware but I felt transactional replication was much more overhead than needed for my purpose. I do not need data changes made in the subscriber database to sync back to the publisher. –  NateReid Dec 14 '10 at 20:27
    
"...Snapshot replication is more expensive in terms of overhead and network traffic and only takes place at intervals. Because locks are held during snapshot replication, this can impact other users of the subscriber database. ..." simple-talk.com/sql/database-administration/… I haven't confirmed this but thought it might be some useful information. –  jl. Dec 14 '10 at 21:15
add comment

SQL Server does not force the subscribing database to be read-only. You can modify a table that is under replication at the subscriber. Just look out for the following issues:

  1. The subscriber will have the data overwritten if an update happens on the publisher to the same record.
  2. The syncrhonization assumes that the subscriber will not be updated so if the synchronization tries to do something that causes an error then replication will fail until the problem is fixed. An example is if you create a record at the subscriber and then create the same record on the publisher. Assuming that you have a primary key constraint on the subscriber, an exception will be thrown when replication attempts to create the record again.

To be more safe, you could setup replication without the auditing tables and then add the auditing tables manually after the snapshot is complete. The auditing tables will then be independent of replication so you can modify them without worry. However, the subscriber will not have the auditing records that are on the publisher.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.