SQL Server Data Archiving scenario

SQL Server Data Archiving scenario

Purpose

This article is intended to provide a solution for archiving SQL Server Big data tables from a database management aspect. It can be used as a reference for the project design when the new business is on line.

Archive Solution Selection programme One: Program Introduction

After BCP exports the data to the local directory directory, the Traverse directory file bcp is imported into the staging table, and the source table data is recycled. By insert INTO ... Left Join ... Exclude existing data through primary key association temporary tables and archive tables. (or through 2008 and subsequent versions of the merge statement, there is no insert, there is an update)

650) this.width=650; "title=" clip_image001 "style=" border-right-width:0px;border-bottom-width:0px;margin-left:0px ; border-top-width:0px;margin-right:0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/53/97/ Wkiol1rscqljinsoaafglcseqq0907.jpg "height=" 526 "width=" 1045 "border=" 0 "/>

Solution Pros and cons

Advantages: Good bcp performance, easy control of logic via SP, easy maintenance

Cons: Logic is a little cumbersome

Scenario Two: Program Introduction

BCP exports the data to the remote directory, loops through the source table data, traverses the directory file bcp into the temp table, and then inserts into ... Left Join ... Exclude existing data through primary key association temporary tables and archive tables. (or through 2008 and subsequent versions of the merge statement, there is no insert, there is an update)

650) this.width=650; "title=" clip_image002 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image002 "src=" http://s3.51cto.com/wyfs02/M02/53/97/ Wkiol1rscqbrnqduaafn9vtoelq848.jpg "height=" 523 "width=" 1040 "border=" 0 "/>

Solution Pros and cons

Advantages: Good bcp performance, easy control of logic via SP, easy maintenance

Cons: Logic is slightly cumbersome, two hosts need to be able to access UNC shares

Programme III: Program Introduction

BCP exports the data to the local directory after FTP to the remote directory, then iterates through the source table data, traverses the directory file bcp into the temp table, and then through insert INTO ... Left Join ... Exclude existing data through primary key association temporary tables and archive tables. (or through 2008 and subsequent versions of the merge statement, there is no insert, there is an update)

650) this.width=650; "title=" clip_image003 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image003 "src=" http://s3.51cto.com/wyfs02/M00/53/97/ Wkiol1rscqmdfpioaagredk1f0o446.jpg "height=" 525 "width=" 1042 "border=" 0 "/>

Solution Pros and cons

Advantages: Good bcp performance, easy control of logic via SP, easy maintenance

Cons: Logic is slightly cumbersome, remote to set up an FTP server

Programme IV: Program Introduction

BCP exports data to a remote directory, loops through the source table data, and traverses the catalog file for SSIS data import and update.

650) this.width=650; "title=" clip_image004 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image004 "src=" http://s3.51cto.com/wyfs02/M01/53/97/ Wkiol1rscquwnvakaaeqzxnnih8071.jpg "height=" 523 "width=" 1041 "border=" 0 "/>

Solution Pros and cons

Pros: Good bcp performance

Disadvantage: The SSIS package needs to be modified after the field changes, the SSIS package maintenance is cumbersome, and the two hosts need to be able to access UNC shares

Programme V: Program Introduction

BCP exports the data to the local directory after FTP to the remote directory, loops through the source table data, and then traverses the catalog file for SSIS data import and update.

650) this.width=650; "title=" clip_image005 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image005 "src=" http://s3.51cto.com/wyfs02/M02/53/97/ Wkiol1rscqygff4zaafg8gdgfk4778.jpg "height=" 525 "width=" 1040 "border=" 0 "/>

Solution Pros and cons

Pros: Good bcp performance

Cons: The field changes well, SSIS package needs to be modified synchronously, SSIS package maintenance trouble, remote to set up an FTP server

Programme VI: Program Introduction

Data is inserted into another temporary archive library to iterate through the source table data, and then copy the Temporary archive table to remote.

650) this.width=650; "title=" clip_image006 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image006 "src=" http://s3.51cto.com/wyfs02/M01/53/99/ Wkiom1rscjyhu4hgaaei-2lopkq419.jpg "height=" 525 "width=" 1042 "border=" 0 "/>

Solution Pros and cons

Pros: Simple logic

Cons: Need to build another temporary archive library, add load

Programme VII: Program Introduction

The data is deleted through linkedserver after inserting the Remote archive table.

650) this.width=650; "title=" clip_image007 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image007 "src=" http://s3.51cto.com/wyfs02/M00/53/99/ Wkiom1rsckljwwt0aadoprjtgzs468.jpg "height=" 527 "width=" 1041 "border=" 0 "/>

Solution Pros and cons

Pros: Simple logic

Cons: LinkedServer Remote performance issues

Programme VIII: Program Introduction

switch partitions to temporary tables, and the data is then plugged into the Remote archive table via LinkedServer.

650) this.width=650; "title=" clip_image008 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image008 "src=" http://s3.51cto.com/wyfs02/M01/53/99/ Wkiom1rsckka9-9caad3q0in2f4877.jpg "height=" 524 "width=" 1043 "border=" 0 "/>

Solution Pros and cons

Advantages: Easy Data archiving

Cons: Only for partitioned tables

Programme IX: Program Introduction

SSIS iterates through the source tables after inserting or updating data into the archive table.

650) this.width=650; "title=" clip_image009 "style=" border-right-width:0px;border-bottom-width:0px; border-top-width:0px, "alt=" clip_image009 "src=" http://s3.51cto.com/wyfs02/M02/53/99/ Wkiom1rscktt9aesaadt6zv6efs378.jpg "height=" 525 "width=" 1042 "border=" 0 "/>

Solution Pros and cons

Pros: Logic Integration

Cons: High maintenance costs, trouble locating problems

Programme ten: Program Introduction

Open source software, such as Eyearchive

Solution Pros and cons

Pros: Open source software

Cons: Single method

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1579443

SQL Server Data Archiving scenario