SQLServer · 最佳实践 · 数据库实现大容量插入的几种方式-阿里云开发者社区

开发者社区> 石沫01> 正文

SQLServer · 最佳实践 · 数据库实现大容量插入的几种方式

简介: --- title: SQLServer · 最佳实践 · 透数据库实现大容量插入的几种方式 author: 石沫 --- ## 背景 很多用户在使用阿里云云数据库SQL Server时,为了加快插入速度,都尝试使用大容量插入的方式,大家都知道,对于完整恢复模式下的数据库,大容量导入执行的所有行插入操作都会完整地记录在事务日志中。如果使用完整恢复模式,大型数据导入会导致
+关注继续查看

title: SQLServer · 最佳实践 · 透数据库实现大容量插入的几种方式

author: 石沫

背景

很多用户在使用阿里云云数据库SQL Server时,为了加快插入速度,都尝试使用大容量插入的方式,大家都知道,对于完整恢复模式下的数据库,大容量导入执行的所有行插入操作都会完整地记录在事务日志中。如果使用完整恢复模式,大型数据导入会导致填充事务日志的速度很快。相反,对于简单恢复模式或大容量日志恢复模式,大容量导入操作的按最小方式记录日志减少了大容量导入操作填满日志空间的可能性。另外,按最小方式记录日志的效率也比按完整方式记录日志高 。
但实际上,当大容量导入与数据库镜像共存时,会出现镜像 Suspend的情况,这个情况是由于微软在2008 R2上的BUG导致,详细你可以了解 https://support.microsoft.com/en-us/kb/2700641 ,微软已经明确表示在2008 R2不会FIXED,那么如何正确在RDS使用大容量导入并避免镜像异常,下面介绍几种方式。

  • 通过ADO.NET SQLBulkCopy 方式
只需要将SqlBulkCopy 指定SqlBulkCopyOptions.CheckConstraints就好,即:SqlBulkCopy blkcpy = new SqlBulkCopy(desConnString, SqlBulkCopyOptions.CheckConstraints)
例如:将本地的一个大表通过SQLBulkCopy方式导入到RDS的实例中

static void Main()
{

    string srcConnString = "Data Source=(local);Integrated Security=true;
    Initial Catalog=testdb";
    string desConnString = "Data Source=****.sqlserver.rds.aliyuncs.com,3433;
    UserID=**;Password=**;Initial Catalog=testdb";

    SqlConnection srcConnection = new SqlConnection();
    SqlConnection desConnection = new SqlConnection();

    SqlCommand sqlcmd = new SqlCommand();
    SqlDataAdapter da = new SqlDataAdapter();
    DataTable dt = new DataTable();

    srcConnection.ConnectionString = srcConnString;
    desConnection.ConnectionString = desConnString;
    sqlcmd.Connection = srcConnection;

    sqlcmd.CommandText = @"
    SELECT top 1000000 [PersonType],[NameStyle],[Title],[FirstName],[MiddleName],
    [LastName] ,[Suffix],[EmailPromotion],[AdditionalContactInfo],[Demographics],NULL 
    as rowguid,[ModifiedDate] FROM [testdb].[dbo].[Person]";

    sqlcmd.CommandType = CommandType.Text;
    sqlcmd.Connection.Open();
    da.SelectCommand = sqlcmd;
    da.Fill(dt);


    using (SqlBulkCopy blkcpy = 
    new  SqlBulkCopy(desConnString,SqlBulkCopyOptions.CheckConstraints))
    // using (SqlBulkCopy blkcpy = 
    // new SqlBulkCopy(desConnString, SqlBulkCopyOptions.Default))
    {
        blkcpy.BatchSize = 2000;
        blkcpy.BulkCopyTimeout = 5000;
        blkcpy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
        blkcpy.NotifyAfter = 2000;

        foreach (DataColumn dc in dt.Columns)
        {
            blkcpy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
        }

        try
        {
            blkcpy.DestinationTableName = "Person";
            blkcpy.WriteToServer(dt);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            sqlcmd.Clone();
            srcConnection.Close();
            desConnection.Close();

        }
    }

}

private static void OnSqlRowsCopied(
    object sender, SqlRowsCopiedEventArgs e)
{
    Console.WriteLine("Copied {0} so far...", e.RowsCopied);
}
  • 通过JDBC SQLServerBulkCopy 方式
同样的道理,需要在copyOptions指定检查约束性
SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();  
copyOptions.setCheckConstraints(true);
测试时,请用Microsoft JDBC Drivers 6.0 的sqljdbc41.jar,sqljdbc4.jar及更老版本没有SQLServerBulkCopy 实现。

例如: 将本地的一个大表通过SQLServerBulkCopy方式导入到RDS的实例中


import java.sql.*;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopy;
import com.microsoft.sqlserver.jdbc.SQLServerBulkCopyOptions;

public class Program {
    public static void main(String[] args)  
    {  
        String sourceConnectionString  = "jdbc:sqlserver://localhost:1433;" +  
                "databaseName=testdb;user=****;password=****";  
        String destConnectionString  = "jdbc:sqlserver://*****.sqlserver.rds.aliyuncs.com:3433;" +  
                "databaseName=testdb;user=****;password=**** ";  
        
        try  
        {  
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");  
            try (Connection sourceConnection =
                 DriverManager.getConnection(sourceConnectionString))  
            {  
                try (Statement stmt = sourceConnection.createStatement())  
                {  
                
  
                    try (ResultSet rsSourceData = stmt.executeQuery(  
                            " SELECT top 1000000 " +
                            "[PersonType],[NameStyle],[Title],[FirstName],[MiddleName],[LastName] ," +
                            "[Suffix],[EmailPromotion],[AdditionalContactInfo]," +
                            "[Demographics],NULL as rowguid,[ModifiedDate] " +
                            "FROM [testdb].[dbo].[Person]"))  
                    {   
                        try (Connection destinationConnection =  DriverManager.getConnection(destConnectionString))  
                        {  
                            
                            Statement stmt1 = destinationConnection.createStatement();
                                    
                            long countStart = 0;  
                            try (ResultSet rsRowCount = stmt1.executeQuery(  
                                    "SELECT COUNT(*) FROM dbo.Person;"))  
                            {  
                                rsRowCount.next();  
                                countStart = rsRowCount.getInt(1);  
                                System.out.println("Starting row count = " + countStart);  
                            }  
                            
                            try (SQLServerBulkCopy bulkCopy =   new SQLServerBulkCopy(destinationConnection))  
                            {  
                                SQLServerBulkCopyOptions copyOptions = new SQLServerBulkCopyOptions();  
                                copyOptions.setKeepIdentity(true);  
                                copyOptions.setBatchSize(2000);
                                copyOptions.setBulkCopyTimeout(5000);
                                //this is importance setting
                                copyOptions.setCheckConstraints(true);
                                 
                                bulkCopy.setBulkCopyOptions(copyOptions);
                                bulkCopy.setDestinationTableName("dbo.Person");  
                                
                                bulkCopy.addColumnMapping("PersonType", "PersonType");  
                                bulkCopy.addColumnMapping("NameStyle", "NameStyle");  
                                bulkCopy.addColumnMapping("Title", "Title");  
                                bulkCopy.addColumnMapping("FirstName", "FirstName");  
                                bulkCopy.addColumnMapping("MiddleName", "MiddleName");  
                                bulkCopy.addColumnMapping("LastName", "LastName");  
                                bulkCopy.addColumnMapping("Suffix", "Suffix");  
                                bulkCopy.addColumnMapping("EmailPromotion", "EmailPromotion");  
                                bulkCopy.addColumnMapping("AdditionalContactInfo", "AdditionalContactInfo");  
                                bulkCopy.addColumnMapping("Demographics", "Demographics");  
                                bulkCopy.addColumnMapping("rowguid", "rowguid");  
                                bulkCopy.addColumnMapping("ModifiedDate", "ModifiedDate");  
  
                                try  
                                {  
                                    bulkCopy.writeToServer(rsSourceData);  
                                }  
                                catch (Exception e)  
                                {  
                                    e.printStackTrace();  
                                }  
                                
                                
                                try (ResultSet rsRowCount = stmt1.executeQuery(  
                                        "SELECT COUNT(*) FROM dbo.Person;"))  
                                {  
                                    rsRowCount.next();  
                                    long countEnd = rsRowCount.getInt(1);  
                                    System.out.println("Ending row count = " + countEnd);  
                                    System.out.println((countEnd - countStart) + " rows were added.");  
                                }  
                                
                            }  

                        }  
                    }  
                }  
            }  
        }  
        catch (Exception e)  
        {  
            e.printStackTrace();  
        }  
    }  
  
 

}
  • 通过BCP方式

第一步:需要将数据BCP到本地

BCP testdb.dbo.person Out "bcp_data" /t  /N /U **** /P *** /S "****.sqlserver.rds.aliyuncs.com,3433"    

