Archiving Data

Question

Archiving Data

Dj463

Ten Centuries

Points: 1181
More actions
January 11, 2011 at 10:03 pm

#146750

Hello All,
I need to archive some tables of the database (Test) to another database with same name and extension as archive (Test_archive), so that the tables contain only 2 months old data . This database(Test) is being replicated. What would be the best strategy to archive this data.
Should the archive database be on same instance (or) separate instance of same server (or) on separate server.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sachnam SSCrazy Points: 2317 More actions · Answer 1

Dj463 (1/11/2011)
Should the archive database be on same instance (or) separate instance of same server (or) on separate server.

For Archiving a database there is no specific limitation that where your archived database should be.

It totally depends upon your business requirement.

If you want to use archived database for reporting purpose then ideally it should be on a seperate server to have better performance . but again it depends upon your database size and your H/w Infrastructure.

now coming back to your Question what is the better approach..

I would suggest to go with import\Export of for all the tables which you want to archive with date limits.

Archiving is the process to reduce\ off load your production database and keeping only the latest data to minimise the size of your database.

Dj463 Ten Centuries Points: 1181 More actions · Answer 2

Thanks for the reply sachnam,

As that database is already being replicated(Transactional replication). If we keep the subscriber database on separate server and modify replication in order to not propagate deletes to subscriber from publisher.

Can we use this subscriber database as archive database, so that even if we delete some data in production database tables it doesn't propagate to archive database tables.

Is Transactional replication a viable solution for Archiving the data?

sachnam SSCrazy Points: 2317 More actions · Answer 3

Dj463 (1/11/2011Is Transactional replication a viable solution for Archiving the data?

yes it can be a solution for you. but as you mentioned that if we avoid delete statements not to get replicated then what about inserts and updates happened after a particular point in it.

Archived database is used primarily for reporting sothat you could retrieve your old data from archived database and current reports from your production database (Publisher in your case)

but if you will allow insert and update to get applied to your subscriber then you will get duplicacy because the same copy of data is lying on both ends.

for Archiving i would recommend you to create a few jobs which run weekly or monthly depending upon your archiving criteria. but the data duplicacy must not exist between production and archived database otherwise it will be difficult for you to generate reports based on current and old data.

I hope you might have understood me.

Dj463 Ten Centuries Points: 1181 More actions · Answer 4

sachnam (1/11/2011)
Dj463 (1/11/2011)
.
I would suggest to go with import\Export of for all the tables which you want to archive with date limits.

I tried import/export tables but i was not able to do that with date limits