Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of SQL Server 2016 snapshot agent process in detail, the detailed explanation of picture and text is easy to learn, and the effect of reading and understanding with the code is better, which is very suitable for beginners. Interested friends can refer to it.

Overview

The snapshot agent prepares the schema and initial data files for published tables and other objects, stores snapshot files, and records synchronization information in the distribution database. The Snapshot Agent runs on the Distributor; the SQLServer2016 version makes some good optimizations for Snapshot Agent, so let's take a closer look at the execution of snapshots.

Snapshot agent files

Four types of files are generated in the specified snapshot directory when the snapshot job is executed.

BCP file: the data file that publishes the object.

IDX files: index creation script fil

PRE file: copy the snapshot script file.

SCH files: schema creation script fil

Default Snapshot Agent profile

-BcpBachSize: the maximum number of rows recorded per bcp operation copy. The default is 100000 rows.

-HistoryVerboseLevel: specifies the size of the history recorded during the snapshot operation.

-LoginTimeout: the number of seconds to wait before login 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: right-click Snapshot Agent-Snapshot Agent configuration file; you can configure Snapshot Agent.

Compare different versions of Snapshot Agent

Next, the test generates a snapshot by comparing 200 million record tables.

Comparison of the number of 1.bcp files

2008R2

2016SP1

Here we focus on BCP files, because the basic unit of applying snapshots to subscribers is BCP files, that is, no matter how much your BCP files are one-time bulk to subscribers, the larger the BCP file, the longer it takes to apply each time. If a BCP file is too large, it may cause the insert to fail on the subscriber side.

From the picture above, you can see that 2008R2 has a total of 8 BCP files, and the largest BCP file size is nearly 1G and the rest are only a few megabytes; 2016 has 16 BCP files, and the first 15 are all about 50m. Next, take a look at the record comparison of each BCP file in the following figure.

two。 Comparison of detailed process of snapshot generation

2008r2

2016SP1

From the comparison of the generated BCP file records:

2008R2: the number of records in each of the first seven files is about 700000, and the last one is 110 million.

2016: the first 15 files record about 7 million each, and the last file 780000.

Description:

The number of records stored in each of the first seven files of 2008R2 is about 700000. The rest of the records will be stored in the last file, so the more suitable number of table records for 2008R2 is about 6 million.

2016 the number of records stored in each of the first 15 files is about 7 million. The rest of the records will be stored in the last file, and 2016 will be suitable for about 120 million table records.

Common disadvantage: when the table records exceed the "appropriate number of replicated table records", 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.

As you can see from the 2008R2 distribution record, each BULK is in bcp files, and it took about 22 minutes to copy the last bcp file, while each of the previous files took more than ten seconds; or because my current table has only three fields and has no index except the primary key.

IV. Snapshot generation process

The replication Snapshot Agent is an executable file that prepares snapshot files that contain schemas and data for published tables and database objects, and then stores these files in the snapshot folder and records synchronization jobs in the distribution database.

From the above figure, you can understand the entire snapshot generation process.

5. Grammar

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.

-Publisher server_ name [\ instance_name]

The name of the publisher. Specify server_name for the default instance of Microsoft SQL Server on the server. Specify server_name for the default instance of server_name\ instance_name instance_name SQL Server on the server.

-Publication release

The name of the publication. This parameter is valid only if the publication is set to always make snapshots available for new or reinitialized subscriptions.

-70Subscribers

This parameter must be used if any subscribers are running SQL Server version 7. 0.

-BcpBatchSize bcp batch\ size

The number of rows sent in a bulk copy operation. When performing a bcp in operation, the size of the batch is the number of rows to be sent to the server as a transaction, and is also the number of rows that must be sent before the distribution agent records the bcp progress message. When performing a bcp out operation, a fixed batch size of 1000 is used. A value of 0 means that no messages are logged.

-DefinitionFile def_path_and_file_name

