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

这篇文章主要介绍了SQL Server中避免触发镜像SUSPEND的N种方法,需要的朋友可以参考下

背景:
我们在使用数据库的过程中,很多时候要追求性能,特别在处理大批量数据的时候更希望快速处理。那么对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表。举个例子:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

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不可是实现,因为不允许上传文件)

?

1

2

3

4

5

6

BULK INSERT testdb.dbo.person_in

FROM N'D:\\trace\\bcp.txt'

WITH

(

CHECK_CONSTRAINTS

);

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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/26755/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-09-28 8:20:42
下一篇 2022-09-28 8:20:49

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!