Hope someone can help me. I have to log tables loggging in and outgoing messages to our plattform. I would like to move all data in them to a separate SQL Server every night, and then delete it.
At the moment I am moving about 2 x 20.000 rows every night, but expect this figure to be about 5 times bigger within a reasonably short amount of time. Now I insert into... and then delete from each table in two big transactions, and it takes just over an hour to complete. Now I am thinking about doing it in a different way. Using continous transactional replication, and just deleting from the log tables once a day. My question is:
Does anyone have any idea of the impact this will have on the performance of the log tables ?
I guess it is more of a question about how much resources the transactional log reader actually uses.