The path to the agent definition file. The agent definition file contains the command line parameters for the agent. The contents of the file are analyzed as executable files. Use double quotation marks (") to specify parameter values that contain any character.

-Distributor server_ name [\ instance_name]

Distributor name. Specify server_name SQL Server for the default instance on the server. Specify server_name for the default instance of server_name\ instance_name instance_name SQL Server on the server.

-DistributorDeadlockPriority [- 1 | 0 | 1]

The priority at which the snapshot agent connects to the distributor when the deadlock occurs. This parameter is specified to resolve deadlocks that occur between the snapshot agent and the user application during snapshot generation.

DistributorDeadlockPriority value

Description

-1

When a deadlock occurs at the Distributor, the application takes precedence over the snapshot agent.

0 (default)

No priority is assigned.

one

When a deadlock occurs at the Distributor, the snapshot agent takes precedence.

-DistributorLogin distributor_login

The login name used to connect to the Distributor using SQL Server authentication.

-DistributorPassword distributor_password

The password used when connecting to the Distributor using SQL Server authentication. .

-DistributorSecurityMode [0 | 1]

Specifies the security mode for the Distributor. A value of 0 indicates SQL Server authentication mode (the default), and a value of 1 indicates Windows authentication mode.

-DynamicFilterHostName dynamic_filter_host_name

Used to set values for HOST_NAME (Transact-SQL) in filtering when creating dynamic snapshots. For example, if the subset filter clause rep_id = HOST_NAME () is specified for the project, and the DynamicFilterHostName property is set to "FBJones" before calling the merge agent, only rows with "FBJones" in the rep_id column will be replicated.

-DynamicFilterLogin dynamic_filter_login

Used to set values for SUSER_SNAME (Transact-SQL) in filtering when creating dynamic snapshots. For example, if the subset filter clause user_id = SUSER_SNAME () is specified for the project, and the DynamicFilterLogin property is set to "rsmith" before calling the Run method of the SQLSnapshot object, only rows with "rsmith" in the user_id column are included in the snapshot.

-DynamicSnapshotLocation dynamic_snapshot_location

The location where the dynamic snapshot should be generated.

-EncryptionLevel [0 | 1 | 2]

The level of secure Sockets layer (SSL) encryption used by the snapshot agent when establishing the connection.

EncryptionLevel value

Description

0

Specifies that SSL is not used.

one

Specifies to use SSL, but the agent does not verify that the SSL server certificate has been signed by a trusted issuer.

two

Specify the use of SSL and verify the certificate.

-FieldDelimiter field_delimiter

A character or sequence of characters used to mark the end of a field in an SQL Server bulk copy data file. The default value is\ n\ n.

-HistoryVerboseLevel [1 | 2 | 3]

Specifies the size of the history recorded during the snapshot operation. Select 1 to minimize the impact of history logging on performance.

HistoryVerboseLevel value

Description

0

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

one

Always update the last history message with the same status (startup, in progress, success, and so on). If there is no previous record with the same state, a new record is inserted.

2 (default)

Unless the record is recorded as information such as idle messages or long-running job messages (the previous record will be updated), a new history is inserted.

three

Always insert a new record unless it is related to an idle message.

-HRBcpBlocks number_of_blocks

The number of bcp blocks queued between writer and reader threads. The default value is 50. HRBcpBlocks is used for Oracle publishing only.

Remarks

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

-HRBcpBlockSizeblock_size

The size, in KB, of each bcp block. The default value is 64 KB. HRBcpBlocks is used for Oracle publishing only.

Remarks

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

-HRBcpDynamicBlocks

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

Remarks

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

-KeepAliveMessageInterval keep_alive_interval

The amount of time in seconds that the snapshot agent waits before recording "waiting for backend message" in the MSsnapshot_history table. The default value is 300 seconds.

-LoginTimeOut login_time_out_seconds

The number of seconds to wait before logon times out. The default value is 15 seconds.

-MaxBcpThreads number_of_threads

Specifies the number of bulk copy operations that can be performed in parallel. The maximum number of simultaneous threads and ODBC connections is MaxBcpThreads or the smaller of the bulk copy requests shown in synchronous transactions in the distribution database. The value of MaxBcpThreads must be greater than 0, and there is no upper limit for hard coding. The default value is 1.

-MaxNetworkOptimization [0 | 1]

Whether an extraneous delete operation is sent to the subscriber. Irrelevant delete operations are DELETE commands sent to subscribers for rows that are not part of the subscriber partition. Extraneous deletions do not affect the integrity or convergence of data, but they can lead to unnecessary network traffic. The default value for MaxNetworkOptimization is 0. Setting MaxNetworkOptimization to 1 minimizes the chance that unrelated deletions will occur, thereby reducing network traffic and maximizing network optimization. If there are multiple levels of join filters and complex subset filters, setting this parameter to 1 also increases the storage of metadata and results in poor publisher performance. You should carefully evaluate your replication topology and set MaxNetworkOptimization to 1 only if the network traffic caused by extraneous deletions is unacceptably high.

Remarks

Setting this parameter to 1 is useful only if the synchronization optimization option of the merge publication (the @ keep_partition_changes parameter of sp_addmergepublication (Transact-SQL)) is set to true.

-Output output_path_and_file_name

The path to the agent output file. If no file name is provided, the output is sent to the console. If the specified file name already exists, the output is 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).

