Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Distribution Agent
The distribution agent is also implemented by using three steps. The first step records the status and history of the agent, the second step actually runs the agent, and the third optional step attempts to determine the cause of failure, if any.
A typical command for running the distribution agent is as follows:
-Publisher D10ZF411 -PublisherDB [pubs] -Distributor [D10ZF411] -SubscriptionType 1 -Subscriber [D10ZF411\SUBSCRIPTION_SRV] -SubscriberSecurityMode 0 -SubscriberLogin [my_login] -SubscriberEncryptedPassword [********] -SubscriberDB [Northwind] -Continuous
This command advises the distribution agent to use D10ZF411\SUBSCRIPTION_SRV as the subscriber server and to utilize the my_login account to connect to the subscriber. Notice that the subscriber password is encrypted so that unauthorized eyes that might gain access to view the agent properties can't figure out the password of the login. You could change the login used for connecting to the subscriber by specifying a different account and valid password. If you decide to override the account within the distribution agent's job, you must provide a clear text password, as follows:
-SubscriberLogin [my_new_login] -SubscriberPassword [clear_text_password]
Distribution agent profiles have many of the same parameters as the snapshot and log reader agents. The following table explains those parameters that are specific to the distribution agent:
|
Parameter Name |
Default Value |
Meaning |
|
Commit Batch Size |
100 |
Number of transactions executed at the subscriber before the COMMIT statement. |
|
Commit Batch Threshold |
1000 |
Number of replicated commands to be executed at the subscriber before the COMMIT statement. |
|
Max Delivered Transactions |
0 |
Upper limit for the total transactions to be delivered within a single synchronization. The default value of 0 sets no upper limit; however, if you'd rather synchronize in small chunks, you can override the default. The only time you might want to change this parameter is when the subscriber is too busy to accept all replicated transactions within a single synchronization. |
|
Polling Interval |
10 |
Number of seconds for querying the distribution database |
|
Skip Errors |
None |
Specific error number to skip without reporting replication failure. |
|
Skip Failure Level |
1 |
Allows the agent to skip errors. A value of 0 does NOT ignore any errors. |
|
Transactions Per History |
100 |
Number of transactions after which a history message is logged. You can view such messages in the agent history or job history. |
Occasionally, replication might encounter an error when delivering a large batch of commands. If so, you can change the value of the commit batch size parameter to 1. Doing so issues COMMIT after each transaction.
You might wonder why you'd ever want to skip replication errors. One way to fix issues with replication is to examine replicated commands in the distribution database and figure out why each one of them failed. However, if you have thousands of failed transactions, hanging out in the distribution database and chasing each one of them down can be quite challenging. I experienced a situation when numerous replicated transactions were failing with a primary key violation error. Unfortunately, the number of affected rows was very large; by examining the failed replicated commands, I noticed that the content of the existing rows was NOT different from the commands that were attempting to populate the affected table. Therefore, I enabled the distribution agent to skip the primary key violation errors (the error code is 2627). This allowed replication to mark failed commands as delivered, and the distribution database cleaned up after itself nicely. Once I had resolved the issue with failed replication commands, I reset the agent to not skip any errors.
During initial synchronization, you might consider using the –InProcLoader parameter with the distribution agent to speed up snapshot delivery. This parameter, which advises the agent to perform in process BULK INSERT operations, is available only after you apply Service Pack 1 to SQL Server 2000. Using –InProcLoader along with –MaxBCPThreads higher than its default value, you can expect considerable performance improvements for delivering snapshots.