Data export and import methods and Performance Comparison between different versions of SQL Server

During the work period, the problem of exporting and importing data between databases of different versions is often involved. Simply sort it out and compare the performance. You are welcome to discuss and supplement some methods that are missing.

00. Create a test environment

01. use SQL Server Import and Export Tool

02. Use generate scripts

03. Use BCP

04. Use sqlbulkcopy

05. Use the linked server for data migration

06. SQL data compare using Redgate

07. Comparison of results

You can check the test results first.

 

00. Create a test environment

Create a test environment, a data source database, SQL Server 2008, a target database, and SQL Server 2000.

As shown in the experiment environment, the source database uses statements to generate 1 million of the test data.

 

Create a test table and generate 1 million Test Data

1 If Object_id ( ' Demotable ' ) Is Not Null
2 Drop Table Demotable
3 Go
4 Create Table Demotable
5 (
6 Col1 Varchar ( 50 ),
7 Col2 Varchar ( 50 ),
8 Col3 Varchar ( 50 )
9 )
10 Insert Into Demotable
11 Select Top 1000000
12 Newid (),
13 Newid (),
14 Newid ()
15 From Master .. spt_values T1
16 Inner Join Master .. spt_values T2 On 1 = 1
17 Inner Join Master .. spt_values T3 On 1 = 1 01. use SQL Server Import and Export Tool

Use SQL Server Import and Export tool to export data, or use import on the target database, which is also part of SSIs.

Right-click the source database and choose task> export data.

Enter the connection information of the source database and target database respectively.

 

 

Select "Copy data from one or more tables or views"

Select the table for data import and edit the mapping relationship of the column.

You can choose to run the package immediately or store it as SSIS for other purposes such as execution plan.

Here we choose to execute now.

Note: If the following error occurs during import

Error 0xc020f4: Data Flow task: the column "tel" cannot be processed because more than one code page (936 and 1252) are specified for it.
(SQL Server Import and Export wizard)

Because the collation settings of databases on both sides are different, you need to set the same collation.

• About 1 minute 30 seconds

02. Generate a script using generate scripts

Right-click the source database and choose task> geneate scripts...

Configure related information. Select the database version and set script data to true.

Note that the exported SQL file contains more than 1 million MB of data, so SQL Server Management studio cannot be used.

Therefore, only sqlcmd can be used for execution.

Sqlcmd statement

1 C :\ > Sqlcmd - I export. SQL - D exportdatademo_destination - S 192.168 . 21.165 - U sa - P 1234567890

• It takes about 28 minutes

03. Use BCP for Export and Import

After trying the previous two inefficient tools, we finally started to try SQL Server's dedicated data import tool: BCP.

For detailed usage of BCP, see the msdn help documentation.

We first use bcp to export data.

-U and-P are followed by the user name and password of the database respectively.

We can see that 1 million of data export takes only 1.8 seconds.

Now we can use BCP for import.

After execution, we found that it took 20.8 seconds to import the data, but it was still very fast.

• 1.872 seconds + 20.810 seconds = 22.682 seconds

04. Use sqlbulkcopy

The sqlbulkcopy class added in. NET Framework 2.0 can be used for efficient data migration.CodeProvides interfaces for data migration.

In addition, the sqlbulkcopy class provides the method to modify the mapping relationship of a field: columnmappings.

Use the sqlbulkcopy class for data migration

1 Using System;
2 Using System. Data;
3 Using System. Data. sqlclient;
4
5 Namespace Bulkinsert
6 {
7 Static Class Program
8 {
9 Static Void Main ()
10 {
11 Datetime datetimestart = datetime. now;
12 Console. writeline ( " Start insert: " + Datetimestart. tostring ( " Hh: mm: SS fff " ));
13 // Import and export database connections
14 Sqlconnection connectiondestination = New Sqlconnection ( " Server =.; user id = datascan; Password = dtsbsd7188228; initial catalog = exportdatademo_destination; " );
15 Sqlconnection connectionsource = New Sqlconnection ( " Server =.; user id = datascan; Password = dtsbsd7188228; initial catalog = exportdatademo_source; " );
16
17 // Instantiate a sqlbulkcopy
18 VaR Bulker = New Sqlbulkcopy (connectiondestination) {destinationtablename = " Demotable " , Bulkcopytimeout = 600 };
19
20 // Obtain data from the source database
21 Sqlcommand sqlcmd = New Sqlcommand ( " Select * From demotable " , Connectionsource );
22 Sqldataadapter = New Sqldataadapter (sqlcmd );
23 Datatable datatablesource = New Datatable ();
24 Sqldataadapter. Fill (datatablesource );
25
26 // You can redefine the mapping relationship of fields.
27 // Sqlbulkcopycolumnmapping = new sqlbulkcopycolumnmapping ("col1", "new_col1 ");
28 // Bulker. columnmappings. Add (sqlbulkcopycolumnmapping );
29 Connectiondestination. open ();
30 Bulker. writetoserver (datatablesource );
31 Bulker. Close ();
32 Datetime datetimeend = datetime. now;
33 Console. writeline ( " Insert ending: " + Datetimeend. tostring ( " Hh: mm: SS fff " ));
34 }
35 }
36 }

After execution

• Time: 14.8 seconds

05. Use the linked server for data migration

Create a linked server for the target database on the source database, or vice versa.

Create a linked server

1 Exec Sp_addmediaserver @ Server = ' Linkedservertodemo ' ,
2 @ Srvproduct = ' Export data testing ' , @ Provider = ' Msdasql ' ,
3 @ Provstr = ' Driver = {SQL Server}; server = 192.168.21.165; uid = sa; Pwd = password; '

 

USE insert into... select... to import

1 Declare @ Begin_date Datetime
2 Declare @ End_date Datetime
3 Select @ Begin_date = Getdate ()
4
5 Insert Into Linkedservertodemo. exportdatademo_destination.dbo.demotable
6 Select *
7 From Exportdatademo_source.dbo.demotable
8
9 Select @ End_date = Getdate ()
10 Select Datediff (MS, @ Begin_date , @ End_date ) As ' Time/millisecond '

Execution time

• Time: 7.97 minutes

06. Use Redgate's SQL data compare for data migration

Third-party tools include SQL compare for database structure comparison and SQL data compare for data comparison.

Run

Because the SQL statement that generates the insert statement is executed, it is not compared too much. It has been tested above.

07. Comparison of results

The test environment has special network and table structures, which cannot indicate the differences in performance in all cases, but can also be used as a reference.

The comparison result is given below.