欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

SQL Server中避免触发镜像SUSPEND的N种方法

程序员文章站 2022-04-14 11:37:37
背景: 我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对sql server而言,数据库实现大批量插入的优化方案,这里特别介...

背景:
我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对sql server而言,数据库实现大批量插入的优化方案,这里特别介绍通过大容量插入的一种方式。
基本原理:
简单恢复模式按最小方式记录大多数大容量操作,对于完整恢复模式下的数据库,大容量导入期间执行的所有行插入操作被完整地记录到事务日志中。如果数据导入量较大,会导致迅速填满事务日志。对于大容量导入操作,按最小方式记录比完整记录更有效,并减少了大容量导入操作填满日志空间的可能性,所以性能会得到极大的提升。
       但是,大容量导入中按最小方式记录日志的前提条件需要满足:
1. 当前没有复制表
2. 指定了表锁定:

注意:锁定是 sql server 数据库引擎用来对多个用户同时访问同一数据块的操作进行同步。当事务修改某个数据块时,它将持有保护所做修改的锁,直到事务结束。指定大容量导入操作的表锁定后,该表将在大容量导入操作期间采取大容量更新 (bu) 锁定。大容量更新 (bu) 锁允许多个线程将数据并发地大容量导入到同一表中,同时阻止其他不进行大容量导入数据的进程访问该表。表锁定可以通过减少表的锁争用来提高大容量导入操作的性能。
基本的理论信息还很多,这里不再累述。 

在阿里云sql server的主备架构中,使用大容量插入时,使用时需要特别留意一个特性需要明确指定,如果不指定,会触发微软尚未在sql server 2008 r2中未修复的bug,会导致镜像suspend,那么如何来避免各种情况呢? 下面列举了一些常见的场景:
 1、通过ado.net sqlbulkcopy 方式:
只需要将sqlbulkcopy 指定sqlbulkcopyoptions.checkconstraints就好,数据库指定adventureworks2008r2的person表。举个例子:

static void main()
{
  string srcconnstring = "data source=(local);integrated security=true;initial catalog=testdb";
  string desconnstring = "data source=****.sqlserver.rds.aliyuncs.com,3433;user id=**;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);
}

 2、通过jdbc  sqlbulkcopy 方式:
只需要在初始化对象时指定setcheckconstraints属性为true,例如:
qlserverbulkcopyoptions copyoptions = new sqlserverbulkcopyoptions();
copyoptions.setcheckconstraints(true);
3、通过dts/ssis方式:
1.    import/export data方式需要先保存ssis包,然后修改connection manager的属性

SQL Server中避免触发镜像SUSPEND的N种方法 

2.    直接使用sql server business intelligence development stuidio新建 ssis包

SQL Server中避免触发镜像SUSPEND的N种方法

3、通过bcp方式
1.      先将数据bcp出来 bcp ...out
bcp testdb.dbo.person out "bcp_data" /t  /n /u **** /p *** /s "****.sqlserver.rds.aliyuncs.com,3433"
2.      然后将数据bcp进去 bcp...in ,但需要指定提示:/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"
 4、通过bulk insert方式(在rds不可是实现,因为不允许上传文件)

bulk insert testdb.dbo.person_in
from n'd:\trace\bcp.txt'
with
(
 check_constraints 
);

四种方式教你在sql server中避免触发镜像suspend,希望对大家的学习有所帮助。