May 4, 2011 at 6:24 am
HI All,
In Transactional replication I want to maintain 3 months data in Publisher DB and 6 months data at Subscriber end.
To my knowledge in the replication data will be same at both end.
can we achieve this ?
Also what files will be created after creating the snapshot replication in repldata folder.
Thank You.
Regards,
Raghavender Chavva
May 9, 2011 at 3:12 am
Transactional replication has option to filter the rows/columns. See whether it is sutable for your scenario.
.sch, .bcp, .pre files are created.
May 12, 2011 at 4:06 am
You cannot keep more data at the subscriber. All deletes on the publisher would be replicated.
You "could" do this with replication to a degree. It would involve preventing deletes at the subscriber in some way, perhaps by editing the delete replication procs on the subscriber (comment out the command) and implementing your maintenance job on both servers.
I think a more logical solution would be to use a single SSIS package to copy data regularly to the destination and manage the data maintenance.
May 12, 2011 at 3:01 pm
This can be done. You need to modify the delete procedure used by replication to delete if older than 6 months. The procedure can be found on the subscriber database and should be something like, sp_MSdel_<publication>.
Alter this sp to suit your needs. Hope this helps
May 13, 2011 at 3:55 am
Pankaj Shere (5/12/2011)
This can be done. You need to modify the delete procedure used by replication to delete if older than 6 months. The procedure can be found on the subscriber database and should be something like, sp_MSdel_<publication>.Alter this sp to suit your needs. Hope this helps
Thats what I said was an option (a messy one).
BUT this wont work on its own as once the rows are deleted on the publisher database at 3 months, the command will never reach the subscriber for the 6 months delete.
E.g. you modify the delete command on the subscriber to delete where date <= (dateadd(mm, -6, getdate()). The delete at 3 months will not remove the row which is correct. However there will never be another command to delete it after 6 months as the publisher row has been removed.
May 13, 2011 at 8:56 am
Pankaj Shere (5/12/2011)
This can be done. You need to modify the delete procedure used by replication to delete if older than 6 months. The procedure can be found on the subscriber database and should be something like, sp_MSdel_<publication>.Alter this sp to suit your needs. Hope this helps
sp_MSDel_ stored procs deletes based on the PK. So modifying it is not going to work. What you can do is to exclude delete commands from replicating. This can be set up on the Publisher.
Then do what mysteryjimbo advised. That is to create a maintenance job in the subscriber that deletes data after 6 months.
But this has its downfall. If a delete is done on the publisher because of bad data, that will not be done automatically in the subscriber. You will have to take care of it manually.
Just my 2 cents
-Roy
May 13, 2011 at 9:48 am
Roy Ernest (5/13/2011)
Pankaj Shere (5/12/2011)
This can be done. You need to modify the delete procedure used by replication to delete if older than 6 months. The procedure can be found on the subscriber database and should be something like, sp_MSdel_<publication>.Alter this sp to suit your needs. Hope this helps
sp_MSDel_ stored procs deletes based on the PK. So modifying it is not going to work. What you can do is to exclude delete commands from replicating. This can be set up on the Publisher.
Then do what mysteryjimbo advised. That is to create a maintenance job in the subscriber that deletes data after 6 months.
But this has its downfall. If a delete is done on the publisher because of bad data, that will not be done automatically in the subscriber. You will have to take care of it manually.
Just my 2 cents
You can.
DELETE FROM table
WHERE pk1 = @c1
and date <= (dateadd(mm, -6, getdate()) /* Custom code */
but as I said it'll never delete anything as they will be deleted on the publisher at 3 months.
May 13, 2011 at 9:55 am
Thx... I did not think that far... 😀
-Roy
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply