27. Trading Document
Trading replication overview
Trading-based compliance
Set trading replication system
Set, monitor and school
Trading replication
Executive trading
Summary of this chapter
Chapter 26 describes the Microsoft SQL Server 2000 replication, but also briefly describes the trading replication. This chapter will continue to introduce transaction-based compilation, including how to rewrite, when to use compilation, and how to set, monitor and automate transaction replication systems.
Trading-based compliance is more commonly used, it can quickly send update information to the subscriber, and will not cause too much burden on the issuer.
Trading replication is obviously famous, and trading is written for replying transactions. To perform trading, the snapshot set will be used in the subscriber end, and then the individual transaction is captured and represented to the subscriber. All database changes, such as database updates, inserts or deletes, can be docked in. Record the reader agent program read the data library transaction record files written by the transaction-based, executed in the distributor side and establishes a link between the issuer. The transaction to be replicated From the radiator end to the distributed database, and then the transaction data in the distribution database will be transferred to the subscriber Data Library. Each trading repository has a record reading agent that belongs to the database, which is executed in the distributor side and monitors the trading record file of the dealer. A database may set several issues at the same time, but a database can only have a record reader agent program. Record the reader agent programming settings can be set at the time of establishment. This scales can be set to continuously, or intervals scheduled by subscribers. Recording the reader agent program will be performed in the following event:
Record the reader agent program read the transaction record file within the issuance set system, create a list, and record the statements or other devices to be replied in the repository, such as deleting the data sheet, change The transaction of the class definition and other classes. Record the reader agent starts to process information within the data record file, and horizontally or vertical screens according to the information table issuance (Article) defined within the Issue. These changes will be sent to the distribution database on the batch transaction to the distributed database, there are several data sheets to track the changes and jobs of the replication. All changes in the issuer should be transferred to the subscriber, and these changes will exist in the MSREPL_COMMAVANDS table, this data sheet will reply command compression storage. For each defined issuance, the MSREPL_COMMAVANDS table contains the data columns of the repred command, such as insert, update, and deletion. If the data sheet in the issuer database is modified, and the database is included in several issuings, this modification will be replied in the library of the distributor. For example, if the three issuances contain a A data table, updating the A data sheet will add three data sheets to the distribution of the distributor. After each batch successfully transferred to the radiator database, the transactions in each batch will be recognized. This error message is recorded if it is recognized, and this error message is recorded in the agent error record. After the change of the distributor database is recognized, the recording reader agent will indicate the last change, and the latest copy execution to avoid repeated changes. When the transaction record file reads the transaction and is approved by the distributor database, the recording reader agent has been launched in the transaction record file that can be cut off. Every modification in the issuer's database will be registered at least once in the distributor database. In some cases, the modification of the issuer's database is registered several times in the distributor database, and the following is the cause of this situation: When there is an inserted action in the data sheet, it belongs to the distribution The information sheet for issuance items will have inserted actions. If the data table exists in two different issues, it will be defined as different issuations. In the two issuings, there is a data column that stores each insert, update, and delete actions in the Incebted Crop Base in the issuer database.
The operation affects multiple columns of updates or deletions will establish a data line in the distributed data bank for each affected data. Perform updates or delete multiple columns of SQL statements will enable the record reader agent to build individual commands for each affected data column in the radiator database. The WHERE clause in SQL statement is converted into a WHERE clause that can specify the data column in the database according to the main index key value. For example, a data sheet containing 10 lines of information, when performing an update command that can affect all the information, 10 registrations in the distributor database, each item specifies the main index in the WHERE clause Key value. The trading type of complimentary comprehension can be used to keep synchronization when the subscriber needs and the issuer. Trading-type recording can be set to make subscribers can update the subscriber's data immediately after the issuer is updated. Even if the recording reader is constantly being executed, there are also a few seconds in a few seconds each time, not a constant reading record to avoid the impact on the execution efficiency of the issuer's transaction record. Trading-based compilation can also be set to enable subscribers to update the database. Due to this factor, there are many applications, such as: transferring messages: Trading-type recording can be used in delivery system messages, especially when you must determine that the subscriber does have a message. Once a network failure, the information will continue to transfer after the network reply. Save two systems Synchronous information: Many companies use trading replication to transfer information between the heads and branches. When the information is updated in the head office, the updated information will be received in the branch. Dispersion load: Trading-based recording can be used to disperse the load of the database, used to reply time longer and require a large number of queries to use resources to reduce the main system load. Setting the transaction-type replication system setting trading replication method and setting snapshot. First set the release set, then transfer the issued set to the subscriber side to the subscriber. Note Before setting any SQL Server replication, set the issue and distribution first, please refer to Chapter 26.
Installation Distribution Set The process of establishing a trading issue and establishing a snapshot issuation is similar. The following is the step of establishing a trading issue:
Select Tools in Enterprise Manager. The next step can be selected to copy / establish and manage the issuance set, or click the execution spirit in the function table, select the establishment of the dialogue to establish a dialogue to establish and manage the issuance set, as shown in Figure 27-1. In this dialogue, you can choose the database or data sheet you want to include within the release.
Figure 27-1 "Establishing and Management Distribution Set" dialogue
If the distribution set already exists, you can also click more options:
Send a new subscription: Create an existing distribution set to send a subscription (PUSH Subscription). The section of this chapter will be described in detail. Properties & Subscribe: Allows you to modify the properties of the issuance set and subscription. Credit Distribution Set: Allows you to create SQL instructions to build other more issues. Delete Distribution Set: Delete the established issues.
Select the database that wants to establish a set of issues (this chapter is Northwind as an example), select the database after selecting the Dance Set to turn on the establishment of the issuing set wizard, then see the welcome screen (shown in Figure 27-2). Check the progress option in the lower left corner. Press the next step to pick the selection radiator screen (as shown in Figure 27-3). Here you can choose the database or data sheet you want to include in the release. The preset value will set the distributor to the database selected by the previous step.
Figure 27-2 Welcome screen "Establishment of the Wake Elf"
Figure 27-3 "Select the distributed person" screen
Note If you do not specify a distributed person in the system, you select the distributor's screen asks you to specify the distribution. A distribution set can only have a distributor. If you have set a server-free server, you will see the following choices to issue a fund-funded library screen. Press Next to enter the selected issue set type, as shown in Figure 27-4.
Figure 27-4 "Select the Issue Type" screen
There are three types of distribution sets to choose:
Snapshot Issue: Snapshot issuers fully copies a issuer (Article) to the subscriber at a certain point in time. The snapshot issuation set can be established on any information. Trading Practice: In the trading replication, the transaction is transferred to the subscriber when the issuance data is modified. The issuance must be established in the data sheet containing the main index key. Complete Issue: In the consolidated issuance, the change in the subscriber and the issuer will be tracked, and the changes will be combined with a single data version later. The merged issuance can be established on any information.
Select the trading issue / next to update the updated subscription screen, as shown in Figure 27-5. This picture is to be displayed at this stage, check the advancement options in this wizard (Figure 27-2) in the Welcome Screen to establish the issued wizard. If there is no check, the specified subscriber type screen will appear.
Figure 27-5 "Updated Subscription" screen
In this picture, you can specify changes that the subscriber ends, and are represented to the issuer, and the following options are required:
Instant update: The replication agent program uses the Microsoft Distributed Trading Coordinator to conduct two phased transactions, when the subscriber is changed, it will instantly update to the issuer. (Instant update will confirm that there is no conflict, and these data changes will be recognized by two-stage transactions, and the details of the two-stage transaction recognition can be referred to in Chapter 25.) The preset value does not set instant update into a preset subscriber Options.伫 伫 Update: Lie Update allows the subscriber's update information, and then updates the data to the issuer after connecting the network. This option allows subscribers to update the database and transmit update information to the issuer, and do not need to be recognized by two-stage transaction.
Note When the system requires an exact same amount, instant update must be set, but this option needs to be recognized by two phases, so it may cause a large system burden. And if the two systems are not immediately set, the transaction will not be recognized. Therefore, it is not necessary to set it in a timely update if it is necessary.
Press Next to enter the conversion distribution data screen, as shown in Figure 27-6. This option is a new feature of SQL Server. Microsoft Data Conversion Services (DATA Transformation Services, DTS)
Conversion data values or types change the case-sensitive information distribution of English words
Figure 27-6 "Conversion Distribution Information" screen
Press Next to enter the specified subscriber type screen, as shown in Figure 27-7. This screen allows you to specify whether all subscribers are executed by SQL Server. Try to accept the preset settings, that is, all subscribers are specified using SQL Server 2000. If you accept this setting, accept all SQL Server data types. If the SQL Server 7 system is also used in the reputation, select the second verification block; if there is a system with non-SQL Server, select the third verification block, make the copy data into a text format. Such transformations can cause excess load.
Figure 27-7 "Specify Subscriber Type" screen
Press Next to enter the specified issuance item screen, as shown in Figure 27-8. Here, you can specify a data sheet, pre-deposit program, and view table that will be replied to an issue. These issuances will be organized as issues. Select the desired information table, pre-deposit program, and view table in the square of the right hand, or check the type of item that you want to issue in all Issuance Data lines. Remember, each item is an issue, and a distribution set is a group of logical issues. Figure 27-8 "Specify issuance item" screen
Note If you have a pre-stored program at the subscriber, you can set the execution of the replication pre-depiction, and the information changes caused by these pre-deposited procedures are not followed.
Press Next, in this step, SQL Server checks the issuance set. If there is any error, the screen as shown in Figure 27-9 will appear.
Figure 27-9 "Problem of the issuance" screen
After the issuance set analysis is complete, press OK to return to the elves, enter the issuance set name and description screen, as shown in Figure 27-10. Press Next to enter the properties screen of the customization set, as shown in Figure 27-11. Here you can specify whether to define data filtering or other properties, choose Yes, .....
Figure 27-10 "Distribution Set Name and Description" screen
Figure 27-11 "Property" screen "
Press Next to enter the filter information screen, as shown in Figure 27-12. In this picture, you can specify whether to filter the data line with a vertical screening, or filter data columns in parallel. Both are selected, press the next step.
Figure 27-12 "Screening Information" screen
Enter the screening data line screen, as shown in Figure 27-13. This picture allows you to choose the information that you don't want to reply. You can remove the information rows that you don't want to reply from the information line list of the selected data sheet. This will establish a issuance in a vertical screen to make the issuer to reply less information.
Note that the information line containing the main index key cannot be removed because the main index key is used in transaction-based. This chapter has explained this restriction earlier.
Press the next step to enter the filter list of the filter sheet, as shown in Figure 27-4. In this picture, you can filter the data column, press this [...] button that can enter the specified filter, you can set the filter.
Figure 27-13 "Screening Data Line" screen
Figure 27-14 "Screening Information Column" screen
Specifies the screening dialog box, as shown in Figure 27-15. This dialogue allows you to add a WHERE clause to SQL statements to filter data columns. When the data column to be replusive is selected, press OK to return to the elves.
Figure 27-15 "Specifying Filter" dialog
Press Next to enter the allowed anonymous subscription screen, as shown in Figure 27-16. This screen allows you to specify whether an anonymous subscription or name subscription is allowed. You can choose from you.
Figure 27-16 "Allow anonymous subscription" screen
Press Next to enter the scheduled snapshot set agent program, you can refer to the
In the pane of the right hand, the selected issuance set is pressed and the right button is called, as shown in Figure 27-17.
Figure 27-17 Explicit function table for the release set
Select the agent program attribute to enter the record reader agent program properties window, as shown in Figure 27-18.
Figure 27-18 "Record Reader Agent Properties" Window
Select the step check, as shown in Figure 27-19. On the tab, we can see the steps executed by the record reader agent, these steps perform as follows:
Record the reader agent startup message: Record the message in the historical data sheet recorded by the record reader (in the MSLogReader_Histroy Data Sheet of the Transport Base). Perform a proxy program: Start the agent according to the specified scheduling, when the mode is continuous, the agent will stop execution at the end of the system. Detecting the end of the non-recorded agent: Put the message that the agent event failed in the historical data sheet for the record reader agent program. Select the proxy program step and click Edit button to enter the editing job step dialog, as shown in Figure 27-20. In this dialogue, you can set how to start the record reader agent.
Figure 27-19 "Record Reader Agent Properties" Window "Step" Type
Figure 27-20 "Edit Step" Dialogue Square "General" Tag
Recording the reader agent program can be set to many options. The preset record reader agent program parameters can be changed in the command block in this step and the detailed information dialog box of the reproach set file in Figures 27-22. The two parameters that can be changed are as follows:
Continuous mode: Specifies whether the reader agent program is executed in a continuous mode or according to the schedule. Delete parameters can be executed according to the schedule. Sander Safety Mode: Specifies the record reader agent program to use SQL Server or Microsoft Windows 2000 certification.
Alternatively, you can also specify other parameters in the editing dialogue, such as AsynchLogging, Buffers, definitionFile, distributors, and distributors and MessageInterVal.
Related Information The settings of these parameters can be found in SQL Server "Online Books". Enter the recording reader agent program in the Index Tim sign to select the startup topic.
After completing the modification of the record reader agent program, press OK to save the modification. With the record reader agent program, you can modify other options. Please refer to the following steps: In the pane of the ENTERPRISE Manager right hand, select a set of issues and press the right button to press the right button. Menu, select the agent program set file, as shown in Figure 27-21.
Figure 27-21 "Agent Program Settings" Dialogue
Press the new setup button to create a new setting file, existing setting files do not allow modifications. When the button is pressed, you will see the screen as shown in Figure 27-22.
Figure 27-22 Details of the Details of the Agent Settings Settings
In this dialogue, you can modify the following parameters:
HistoryverBoseEvel: You can specify the number of record information. The preset value is usually sufficient unless there is a problem in actual implementation. Logintimeout: Specifies the number of seconds that are trying to log in before the agent has timeout. PollingInterval: Specify this record query the number of seconds of the transaction. QueryTimeout: Specifies the number of seconds of queries that have been issued before the agent has timeout. ReadbatchSize: Specifies the number of times the information within a batch is read in the transaction record.
Description As mentioned earlier, if the recording reader agent is executed according to the schedule, when the SQL Server Agent is started, the recording reader agent program is read in the transaction record file to be replied. All transactions. Record the reader agent program to read the number of transactions, or read the command specified in the transaction record file in the ReadbatchSize parameter, insert the command into the distributed library. When all the transactions that are labeled are read, the record reader agent will be suspended until the beginning of the next schedule.
Settings a subscriber such as the setting mode in the previous snapshot, the last step in the trading replication is also a set subscriber. First, you must enable subscribers in the radiator database (see the previous chapter
In the Enterprise Manager selection tool / replication / extract subscription to, select the new subscription in the subsequent subscription to the dialogue, or select Elf / Double / Extract in the Tool. Any way can you enter your welcome to use extraction subscription wizard, as shown in Figure 27-23. Note that the advanced option can be checked under the screen, in which we check this option, this selection can enable data conversion.
Figure 27-23 Welcome screen "Use Extraction Subscription Elf"
Select Next to enter the search screen, as shown in Figure 27-24. Here you can choose to find the issuance set in the environment where the standard Windows 2000 network is required or in the Active Directory service. Select the preset value, that is, let you find a distribution set in the registered server.
Figure 27-24 "Looking for the issuance set" screen
Press Next to enter the selection issuing set screen, as shown in Figure 27-25. Here you can see which will be applied in the issuance set. The server registered on the SQL Server system will be listed here. Expand the selected issuer system and select the issued set to use. After selecting the issuance set, press step to enter the specified synchronization agent login screen, as shown in Figure 27-26. Here you can specify how the agent is wired to the issuer and login account.
Figure 27-25 "Select the issuance set" screen
Figure 27-26 "Specify synchronization agent login" screen
Press the next step to enter the selected target database screen, as shown in Figure 27-27. Here you can specify which database is to be placed in. If you want to create a new database, enter the database properties window by adding. Figure 27-27 "Select the Database" screen
Press Next to enter the initial subscription screen, as shown in Figure 27-28. Select Yes, .... To start the database structure and information on the subscriber. If you have created a structure before, you will choose no, .....
Figure 27-28 "Initialization Subscription" screen
Press Next to enter the snapshot collection, as shown in Figure 27-29. Here you can choose where the snapshot set is transmitted. If you do not modify the quick-illustrative folder, select the location where the preset value is accepted.
Figure 27-29 "Snapshot Collection" screen
Press the next step to enter the set distribution agent schedule screen, as shown in Figure 27-30. This screen can choose a continuous update, updated according to the schedule, or updated only when needed. In most cases, it is selected. When determining how the system operates an update program, remember that the more frequent execution is performed, and the burden on the distributor and the subscriber is heavier. So the agent has been implemented as needed, not excessive execution. Press the change button to change the scattering schedule, and press the change button to enter the editing duplicate job schedule dialog, self-subscribe to repeatedly perform job scheduling.
Figure 27-30 "Setting the Distributed Agent Schedule" screen
Note If the conversion is selected, the specified DTS packet screen will appear here. To continue to perform the steps, you must establish a DTS packet before, otherwise, stop the installation steps of the elves, and return to the setting wizard after establishing a package. This example we do not choose to conversion.
Press Next to enter the necessary service screen, as shown in Figure 27-31. If the SQL Server Agent has not been launched, you can start the service from this screen. Here you can see if the SQL Server Agent has started at the subscriber. If it is not yet started, you will be prompted to start the agent program. If you want to manually start the agent, expand the management data clip in Enterprise Manager, press the right button in the SQL Server agent program, select in the quick function table Start or stop the SQL Server agent program. Press Next to enter the completion extraction subscription wizard screen, as shown in Figure 27-32. Select to complete the setting job of the subscription set.
Figure 27-31 "Start the necessary service" screen
Figure 27-32 "Complete Extraction Subscription Elf"
The issue now will be rewritten in the subscriber, and regularly update the scheduled scheduled schedule. Enable replication after confirming the schedule of the issuer agent program. If there is no setup time to generate a snapshot set to the radiator, in the case of the snapshot agent, it may take some time to send the data to the distributed person. Even if the reply is in execution, the actual information will also be sent to the subscriber after the snapshot agent program is completed. Setting Send Subscription Send Subscription To start in the issuer, you can use the Send Subscribe Elf to be set. When sending subscriptions, the scrute is determined by the issuer. Send a subscription to a subscriber for a connection. The purpose of using this type of subscription is to easily manage all subscription sets in the distributor end, and do not need to manage each subscription set individually on the subscriber. Please refer to the following steps to send subscription wizels:
Use any of the following methods to enter the send subscription wizard. The first method is to select a tool in Enterprise Manager, select Subscribe / Send Subscription to another server, enter the establishment and management server's distribution dialogue, as shown in Figure 27-33. Then select Send a new subscription.
Figure 27-33 "Establishing the Package of Management Server" dialogue
The following is welcome to use the Send Subscription Spirit screen, as shown in Figure 27-34.
Figure 27-34 "Welcome to send subscription wizard" screen
Press Next to enter the selection subscriber picture, as shown in Figure 27-35. Here you can specify which subscriber end to send to the issuance set. Figure 27-35 "Select Subscriber" screen
Select the next step to enter the logo screen, as shown in Figure 27-36. Here you can specify the subscriber's database, you can choose the existing database, or create a new database as needed.
Figure 27-36 Select the Database Library
Press Next to enter the set distribution agent program screen, as shown in Figure 27-37. Here you can choose to perform a distributed agent (this is a preset value, which is recommended), or execute it on the subscriber. This is the new option for SQL Server, which is not in the previous version of Server 7. Press Next to enter the set distribution agent schedule screen, as shown in Figure 27-38. Here you can choose a continuous update subscription or select the scheduled schedule. Select the Update Subscriber according to the scheduling, and press the change button to enter the editing repeated execution job schedule dialog to change the schedule and update the frequency of the subscriber. Remember If you choose a continuous update, it will cause the system's burden.
Figure 27-37 Setting the distributed agent
Figure 27-38 "Setting the Agent Schedule" screen
Press Next to enter the initial subscription screen, as shown in Figure 27-39. Here you can specify whether the subscribe set begins. The initialization structure description and the data set in the subscriber have been specified by the preset. If the structure already exists, no, ... this option will not provide a selection. You can also start the snapshot set agent program in this picture. It is recommended to start the snapshot agent program when starting the snapshot set, otherwise you must manually start the agent. Once the snapshot set is enabled, the snapshot set is required before the new subscription set is created. Each time you build a subscription, you will have a new snapshot set, so you don't need to set up the snapshot set unless you plan to use the snapshot set synchronous update subscriber's data.
Figure 27-39 "Initialization Subscription" screen
Press Next to enter the necessary service screen, as shown in Figures 27-40, you can specify the SQL Server agent program in the manner that is started. Press Next to enter the completion of the subscription spirit screen, as shown in Figure 27-41. Check if the setting is correct, press the completion to start the snapshot set to the subscriber's program. You will see a conversation block for a description program, and finally the message block completed by the operation program. When the wizard is completed, send a subscription to establish and update regularly.
Figure 27-40 "Start the necessary service" screen
Figure 27-41 "Complete Send Subscription Elf"
Referring to the section of Chapter 26, please refer to Management and Troubleshooting on Manage and About Demand; Monitoring and Managing Replication Agents; Disabled Documents; and Removing the Subscriber Set, Dispersive Sets, and Distribution Sets.
Setting, monitoring and school invasive senders will learn how to set up, monitor and school circulation. As mentioned in the previous chapter, the radiator is a server containing the SQL Server library (called a distributor database) for storage of replications. This profile is saved in the SQL Sever database, which will have the following advantages: Performance: SQL Server provides the performance efficiency required to distribute, save and distribute data. High credibility: SQL Server Database is very suitable for data. Due to high-order reply characteristics of SQL Server. Using the transaction record allows SQL Server to reply to the system failure, making the information not lost. Easy to operate: Since SQL Server is overwritten through the SQL Server communication protocol, directly and distributed communication, the step of setting the radiator is very simple. Setting the exuberant radiator's ending depends on the frequency of the database. Due to the SQL Server Database, all modifications in the radiator end are recorded in the transaction record file, so when setting the distributed library and record files, to determine the size required to execute It should be effective. The preset value can be applied to a smaller replication system, but when the system increases, the value of the SQL Server wizard preset may no longer be the optimization setting, because the elves did not clip the transaction file and the data folder to best Placement, usually they are placed in the same location in SQL Server. If appropriate, the distribution library can avoid problems generated by the performance performance in the future. The following is a few key points for setting the distributed library: use the RAID controller in the distributed library system. The use of hardware RAID controllers is efficient than using the RAID controller using the software. Set the transaction record of the distributor database in the RAID 1 disk area. The transaction record file should be subsequently arranged to achieve preferred performance efficiency by sequential I / O operation. Set sufficient storage space for the transaction record file to avoid the need for constant update transaction record files. Visual information updates a transaction record file per night. Set the distributed library in the RAID 1 or RAID 10 disk area. Since the number of times the written radiator database is more frequent, we do not recommend RAID 5 here. Set enough space for the distributor database to save extra replications. If the subscriber is faulty, the database may need to save several days of replication. Start your distributed library in a way that call other SQL Server libraries. Setting the ENTERPRISE Manager to set the partial point to set the appropriate distributor database according to the previous point, you must specify the location of the database. To perform this step using Enterprise Manager, first turn on the setting distribution and distribution wizard, use this wizard to set the issuance and distribution, and select Enter the self-subscriber distribution in the custom setting screen. This option allows the distributed library to manually specify the database location, which allows the database to rename, enable issuance, and establish a distribution set and subscriber. However, when using the elves, the size of the distributor database cannot be set. To set the database size, select the database properties in Enterprise Manager, change the database size and transaction record clamps in ENTERPRISE Manager. If you want one to specify a database location and size, you can use sp_adddistributionab this pre-depreciation. Use sp_adddistributiondb to set radiators using sp_addidistributiondb this pre-depicting program to establish a distributed library with an instruction code. When you want one and set the database size, the location is applicable to the transaction record. A distributed library is established using the instruction code, which can be repeated to use the specified code to rebuild the distributed library when the command code is used in other systems, or when the reconstruction system is set.
Description Before establishing a distributed library, you should enable distributors first. You can use sp_adddistributor to make settings, then use the distributed system name. In SQL Server "Online Book", there is a syntax using sp_adddistributiondb pre-depreciation, the following is an example:
SP_ADDISTRIBUTOR DASH
The following SQL statement starts setting Dash this database is distributed:
sp_adddistributiondb
@ Database = DIST,
@ Data_folder = 'C: / MSSQL2000 / DATA',
@ Data_File = 'dist.mdf',
@ data_file_size = 10,
@ log_folder = 'c: / mssql2000 / data',
@ log_file = 'dist.ldf,
@ log_file_size = 2,
@ min_distRextion = 0,
@ MAX_DISTRETENTION = 72,
@ history_retention = 96,
@ security_mode = 0,
@ login = 'sa',
@ Password = '',
@ createmode = 0
Monitoring distributors use the Windows 2000 Efficvation Monitor to monitor the distributor. There are several items in the Efficacy Monitor to be added when using SQL Server, including: SQL Server: Replication Agents: Computing Now there are several different types of agents in the system being executed. SQLServer: Replication Dist: Provides information about the distributor delay time. SQLServer: Replication LogReader: Provides information about the activity and delay time of the record reader agent program. SQL Server: Replication Merge: Provides rate information about the merged documered. SQLSERVER: Replication Snapshot: Provides information about snapshots. Using the performance monitor to monitor these values to let you know if you have difficulties in performing performance. Although these performance monitor materials provide a lot of useful information, it is not necessarily to find true problems. Among the above items, the monitoring distributed vectors is the SQL Server: Replication DIST object. This object provides several counters: dist: Delivered cmds / sec: The number of distributed instructions transmitted to the subscriber per second. This counter provides how much activity is available on the subscriber. Dist: Delivered Trans / Sec: Transfer number of distributed transactions per second. This counter provides how much activity is available at the subscriber. Dist: Delivery Latency: Dispersive Delay (in microseconds). Pass the transaction to the distributor and use it in the subscriber. Although these counters record the process of execution of the distributed person, if you want to use these information to determine how to transfer the distributed vendor, it is still slightly insufficient. Because the most important part of the school-adjusted vendor is a calibration that belongs to the SQL Server Database. The focus of the school is as follows: CPU's high usage: Is there a high usage rate of one or several CPUs (75% of the CPU capacity is high)? I / O bottleneck: Is I / O too high? Monitor how much I / O operation per second and how much time does it take to perform an I / O operation? Response time: Is the response time of SQL Server too long? As mentioned earlier, the transferr is mentioned earlier, the radiator is a servo of the distributed library, and the way to adjust is like other SQL Server libraries before, such as the correct setting of the distributed. The distributor is a bridge that the issuer communicates with the subscriber, so be careful to set it enough to cope with the flow to avoid the factors of the performance bottleneck. The following items can be made to set with the number of revision of the radiator: Automatic I / O subsystem: Determining the distributed I / O capacity. Using multiple processor systems: The execution capability of the CPU is usually not the problem, because in many cases, the action processed in the distributed person does not use a large number of CPUs. Even so, it is best to prepare for the least two CPUs to cope with certain jobs at the same time. Calling operation system: Setting the server service to achieve the best productivity, through the network within the console, so this setting. In addition, unwanted services can be removed, such as IIS and FTP services.
Monitor distributed vessels when performing snapshot sets: When the quick-illumination is executed in trading and merger (including the first time to reply), because many of the information will be written to the distributed person, there is a lot of i The / O operation will be generated at the same time, which also makes the I / O subsystem possible load too much. Once this condition is generated, the time required to generate a snapshot set will increase, so you must transfer the quick-illuminated collection time and monitor the distributor. Calling SQL Server: Use the tips and focus to adjust your SQL Server system with the continuous mention of this book. Trading Document Calling This section will learn how to set and calibrate to optimize the execution of trading. First, we will first understand the trading replication attribute, and then begin learning setting, monitoring, and calibration points. Trading-based DC Duty Docked First Copy the snapshot set to the sender, then go to the subscriber. When the snapshot is copied, the record reader agent program executed on the radiator end will continue or read the transaction record file of the issuer. The frequency of reading is determined by the setting of the reader agent. (The issuer will encounter questions related to the replication when reading the transaction record file.) Record the record of the transaction record file in the transaction record file reader, which will be placed in the distributor database These transactions will eventually be sent to the subscriber. The following factors will affect the performance efficiency of trading-based compilation: I / O performance of the issuer transaction record: Read the transaction record file in the issuer to know what information modified. When replication is executed, the transaction record file is also written while being read, so the access to subsequent transaction records may be interfered, causing bottlenecks. To avoid this situation, you must pay special attention to the settings of the record file. The performance of the distributor: the performance of the distributor, which is related to the number of dollivers and issuers using the distributors, this chapter has learned how to install and school radiator. Subscriber's performance: The performance of the subscriber is related to the polynity of activities on its system. Effective SQL Server Data Sheets on the subscriber can avoid the problem of subscriber performance bottlenecks. Since many factors can affect the performance performance of the system, the appropriate setting size and system can effectively avoid the generation of these factors, so that the system is efficient. Setting the trading replication or the old saying, to avoid replying the burden on the system, in the issuer, the transaction record must be appropriately set. In this section, we will look at the focus of setting trading reproes: all replication systems have enough I / O capacity (hereinafter refer to how to set enough I / O capacity). Or consider setting the I / O capacity of the issuer's transaction record more than needed. Increase the size of the radissipper end. Call the record reader agent program. Set enough I / O capacity If the capacity of I / O is sufficient, the performance of the replication process can be enhanced. Like other SQL Server systems, transaction record files responsible for records in the system should be placed in their own RAID 1 disk area to protect data, and the data clip is placed in one or several RAID 10 or RAID 5 disk disks. Different from snapshots Differently, trading replication only requires micro-adjustment standard I / O settings, which will be discussed in this section. In general, in accordance with the I / O subsystem of the issuer, the issuer is set to set the key points in this book. However, sometimes it is necessary to set the I / O capacity of the issuer's transaction record file, and set more than the required capacity. Usually we will set the transaction file in the RAID 1 disk area. When needed (depending on the busyness of the system), it may be required to be located in the RAID 10 disk area, using more disk zones. RAID 5 is not applicable.
The transaction record gear of the distributed I / O subsystem emitting the I / O subsystem should be set in the RAID 1 disk area. This allows the record file of the distributor database to achieve the best performance, improve the performance of the distributed person. The subscriber end of the subscriber setting I / O subsystem transaction type does not require special I / O settings. As long as the general point is set, it can be set. Setting the distributor's approval batch size radiator's number of transactions determines the number of reply transactions that can be recognized in a single batch. If the batch size increases, the information columns that can be recognized in a single batch increase. Based on when the distributed table is updated, the processing time increases to use to the distributed person data sheet. If the size of the batch is reduced, the data recognized in a single batch is reduced, and the time waiting for the distributed person's data sheet is relatively decreased. If the distributor database is accessed by different data sources due to the same time (referring to a distributor and several subscribers), try to reduce the batch size. If the reader agent is executed in a regular schedule, and the large number of transactions inserts a large number of transactions in the database, it is advantageous. You may not need to change the batch size at all, but if you really need it, it is recommended to decide which setting is better before the change is more than a batch before the change. In Enterprise Manager, you can enter the property setting of the distributed agent program, and set the approved batch size. Related information can also be found in the "Surveillance and Management Docked Agent" section of this chapter. Calling the Reader Agent Program As mentioned previously, the record reader agent can read the transaction record file of the issuer, so that the recording file replaces sequential I / O operation in a random manner. After setting the record reader agent program, you can reduce its impact generated by the record. There are several ways to make the operation of the reader agent more efficient, one of which is to let the transaction record file disks use a fast-tap control card, due to the recording reader agent program read record file disk The quick break on the control card will make the reading operation as much as possible from the quick acquisition, will not cause random I / O operations. Another method of calling a reader agent program is to reduce its number of execution. The recording reader agent can be performed in continuous or periodic. If the system's update is not frequent, it can be performed in continuously. Conversely, if the system's transaction record file is frequently used, reduce the number of executions of the issuer's side record reader agent program. In this way, the recording reader agent will not read the transaction record frequently, so that the I / O of the transaction record file remains sequential. If the system is used very often, it can increase the method of reading the batch size, so that the record reader agent program is more efficient. Here is the number of times the transaction read from a transaction record file, and how much transaction is written over time to the distributed person. In a system with high usage, add this parameter to improve performance performance. In addition, when you increase the polling interval, it is helpful to increase the batch size. If the size of the reader read batch is added, it should also be increased in the size of the distributed batch to comply with the new read batch size. You can use Enterprise Manager to set the record reader agent program. This book
