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

  IF OBJECT_ID('DEMOTABLE') IS NOT NULL       DROP TABLE DEMOTABLE  GO  CREATE TABLE DEMOTABLE      (        COL1 VARCHAR(50) ,        COL2 VARCHAR(50) ,        COL3 VARCHAR(50)      )  INSERT  INTO DEMOTABLE         SELECT TOP 1000000                 NEWID() ,                 NEWID() ,                 NEWID()         FROM    MASTER..SPT_VALUES T1                 INNER JOIN MASTER..SPT_VALUES T2 ON 1 = 1                 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

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, which also provides an interface for code to implement 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

Using system; using system. data; using system. data. sqlclient; namespace bulkinsert {static class program {static void main () {datetime datetimestart = datetime. now; console. writeline ("START insert:" + datetimestart. tostring ("HH: mm: SS fff"); // import and export the database connection sqlconnection connectiondestination = new sqlconnection ("Server = .; user ID = datascan; Password = dtsbsd7188228; initial catalog = exportdatademo_destination; "); sqlconnection connectionsource = new sqlconnection (" Server = .; user ID = datascan; Password = dtsbsd7188228; initial catalog = exportdatademo_source; "); // instantiate a sqlbulkcopy var Bulker = new sqlbulkcopy (connectiondestination) {destinationtablename =" demotable ", bulkcopytimeout = 600}; // obtain the data of the source database sqlcommand sqlcmd = new sqlcommand ("select * From demotable", connectionsource); sqldataadapter = new sqldataadapter (sqlcmd ); datatable ableablesource = new datatable (); sqldataadapter. fill (datatablesource); // You can redefine the mapping relationship of a field. // sqlbulkcopycolumnmapping = new sqlbulkcopycolumnmapping ("col1", "new_col1"); // Bulker. columnmappings. add (sqlbulkcopycolumnmapping); connectiondestination. open (); Bulker. writetoserver (datatablesource); Bulker. close (); datetime datetimeend = datetime. now; console. writeline ("insert ending:" + datetimeend. tostring ("HH: mm: SS fff "));}}}

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

 EXEC sp_addlinkedserver @server = 'LinkedServerToDemo',     @srvproduct = 'Export Data Testing', @provider = 'MSDASQL',     @provstr = 'DRIVER={SQL Server};SERVER=192.168.21.165;UID=sa;PWD=password;'

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

Declare @ begin_date datetime declare @ end_date datetime select @ begin_date = getdate () insert into linkedservertodemo. exportdatademo_destination.dbo.demotable select * From exportdatademo_source.dbo.demotable select @ end_date = getdate () 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.