This article gives you a detailed analysis of the SQL Server 2016 snapshot agent process, and an example of what needs to be paid attention to. Follow the editor to learn. We will use an instance data table of SQL Server 2016 to give you a detailed analysis of the problems and solutions encountered in the snapshot agent process, and a detailed explanation of the snapshot generation process. The following is the full content:
Overview
The snapshot agent prepares the schema and initial data files of published tables and other objects, stores snapshot files, and records synchronization information in the distribution database. The snapshot agent runs on the distribution server; the SQL Server 2016 version has made some better optimizations for the snapshot agent. Next, let’s learn more about the snapshot execution process.
1. Snapshot proxy file
When executing a snapshot job, 4 types of files will be generated in the specified snapshot directory.
BCP file: the data file of the publishing object.
IDX file: Index creation script file
PRE file: Copy snapshot script file.
SCH file: Architecture creation script file
2. Default snapshot agent configuration file
-BcpBachSize: The maximum number of record lines for each bcp operation copy. The default is 100,000 lines.
-HistoryVerboseLevel: Specifies the size of the history recorded during the snapshot operation.
-LoginTimeout: Number of seconds to wait before login times out. The default value is 15 seconds.
-QueryTimeOut: Number of seconds to wait before query times out. The default value is 1800 seconds
Note: You can configure the snapshot agent by right-clicking the snapshot agent-snapshot agent configuration file.
3. Compare different versions of snapshot agents
Next, test and compare 200 million record tables to generate snapshots
1. Comparison of the number of bcp files
2008R2
2016SP1
##The focus here is on the BCP file, because Applying snapshots to the subscriber uses BCP files as the basic unit, which means that no matter how big your BCP file is, it will be bulk-bulked to the subscriber at once, so the larger the BCP file, the longer each application will take. If a BCP file is too large, insertion into the subscriber may fail.
As you can see from the picture above, there are also 200 million records. 2008R2 has a total of 8 BCP files, and the largest BCP file size is nearly 1G. The others are only a few megabytes; 2016 has 16 BCP files, and The first 15 are all about 50M data, which is relatively even. Next, look at the record comparison of each BCP file in the figure below.
2. Comparison of the detailed process of snapshot generation
2008r2
##2016SP1
Comparison of the generated BCP file records:
2008R2: The first 7 files each have about 700,000 records, and the last file has 110 million records.
2016: The first 15 files record about 7 million each, and the last file records 780,000.
Explanation:
The approximate number of records stored in each of the first seven files of 2008R2 is 700,000. The remaining records will be stored in the last file, so the number of table records that is more suitable for 2008R2 is Around 6 million.
The approximate number of records stored in each of the first 15 files in 2016 is 7 million. The remaining records will be stored in the last file. The number of suitable table records in 2016 is about 120 million.
Common disadvantage: After the table records exceed the "appropriate number of replicated table records", all remaining data will be stored in the last bcp file.
3. Distribution comparison
Let’s take a look at the detailed process of distribution
## You can see each time from the 2008R2 distribution record process BULK is based on bcp files. It took about 22 minutes to copy the last bcp file, and each of the previous files took more than ten seconds; or because my current table only has three fields and no index except the primary key. Otherwise, the time Just longer. 
4. Snapshot generation process
The replication snapshot agent is an executable file used to prepare snapshot files (which contain published tables and database object schema and data), then stores these files in the snapshot folder and records the synchronization job in the distribution database.
You can understand the entire snapshot generation process from the above figure. 
5. 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.
-Publisher server_name[\instance_name]
The name of the publishing server. Specify server_name for the default instance of Microsoft SQL Server on this server. Specify server_name for the server_name\instance_name instance_name SQL Server default instance on this server.
-Publication Publication
The name of the publication. This parameter is only effective if the publication is set to always make snapshots available to 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 batch size is the number of rows to be sent to the server as one transaction, and is the number of rows that must be sent before the distribution agent records a bcp progress message. When performing a bcp out operation, a fixed batch size of 1000 will be used. A value of 0 means 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 an executable file. Use double quotes (") to specify parameter values containing any characters.
-Distributor server_name[\instance_name]
Distributor Name. Specify the server_name SQL Server on this server. Specify the server_name on the server_name\instance_name instance. #-DistributorDeadlockPriority [-1|0|1]
The priority of the snapshot agent connecting to the distribution server when a deadlock occurs. This parameter is specified to solve the problem between the snapshot agent and the distribution server during snapshot generation. Deadlock issues occurring between user applications
|
##DistributorDeadlockPriority Value
Description
-1
When a deadlock occurs on the distribution server, the application instead of the snapshot agent Priority.
0 (Default)
No priority assigned.
1
When a deadlock occurs on the distribution server, the snapshot agent takes precedence.
-DistributorLogin
distributor_login
##The login name to use when connecting to the distribution server using SQL Server authentication -DistributorPassword
distributor_password
##The password to use when connecting to the distribution server using SQL Server authentication.
-DistributorSecurityMode [ 0| 1]
Specifies the security mode of the distribution server. 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 the value for HOST_NAME (Transact-SQL) in filtering when creating a dynamic snapshot. For example, if the subset filter clause rep_id = HOST_NAME() is specified for the project, and DynamicFilterHostName is used before calling the merge agent. property is set to "FBJones", only rows with "FBJones" in the rep_id column will be copied.
-DynamicFilterLogin dynamic_filter_login
Used to set the value for SUSER_SNAME (Transact-SQL) in filtering when creating a dynamic snapshot. For example, if you specify the subset filter clause user_id = SUSER_SNAME() for a project, and you set the DynamicFilterLogin property to "rsmith" before calling the SQLSnapshot object's Run method, only rows with "rsmith" in the user_id column are included in the snapshot.
-DynamicSnapshotLocation dynamic_snapshot_location
The location where dynamic snapshots should be generated.
-EncryptionLevel [ 0 | 1 | 2 ]The level of Secure Socket Layer (SSL) encryption used by the snapshot agent when establishing a connection.
EncryptionLevel Value
Description
##0Specifies not to use SSL. 1Specifies to use SSL, but the agent does not verify that the SSL server certificate is signed by a trusted issuer. 2Specify to use SSL and verify the certificate. -FieldDelimiter field_delimiterA character or sequence of characters used to mark the end of a field in SQL Server bulk copy data files. The default value is \n\n.
-HistoryVerboseLevel [ 1| 2| 3]Specifies the size of the history recorded during the snapshot operation. Choose 1 to minimize the performance impact of historical logging.
#HistoryVerboseLevel value
Description
0
Progress messages will be written to the console or output file. Does not record history in the distribution database.
1
Always update the previous history message with the same status (starting, in progress, successful, etc.). If there is no previous record with the same status, a new record will be inserted.
2 (Default value)
Insert a new history record unless the record is an idle message or a long-running job message (in which case the previous record will be updated).
3
Always insert a new record unless it is related to an idle message.
-HRBcpBlocks
number_of_blocks
The number of bcp data blocks queued between the writer thread and reader thread. The default value is 50. HRBcpBlocks are only used in Oracle releases.
Remarks
This parameter is used to optimize the performance of bcp through Oracle Publisher.
-HRBcpBlockSize
block_size
The size of each bcp data block (in KB). The default value is 64 KB. HRBcpBlocks are only used in Oracle releases.
Remarks
This parameter is used to optimize the performance of bcp through Oracle Publisher.
-HRBcpDynamicBlocks
Whether the size of each bcp data block can grow dynamically. HRBcpBlocks are only used in Oracle releases.
Remarks
This parameter is used to optimize the performance of bcp through Oracle Publisher.
-KeepAliveMessageInterval
keep_alive_interval
The time (in seconds) the snapshot agent waits before logging a "waiting for backend message" to the MSsnapshot_history table. The default value is 300 seconds.
-LoginTimeOut
login_time_out_seconds
The number of seconds to wait before login 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 concurrent threads and ODBC connections is the smaller of MaxBcpThreads or the number of bulk copy requests shown in synchronization transactions in the distribution database. The value of MaxBcpThreads must be greater than 0 and there is no hard-coded upper limit. The default value is 1.
- MaxNetworkOptimization [ 0| 1]
Whether to send irrelevant delete operations to the subscriber. An unrelated delete operation is a DELETE command sent to the Subscriber for rows that do not belong to the Subscriber partition. Irrelevant delete operations do not affect data integrity or convergence, but they can cause unnecessary network traffic. The default value of MaxNetworkOptimization is 0. Setting MaxNetworkOptimization to 1 minimizes the chance of unrelated delete operations, 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 can also increase the storage of metadata and cause a decrease in Publisher performance. You should carefully evaluate your replication topology and set MaxNetworkOptimization to 1 only when the network traffic caused by unrelated delete operations is unacceptably high.
Remarks
Setting this parameter to 1 is useful only when the synchronization optimization option of the merge publication (@keep_partition_changes parameter of sp_addmergepublication (Transact-SQL)) is set to true.
-Output
output_path_and_file_name
The path of the agent output file. If no filename is provided, this 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 verbose content.
|
OutputVerboseLevel Value
Description
0
Output only error messages.
1 (default value)
Output all progress report messages (default value).
2
Outputs all error messages and progress report messages, which is useful for debugging.
-PacketSize packet_size
The packet size (in bytes) used by Snapshot Agent when connecting to SQL Server. The default value is 8192 bytes.
Note
Do not change the packet size unless you are sure it will improve performance. For most applications, the default packet size is the optimal value.
-ProfileName profile_name
Specifies the agent profile used for agent parameters. If ProfileName is NULL, the agent profile will be disabled. If ProfileName is not specified, the default profile for this agent type is used.
-PublisherDB publisher_database
The name of the publishing database. Oracle Publisher does not support this parameter.
-PublisherDeadlockPriority [-1|0|1]
The priority of the snapshot agent connecting to the publisher when a deadlock occurs. This parameter is specified to resolve a deadlock issue that occurs between the snapshot agent and the user application during snapshot generation.
#PublisherDeadlockPriority Value Description
-1When a deadlock occurs on the Publisher, the application takes precedence, not the Snapshot Agent. 0 (Default) No priority assigned. 1When a deadlock occurs on the publishing server, the Snapshot Agent takes precedence. -PublisherFailoverPartner server_name[\instance_name]
Specifies the SQL Server failover partner that participates in database mirroring sessions with the publishing database Example. -PublisherLogin publisher_login
The login name to use when connecting to the publisher using SQL Server authentication. -PublisherPassword publisher_password
The password to use when connecting to the publisher using SQL Server authentication. . -PublisherSecurityMode [ 0| 1]
Specifies the security mode of the publishing server. A value of 0 indicates SQL Server Authentication (the default), and a value of 1 indicates Windows Authentication mode. -QueryTimeOut query_time_out_seconds
The number of seconds to wait before query times out. The default value is 1800 seconds. -ReplicationType [ 1| 2]
Specifies the type of replication. A value of 1 indicates transactional 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 row in SQL Server bulk copy data files. The default value is \n<,@g>\n. -StartQueueTimeout start_queue_timeout_seconds
When the number of running 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 means that the agent will wait indefinitely, although it can be canceled. - UsePerArticleContentsView use_per_article_contents_view
This parameter is deprecated and is supported for backward compatibility.
Data integrity of SQL server database
SQL server constraints
Summary of the use of commonly used functions in SQL Server method
The above is the detailed content of Detailed explanation of SQL Server 2016 Snapshot Agent process. For more information, please follow other related articles on the PHP Chinese website!
