Conditional MS SQL replication

Question

I have an issue that I want to do conditional replication to one of my enterprise level client databases. This database is very large: approximately 300 GB MDF file and a 160 GB LDF file because of archiving and accountability.

My primary DB has lots of accountability data and I want to transfer them to the secondary replicated DB for reference purposes. Once the accountability data has been deleted by a job on the primary server, when the replication job runs again the secondary DB should retain all the accountability data without overwriting and/or deleting anything.

Does anybody have a suggestion on the best way to do this?

• user37701: what do you mean by conditional replication? (there are 3 main replication in SQL Server: Snapshot, Transactional, Merge). and what do you mean by proof?
• Xtro-World: I tried snap-shot replication and in the menu, I got to choose what components I want to replicate as well but it didn't work very well, in the end, it took so long to take the snapshot. DB size also mattered. now I am trying to do the same again since I want the secondary DB as the redundant server as well.
• user37701: can you please update your question. what is proof? what have you tried so far?.. try to provide as much detail as possible. so someone here can also provide a much more detailed solution to your problem.
• John K. N.: The question has been updated to reflect what "proof" could mean. I assume the OP would like to retain "proof" data for archiving and/or accountability reasons. Please feel free to rollback my edit, if it does not correspond with your original question/intention.
• Xtro-World: the proof is binary files(Document attachments) which client wants to remain in the secondary(replicated) DB and client want to retain only 3months proofs in the primary DB, so far we tried to do snapshot replication and this DB is around 300GB so it takes a long time for the snapshot process and last time it didn't go well process stop in middle and we have reverted all the process(replication) now I am going to start from the scratch if you need any more details please let me know. thanks for all the answers.

Answer 1

Reference:

• Transactional Articles - Specify How Changes Are Propagated
• sp_addarticle (Transact-SQL)
• Transactional Replication insert only new Records

Yes it is possible with transactional replication. I am assuming initially you want all data to be replicated to secondary. For that you will do a reinitialize and snapshot. From there on if your article is set up I mentioned below only new inserts will be replicated. For update you can do the same if you chose to.

When using sp_addarticle set @del_cmd value to NONE.

If you do it from GUI it will look something like this.

enter image description here

• SqlWorldWide: If the answer fully satisfied your question please mark it as accepted so others can benefit and others are not trying to answer the same question.
• Xtro-World: hi, I was able to create the job successfully and snapshot also created but my data is not transferring to the subscriber. I have reinitialized and recreated the subscriber. but still data is not Transfering to my subscriber DB. can you please help me on this?
• SqlWorldWide: @Xtro-World I can definitely help. First does this answered your initial question? If yes accept this as an answer so other can take advantage of the knowledge. Secondly create a new question with more details. What you see in log, error message etc. You can always refer to this question as a reference.
• Xtro-World: I am sorry since I was not able to transfer the data to the subscriber I was not sure about this that's why I didn't mark it answered.