DBA day-to-day management-data archiving (Archiving-data)

question:

As databases grow larger, the challenges to performance and management are growing. Each query may need to find more data pages, especially if the query has a scan operation, which can cause the query to become more and more slow. At the same time, there will be more and more data to be backed up and backup operations continue to grow longer. Backup files and databases use more and more data files, and so on, a whole range of problems will follow.

Data archiving becomes very important at this point. Data archiving and capacity planning are really relevant, but capacity planning will be covered in other articles.

The goal of data archiving is to monitor the size and growth of data, it's particularly important to plan for even a small library, because you can hardly guarantee that a small library will grow or become very important in the future, and that planning can have a very important impact on future management and optimization. In addition, archiving means that data needs to be saved for subsequent use, that is, you cannot delete it directly. Because many data warehouses require this data, the business database does not always need to be saved, which in turn reflects the importance of archiving.

In addition, the benefits of archiving are as they begin, reducing the size of backup files, speeding up the speed and cost of database restores, reducing the overhead of managing databases, improving performance, and so on, and just organizing the activity data.

In short, doing a good job of data archiving and capacity planning is one of the key tasks for DBAs or for managing database personnel on a day-to-day basis.

thinking:

1, to locate what data needs to be retained.

Generally archived data is a more obvious time column or business column, which can better identify the data to be processed. But it still needs to be tailored to the business needs. 2, how to access the data when needed.

Depending on the way the file is archived, there are different ways to access the data, which are described separately below. 3, the security requirements of archived data.

Since archived data no longer exists in the corresponding tables or even libraries or even servers, it is safe to consider security issues when archiving and to keep the archive data in mind. Solution:

For the above questions, there are a few things to consider: if the data must be kept in the same database:

Then you can move the data that needs to be archived to the new table, in practice, the archive table should have a certain prefix or suffix, for daily use. At the same time, put these tables into a separate file group, because these files are almost only "read-only" features, so put in a filegroup, on the one hand, you can reduce the size of the backup, you only need to use file backup or partial backup function, backup the active data in the filegroup. As for archiving data, you just have to do a regular backup. The time to restore is also fast. On the other hand, because this part of the filegroup is read-only, you can use some of the performance of static data is very effective technology, such as indexed view, column storage index, and other for read-only files and filegroups, do not need to lock, can reduce the problem of lock contention.

But be aware that if you do a full backup, this part of the data will still be included in the backup file, and not reduce the file size, so for this type of archive, it is usually recommended to use a partial backup or file backup. The other thing is that when you need to access this data, typically, you use views, merge the data you need, and then show that the presentation is not going to be much more general, because the view is not designed to improve performance, but it also has to be considered in terms of access, but because it's all in one library, the impact is not obvious.

For this kind of case archiving, a very useful feature in the 2005 and later versions--table partitioning, by partitioning the table, you can logically make no changes, but there are several areas that are physically divided, ideally, where data manipulation can occur only in a few or even a few areas, with partitioned indexes, can improve I/O utilization well. If you add the right data compression (remember not to shrink, 2008), you can improve I/O utilization and reduce space usage. Here is an example of how to move unwanted data to a new filegroup and do an access operation, and this example includes two implementations: for 2000 or 2005, but not the version of the table partition, such as Standard Edition, only in this way. is to demonstrate how to use table partitioning to implement. Here's how to do this without a table partition:

1. Create a filegroup and create a file to hold the archived data separately, this example uses the table Sales.SalesOrderHeader in the sample database AdventureWorks2012 to archive the data for 2006 in this table:

   1: Use  Master

   2:  

   3: Go  

   4:  

   5:  ALTER DATABASE AdventureWorks2012

   6:  

   7:  ADD FILEGROUP test1fg1;

   8:  

   9: Go  

  Ten:  

  11:  

  A:  

  :  ADD FILE

  :  

  :  (

  :  

  "  NAME = TEST1DAT3,

  :  

    FILENAME = ' D:\DB_Data\t1dat3.ndf ',

  :  

    SIZE = 5MB,

  :  

    MAXSIZE = 100MB,

  :  

    filegrowth = 5MB

  Num:  

  :  ),

  :  

  :  (

  :  

  :  NAME = TEST1DAT4,

  :  

  :  FILENAME = ' D:\DB_Data\t1dat4.ndf ',

  A:  

    SIZE = 5MB,

  By:  

  Notoginseng:  MAXSIZE = 100MB,

  A:  

    filegrowth = 5MB

  :  

  :  )

  :  

  A: to  FILEGROUP test1fg1;

  :  

  : Go  

To view file groups and file information:

   1:  SELECT * from sys.filegroups

The results are as follows:

To view file information:

   1: Use  AdventureWorks2012

   2:  

   3: Go  

   4:  

   5:  

   6:  

   7: From  sys.database_files

The results are as follows: