Ms SQL Server 2000 administrator manual series-28. Merge Rewriting

28. Consolidated write-back
Introduction to merge write operations
Merge write operations
Set a merge write-back system
Manage rewrites
Monitoring and adjustment of merge write operations
Summary
The difference between the merge and transaction types is that the merge type can be operated by multiple parties. Merge write operations allow both the publisher and subscriber to update the release set. Although transactional rewrites allow subscribers to update the release set, the functionality of these two types of rewrites varies greatly. In this chapter, we will learn how to operate the merge-type secondary writing and how to set, monitor, and adjust the merge-type secondary writing.
Introduction to merge write operations
　
Merge write operations are performed between the publisher and one or more subscribers. This allows several systems to have replica sets that can be updated, and can modify their own replica sets. Modifications made to the subscriber are rewritten to the publisher and then to other subscriber.
Unlike transactional rewrites, the merge statement is executed by installing the trigger program on the publisher and subscriber. When a copy of the release set or release set is changed, an appropriate trigger program is triggered to generate a rewrite command, and the queue waits to be sent to the distributed database. The rewrite command is finally sent to the database and then to the system involved in the rewrite. Based on this method, the merge write operation is more costly than the transactional write operation, especially for publishers.
In this chapter, you will learn two important members: Merge Agent and distribute database. The Merge Agent merges incremental data changes that occur after the last merge. When you use a merge write operation (the distribution agent is not used), the Merge Agent will communicate with the publisher and the publisher. The Snapshot agent is only used to create an initial database. The Merge Agent executes the following tasks:
1. the Merge Agent uploads data changed by all subscribers.
2. All data columns that do not conflict with each other will be uploaded immediately (these data columns are data columns that have not been changed between the publisher and the subscriber ). Conflicting data columns (these columns are the data columns that have been changed in both the publisher and subscriber) will be sent to the conflict solver. The resolver is a module used to resolve conflicting data in a merge write operation. You can set a worker that is suitable for you.
3. All changes will be applied to the issuer.
4. the Merge Agent uploads all change data from the issuer.
5. Data columns without conflict are uploaded immediately. Conflicting columns are sent to the conflict Resolvers.
6. Apply all changes to subscribers.
This process will be repeated according to the schedule. In the send and subscribe mode, the Merge Agent is executed by the reseller. In the extract and subscribe mode, the Merge Agent is executed by the subscriber. Each merge release set has its own Merge Agent.
Merge write operations
　
Merge write operations are applicable to scenarios where two-way write operations are required. They are widely used. The option of allowing subscribers to modify incremental data brings many benefits. The following are the applications of merge write operations:
• Internal data sharing: for example, a department in charge of salary, payable and receivables within a company can access the same data. Users of each department can modify the data on their own, and then merge all the modifications on the department system.
　
• Multi-Point Data Sharing: when users in different locations need to use the same data and need to modify the data, they can use a merge copy.
　
• Message transmission: a consolidated write operation can be used as a message transmission system. You can modify the data and send the changed data back to the original system.
　
Set a merge write-back system
　
You can set the merge and snapshot types to be similar to the transaction type. You must first set the release set and then send or extract the release set for subscriber to subscribe to it.
________________________________________
Description
Before you set any type of SQL Server rewrite, you must set the release and release. See the description in "set release and distribution" in Chapter 26th.
________________________________________
Set release set
　
The procedure for setting a merge replica set is slightly different from that of the previous two types. The procedure is as follows:
1. Use Enterprise Manager to perform settings by performing any of the following steps: Select Tools/secondary writes, and click create and manage release sets; or select Tools/genie, and select create release set genie. The create and manage release set dialog box is displayed in any way, as shown in 28-1. Select the database or data table of the pre-release row.
　
 
Figure 28-1 create and manage a release set dialog box
If the release set already exists, the following buttons are displayed:
O send a new subscription: you can create a new subscription for an existing release set. This step will be discussed in the "set subscription" section in this chapter.
　
O attributes and subscription: You can change the attributes of the release set and subscription.
　
O command-based release set: You can create command codes to create more release sets.
　
O delete a release set: You can delete a set release set.
　
2. Select the release set database (28-1, select northwind as the release set database), and select create release set to enter the welcome to use the create release set wizard window, as shown in 28-2. Select the advanced options in the displayed wizard.
　
 
