Geek Answers Handbook

SQL Server - the best replication model for archiving

I am at the point where I need to start archiving the database of the website that I support. Basically, a lot of transactional data is generated daily, and after about 2 weeks this data is largely useless - it is still necessary for reporting purposes, but it can be safely moved from the production server.

So, ideally, I would like for me to be able to do this:

  • Configure replication replication — The backup server retrieves changes from the production server hourly.
  • The database of the production server is cleaned daily - records older than 2 weeks are deleted.

At # 1 - I'm curious if replicating a run is the most efficient way to transition in terms of performance? I do not want to impose a production server (or, at least, do not strain much) ... I do not care what synchronizes the database.

On # 2 - How to make sure that these deletes are not replicated - is this data stored on the archive server?

Production server launches SQL Server 2008 Enterprise, backup server can run everything you need (currently it runs SQL Server 2008 Express)

+4
source share
2 answers

For # 1, I would say test it and see. Usually, MS has several official documents saying that pulling up subscriptions is more indicative, but I don’t remember saying they were worth it in terms of resources. If you are worried about this, set up a remote distributor (remote = not the same server as the publisher)

There are several ways to do this for # 2. When you create an article using sp_addarticle, there is an @del_cmd parameter, which can be set to "none". This will inform the distributor that it is not possible to delete the subscriber. You can also perform a cleanup using a stored procedure, configure the execution of the stored procedure as articles in replication, and then save the stored procedure to a subscriber who does nothing. Each approach has its pros and cons.

+2
source

I think we should apply a section to archive tables. If you configure replication and archive data for more than two weeks, the problem is to reinitialize replication, all subscriber data will be deleted and new data from the publisher will be applied.

0
source

More articles:


All Articles