SQL Server 2008 R2 Transactional Replication for only insert and update.

Maintaining reports is challenging for dba’s most of the time. The requirements are unique and specific across various environments when it comes to keeping the transactional data and reporting data in the same database. In order to keep the transactional databases slim and to enhance their performance often dba’s get requests to separate the reporting database. We had a similar situation. I have identified the tables required for reporting purpose. Data retention for this database was 1 year and it was only a week for the transactional database. So the question was if I implement transactional replication then how would I prevent the deletes in the reporting database. Fortunately, there is a way to prevent deletes and replicate only the inserts and updates in the reports database.

Below are the steps:

1) Expand the replication folder in SSMS and expand the local publications.

2) Right click on the publication name. Go to properties and select articles on the left hand side.

3) As shown in the below screen shot check on “show only checked articles in the list”. Click on the “Article properties” and select “Set properties of all table articles”

Publisher Article Properties -1

4) Now scroll down to the statement delivery section and  in the delete delivery format select “Do not replicate DELETE statements” as shown below and hit ok. This requires subscriptions to be reinitialized. Click on “Mark for reinitialization”

Publisher Article Properties -2


5) Now to ensure that the relationships exists between the subscriber database tables in the “Copy objects and settings to subscriber” area change the “copy foreign key constraints” value to True as shown below:

Replication new screen

Now only inserts and updates are replicated but not deletes.

Hope it is useful

Image | This entry was posted in SQL Server DBA Stuff and tagged , , , , , , , , , , . Bookmark the permalink.

One Response to SQL Server 2008 R2 Transactional Replication for only insert and update.

  1. Mr.Binh says:

    Hello, I want only replicated insert, update some field in table. But some field not drop replicated in subscriber.
    can You help proving retaining those field in subscriber

    Thank you

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s