Solutions for SQL Server data archiving

A recent new job is to archive SQL Server for four or five years of aging data (contracts, payments, reimbursements, etc.) because of the large daily data increment, and the fact that historical data is infrequently used, affecting data query operations in the production environment. Requirements are:

1 archived data is saved separately from production environment data to improve query efficiency and server performance.

2 Front-end users can query archived data, which means that the functionality provided by the system cannot be changed

It seems that the requirements are not very high, I naturally think of two methods, the first to create a new production environment of the same database, the archive data to the database; the second is to create a table with a suffix of _archive for each table in the production environment, such as invoice, Then create a invoice_archive that represents the data table that holds the archive. These two methods can be used to cross-database access or view of the way to solve data query and other requirements. After careful analysis, the drawback is that the existing system needs to be reformed, that is, the need to modify the code to adapt to the access to the archived data, the actual data access and business operations to Swatch, is a laborious and not pleasing solution.

Is there a way to transparently access production data and archived data without modifying the system? Of course, there is the partition table provided by SQL Server.

Here it is not cumbersome to repeat the definition and role of the partition table, to be proficient in the official Microsoft Documentation: SQL Server 2005 partition tables and indexes. My condensed role is that by using partitioned tables to split the data tables into unused disk files, different disks mean a performance boost, since two of the head read data is certainly faster than a head read, and then the user can transparently select the data according to different access methods. For example: A contract table, there is a field archived identification is archived (0 for non-archived, 1 for archived), we can use partitioned table, the Contract table is divided into two tables are saved on different disks, such as C and D, when we set a contract to be archived, This record will be transferred from C to D, usually we only query the records that have not been archived, if you want to check the archived records, also only need select * FROM contracts where archived = 1 So simple, that is, transparent query, the implementation of the specific we do not care.

Well, you can't just say no to practice. Create two file directories

　　

Create a test database

Use Master; GOif EXISTS (SELECT namefrom sys.databaseswhere name= N'TestDB') DROP DATABASE TestDB; Gocreate DATABASE testdbon PRIMARY (NAME='Testdb_part1', FILENAME='D:\TestData\Primary\TestDB_Part1.mdf', SIZE=Ten, MAXSIZE= -, FileGrowth=1), FILEGROUP testdb_part2 (NAME='TestDB_Part2', FILENAME='D:\TESTDATA\SECONDARY\TESTDB_PART2.NDF', SIZE=Ten, MAXSIZE= -, FileGrowth=1 ); GO

View data properties, a little different

Open Data:

use TestDB

New partition function, parameter type is bit, that is, archived data

Create Partition Function testdb_archivepartitionrange (bit) as RANGE Right For VALUES(1)

Create a new partition scheme where archived 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, bind a partition scheme

CREATE TABLE testarchivetable (archived Bit not NULL, Date DATETIME) On testdb_archivepatitionscheme (archived)

Insert some new data, ready for testing

INSERTIntoTestarchivetable (archived, Date)VALUES(0,‘2011-01-01‘);INSERTIntoTestarchivetable (archived, Date)VALUES (0,'2011-02-01'); INSERT into testarchivetable (archived, Date) VALUES (0,'2011-03-01 ');

Let's start with a normal query.

Take a look at each partition table where the data is stored, partition one has 3 records, Partition 2 is not recorded, that is, no archived data

SELECT * from sys.partitions WHERE object_name(object_id)= 'testarchivetable';

All right, we'll file a record.

UpdateTestarchivetableSetArchived=1whereDate=  2011-03-01 '   Select * < Span style= "color: #0000ff;" >from sys.partitions  where object_name Object_id) " testarchivetable ' ;&NBSP;

The result is what we want.

Summary: The use of partitioned tables not only greatly improve the performance of data access, but also can store data to different files as needed, so as to make efficient use of data and simplify the complexity of system development.

Solutions for SQL Server data archiving