Figure 28-2 welcome window for "create release set Genie"
3. Click Next To Go To The database selection window for the release set, as shown in 28-3. You can select the data to be released again. The Database Selected in step 2 is displayed in the whitelist.
　
 
Figure 28-3 select the database of the release set
4. Click Next To Go To The release set type selection screen, as shown in 28-4.
　
 
Figure 28-4 select release set type
You can select any of the three release sets with the following functions:
O snapshot release set: You can periodically rewrite the snapshot set of the release item to the subscriber. You can use any data table to create a snapshot-type release set.
　
O transactional publishing set: You can create a transactional publishing set that updates subscriber data with changes made by the issuer. Only when a data table has a primary index key can a transaction-type rewrite release item be created.
　
O merge release set: You can create a merge copy release set that allows you to perform two-way duplication between the publisher and the subscriber. You can use any data table to create a consolidated publication.
　
5. Click merge release set and click Next to enter the specified subscriber type window, as shown in Figure 28-5. This window can specify whether all subscribers Execute SQL Server. Figure 28-5 shows the default value. The default setting specifies that all subscribers Execute SQL Server 2000. If you accept this setting value, you can set the rewrite to use the data type of native SQL Server 2000. You can also select an SQL Server 7.0 server or another type of server. This option will convert the secondary data back to the character format, and of course it will go through some complicated conversion procedures.
　
 
Figure 28-5 "specify subscriber type" Window
6. Click Next To go to the specified release item screen, as shown in 28-6. In this screen, you can specify a data table or other database objects as release items for rewriting. These release items constitute the release set you have created. On the screen listed on the left, you can check the items listed in the display, including data tables, pre-stored programs, and view tables. Once you select any project, the details of these projects are listed in the window on the right. Then you can select the data table to be released in the details on the right, or return to the Left window and select all data tables in all release projects. Remember, each data table, pre-stored program, or view table is considered as an independent release item, and the release set is composed of a group of release items.
　
 
Figure 28-6 "specify release items"
________________________________________
Description
If the subscriber already has a pre-stored program, you can use the re-write call pre-stored program without passing the results of the pre-stored program on the network.
________________________________________
7. Because you select a merge copy, we can define more attributes for the release item. Click the [...] button to enter the attribute settings page of the data table release item, as shown in Figure 28-7.
You can set different attributes for the release item on the volume tab page in this window. Figure 28-7 is the General tab. You can specify the release item name and the source data table owner, and determine the definition of data conflict. The default option will change the same data row as a conflict.

　
 
Figure 28-7 "general" tab in the "data table release items" attribute
8. Select the solver tab to specify the solver, as shown in 28-8. If a preset solver is used, when a conflict occurs, the publisher is considered to be one end of the victory. In addition, if there is a conflict between subscribers, the first subscriber to synchronize will be considered as the winner (and other subscriber to execute later will be considered as a failure ). You can also choose another solver or custom solver.
9. On the "merge changes" tab (28-9), you can specify additional security for specific operations. By selecting one or more options in the check permission, you can specify the permissions of the Merge Agent to perform specific operations, or specify to check before performing certain operations. In addition, there is a preset option on the volume tag page, specifying the method for updating multiple data rows. Please accept this default value. Click OK to continue setting.
　
 
Figure 28-8 "solver" volume tab page in the "data table release items" attribute

　
 
Figure 28-9 "merge changes" window in the "data table release items" attribute
10. click Next. The result of checking the release set is displayed here, and the most likely window shown is 28-10. The merge write operation requires a unique row to identify the data. This row automatically adds the data to the data table. In addition, the not for replication option will be created on the identification data line.
　
 
Figure 28-10 issue details
11. Click Next to select the release Set Name and description, as shown in Figure 28-11. On this screen, you can simply specify the release Set Name and description.
12. Click Next To Go To The property screen of the custom release set, as shown in 28-12. In this screen, you can determine whether to define data filtering or custom attributes of other release sets. Select No and create a release set according to the specified settings.
　
 
Figure 28-11 select release Set Name and description

　
 
Figure 28-12 "Custom Attributes of a release set"
13. Select next to complete the release wizard screen. After you click Finish, you can see the issue set creation process of the Genie. After the execution, the issue set is successfully created.
Check the merging data proxy data folder of the rewrite monitor, and no data will appear. This is because the merging agent is used for Bidirectional rewrite. Therefore, you need to set the subscriber before rewriting. After the subscriber settings are complete, the Merge Agent will appear in the data folder of the rewrite monitor.
Set subscription
　
Just as with the previous two types of settings, the last step to set a merge rewrite is to set the subscriber. First, you must enable the subscriber in the distributed database. The activation method is described in Chapter 26th "enable subscriber. Then set the subscription in the subscriber or publisher. The subscriber can set the subscription to be extracted, and the subscriber can set the subscription to be sent.
Set extract subscription
　
The setting of the extraction subscription method is described in the "set extraction subscription" section in Chapter 27th. Since the setting process is almost identical, therefore, only the setup steps are included here.
1. Enter the extract subscription wizard.
2. Enter the welcome to use the subscription genie screen.
3. Click Next To Go To The release set search screen. In this example, select to search for the release set on the server of the registered server.
4. Click Next To Go To The release set selection screen and select the release set to be used.
5. Click Next To go to the logon screen of the specified synchronization agent, and specify the method and account used to connect the agent to the publisher.
6. Click Next To Go To The target database selection screen and specify the database to which the re-write release item should be placed.
7. Click Next to enter the subscription initialization screen and select the appropriate option.
8. Click Next To Go To The snapshot set transfer screen and select the snapshot set transfer location.
9. Click Next To go to the set Merge Agent schedule screen. The setting of this screen is similar to the setting of the distributing agent schedule in Chapter 27th. You can select the appropriate option by referring to the previous setting logic.
10. Click Next To Go To The subscription priority setting screen, as shown in 28-13. Priority indicates which subscription sets are considered as priority when a conflict occurs. The preset setting (also recommended) specifies that when a conflict occurs, the setting of the publisher is used to resolve the conflict first.
11. Click Next to start the necessary service screen and start the SQL Server Agent that is not started.
12. Click Next to extract the subscription sprite screen. After checking that the settings are correct, you can select finish to complete the set job of the subscription set. Now the extraction subscription will be updated on a regular basis.
　
 
Figure 28-13 "set subscription priority"
Set sending and subscription
　
You can use the send subscription Wizard to set the sending subscription to be started in the publisher. Follow these steps to use the send subscription Wizard:
1. Use Any of the following methods to enter the send subscription wizard. The first method is to click the tool in Enterprise Manager and select "rewrite/send subscription to another server" to go to the "Create and Manage Server Release set" dialog box, as shown in 28-14.
　
 
Figure 28-14 create and manage server releases dialog box
Select a release set in the database and release set, and click the send New subscription button. To use the second method to enter the dialog box shown in Figure 28-14, select a tool in the tool column, select the genie in the selection list, expand the rewrite, and select create send subscription genie.
2. Click "Send new subscription" and you will see the send genie screen from 28 to 15.
　
 
Figure 28-15 "Welcome to send Genie"
3. Click Next To Go To The Subscriber selection page, as shown in Figure 28-16. You can specify the subscriber to which the release set is sent. Select a subscriber from the subscriber list that is enabled.
　
 
Figure 28-16 select a subscriber
4. Click Next To Go To The target database selection screen, as shown in Figure 28-17. You can specify the database of the subscriber. You can select an existing database or create a new database as needed. Click Browse or create to view the list of existing databases. To create a new database, click this button to create a new object, and then create a new database in the database Properties window.
5. Click Next To go to the set position of the Merge Agent, as shown in 28-18. Specify the location where the Merge Agent is executed, and select accept default value to execute the Merge Agent in the publisher. You can also choose to execute the agent in the subscriber. We recommend that you accept the default value unless the sender is busy.
　
 
Figure 28-17 select target database

　
 
Figure 28-18 "set merging Agent location"
6. Click Next To Go To The scheduling screen for setting the Merge Agent, as shown in 28-19. You can choose to continuously update the subscription set or choose to update the subscription set by schedule. Select "Update Subscription set by Schedule" and click the "change" button to go to the "Edit repetitive job Schedule" dialog box to change the schedule and the frequency of updating the subscription set. Remember that if you choose continuous update, it will cause a burden on the system.
　
 
