n ways to avoid triggering mirror suspend in SQL Server _mssql

Background:
in the process of using a database, we often want to pursue performance, especially when dealing with large quantities of data. So for SQL Server, the database is optimized for high-volume inserts, and here is a special introduction to the way in which bulk inserts are done.
Basic principle:
The simple recovery model records most bulk operations minimally, and for databases under the full recovery model, all row inserts performed during bulk import are fully logged to the transaction log. If the data is imported in large quantities, it can cause the transaction log to fill quickly. For bulk-import operations, a minimal record is more efficient than a full record and reduces the likelihood that the bulk-import operation fills up the log space, so performance can be greatly improved.
However, the prerequisites for logging in a minimal manner in bulk import need to be met:
1. There are currently no replicated tables
2. Table locks are specified:
Note: Locking is an operation that the SQL Server database engine uses to synchronize multiple users accessing the same data block at the same time. When a transaction modifies a block of data, it holds the lock that protects the modification until the end of the transaction. When you specify a table lock for a bulk-import operation, the table will take a bulk update (BU) lock during the bulk-import operation. Bulk update (BU) locks allow multiple threads to concurrently bulk import data into the same table while preventing other processes that do not bulk import data from accessing the table. Table locking can be used to improve the performance of bulk-import operations by reducing table lock contention.
There is a lot of basic theoretical information, which is no longer described here.

In Aliyun SQL Server's primary standby architecture, when using bulk inserts, you need to pay special attention to a feature that needs to be explicitly specified and, if not specified, triggers a bug that Microsoft has not yet repaired in SQL Server 2008 R2, causing mirroring suspend, So how do you avoid all kinds of situations? Some common scenarios are listed below:
1, through Ado.net sqlbulkcopy way:
It is only necessary to specify the SqlBulkCopy sqlbulkcopyoptions.checkconstraints, and the database specifies the ADVENTUREWORKS2008R2 person table. As an example:

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 (sqlbulkcopyblkcpy = new SqlBulkCopy (desconnstring, sqlbulkcopyoptions.checkconstraints))//using (sqlbulkcopy blkcpy = new SqlBulkC Opy (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); The 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, through the JDBC SqlBulkCopy way:
You only need to specify the Setcheckconstraints property to be True when the object is initialized, for example:
Qlserverbulkcopyoptions copyoptions = new Sqlserverbulkcopyoptions ();
Copyoptions.setcheckconstraints (TRUE);
3, through the Dts/ssis way:
1. The Import/export data method requires that you first save the SSIS package and then modify the Connection Manager's properties

2. Direct use of SQL Server Business Intelligence Development stuidio new SSIS Package

3. By means of BCP
1. First the data bcp out of bcp ... Out
BCP Testdb.dbo.person out "bcp_data"/t/n/u * * * * * * * */S "****.sqlserver.rds.aliyuncs.com,3433"
2. Then bcp the data into bcp ... In, but you need to specify a hint:/h "check_constraints"
BCP Testdb.dbo.person in "Bcp_data"/c/n/q/k/h "check_constraints"/u * * * * * * * * * * 500/s "***.sqlserver.rds.aliyunc" s.com,3433 "
4, through the Bulk Insert method (in RDS can not be implemented, because the upload file is not allowed)

BULK INSERT testdb.dbo.person_in from
N ' D:\trace\bcp.txt '
with
(
 check_constraints 
);

Four ways to teach you to avoid triggering mirror suspend in SQL Server, hope to help you learn.