how to archive very large databases and access the same

Question

how to archive very large databases and access the same

Rohithg4all

SSC Enthusiast

Points: 121
More actions
April 14, 2009 at 2:41 am

#220123

How to archive large databases of almost 350GB on SQL server 2000 and access the same. This is inorder to improve performance.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Atif Hall of Fame Points: 3578 More actions · Answer 1

Archived databases have different priorities than production databases. It would not be a good idea to use same database for archival and production. Even you would have to make certain objects changes to archive a database. There would be more de-normalized tables than production database. Also archival operations will create IO and memory bottle necks when used in production environment.

DBDigger Microsoft Data Platform Consultancy.

Rohithg4all SSC Enthusiast Points: 121 More actions · Answer 2

Dear Sir,

Please let me know how to archive the production databases. Also clarify if we can access the same archive database together production database.

Atif Hall of Fame Points: 3578 More actions · Answer 3

Archival database are mostly created to preserve a historical snapshot of production data. Production databases are meant to change rapidly, so organizations mostly develop archival plans where specific data from production database is transferred periodically with time track. Archival database may also be used for reporting purposes.

Even in certain scenarios production databases are purged after data transfer to archival database.

DBDigger Microsoft Data Platform Consultancy.

Rohithg4all SSC Enthusiast Points: 121 More actions · Answer 4

Dear Atif,

Thanks for your suggestion,

Let me explain the problem here, We have a large Production database of size 350 GB and performance is very slow. Hence we are trying to identify a methord so that database should be splited into two where the current/latest data should be in first database and remaining data should be in second database. Both first and second databases should be able to access.

Please assit

Thank you in advance,

Rohith

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 5

Does your 350 GB database have historical data? If so, is all the historical data being accessed all the time? Have you considered partitioning?

Are you currently having any performance issues?

rajiniforu Ten Centuries Points: 1147 More actions · Answer 6

Hi,

First need to understand if you just want to archive old data which is no more being updated and want it to move to archived databases to future access? If yes, then you can Copy the data to a history table and delete that data from the production database.

Implementing an archival process is a bit complicated and needs understanding of what is actually required. Concentrate on the following:

Identifying the data to be archived.

Copying the identified data to a history table and

Delete it from the production database.

You may need to archive more than just a single table, then we need to understand how different tables in the database are related to each other through foreign key relationships and then, sequence your delete/archive process, so that you are not violating any of the constraints.

-Rajini

Rohithg4all SSC Enthusiast Points: 121 More actions · Answer 7

Yes, it is historical data which increases day to day as the users use. This historical data will be accessed only a few times in a month.

By data partitioning did you mean spliting of tables?

Yes we are facing Performance issue.

Atif Hall of Fame Points: 3578 More actions · Answer 8

As a broad picture you may consider following lines,

figure out the tables required to archive.

After that there may be columns filtration process for the selected tables to include in archiving. Not all data in a production database is meant for archival.

After tables selection identify relations and dependency for selected objects.

Create separate database for archival. To keep it simple you may create as much tables in archival database as you choose to transfer from production.

Number of columns in tables may be less in archival database.

Create scripts for data transfer from production to archival.

Schedule script for specified timing. May be daily or once a week.

DBDigger Microsoft Data Platform Consultancy.

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 9

you can follow the "AShehzad" instructions.

> Create another database like _Archive and create the tables and dump the data into this archive database.

> Write a script in regular database access the Archive database.

Try it.

Atif Hall of Fame Points: 3578 More actions · Answer 10

I would suggest not to create relations among tables in archival database. Time stamps for archived data will matter and new primary keys will be required other than those of production database.

DBDigger Microsoft Data Platform Consultancy.

SA-1 SSCrazy Eights Points: 8113 More actions · Answer 11

It seems, like most historical data, your data serves a reporting function. If the current data is part of an OLTP, I would probably look to move the historical data to its own server. This could provide you with performance improvement as well.

Again this would depend completely on how the current data is being consumed by your users. Of course this will require careful planning.