Figure 28-19 set the schedule of the Merge Agent
7. Click Next to enter the subscription initialization screen, as shown in 28-20. You can specify whether the subscription set needs initialization. The initialization structure description and the dataset on the subscriber have been specified by the preset. If the structure description already exists, you cannot select no,... this option. You can also start the snapshot SET agent in this screen. We recommend that you start the snapshot SET agent when initializing the snapshot set. Otherwise, you must manually start the agent. Once the snapshot set is enabled and the replica set starts, the snapshot set will not be used before the new subscription is created. Each time a new subscription is created, a new snapshot set is created.
8. Click Next To Go To The subscription priority setting screen, as shown in 28-21. Here, you can set the subscription priority when a conflict occurs. Establish accept default value, that is, when a conflict occurs, the issuer's setting will have priority.
　
 
Figure 28-20 "initialize subscription"

　
 
Figure 28-21 "set subscription priority"
9. Click Next to start the necessary service screen, as shown in 28-22. The SQL Server Agent that has not been started can be started here.
　
 
Figure 28-22 "start necessary services"
10. Click Next to complete the sending and subscription wizard screen, as shown in 28-23. After checking whether all the settings are correct, click it To Start copying the snapshot set to the subscriber program. You will see a dialog box describing the program, and a message box about the completion of the operation program will appear. After the Wizard is complete, the subscription will be created and updated on a regular basis.
　
 
Figure 28-23 "Send and subscribe completed"
Manage rewrites
　
After learning how to create and set up a replica database in the SQL Server 2000 environment, you can now learn to use the monitoring function and setting options of Enterprise Manager, manage and troubleshoot the issue where the re-writing fails as expected.
Monitoring and Management of rewrite agent
　
You can find the agent in the rewrite monitor data folder of Enterprise Manager. Follow these steps to access the agent:
1. Expand the server group, your server, and rewrite monitor data folder.
2. If the publisher server is expanded, the publisher and proxy data folder will appear in the rewrite monitor data folder. The publisher data folder contains all senders belonging to this server. The agent data folder contains the snapshot set proxy program, the distribution proxy program, the merge proxy program, and other proxies used for clearing and history data.
3. Although the agent generally does not need to be manually started or stopped, you can use the rewrite monitor to execute the agent as needed. If the rewrite system does not follow the settings, it is likely that the snapshot SET agent is not started immediately because of the preset options. (This is why we recommend that you select to execute the initial snapshot set immediately during the setup process ). In Enterprise Manager, click the agent data folder to check the agent status and view information about the agent in the right pane, as shown in 28-24. You can determine whether to execute a proxy or whether the proxy is in use. When a proxy program is started, the agent runs until the task is completed and then stops the job. Otherwise, the SQL Server Agent will rewrite the agent according to the schedule.
　
 
Figure 28-24 merge agents in Enterprise Manager
4. Click the shortcut menu on the right button of the proxy program. There are several options for monitoring and managing the agent, as shown in 28-25.
These options are described as follows:
O error details: lists all errors that have occurred.
　
O agent history: List agent activities.
　
O proxy properties: allows you to modify the scheduling, database access method, proxy steps, and execution actions after the job is completed, for example, you can choose to let the system send an email to notify you of the completion of the event executed by the proxy program.
　
O proxy setting file: You can view and modify proxy parameters, such as the disconnection time, batch operation size, and query the setting of the disconnection time during the login.
　
O. Execute the proxy program on the subscriber side: You can specify to execute the proxy program on the subscriber side.
　
O execute the proxy program on the sender end: You can specify the proxy program to be executed on the sender.
　
O start and stop the agent: You can start or stop the agent.
　
O refresh speed and set value: you can modify the frequency of refresh and execute monitoring data.
　
O select field: You can specify the data rows to be viewed in the result set.
　
O show anonymous subscriptions: Specify whether to display anonymous subscriptions in the window.
　
O Description: Provides information about this window.
　

　
 
Figure 28-25 Merge Agent options
Set the Merge Agent
　
After creating a release set, you may need to modify the behavior mode of the Merge Agent. For example, you can specify the Startup Mode of the Merge Agent. In continuous mode, the Merge Agent starts after the SQL Server Agent starts. In the schedule-based execution mode, the Merge Agent starts based on the schedule you specify, and the re-write transaction in the read/write transaction record file stops. Changing the mode and other attributes can improve execution efficiency and reduce the burden on the publisher. Follow these steps to set up a merge agent:
1. In Enterprise Manager, expand the server to be modified, expand the rewrite monitor data folder, expand the agent program, and then select Merge Agent data folder.
2. In the right pane, select the release set and press the right button to call the shortcut menu, and then select agent properties.
3. the merging agent Properties window appears, as shown in 28-26.
　
 