two

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

-PacketSize packet_size

The packet size, in bytes, used by the snapshot agent when connecting to the SQL Server. The default value is 8192 bytes.

Remarks

Do not change the size of the packet unless you are confident that you can improve performance. For most applications, the default packet size is the best number.

-ProfileName profile_name

Specifies the agent profile used for agent parameters. If ProfileName is NULL, the agent profile is disabled. If ProfileName is not specified, the default profile for the agent type is used.

-PublisherDB publisher_database

The name of the publication database. This parameter is not supported by the Oracle publisher.

-PublisherDeadlockPriority [- 1 | 0 | 1]

The priority at which the snapshot agent connects to the publisher when the deadlock occurs. This parameter is specified to resolve deadlocks that occur between the snapshot agent and the user application during snapshot generation.

PublisherDeadlockPriority value

Description

-1

When a deadlock occurs at the publisher, the application takes precedence over the snapshot agent.

0 (default)

No priority is assigned.

one

When a deadlock occurs at the publisher, the snapshot agent takes precedence.

-PublisherFailoverPartner server_ name [\ instance_name]

Specifies the SQL Server failover partner instance that participates in the database mirroring session with the publication database.

-PublisherLogin publisher_login

The login name used to connect to the publisher using SQL Server authentication.

-PublisherPassword publisher_password

The password used to connect to the publisher using SQL Server authentication. .

-PublisherSecurityMode [0 | 1]

Specifies the security mode for the publisher. A value of 0 indicates SQL Server authentication (the default) and a value of 1 indicates the Windows authentication mode.

-QueryTimeOut query_time_out_seconds

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

-ReplicationType [1 | 2]

Specifies the type of replication. A value of 1 indicates transaction replication and a value of 2 indicates merge replication.

-RowDelimiter row_delimiter

A character or sequence of characters used to mark the end of a line in a SQL Server bulk copy data file. The default value is\ n\ n.

-StartQueueTimeout start_queue_timeout_seconds

The maximum number of seconds that the snapshot agent waits when the number of concurrent dynamic snapshot processes running reaches the limit set by the @ max_concurrent_dynamic_snapshots property of sp_addmergepublication (Transact-SQL). If the snapshot agent is still waiting after the maximum number of seconds has elapsed, the snapshot agent exits. A value of 0 means that the agent will wait indefinitely, although it can be cancelled.

-UsePerArticleContentsView use_per_article_contents_view

This parameter is no longer recommended and is supported for backward compatibility.

Data Integrity of SQL server Database

SQL server constraint

Summarize the usage of common functions in SQL Server

The above is a detailed introduction to the SQL Server 2016 Snapshot Agent process, which is more comprehensive, and I believe there are quite a few tools that we may see or use in our daily work. Through this article, I hope you can gain more.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.