Best way for SQL Data archive

[l***@gmail.com]

What are the best ways of Archive data?

We are facing read and write performance problem because huge data set.
how to solve this.
Note:
1)We need last 12 month data for fast access other data reading if it bit slow also not a problem.

Regards,
Lalit

[Erland Sommarskog]

Post by l***@gmail.com
What are the best ways of Archive data?
We are facing read and write performance problem because huge data set.
how to solve this.

Those are two different problems. There is no problem with having ten
years or more of data and get good query performance if you just have
the appropriate indexes and your queries are well-formed.

On the other hand, having more data than you need in your main database
means that your backups will be bigger and more difficult to manage. This
also means that in case of a disaster, it will take longer time for your
database to come online. So from this perspective, it does make sense
to reduce the size of your database.

In any case, I responded to a similar question of yours the other day. This
post gives some more detail, but not much. (And the detail it gives,
possibly indicates that you are looking at the wrong approach for your
real problem.)

[l***@gmail.com]

Post by Erland Sommarskog

Post by l***@gmail.com
What are the best ways of Archive data?
We are facing read and write performance problem because huge data set.
how to solve this.

Those are two different problems. There is no problem with having ten
years or more of data and get good query performance if you just have
the appropriate indexes and your queries are well-formed.
On the other hand, having more data than you need in your main database
means that your backups will be bigger and more difficult to manage. This
also means that in case of a disaster, it will take longer time for your
database to come online. So from this perspective, it does make sense
to reduce the size of your database.
In any case, I responded to a similar question of yours the other day. This
post gives some more detail, but not much. (And the detail it gives,
possibly indicates that you are looking at the wrong approach for your
real problem.)
--

Hi Erland,

Thank you for your reply and sorry for in complete information in my post. Please find below clarification on problems regarding our database operations.

Our Database is in SQL server and it has huge data set. we are facing slowness issue on below operations.

1) Database query operations.
2) Database maintenance.


we need to resolve the above issues. Please tell me the best way.

Regards,
Lalit

[Erland Sommarskog]

Post by l***@gmail.com
Our Database is in SQL server and it has huge data set. we are facing
slowness issue on below operations.
1) Database query operations.
2) Database maintenance.
we need to resolve the above issues. Please tell me the best way.

If the queries are slow, the best course of action is to review indexing
and queries. Deleting old data, will of course alleviate the situation,
since there will be less to scan. But it will not address the root cause.

When it comes to database maintenance, including backups, reindexing and
statistics update, size can certainly be an issue, and if the old data
is not needed or considered "cold", there is all reason to take action.

But which is the best way depends on your requirements. That is, how do
you want to access the old data? Not at all? Must be part of the database
and seamless to users? Can you accept to have an archive database?

There are also questions about interdependence between "old" and "new"
data, that is entirely dependent on the business domain. It can be the
case that you must keep rows that would qualify as old, because they
affect data that is active.

[l***@gmail.com]

Post by Erland Sommarskog

Post by l***@gmail.com
Our Database is in SQL server and it has huge data set. we are facing
slowness issue on below operations.
1) Database query operations.
2) Database maintenance.
we need to resolve the above issues. Please tell me the best way.

If the queries are slow, the best course of action is to review indexing
and queries. Deleting old data, will of course alleviate the situation,
since there will be less to scan. But it will not address the root cause.
When it comes to database maintenance, including backups, reindexing and
statistics update, size can certainly be an issue, and if the old data
is not needed or considered "cold", there is all reason to take action.
But which is the best way depends on your requirements. That is, how do
you want to access the old data? Not at all? Must be part of the database
and seamless to users? Can you accept to have an archive database?
There are also questions about interdependence between "old" and "new"
data, that is entirely dependent on the business domain. It can be the
case that you must keep rows that would qualify as old, because they
affect data that is active.
--

Hi Erland,

Client needed old data we cannot delete it, we can go for data archive.
Please tell me, what are ways of data archiving in sql server? if needed how we can read it?

Regards,
Lalit

[Erland Sommarskog]

Post by l***@gmail.com
Client needed old data we cannot delete it, we can go for data archive.
Please tell me, what are ways of data archiving in sql server? if needed
how we can read it?

So go back to my previous post where I listed a number of options. You
have not given more information that permits me to give more specific
advice. All I know is that the option to just delete it is not of interest.