第二步:将导出的文件直接导入到RDS的实例中,但需要指定提示:/h "CHECK_CONSTRAINTS"

BCP testdb.dbo.person In "bcp_data" /C /N /q /k /h "CHECK_CONSTRAINTS" /U *** /P *** /b 500 /S  "***.sqlserver.rds.aliyuncs.com,3433"  
  • 通过DTS/SSIS方式

第一种:import/export data方式需要先保存SSIS包,然后修改Connection Manager的属性 ,如下图
1111111

第二种:直接使用SQL Server Business Intelligence Development Stuidio新建 SSIS包:

222222

  • 特别说明

不能在RDS通过下列两种方式进行大容量插入 :原因是基于安全考虑不提供上传文件到RDS 数据库服务器。

第一种:

BULK INSERT testdb.dbo.person_in
FROM N'D:\trace\bcp.txt'
WITH
(
 CHECK_CONSTRAINTS 
);  

第二种:

INSERT ... SELECT * FROM OPENROWSET(BULK...)
  • 总结

大容量导入数据会带来更快的插入,解决了用户在有大量数据导入缓慢困惑,在阿里云数据库中,你可以使用五种方式来实现业务场景,但是基于镜像的主备关系,需要特别加入一个检查约束的选项,这是写这个最佳实践的目的,一旦镜像SUSPEND,不断有DUMP文件产生,一来需要时间来修正,二来DUMP文件也会不断占用空间,但不会影响用户的可用性和可靠性。有两种方式在RDS中不能实现,另外,还可以通过ODBC来实现大容量导入,具体请参见https://msdn.microsoft.com/en-us/library/ms403302.aspx。希望这些对大家有用,特别是阿里云云数据库使用用户。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
大数据量查询容易OOM?试试MySQL流式查询
本文主要介绍如何使用流式查询并对比普通查询进行性能测试。
249 0
sql server 备份与恢复系列四 大容量模式下的备份与还原
原文:sql server 备份与恢复系列四 大容量模式下的备份与还原 一. 概述   在sql server 备份与恢复系列的第一篇里,有讲到大容量模式下备份与还原的相关知识。这篇重点来演示在大容量模式下常用的备份与还原模式“完整备份+差异备份+日志备份”。
763 0
MSSQL-最佳实践-实例级别数据库上云RDS SQL Server
--- title: MSSQL-最佳实践-实例级别数据库上云RDS SQL Server author: 风移 --- # 摘要 到目前,我们完成了SQL Server备份还原专题系列八篇月报分享:三种常见的数据库备份、备份策略的制定、查找备份链、数据库的三种恢复模式与备份之间的关系、利用文件组实现冷热数据隔离备份方案、如何监控备份还原进度、阿里云RDS SQL自动化迁移上云的一种
1482 0
将DataTable 数据插入 SQL SERVER 数据库
原文: 将DataTable 数据插入 SQL SERVER 数据库 以下提供3中方式将DataTable中的数据插入到SQL SERVER 数据库: 一:使用sqlcommand.
889 0
大数据&AI的16种可能,2020阿里云客户最佳实践合集下载
2020年9月18日下午13:00云栖大会正式发布 《大数据&AI的16种可能,2020阿里云客户最佳实践合集》
2703 0
HDFS 是如何实现大数据高容量、可靠的存储和访问的。
大数据数据量大、类型多种多样、快速的增长等特性,那么HDFS是如何去解决大数据存储、高可用访问的了?
126 0
PHP获取当前url路径的函数及服务器变量:$_SERVER["QUERY_STRING"],$_SERVER["REQUEST_URI"],$_SERVER["SCRIPT_NAME"],$_SER
1,$_SERVER["QUERY_STRING"] 说明:查询(query)的字符串 2,$_SERVER["REQUEST_URI"] 说明:访问此页面所需的URI 3,$_SERVER["SCRIPT_NAME"] 说明:包含当前脚本的路径 4,$_SERV...
819 0
MySQL---数据库从入门走向大神系列(九)-用Java向数据库读写大文本/二进制文件数据
介绍MySQL的文本和图形数据类型: Text 类型: 数据类型:描述 ------------------------------------------------------ char(size):保存固定长度的字符串(可包含字母、数字以及特殊字符)。
998 0
+关注
石沫01
长期在电子商务行业从事SQL Server的设计,开发与维护,拥有10年的相关经验,擅长数据库的架构与设计,擅长数据库的性能优化,擅长数据库的自动化和智能化运维,从2014年开始, 在云计算领域坚持奋斗, 阿里云SQL Server系列产品的设计与规划者
8
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载