Figure 28-26 "general" tab in the "Merge Agent properties" Window
4. Select the step tab, as shown in Figure 28-27. On the volume page, we can see the steps for executing the Merge Agent. The steps for executing these steps are as follows:
O record read Agent start message: record the message in the history record of the log read Agent (in the mslogreader_histroy data table of the distribution database ).
　
O run the agent: Start the agent according to the specified schedule. When the execution mode is continuous, the agent stops running at the end of the system.
　
O end of non-record Agent Detection: Put the message of Agent event failure in the history record of the reader agent.
　
5. Select the edit button to go To the edit job step dialog box, as shown in 28-28. In this dialog box, you can set how to start the Merge Agent.
　
 
Figure 28-27 "Merge Agent properties" tab

　
 
Figure 28-28 "general" tab in the "Edit steps" dialog box
You can set many options for merging agents. The preset merging proxy parameters can be changed in the Command box in this step and the detailed data dialog box in the rewrite proxy settings file (which will be introduced later ). The two parameters that can be changed are as follows:
O contunuous: Specifies whether to merge the agent in continuous mode or by schedule. Delete the parameters to execute the Merge Agent according to the schedule.
　
O distributorsecuritymode: specifies that the Merge Agent uses SQL Server or Windows NT mode for verification.
　
In addition, you can specify other parameters in the edit dialog box, such as login timeout, buffers, polling interval, query timeout, producer and Publisher Information, and output.
________________________________________
Related information
To set these parameters, you can find the data in SQL Server books online. On the index volume page, enter the Merge Agent and select start topic.
________________________________________
6. After modifying the attributes of the Merge Agent, click OK to save the modification.
You can modify other options by merging the agent settings file. follow these steps:
1. In the left pane of Enterprise Manager, expand the Merge Agent, select the release set, and press the right button to open the shortcut menu. Select the proxy settings file, as shown in 28-29.
　
 
