MSSQL SERVER based table partition and automatic maintenance method and system

An automatic maintenance and table partitioning technology, applied in the field of database applications, can solve the problems of increasing the difficulty of data file management and the workload of maintenance, and achieve the effect of improving storage space performance and reducing workload.

Active Publication Date: 2014-04-02
XIAMEN MEIYA PICO INFORMATION
View PDF2 Cites 15 Cited by
  • Summary
  • Abstract
  • Description
  • Claims
  • Application Information

AI Technical Summary

Problems solved by technology

[0011] However, when the MSSQL server table partition technology is applied to the relevant application fields of massive data, due to the characteristics of the MSSQL server table partition itself, the number of table partitions will be as many as the number of file groups, which increases the difficulty of data file management
In addition, when the data reaches the limit of the partition function, it is necessary to manually expand and create a new file group to store the new data, which will increase the difficulty of data file management due to too many data files; , as many as hundreds) partition table, the workload of maintenance brought to the dba is indeed relatively large

Method used

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
View more

Image

Smart Image Click on the blue labels to locate them in the text.
Viewing Examples
Smart Image
  • MSSQL SERVER based table partition and automatic maintenance method and system
  • MSSQL SERVER based table partition and automatic maintenance method and system
  • MSSQL SERVER based table partition and automatic maintenance method and system

Examples

Experimental program
Comparison scheme
Effect test

Embodiment Construction

[0064] In order to describe the technical content, structural features, achieved goals and effects of the present invention in detail, the following will be described in detail in conjunction with the embodiments and accompanying drawings.

[0065] The invention relates to a table partition and automatic maintenance method based on MSSQL SERVER, which includes partition steps and automatic maintenance steps;

[0066] The partitioning step includes, designing a data retention strategy for tables of the same type according to requirements, and creating a table for the same type of table based on the same data retention strategy, partition function and partition architecture; the table creation includes sequentially creating a table partition function, creating a table partition Schema, which defines the table partition structure;

[0067] The automatic maintenance step includes: judging whether the tables in the database need to be sorted by table partitions, if necessary, dumpi...

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

PUM

No PUM Login to view more

Abstract

The invention provides an MSSQL SERVER based table partition and automatic maintenance method and system. When tables are created, the same class of tables refer to data retention strategies and a same partition function, a partition framework and structural definitions are configured, and further when maintenance is needed, functions of automatic dumping of historical data of MSSQL server table partitions, automatic addition of new partitions, automatic merging of data-dumped old partitions, duty-cycle operation of file groups and the like are realized according to the data retention strategies made in advance; data needed by set services in the data retention strategies are retained, and stale data are deleted or dumped, so that the purposes for automatically maintaining a large amount of partitioned tables and maintaining forms of a database by itself are achieved, storage space performance of the database is improved effectively, and workload of a dba (database administrator) is relieved greatly so as to enable the dba to concentrate more energy on other critical fields.

Description

technical field [0001] The invention relates to the field of database application, in particular to a method and system for table partitioning and automatic maintenance based on MSSQL SERVER. Background technique [0002] MSSQL SERVER refers to Microsoft's SQL Server database server, which is a database platform that provides a complete solution from the server to the terminal of the database. The database server part is a database management system for establishing, using and maintaining the database. In MSSQLSERVER, table partition (PARTITION) is a component of MSSQL server database. This technology was first introduced in MSSQL server2005 Enterprise Edition. This process is to divide data into areas according to certain standards and store them in different file groups. Use Partitions make large tables or indexes easier to manage by quickly and efficiently managing and accessing subsets of data. As far as the application accessing the MSSQL server database is concerned, ...

Claims

the structure of the environmentally friendly knitted fabric provided by the present invention; figure 2 Flow chart of the yarn wrapping machine for environmentally friendly knitted fabrics and storage devices; image 3 Is the parameter map of the yarn covering machine
Login to view more

Application Information

Patent Timeline
no application Login to view more
Patent Type & Authority Applications(China)
IPC IPC(8): G06F17/30
CPCG06F16/2282G06F16/24554
Inventor 余有能
Owner XIAMEN MEIYA PICO INFORMATION
Who we serve
  • R&D Engineer
  • R&D Manager
  • IP Professional
Why Eureka
  • Industry Leading Data Capabilities
  • Powerful AI technology
  • Patent DNA Extraction
Social media
Try Eureka
PatSnap group products