Detailed description of SQL Server 2016 snapshot proxy process, sql2016

Detailed description of SQL Server 2016 snapshot proxy process, sql2016

In this article, we use an instance data table of SQL Server 2016 to give you a detailed analysis of the problems encountered during the snapshot proxy process and solutions, and provide a detailed description of the snapshot generation process, the following is all:

Overview

The Snapshot agent prepares the architecture and initial data files and other objects of published tables, stores snapshot files, and records synchronization information in the distribution database. The Snapshot Agent runs on the distributor. SQLServer2016 optimizes the snapshot agent. Next, let's take a look at the snapshot execution process.

I. snapshot proxy File

When a snapshot job is executed, four types of files are generated in the specified snapshot directory.

BCP file: the data file of the published object.

IDX file: index creation script file

PRE file: copy the snapshot script file.

SCH file: script file for Schema Creation

Ii. Default snapshot proxy configuration file

-BcpBachSize: Maximum number of record lines for each bcp copy operation. The default value is 0.1 million.

-HistoryVerboseLevel: Specifies the size of historical records recorded during the snapshot operation.

-LoginTimeout: the number of seconds to wait before the logon times out. The default value is 15 seconds.

-QueryTimeOut: the number of seconds to wait before the query times out. The default value is 1800 seconds.

Note: you can right-click the snapshot proxy-Snapshot proxy configuration file to configure the snapshot proxy.

Iii. Comparison of Snapshot Agents of different versions

Next, we will test and compare the snapshot generated from the 0.2 billion record table.

1. Comparison of bcp files

2008R2

 

2016SP1

Here we will focus on BCP files, because the application snapshot to the subscription Server is a BCP file as the basic unit, that is, no matter how large your BCP file is, it is a one-time bulk to the subscription server, therefore, the larger the BCP file, the longer the application time. If a BCP file is too large, insertion into the subscription end may fail.

We can see that the record is also 0.2 billion. 2008R2 has a total of 8 BCP files, and the maximum BCP file size is nearly 1 GB. The remaining BCP files are only a few megabytes. 2016 has 16 BCP files, the first 15 data records are about 50 MB in size. Next we will look at the record comparison of each BCP file.

2. Detailed Process Comparison of snapshot generation

2008r2

2016SP1

Compare the generated BCP file records:

2008R2: Each of the first seven files has about 0.7 million records, and the last file has 0.11 billion records.

2016: about 7 million records for each of the first 15 files and 0.78 million records for the last file.

Note:

The total number of records stored in each of the first seven files in 2008R2 is 0.7 million. The remaining records are stored in the last file. Therefore, 2008R2 is suitable for about 6 million table records.

2016 of the first 15 files, each of which stores about 7 million of the remaining records, will be stored in the last file, and about 2016 suitable table records are about 0.12 billion.

Common disadvantage: If the table record exceeds the "number of records suitable for copying tables", all the remaining data will be stored in the last bcp file.

3. Distribution comparison

Next, let's take a look at the detailed process of distribution.

From the R2 distribution record process, we can see that every BULK is based on bcp files. It takes about 22 minutes to copy the last bcp file, and every previous file is a dozen seconds; because my current table only has three fields and the primary key is not indexed, it will take a longer time.

Iv. snapshot Generation Process

The copy snapshot agent is an executable file used to prepare snapshot files (including the Architecture and Data of published tables and database objects) and store these files in the snapshot folder, and record synchronization jobs in the distribution database.

You can understand the entire snapshot generation process.

V. Syntax

snapshot [ -?]  -Publisher server_name[\instance_name]  -Publication publication_name  [-70Subscribers]  [-BcpBatchSize bcp_batch_size] [-DefinitionFile def_path_and_file_name] [-Distributor server_name[\instance_name]] [-DistributorDeadlockPriority [-1|0|1] ] [-DistributorLogin distributor_login] [-DistributorPassword distributor_password] [-DistributorSecurityMode [0|1] ] [-DynamicFilterHostName dynamic_filter_host_name] [-DynamicFilterLogin dynamic_filter_login] [-DynamicSnapshotLocation dynamic_snapshot_location]  [-EncryptionLevel [0|1|2]] [-FieldDelimiter field_delimiter] [-HistoryVerboseLevel [0|1|2|3] ] [-HRBcpBlocks number_of_blocks ] [-HRBcpBlockSize block_size ] [-HRBcpDynamicBlocks ] [-KeepAliveMessageInterval keep_alive_interval] [-LoginTimeOut login_time_out_seconds] [-MaxBcpThreads number_of_threads ] [-MaxNetworkOptimization [0|1]] [-Output output_path_and_file_name] [-OutputVerboseLevel [0|1|2] ] [-PacketSize packet_size] [-ProfileName profile_name] [-PublisherDB publisher_database] [-PublisherDeadlockPriority [-1|0|1] ] [-PublisherFailoverPartner server_name[\instance_name] ] [-PublisherLogin publisher_login] [-PublisherPassword publisher_password]  [-PublisherSecurityMode [0|1] ] [-QueryTimeOut query_time_out_seconds] [-ReplicationType [1|2] ] [-RowDelimiter row_delimiter] [-StartQueueTimeout start_queue_timeout_seconds] [-UsePerArticleContentsView use_per_article_contents_view] 

