SQL Server Data archiving solution

Recently, a new job was to archive the old-year data (contracts, payments, reimbursement, and other documents) stored in SQL Server for year 45, because the daily data increase is large, historical data is not frequently used and affects data queries in the production environment. Requirements:

 

1. archive data is stored separately from production environment data to improve query efficiency and server performance.

2. The front-end user can query archived data, that is, the functions provided by the system cannot be changed.

 

It seems that the requirements are not very high. I naturally think of two methods. The first method is to create a database that is the same as the production environment and save the archive data to this database; the second is to create a table with the extension of _ Archive for each table in the production environment. For example, if Invoice is used, an Invoice_Archive table is created to store the archived data table. These two methods can be used for cross-database access or view to solve data query and other needs. After careful analysis, the disadvantage is that the current system needs to be transformed, that is, the code needs to be modified to adapt to the access to the archive data. In fact, the data access and business operations are also carried out, is a laborious and thankless solution.

 

Is there a way to transparently access production data and archive data without modifying the system? Of course there are some partition tables provided by SQL Server.

Here, we will not repeat the definition and function of partition tables. To be proficient, we must carefully read the Microsoft official document: partition tables and indexes in SQL Server 2005. My function of concentration is to use partitioned tables to split data tables into unnecessary disk files. Different disks mean higher performance, because the two headers read data faster than the other one, you can select data transparently based on different access methods. For example, in a contract table, there is a field Archived to identify whether to archive (0 indicates not Archived, 1 indicates Archived). We can use the partition table method, divide the contract table into two tables and save them on different disks, such as c and d. When we set a contract to archive, this record will be transferred from disk c to disk d, normally, we only query Unarchived records. to query Archived records, we only need to select * from Contracts where Archived = 1, which is transparent query, we don't need to care about the specific implementation.

 

Okay, you can't just say no, just verify it. Create two file directories

　　

Create a Test Database

USE Master;
GO
If exists (
SELECT name
FROM sys. databases
WHERE name = n' testdb ')
Drop database TestDB;
GO
Create database TestDB
ON PRIMARY
(NAME = 'testdb _ Part1 ',
FILENAME =
'D: \ TestData \ Primary \ TestDB_Part1.mdf ',
SIZE = 10,
MAXSIZE = 100,
FILEGROWTH = 1 ),
FILEGROUP TestDB_Part2
(NAME = 'testdb _ part2 ',
FILENAME =
'D: \ TestData \ Secondary \ TestDB_Part2.ndf ',
SIZE = 10,
MAXSIZE = 100,
FILEGROWTH = 1 );
GO

Viewing data properties is a little different

Open Data:

Use TestDB

 

Create a partition function. The parameter type is bit, that is, archived data.

Create Partition Function TestDB_ArchivePartitionRange (bit)
As range right
VALUES (1)

 

Create a new partition scheme. archive data is saved to the TestDB_Part2 partition file.

CREATE Partition Scheme TestDB_ArchivePatitionScheme
As partition TestDB_ArchivePartitionRange
TO ([PRIMARY], TestDB_Part2 );

 

Create a test data table and bind a partition scheme

Create table TestArchiveTable
(Archived Bit not null, Date DATETIME)
ON TestDB_ArchivePatitionScheme (Archived)

 

Insert new data for testing

Insert into TestArchiveTable (Archived, Date) VALUES (0, '2017-01-01 ');
Insert into TestArchiveTable (Archived, Date) VALUES (0, '2017-02-01 ');
Insert into TestArchiveTable (Archived, Date) VALUES (0, '2017-03-01 ');

 

First, a common Query

 

Check the data stored in each partition table. There are three records in the partition, and partition 2 has no records, that is, there is no archive data.

SELECT * FROM sys. partitions
WHERE OBJECT_NAME (OBJECT_ID) = 'testarchivetable ';

 

Okay. Let's archive a record.

Update TestArchiveTable set Archived = 1 where Date = '2017-03-01'
SELECT * FROM sys. partitions
WHERE OBJECT_NAME (OBJECT_ID) = 'testarchivetable ';

 

The result is what we want.

 

Summary: Using Partitioned Tables not only greatly improves data access performance, but also stores data to different files as needed, so that we can use data effectively and simplify the complexity of system development.