Figure 28-29 merge Proxy Settings dialog box
Note that this dialog box contains more options than the record Reader Agent (described in Chapter 27th. These configuration files provide the function range, allowing you to select a configuration file that best suits the system.
2. Click the Add Profile button to create a new profile. The existing settings cannot be modified. After you press the button, the 28-30 screen is displayed.
　
 
Figure 28-30 "detailed data of the rewrite proxy settings file" dialog box
3. In this dialog box, you can modify the following parameters:
O bcpbatchsize: Number of Data columns transferred in a large number of replication jobs. When BCP is executed in a job that applies a structure description change, the Merge Agent determines the time for recording progress messages by batch size. The value 0 indicates that no message is recorded.
　
O changesperhistory: Critical Value for recording upload and download messages.
　
O downloadgenerationsperbatch: number of generations processed in a single batch when downloading a change from "poster" to "subscriber. The generation is defined as the logical group of changes to each release item. The default value of unreliable communication links is 10.
　
However, in various situations, the actual number of generations processed by each batch is equal to the greater limit between the uploadgenerationsperbatch setting and the number of released releases plus one.
O downloadreadchangesperbatch: number of changes read in a single batch when a change is downloaded from the publisher to the subscriber.
　
O downloadwritechangesperbatch: number of generations applied in a single batch when downloading a change from a publisher to a subscriber.
　
O fastrowcount: specifies the type of the data column count method used to verify the data column count. Value 1 (default value) indicates that the quick method is used; value 0 indicates that the full data column count method is used.
　
O historyverboselevel: the number of records recorded during the merge job can be:
　
Always update previous history messages in the same status (startup, progress, success, etc ). If no previous record in the same status exists, a new record is inserted. At this level, the minimum number of messages is recorded.
　
Insert a new history record, unless this record is used in a project similar to an idle (idle) message or a long-term work message, the previous record is updated. This level records level 1 messages and other ongoing messages.
　
Always Insert a new record, unless it is an idle (idle) message.
　
You can set the changesperhistory parameter to minimize the impact of history on performance.
O keepalivemessageinterval: the number of seconds before the thread execution checks whether a connection is waiting for the server to respond. This value can be added to prevent the agent from being checked as suspicious during long-term execution of batches.
　
O logintimeout: the number of seconds before the agent attempts to log on.
　
O maxdownloadchanges: Maximum number of changes to be downloaded during the specified merge phase. To process the entire generation, the number of data columns to be downloaded may exceed the specified maximum.
　
O maxuploadchanges: the maximum number of changes to be uploaded during the specified merge phase. To process the entire generation, the number of uploaded data columns may exceed the specified maximum.
　
O maxdeadlockretries: Number of times the internal job is retried when the merge processing sequence encounters a deadlock. Can be any value between 1 and 100.
　
O pollinginterval: the number of seconds that a publisher or subscriber queries for data changes in continuous mode.
　
O querytimeout: the number of seconds before the agent expires.
　
O uploadgenerationsperbatch: number of generations processed in a single batch when the subscriber uploads a change to the poster. The generation is defined as the logical group of changes to each release item. The default value of the unreliable communication link is 1.
　
However, in various situations, the actual number of generations processed by each batch is equal to the greater limit between the uploadgenerationsperbatch setting and the number of released releases plus one.
O uploadreadchangesperbatch: number of changes read in a single batch when the subscriber uploads a change to the publisher.
　
O uploadwritechangesperbatch: number of changes applied in a single batch when the subscriber uploads a change to the publisher.
　
O validate: Specifies whether verification is required at the end of the merge phase. If so, what type of verification is required. The value 0 (default) indicates no verification. The value 1 indicates that only data column counts are verified. Value 2 indicates data column count and sum check code (checksum) verification. Value 3 indicates the binary addition of the total check code for verification (only available for SQL Server 2000 ).
　
O validateinterval: the number of minutes for this subscription to be verified when it is set to continuous mode.
　
If the merge mode is executed in Schedule mode, the SQL Server Agent starts and changes are processed according to the number of times specified by maxuploadchanges and maxdownloadchanges before the end.
Disable rewrite
　
Enterprise Manager can be used to disable all or part of the rewrite operations. You only need to remove the selected rewrite component from the rewrite Wizard of Enterprise manger. This section will learn how to simply execute this job.
Remove send subscription
　
In the sender system, use the Enterprise Manager's send and subscribe Wizard to remove the send and subscribe. After the send subscription wizard is started, the create and manage release set dialog box appears. After you click the subscription to be deleted, click the delete release set button on the right to display the prompt box, check whether you are sure to delete this subscription. Click here to remove the subscription.
Remove extract subscription
　
In the subscriber system, the extract subscription Wizard of Enterprise Manager can be used to remove the extract subscription. After the subscription extraction wizard is started, the create and manage release set dialog box appears. Click the subscription to be deleted, and then click the delete release set button on the right to display the prompt box, check whether you are sure to delete this subscription. Click here to remove the subscription.
Remove distribution and release
　
To remove the distribution and release, you must enable the disable distribution and release wizard. On the first screen of the Stop genie, you must specify whether to disable all distributions and releases, or only remove releases. If the first option is selected, all the distributions, subscriptions, and senders on the server will be removed. If the second option is selected (the default option ), only the release assembly is removed. After the option is determined, a confirmation screen is displayed. click the button to remove the selected rewrite component.
Monitoring and adjustment of merge write operations
　
This section describes how to monitor and tune a consolidated rewrite System for optimal execution. In addition, we will introduce the configuration points of the merge rewrite System and the attributes of the merge rewrite system.
Merge write attributes
　
The merge method is not the same as that of the other two types. The biggest difference is that the merge method does not adopt one-way rewrite method and can be modified by the publisher or any subscriber. In addition, transactional record files are read to track changes, which are external operations of SQL Server. In a consolidated record, a trigger program is set up in the data table to track changes.
The Snapshot set will be used for the first execution of merge-Type Secondary write, but only once. Therefore, you do not need to adjust the snapshot-Type Secondary write process. Merge rewrites create data tables between the publisher and the publisher to perform the rewrites. In addition, a guid data row is added to each data table in the secondary write, so that each data column can be uniquely identified. Therefore, the secondary write agent can track the affected changes.
When a data column is inserted or modified, the trigger will mark the data column for rewriting. When the merging agent is started in the future, all marked data columns are sent to the poster database for rewriting. In addition, the modified data column in the subscriber system will be updated to the publisher by the Merge Agent. In this way, the two-way rewrite program is completed.
Set merge rewrite
　
For merge write operations, you must correctly set the I/O subsystems of all participating systems. The Network is also very important to improve the performance of the write operation. It is also important to set the correct batch operation size. If the batch operation size is increased, the content of each batch operation increases, and the number of files is small, this setting is more efficient. In addition, you can adjust the snapshot type for rewriting. However, because the snapshot Set application only works once, you can skip this step. Finally, you can modify the Merge Agent, which is mentioned earlier in this chapter. This section describes how to set the capacity of I/O and merge batch operations.
Set sufficient I/O Capacity
　
If the I/O capacity is sufficient, the performance of the rewrite process can be improved. Like other SQL Server systems, transaction record files responsible for rewriting should be stored in your raid 1 disk to protect data, data files should be stored in one or more raid 10 or RAID 5 disks. In the same way as transactional write operations, merge write operations only need to slightly adjust the standard I/O settings.
In general, when setting the I/O subsystem for a publisher, the focus of this book should be followed. Unlike transactional rewrites, consolidated rewrites do not add to the burden on transaction record files, as long as they are set according to the general points.
The transaction record file of the I/O subsystem set for the producer database should be set in the raid 1 Disk zone. In this way, the record files of the poster database can achieve the best execution efficiency and improve the execution efficiency of the poster.
In the subscriber's set I/O subsystem, because the merge write-back operation is bidirectional, the adjustment method of the subscriber and the subscriber is similar, as long as the general points are followed.
Set merging batch operation capacity
　
In a busy system, you can set the size of the combined batch operation to improve the write operation. The size of the merge batch operation determines the number of changed data columns to be rewritten in a single batch operation. If the batch operation capacity is increased, the content of each batch operation increases, and the number of files is small, this setting is more efficient.
Monitoring consolidated write-back system
　
You can monitor transactional write operations through performance monitor. Performance Monitor will add some objects when installing SQL Server rewrite. The objects are as follows:
• Sqlserver: replication agents: several different types of proxies are running in the computing system.
　
• Sqlserver: Replication merge: provides data on the merge write rate, including the number of conflicts, uploads, and downloads per second. Please note that this information does not really have a way to assist in School Adjustment and merge-style rewriting.
　
Using Performance Monitor to monitor these values allows you to see if the sender has difficulty performing performance. Although these performance monitor data provides a lot of useful information, it is not necessarily possible to find out the real problem. The best way to merge the School Adjustment and rewrite operations is to pay attention to the network execution efficiency, and check where network performance bottlenecks may occur. Check whether the load of the sender is heavy by referring to the highlights of the previous two chapters.
School Adjustment and merge write-back system
　
The primary step of the School Adjustment and merge-type rewrite system is to correctly set and monitor the system, which has been mentioned in the previous section. Monitor your system using the performance monitor and pay attention to the network performance. However, the data provided by the Performance Monitor is not that useful for consolidated write operations. Instead, you may want to refer to the counters of other SQL Server and Microsoft Windows 2000 to tune the system.
As mentioned in chapter 24th, when the system is updated frequently, you may need to modify the BCP batch operation capacity and the merge batch operation capacity. If the BCP batch operation capacity is increased, the execution efficiency of the original snapshot set proxy will also increase. If you increase the size of the merge batch operation, the number of files and the number of times that affect the system will be reduced as the number of operations in each batch increases, which is a more efficient setting. However, the re-writing process will increase the workload.
In addition, you can change the polling frequency, and change the polling frequency can increase or decrease the frequency of merge write operations. However, such changes are not recommended unless necessary. If you want to adjust the polling time, try to change the batch operation capacity first.
Monitor whether the network can carry load if necessary. If the system is not executed properly, for example, if the CPU and I/O subsystems reach saturation or the rewrite process takes too long, the network may be faulty. Network problems are tricky, because network problems are not enough to check performance monitors, and may need to be used such as Microsoft System Management Server (SMS) this network monitoring product can monitor whether the network card has reached saturation. If the network card is saturated, the solution is to buy a faster network card or add a dedicated network for rewriting, backup, and recovery. Both the publisher, publisher, and subscriber belong to the SQL server system. Therefore, the method of tuning these systems is like the key points that should be paid attention to when the SQL Server System is being adjusted in the same school.
Summary
　
This chapter is the last chapter of the record-writing series. In this chapter, you should have learned the execution of the merge record-writing and the purpose of the merge record-writing. In addition, we also learn how to correctly set, monitor, and merge the school-based write operations. In the next chapter, we will learn how to use Microsoft Analysis Service.