Parameters

-?

Output all available parameters.

-PublisherServer_name[\Instance_name]

The name of the Publishing Server. Specify server_name for the default Microsoft SQL Server instance on the Server. ForServer_name\Instance_nameInstance_name the default instance of SQL Server specifies server_name.

-PublicationRelease

The name of the release. This parameter is valid only when publishing is set to always make a snapshot available for a New subscription or reinitialization subscription.

-70 Subscribers

This parameter is required if any subscription Server is running SQL Server 7.0.

-BcpBatchSizeBcp batch \ Size

The number of rows sent in a large-capacity copy operation. When the bcp in operation is executed, the batch size is the number of rows to be sent to the server as a transaction, and it is also the number of lines that must be sent before the distribution agent records the bcp progress message. When the bcp out operation is performed, a fixed batch size of 1000 is used. If the value is 0, no messages are recorded.

-DefinitionFileDef_path_and_file_name

The path of the file defined by the proxy. The proxy definition file contains the command line parameters of the proxy. The file content is analyzed as an executable file. Double quotation marks (") are used to specify the parameter value that contains any character.

-DistributorServer_name[\Instance_name]

The name of the distributor. ForDefault instanceServer_name SQL Server. ForServer_name\Instance_nameInstance_name the default instance of SQL Server specifies server_name.

-DistributorDeadlockPriority [-1 | 0 | 1]

Priority of the snapshot proxy connecting to the distributor when a deadlock occurs. This parameter is specified to solve the deadlock between the snapshot proxy and the user application during snapshot generation.

DistributorDeadlockPriority Value

Description

-1

When a life-or-death lock is triggered on the distributor, the application rather than the snapshot proxy takes priority.

0 (default)

No priority is assigned.

1

The Snapshot proxy takes precedence over the lifecycle lock on the distribution server.

-DistributorLoginDistributor_login

The login name used to connect to the distributor using SQL Server Authentication.

-DistributorPasswordDistributor_password

The password used to connect to the distributor using SQL Server Authentication. .

-DistributorSecurityMode [0 | 1]

Specifies the Security Mode of the distributor. The value 0 indicates the SQL Server Authentication Mode (default), and the value 1 indicates the Windows Authentication mode.

-DynamicFilterHostNameDynamic_filter_host_name

When creating a dynamic snapshot, it is used to set a value for HOST_NAME (Transact-SQL) in the filter. For example, if you specify the subset filter clause rep_id = HOST_NAME () for the project and set the DynamicFilterHostName attribute to "FBJones" before calling the merge proxy ", only the rows with "FBJones" in the rep_id column will be copied.

-DynamicFilterLoginDynamic_filter_login

When creating a dynamic snapshot, it is used to set a value for SUSER_SNAME (Transact-SQL) in the filter. For example, if you specify the subset filter clause user_id = SUSER_SNAME () for the project and set the DynamicFilterLogin attribute to "rsmith" before calling the Run method of the SQLSnapshot object ", only the rows with "rsmith" in the user_id column are included in the snapshot.

-DynamicSnapshotLocationDynamic_snapshot_location

The location where dynamic snapshots should be generated.

-EncryptionLevel [0 | 1 | 2]
The SSL encryption level used by the snapshot proxy when a connection is established.

EncryptionLevel Value

Description

0

Specify not to use SSL.

1

Specifies that SSL is used, but the proxy does not verify that the SSL server certificate has been signed by a trusted issuer.

2

Specify to use SSL and verify the certificate.

-FieldDelimiterField_delimiter
The character or character sequence used to mark the end of a field in the SQL Server large-capacity copy data file. The default value is \ n <x $3> \ n.

-HistoryVerboseLevel [1 | 2 | 3]
Specifies the size of historical records recorded during the snapshot operation. Select 1 to minimize the impact of historical logs on performance.

HistoryVerboseLevel Value

Description

0

The progress message is written to the console or output file. Records are not recorded in the distribution database.

1

Always update previous history messages with the same status (startup, in progress, success, etc. If there is no previous record with the same status, a new record is inserted.

2 (default)

A new history is inserted unless it is recorded as an idle message or a job message that has been running for a long time (the previous record will be updated at this time.

3

Always Insert a new record, unless it is related to idle messages.

-HRBcpBlocksNumber_of_blocks

The number of bcp data blocks that are queued between the writer thread and the reader thread. The default value is 50. HRBcpBlocks is only used for Oracle release.

Remarks

This parameter is used to optimize the performance of bcp through the Oracle Publishing Server.

-HRBcpBlockSizeBlock_size

The size of each bcp data block (in KB ). The default value is 64 KB. HRBcpBlocks is only used for Oracle release.

Remarks

This parameter is used to optimize the performance of bcp through the Oracle Publishing Server.

-HRBcpDynamicBlocks

Whether the size of each bcp data block can be dynamically increased. HRBcpBlocks is only used for Oracle release.

Remarks

This parameter is used to optimize the performance of bcp through the Oracle Publishing Server.

-KeepAliveMessageIntervalKeep_alive_interval

The time (in seconds) that the snapshot agent waits before recording "waiting for backend message" in the MSsnapshot_history table ). Default Value: 300 seconds.

-LoginTimeOutLogin_time_out_seconds

Number of seconds before logon timeout. The default value is 15 seconds.

-MaxBcpThreadsNumber_of_threads

Specifies the number of large-capacity replication operations that can be executed in parallel. The maximum number of concurrent threads and ODBC connections is MaxBcpThreads or the one that shows a small number of large-capacity replication requests in synchronization transactions in the distributed database. The MaxBcpThreads value must be greater than 0, and there is no hard encoding limit. The default value is 1.

-MaxNetworkOptimization [0 | 1]

Whether to send irrelevant delete operations to the subscription server. An unrelated DELETE operation is a DELETE command sent to the subscription server for rows that do not belong to the subscriber partition. Unrelated deletion operations will not affect data integrity or convergence, but they will lead to unnecessary network communication. The default value of MaxNetworkOptimization is 0. Setting MaxNetworkOptimization to 1 minimizes the chances of unrelated delete operations, reducing network communication and maximizing network optimization. If there are multiple levels of join filters and complex subset filters, setting this parameter to 1 will also increase the storage of metadata and cause the performance of the Publishing Server to decline. You should evaluate your replication topology carefully. Set MaxNetworkOptimization to 1 only when the network communication caused by irrelevant delete operations is too high to be accepted.

Remarks

It is useful to set this parameter to 1 only when the @ keep_partition_changes parameter of sp_addmergepublication (Transact-SQL) is set to true.

-OutputOutput_path_and_file_name

The path of the proxy output file. If no file name is provided, the output is sent to the console. If the specified file name already exists, the output will be appended to the file.

-OutputVerboseLevel [0 | 1 | 2]

Specifies whether the output should provide details.

OutputVerboseLevel Value

Description

0

Only error messages are output.

1 (default)

Output all progress report messages (default ).

2

Output all error messages and progress report messages, which are useful for debugging.

-PacketSizePacket_size

The data packet size (in bytes) used by the snapshot agent to connect to SQL Server ). The default value is 8192 bytes.

Remarks

Do not change the data packet size unless you are sure to improve performance. For most applications, the default data packet size is the optimal value.

-ProfileNameProfile_name

Specifies the proxy configuration file used for proxy parameters. If ProfileName is NULL, the proxy configuration file is disabled. If ProfileName is not specified, the default configuration file of this proxy type is used.

-PublisherDBPublisher_database

The name of the published database. The Oracle Publishing Server does not support this parameter.

-PublisherDeadlockPriority [-1 | 0 | 1]

Priority of the snapshot proxy connecting to the Publishing Server when a deadlock occurs. This parameter is specified to solve the deadlock between the snapshot proxy and the user application during snapshot generation.

PublisherDeadlockPriority Value

Description

-1

When a life-or-death lock is triggered on the Publishing Server, the application rather than the snapshot proxy takes priority.

0 (default)

No priority is assigned.

1

The Snapshot proxy takes precedence over the lifecycle lock on the Publishing Server.

-PublisherFailoverPartnerServer_name[\Instance_name]

Specifies the instance of the SQL Server failover partner that participates in the database image session with the published database.

-PublisherLoginPublisher_login

The login name used to connect to the Publishing Server using SQL Server Authentication.

-PublisherPasswordPublisher_password

The password used to connect to the Publishing Server using SQL Server Authentication. .

-PublisherSecurityMode [0 | 1]

Specifies the Security Mode of the Publishing Server. Value 0 indicates SQL Server Authentication (default), and value 1 indicates Windows Authentication mode.

-QueryTimeOutQuery_time_out_seconds

The number of seconds before the query times out. Default Value: 1800 seconds.

-ReplicationType [1 | 2]

Specifies the replication type. Value 1 indicates transaction replication, and value 2 indicates merge replication.

-RowDelimiterRow_delimiter

The character or character sequence used to mark the end of a row in the SQL Server large-capacity copy data file. The default value is \ n <, @ g> \ n.

-StartQueueTimeoutStart_queue_timeout_seconds

When the number of concurrent dynamic snapshot processes reaches the limit value set by the @ max_concurrent_dynamic_snapshots attribute of sp_addmergepublication (Transact-SQL), the maximum number of seconds the snapshot agent waits. If the snapshot agent is still waiting after the maximum number of seconds, the snapshot agent will exit. A value of 0 indicates that the proxy will wait for an indefinite period, although it can be canceled.

-UsePerArticleContentsViewUse_per_article_contents_view

This parameter is not recommended for backward compatibility.

Summary

Because the architecture lock of the object is required for snapshot generation, the table object is read-only during snapshot generation. If you create a snapshot for a large table, do not choose to generate a snapshot when the business is busy. Otherwise, the system may be paralyzed. the snapshot generation time of 2016 is much faster than that of 2008. By comparison, we can find that 2016 of the copies generate snapshots, which has a much higher performance than 2008. However, the reason for changing from 2014 to BCP from 32 to 16 is unknown.