I have a very large table from which I need to extract a subset of records, the last 30 days records, and to replicate this 30 days records to a second database, for reporting purposes. Now I am using transactional replication where I added a filter in the published articles to isolate the 30 days records, to get a near real time replication envirnment.
The issue I have is that : the replication seems to be incremental, meaning that the most recent records are added to the replica, but the older records are not removed so it keeps getting large.
When a record that is out of filtering criteria is updated and enters again under the filtering criteria the replication crashes with an "duplicate primary key error".
How to make it work so that the replica to contain only the last 30 days of data ? Is the above described behaviour something that I shall expect to see ?
Many thanks,
Well the simplest way, is not to use mssql's filter. The simplest way is to change the SPS used for update and delete with custom sps so that you will not get errors on deleting (absent rows) and updating (absent rows). This is done from the article's advanced properties. In case of a delete you should just use a merge and filter there your criteria.
Also have a job that deletes from the tables what you need to have deleted. Of course you will need to be very careful when doing structure updates, but it is doable.
Another more ugly way is to keep sql's stored procedures and just ignore the errors (through the distribution agent .. -SkipErrors 2601:2627:20598). This will require again a job to delete old rows and it will not bring you back into your scope the old rows that are just updated. All in all the first solution should be the best one.
Hope it helps.