I am at the point where I need to start archiving database of website I'm maintaining. Basically, lots of transactional data is generated daily, and after approximately 2 weeks, this data is pretty much useless - it is still needed for reporting purposes, but it can be safely moved from production server.
So, ideally I would love if I could do this:
On #1 - I am curious if pull replication is the most efficient way to go from performance standpoint? I don't want to put strain on production server (or at least not a big strain)... I don't care much about keeping databases in sync.
On #2 - How to make sure that these deletes are not replicated - that data is kept on archive server?
Production server runs SQL Server 2008 Enterprise, Backup server can run whatever needed (currently it runs SQL Server 2008 Express)
For #1, I'd say test it out and see. MS typically has a couple of whitepapers saying that pull subscriptions are more performant, but I don't recall them saying what it costs in terms of resources. If you're worried about it, set up a remote distributor (remote = not the same server as the publisher)
For #2, there are a couple of ways to do it. When you set up the article with sp_addarticle, there's a parameter @del_cmd which can take a value of "none". This will tell the distribution agent to no-op deletes at the subscriber. You could also do your purging with a stored procedure, set up the execution of the stored procedure as an article in replication and then have a stored procedure at the subscriber that does nothing. Each approach has its pros and cons.