26. Microsoft SQL Server Replication: Overview and Snapshot
What is the database replication?
Replication concept
Set distribution and distribution
Snapshot
Management
Snapshot copy school
Summary of this chapter
Microsoft SQL Server Database Database Database Database Database Capability can be used to distribute data and pre-depreciations in different servers, and the copy allows the system to automatically copy data to another system. Database SUM, you can decide the number of data should be replicated according to your needs, and you can decide to assign the information to how many systems. Since the replication program is automatic, and the repository will store the repository and replica information during the replication period, there is no need to worry about the loss of information loss. If the copy program is interrupted (such as power out), the compression will continue from the interruption after the system restores the normal operation.
Because the program is quite complex, this book discusses this topic in three chapters. This chapter will first learn the basic methods and concepts of reproces and snapshots. Chapter 27 will introduce transaction-type replication. Chapter 28, introduce merger compounds. The information provided by these three chapters will allow you to completely understand SQL Server. Includes how to plan, manage and use.
What is the database replication? Database replication is actions to copy or rewrite data from a data sheet or database to another data sheet or a database. Use a copy, you can completely or specify a partial repository copy, disperse to other systems within the company. When using SQL Server replication, assignment, and replication data, it is automatically performed, and the user's intervention is not required during the process of copying. Since the data is completed inside the SQL Server, stability and reusability are there. If you have an error during the recall (or any SQL Server transaction), the job will continue to execute from the interruption after the problem is fixed. Therefore, many people use the compound to replace other methods of moving data between systems.
There are many options that can be used to plan a copy on the Internet. For example, you can specify where the data to be replied. You can specify a copy of the copy whether it is only read or modified. It is also possible to specify that the information needs to be replied every long. These and other options will be described in the
Copy concept This section will learn basic database. Repronder concept. First understand the issuance and subscription models, as well as the three types of replication: copy information, data dissemination, and replication agent. Document in Microsoft SQL Server 2000 is to use the "Publish-and-subScribe" mode, this mode is first used in SQL Server 6.0 to build data, mainly including three concepts, ie Publishers, distributors, and subscribers (SUBSCRIBERS). "Publisher" is a database system that allows data to be written; "distributed person" is a database system containing a distributed database or virtual data (Pseudodata) to maintain and manage replication; "Subscriber" is a copy information and storage Database system of the repository. The issuer issuer consists of a Microsoft Windows system installed with a SQL Server repository. This database is a source of replica. In addition, it is also detected that the information has been changed, which makes the data are rewritable. The issuer also needs to maintain all information about the issuance set in the station, such as which information is programmed, and so on. According to the type of copy, the issuer will have a different workload in the demand, which will be further explained later.
A replication environment can contain many subscribers, but any issuer to be issued, that is, the data set to be replied, can only have a issuer. (The issuance will be detailed in this chapter.) A detailed manner in this chapter.) A specific information set can only have a distributor, which means that the issuer is the only component that can modify the information, and the subscriber can also modify the information. And then release information, but this requires a little skill, you can refer to this chapter and the next two chapters. Radiator
The distributor is a servo that includes distributed database and storage relay information, history record information, and other information; distributed people need to be partitioned to the subscriber. Publisters and distributors don't need to be placed on the same server. In fact, you may prefer to use a dedicated server to serve as a distributor. When the issuer is established, each issuer must assign a distributor, and a issuer can only have a distributor. This arrangement will have a detailed description in this chapter later
Description
Trunk Data is a summary of information to track the status of the reproction job in the repritic. This information also transmits other members of the issuer to the copy collection, including information about the data structure and the data attribute, such as the type of data in a piece of information (numerical state, text, etc.) or information line Length, etc.
subscriber
Just mentioned that the subscriber is a repository server that stores reprocessing and receiving updated. Subscribers can also update and serve other systems as issuers. At the same time, an subscriber can also be used as issuers of other systems. To allow subscribers to receive replications, it must subscribe to this information. "Subscribing to replication" means the subscriber's planning method for receiving the information. And a subscription refers to the database information you subscribe to. We will discuss the interconnectivity between these components in the Multiplex type SQL Server provides three types of replications: snapshot, transactional, and mergex (MERGE). The types of these replications provide different levels of data in the repository, and there will be a system load of different levels. Snapshot Snapshot compilation is the easiest and easy to understand. In snapshot, Snapshot will periodically produce and transmit to subscribers. The most important advantage of snapshot replication is that it does not involve continuous system loads between issuers and subscribers; that is, it does not require continuous monitoring issuers, and does not need to continue to transfer information. subscriber. The biggest disadvantage is that the database currently existing on the subscriber is not necessarily the latest snapshot set. In many cases, the snapshot compound is sufficient and appropriate. For example, when the modification of the source data is only when it occasionally, information such as a phone list, price list, and project description can be easily handled, which can be updated daily at a daily spare time. Trading Trading replication can be used to reply to the database. Using trading replications, any change in issuance (plan to be replied) will immediately take it from the transaction record file and spread to the distributed person. In accordance with your programming, use trading replications to keep your issuers maintain a near-consistent state of their subscribers. When you need to keep all replies systems for the latest state, trading is used. Since the transactions in the system are changed in the system, they use them to the replication system, and the trading replication is more system resources than snapshots. However, trading replica is more able to keep the system in the latest state than fast-acting or merged. Trading Documents will be described in detail on Chapter 27. Consolidated copy Core-form-type replications and trading reprocesses are thereby continuously tracking changes to the issuance. However, merged complications do not have individual dissemination to change transactions, but a cyclical transfer of a whole group. It is also similar to a snapshot-based reply due to the merchant replication. (Although all materials to be replied in snapshots are propagated, not just change.) Core-forms will be described in detail on Chapter 28. Rewinding information You must store the data to be replied in a item called the distribution set. A publication is composed of a (including) issuance. Let us take a more detailed look at the issuance and distribution set. Before the issuance, an issuance is an individual collection of information to be replied. A issuance may be a full information list, or a data sheet subset consisting of a specific data line and a data column, or a pre-store program. You can use a filter to create a subset. "Horizontal Filter" can be used to establish a subset consisting of data columns; "Vertical Filter" can be used to establish a subset consisting of data lines. The horizontal and vertical filters will be described in detail later in this chapter. Note In combined compound, the definition of the filter is not the same as the snapshot and trading. This chapter will explain the next chapter. The Issue Distribution Set is a collection of issues that are combined together as a basic unit. The issuance sets the issuance of logical groups as a group of replies. For example, you can establish a distribution set to compose a database composed of many data sheets, and each data sheet is defined as an issue. When rewriting the database, copy the entire database with a distribution set will be more efficient than the data table individual. The issuance set can be constructed from a single issue, but most of them contain more than one issue. However, subscribers can only subscribe to the issuance, and cannot subscribe to the issuance. Therefore, if you want to subscribe to a single issue, you must set a distribution set with this issue and then subscribe to this distribution set. Sending and extraction subscription rewritable can be transmitted using several methods. All transfer methods are not the "Push Subscriptions" (PULL Subscriptions). Subscribers can support simultaneous subscriptions that send subscriptions and extract subscriptions. Send a subscription When you use the send subscription, the distributed person is responsible for providing updates to the subscriber. Update initialization does not require any request from the subscriber. The send subscription is useful when you need to centrally manage, because these rewrites are controlled and managed by distributors, not different subscribers. In other words, the initialization and scheduling of the replication is controlled by the distributed person. Send Subscription Allows you to have more flexibility on a reply schedule. Send subscriptions can be set to keep the copy scenario approach instant synchronization, or perform updates according to the specified schedule. In this section of this chapter, you will learn more about these options. Extract subscription Extract subscription (PULL SUBSCRIPTION) allows subscribers to be initially replied, replied can be performed manually. Extraction Subscription Suitable for use in a large number of subscribers or subscriber connection time when the issuer has a large number of subscribers. Subscribers can regularly connect online and extract replication through extract subscriptions. Another benefit of extracting subscriptions is to reduce the wiring error message sent to the distributed person. If the distributed subscriber is initially written to a subscriber that has not responded, an error message will generate an error message. Therefore, if the copy is initialized by the subscriber, it will not generate an error message. Complion agent Several agents can perform a copy of the subscriber from the distributor to the end to the subscriber. These agents include: Snapshot Set Agent, Record Reader Agent, distributed agent program, merge agent program, and column reader agent program. This section will learn how to manage these agents. Snapshot set agent "Snapshot Agent" is used to establish and disseminate quick-discharge collection to distributed (or snaps). The snapshot set agent has established a copy of the data (ie, the quick photo) and the establishment of information, which allows the distributor agent to disseminate information (this information is relay information). The snapshot agent stores the data snapshot to the distributor, or specifies other or additional locations to store. The snapshot collection agent is also responsible for maintaining the synchronous status information of the recording object; this information is stored in the issuer database. The snapshot agent has different scheduled, which can be scheduled to start the execution job. The snapshot set agent will perform the following steps: Establish a connection between distributors and issuers. Suppose the two are not connected, the snapshot agent will not establish a snapshot set. When the connection is established, the snapshot set agent is locked all issuings to ensure the consistency of the information quickly. (Because other users can update the data sheet after the lock, the snapshot agent must arrange the peak time execution of the database activity.) Establish the connection between the issuer and the distributor, and write for each issuer Enter a data sheet structure to the radiator database, this information is relay data. Copy the information on the issuer has issued the information sheet to write it to the snapshot set folder. The snapshot assembly clip does not have to be placed in the distributor. If all subscribers belong to the SQL Server system, the snapshot group is stored as the Native Bulk Copy Program (a large number of copies of discussion in Chapter 24). If one or more subscribers belong to the source of heterogeneous data, the snapshot group is stored as a text file. Synchronization information is set by the snapshot agent program. When the data is copied, the snapshot agent is updated to the distributor database information. Release each shared lock that has been issued, and completes the write action of the recording process table. Therefore, the snapshot agent program is only responsible for establishing a snapshot set, while the distribution of materials is executed by other agents. The snapshot set should be updated frequently when using the snapshot set and the issuer's data is used. Since the snapshot set is fixed to the subscriber, the latest state should be maintained. Description When using trading replication or merger, or add new subscriptions, there is no need to update the snapshot set. Record reading agent program Record the read agent program for trading replication. It can move the trading labeled as a replication, from the issuer's transaction record to the distributed database. Each database indicated as a trading type has a record reader agent program in a distributor. The record reader agent has discussed in detail on Chapter 27 of this book. Distributive agent Distribute Agent Distribute Quick Collection and Trading from the Venerability Database to the subscriber. Every issuer has its own distribution agent. If you send a subscription, you will be performed through the distributed agent of the distributed person. If you use extraction subscription, it is performed through the subscriber's distributed agent. Merger agent Merger Agent can be used with merchant-in-incompatible data. When using a merged documered, it is not used to distribute the agent program or the snapshot agent, but use the merged agent program to connect the issuer and subscriber. In Chapter 28, we will discuss the consolidated agent in detail. 伫伫 Reader Agent The Queue Reader Agent is used to transfer the update data to the snapshot replication or trading replication subscriber of the column update option. This option allows for changes in the subscriber without using a decentralized transaction. Monitoring Replication Agent You can use the Enterprise Manager to monitor the reply proxy, steps below: Expand the server group in Enterprise Manger and expand the server specified as a distributed library. Right click on the Swout Monitor and press the Efficiency Monitor. Expand the copy monitor data clip, then expand the agent program folder. Expand the agent program folder to monitor. Press a proxy program to surveillance, and press the right button in the right detail pane to call the fast display function table, then press the agent program process to monitor the replication agent history. Setting issuance and dissemination To set issuance and distribution, you can distribute and distribute through the set distribution and distribution wizard, but through the pre-deposit program, it is more recommended by the set distribution and distribution wizard. Through Enterprise Manager, the following wizards can be used to copy and write: Establishment issuance wizard to establish an extraction subscription Elf Establishment Send Subscription Elf Disable the Issue and Distribute Elves This section describes the settings and distribution procedures for all replication types. After setting the issuance and distributor, set the copy type (this chapter will show you how to set various types) later. The following is a set release and distribution program: At SQL Server Enterprise Manager, press the server set to the distributed server. Select the wizard from the tool column. After the elf dialog is displayed, the copy data clip is expanded, select the setting distribution and distribution, as shown in Figure 26-1. Press OK. Figure 26-1 "Select Elf" dialogue The setting distribution and distribution wizard welcome screen, as shown in Figure 26-2. Figure 26-2 "Setting the distribution and distribution of the wizard" Welcome screen Press Next to display the selection of the distributor screen, as shown in Figure 26-3. Figure 26-3 "Select the distributed person" screen In this picture, you can choose to set our servo to a radiator and issuer, or set only the issuer and use a distributed distributor previously completed. If you choose to set the nearest system as a distributor, press the first option. The elves will build a distribution database and record files for you. If you want to use another system as a distributor, press the second option. If there is no other SQL Server system being set into a distributed person, the available option will only be the first. As a dedicated system, it is a relatively suitable choice. Note An issuer can only define a distributed person, and all issuers use the same distribution. If you choose to use another system as a distributor, you must register the SQL Server system and it must have been set to distributed. Press the "New Server" button and select a verification method to create a connection from SQL Sever on the "Register SQL Server Properties" window. In the example, select the system in the settings for distributors. Now, the wizard will confirm that the issuer has access to the distribution. If the login program fails, the SQL Server Agent Properties window will appear, allowing the agency registration account to be modified, as shown in Figure 26-4. Figure 26-4 "SQL Server Agent Properties" Window Note If you use the native system account when setting SQL Server, you will see this message: This unit system account is only local system permissions; If you use a domain account when setting SQL Server, the SQL Server Agent Properties Window of Step 7 will not appear. In this window, you can modify other features of the SQL Server Agent program (relevant information can be referred to Chapter 31). After the setting is completed, it will be sure. Set the SQL Server Agent program appear, as shown in Figure 26-5. This screen prompts to set the SQL Server agent to automatically start. SUM will execute according to the SQL Server Agent command. So if the agent is not executed, the replication will also do without hair. It is recommended to let the SQL Server agent start automatically, press Next. Figure 26-5 "Set SQL Sever Agent Program" screen Specifies the appearance of the snapshot data clip, as shown in Figure 26-6. Specify the snapshot position, you can use a preset location or a new location. Press Next. Figure 26-6 "Specify the snapshot data clip" screen The self-subscription configuration screen appears, as shown in Figure 26-7. Figure 26-7 "Self-subscription Configuration" screen In this picture, you can select all presuppositions (2nd option) or self-subscriber libraries. If you choose to accept the preset, the installation process is completed and the screen appears. Here, let's first select the first option to continue setting. Note The preset location may be selected to a disk that is not large enough, so we continue to set the steps to learn how to subscribe to the distributed library. Select the next step to enter the provision of the distribution library information screen, as shown in Figure 26-8. Specifies the distributed database name and file location. Figure 26-8 "Providing Transport Database Message" screen Press Next to enable the enable distributor screen, as shown in Figure 26-9. Choose a different issuer with steps, or add a distributor database (add a new line to SQL Server issuer). Figure 26-9 "Enable Publisher" screen Press Next to enter the enable distribution cashbin screen, as shown in Figure 26-10. You can choose to enable transaction or consolidated replications for a single database. No copy type is selected, so you will have a distributor and distributor if you skip this step, but you will not define the issuer. The issuance set can be set by the establishment of the wizard setting (more recommended approach). Figure 26-10 "Enable Issue Capital Relief" screen Press Next to enter the enable subscriber picture, as shown in Figure 26-11. Select the subscriber subscribe to this release set. We recommend not use this step to choose, it is best to do this through the setup and distribution wizard. Press the next step to enter the setup screen of the distributed and distributed elf, as shown in Figure 26-12. This screen will display a summary. Press the completion of the completion setting, take a point to establish a setting. When the setting is complete, the program is listed, and whether the SQL Server agent is required to start. To perform in the SQL Server agent program, the replication agent will be executed. When the setup is complete, you can see more than the monitor in Enterprise Manager. Figure 26-11 "Enable Subscriber" screen Note If you want to change the settings of the repository, select the database in the Enterprise Manager, select the duplication of the tool, and select the pre-change option from the listed items. Figure 26-12 "Completing the Setting of the Evil of the Elf" Snapshot DC This section provides detailed information for snapshots. First learn when to use snapshots. Then introduce the planning snapshot. Use snapshot reply snapshots to be used in situations where data is mostly static and unconventional. Like the previous studies, snapshots are overwritten to generate snapshots and transfer them to subscribers. The information will be updated when using the next snapshot set. If you use a snapshot, you will update it when you use a snapshot set next time, so you don't need to modify the information on the subscriber. The timing for use of snapshots is as follows: a sales unit distributed product price list to all flagship stores, all prices updated every night. The information sheet is not required to be updated frequently, which is static. When data requires regular updates (often need to be rewritable), trading or consolidated compounds are more applicable. Setting the snapshot multiplexing as previously mentioned, set the issuer and the issuance set (as shown in this chapter) before setting any reply. To set a snapshot, set the release set, then set the subscriber. Setting the Distribution Set Settings The information, the way you want to copy, and when you generate a copy. The setting steps are as follows: Use Enterprise Manager to perform settings, select Tools / Replication, Click to establish and manage the issuance set, or select Tools / Elf, select Tools / Elf. The dialogue between the establishment and management issuance set can be entered with any party, as shown in Figure 26-13. Here you can choose the pre-issued database or data sheet. Figure 26-13 "Establishing and Management Distribution Set" dialogue If the issuance set exists, the following buttons will be displayed: Send a new subscription: You can create a new send subscription for the established issues, which will be discussed in this chapter Critical Distribution Set: The instruction can be established to establish more issues. Delete Distribution Set: Delete the set release set. Choose a distribution cashibrary (as shown in Figure 26-13, select Northwind as the issuing price library), select the Welcome to establish the issuance set into the establishment of the wizard, as shown in Figure 26-14. Figure 26-14 Welcome screen "Establishing the Works Elf" Note that in this window, there is a progress option option to display this elfrice below. We don't check this option first, because if this option is checked, it will display the establishment of the update and the column update subscriber option. The advancement option allows the subscriber to update the issuer and the issuer. In addition, options that allow subscription data conversion will appear. Click Next to enter the library window for selecting the issued set, as shown in Figure 26-15. You can select the information you want to issue again. The library selected in step I will be selected. Figure 26-15 "Database of the Selected Set" Select Next Go to Select the Type Window, as shown in Figure 26-16. Figure 26-16 "Selection Disabled Set" window You can choose any copy type. The functions of the three issues are as follows: Snapshot Issue: The snapshot set of issuance items can be replicated to the subscriber. Snapshot types can be established on either data sheet. Trading-style issuance set: Create trading replication, when the issuance set data is changed, the transaction is collected and passed to the subscriber. The issuance can only be established only when the data sheet is provided with a main index key. Completed issues: The issuer can be allowed to double-way with the subscriber. Any information sheet can be established. Select the snapshot issuation, press the next step to enter the specified subscriber type window, as shown in Figure 26-17. This window can specify if all subscribers perform SQL Server. Figure 26-17 shows the selection in the preset, and the preset setting specifies that all subscribers perform SQL Server 2000. If this setting is accepted, it is set to use the type of data using the original SQL Server 2000. Others can also choose SQL Server 7.0 server, or other nature of server, this option will convert the reply information to the filament format, of course, have some complicated conversion procedures. Figure 26-17 "Specify Subscriber Type" screen Press Next to enter the specified issue screen, as shown in Figure 26-18. In this picture, you can specify that the data sheet or other database object is written as an issue. These issues constitute the issued set you established. On the screen listed on the left, you can check the listed items in the display, including the data sheet, pre-deposit, and view tables, one, check any project, and the window of the right hand will list these items. The information indicates fine. Then you can check the information table you want to issue in the detail of the right hand, or return to the window of the left hand, check all the information sheets in the release of the selected item. Remember, each data sheet, pre-deposit procedure or inspection table is an independent issue, and the issuance set is constructed of a set of issues. Note the issuance preset value button in this picture. This button allows you to set a preset value in the system. When you press this button, the type of issue will appear for you to choose. After selecting the information sheet issuance, press OK to enter the preset data sheet issuance item properties window, as shown in Figure 26-19. The following options can be specified in the general tab page: Item Name Offer Item Description Source Data Sheet Name Off Source Data Sheast Figure 26-18 "Specified issuance item" screen Figure 26-19 "Preset Data Sheet Properties" Window "General" tab In the snapshot tab, as shown in Figure 26-20, the following options can be set: Remove any existing information and specify how to operate the index. Specifies whether or not to copy the clustered index or nonset consolidation index. Specifies whether to convert the user's self-customized data type to a substrate data type. Specifies whether to copy the condition constraint. Specify the settings and press OK. Figure 26-20 "Snapshot Set" tab of the "Preset Data Sheet Properties" window After determining the option to specify the issuance, press Next, enter the issue of the issue, and the analysis window of a distribution set is created. If you want to issue a Northwind library, as shown in Figure 26-21, you will remind you that when the Identity Data Line is transmitted to the subscriber, the IDENTITY property is not transmitted. After analyzing the issuance set, press the next step to enter the selected issue set name and description window, as shown in Figure 26-22. In this picture, you can simply specify the issuance set name and description, or you can choose to list this distribution set in the Active Directory service. Figure 26-21 Windows that appear when you want to copy the Identity data Figure 26-22 Select the name and description of the issuance set Press Next to enter the properties window of the custom release set, as shown in Figure 26-23. In this picture, you can specify whether to define the data condition filter (select Yes), or use the preset setting option (Select No). If you choose No, then press the next step, then complete the establishment of the distribution wizard, as shown in Figure 26-31 (where you want to end this section). In this case, let's choose to see what options for the remaining screens. Figure 26-23 "Property" screen " Select Next to enter the filter information screen, as shown in Figure 26-24. In this picture, you can specify whether you want vertical screening or horizontal screening data line (you can choose both). In this example, we have two items. Explain that the functional screening provided by the use of snapshot or trading replication is used. However, the implementation method of conditional screening is related to the copy type used. In the next chapter, we will learn the effects of conditions to screen the impact on trading. In the snapshot, it is a screening action with the WHERE clause to establish a quick-illustrative set. This section step thirteen will explain how to specify the WHERE clause. Press the following step to enter the data line screen of the filter data sheet, as shown in 26-25. This screen can exclude the information line not to be written. First click on the information table from the information table in the issuance, and then cancel the verb block of the data line not to be replied within the data line in the data sheet. This allows the issuance of the issuer vertically, which is also a small information on the data sheet for the issuer. Figure 26-24 "Screening Information" screen Note that the main index key data cannot be excluded, the next chapter explains the reason that cannot be excluded. Figure 26-25 "Data Line of Screening Data Sheet" screen Press the next step to enter the data list screen of the filter data sheet, as shown in Figure 26-26. This screen can choose to exclude unwanted information columns. After selecting a data sheet, press the [...] button to establish a filter. Figure 26-26 "Screening information list" screen Specifies the screening dialog box, as shown in Figure 26-27. This dialogue allows the WHERE clause to be added to the SQL statement to exclude the necessary information columns. When you complete the data columns you want to exclude, press OK to return to the setting wizard. Figure 26-27 "Specifying Filter" dialog Press Next to enter the allowed anonymous subscription screen, as shown in Figure 26-28. This screen allows you to specify whether an anonymous subscription or name subscription is allowed. You can choose from you. Figure 26-28 "Allow anonymous subscription" screen Press Next to enter the schedule of the set snapshot set agent, as shown in Figure 26-29. You can accept the preset scheduled setting or press the Change button to edit the interrogation block. Figure 26-29 "Setting the Screen Examination Agent Program" screen Press Change Enter Edit Repeat the job scheduling dialogue, as shown in Figure 26-30. This dialogue can specify the schedule of the snapshot set, select the settings that suits you. Figure 26-30 shows the start time and end time of the fast illumination set as the same day. If the snapshot duplication is set (as the program is currently in operation), and the snapshot schedule is set to the loop schedule when the snapshot is set. When the set is a trading, it is necessary to perform a snapshot set after establishing a new subscriber (unless the anonymous subscriber) is set. The merged duplication will send the latest snapshot to the subscriber. Therefore, when setting the merger, you can perform a snapshot set immediately after the new subscriber will be established or established. After the setting is completed, press OK. Figure 26-30 "Edit Repeat Implementation Job Scheduling" dialogue Press the next step to enter the completion of the establishment of the wizard screen, as shown in Figure 26-31. Here you can see the summary of the set issuance set. After the summary information is checked, you can see the process of establishing the issuance set of the wizard, you will see the screen as shown in Figure 26-32 after completion, and inform the issuing set has been established. Figure 26-31 "Completion of the Establishment Elf" Figure 26-32 Inform the issuance set successfully established screen The presentation set has been successfully established and can be released to the subscriber. You can establish a dialog that is established and managed (mentioned earlier this section), allows the subscriber to start operation, or modify the properties of this distribution set. Modifying the quick-illustrative schedule When there is a change in network conditions, it may be necessary to modify the discussion of the quick-illustrative set (enabled the snapshot schedule setting steps to introduce this section later), when setting the snapshot, you must consider the following Item factors: Volumeability of data: If the information is often updated, the snapshot set must be updated frequently, and vice versa. Necessity to update: Is the subscribed system depends on the update of the information? If the answer is affirmed, the snapshot set must be updated frequently. The system's execution speed: If the network, issuer, distributor, and subscriber's system speed is very fast, even if the frequency of the quick-illustrative set is higher, it does not affect the performance performance of other components in the system. To set the snapshot scheduling in accordance with the following steps: In Enterprise Manager, expand the server you want to modify, expand the copy monitor data clip, start the agent program, select the snapshot set agent program folder. In the pane of the right hand, click the duty button to modify the right button, and then select the agent program attribute after the fast display function is called, as shown in Figure 26-33. Figure 26-33 An issue set in Enterprise Manager Enter the properties window, as shown in Figure 26-34. This window displays the optional properties of the issuance set agent program. Figure 26-34 "Agent Properties" window "General" tab Select the scheduled tab in this picture, as shown in Figure 26-35. Existing distribution set scheduling can be viewed in this tab. The new scheduling button allows new scheduling, and the new alert button allows new alerts. The Edit button can be used to modify the existing schedule, and the delete button is used to delete the existing scheduling. Figure 26-35 "Scheduling" tab of the "Agent Properties" Select Editing Enter the Edit Job Schedule Square, as shown in Figure 26-36. This dialogue allows the snapshot set agent to set up the requirements of the requirements. Press the change button when you click Repeat, enter the editing duplicate job scheduling dialog. The agent program can be set when the CPU is resting. Such settings have their advantages in some situations, but one but set this option, you cannot expect the agent to execute when it is performed. Enter the appropriate schedule to press the OK button. Figure 26-36 "Editing Job Scheduling" dialogue When completing the above steps, the issuer updates the information of the snapshot set in the issuer database. This schedule will determine the frequency of the snapshot set. If you want to update the snapshot set to the frequency of the subscriber, you must set the setup agent program for the subscription setting. Enabling subscribers must enable subscribers in the issuance of funds before setting the subscriber. Enable subscribers can communicate with the SQL Server system and the issuance of the distribution. After the distribution cashbase and the subscriber are established, the subscriber can be set (this process will be explained in the next section In Enterprise Manager, select Tool Menu. Select a copy / set release, subscription, and distributed, or select the wizard / replication / set distribution and distributed wizards in the selection tool function table, enter the issuer and radiator property window, as shown in Figure 26-37. The issuer and the issuer property window, select the subscriber tab, as shown in Figure 26-38. You can see the list of subscribers on the Internet in this tab. Subscribers must have defined to receive subscriptions in the list. Figure 26-37 "Publisher and Transport Attributes" Window This tab can be specified, which subscribers have subscribers to the issuers listed in the tab. When the issuer and radiator property window is opened for the first time, only the issuer system is listed in the window because any subscriber has not yet been added. Figure 26-38 "Subscriber" tab of the "Punner and Transport Attribute" To add a subscriber, press the new button to enable the new subscriber dialogue, as shown in Figure 26-39. In this dialogue, select the subscriber you want to enable (SQL Server, Microsoft Access, OLE DB, ODBC, etc.). The setting here determines the type of issuer subscribed to the new subscriber. After selecting SQL Server Database, press OK. Figure 26-39 "Enable new subscriber" dialogue Now enter the registered SQL Server property window, as shown in Figure 26-40. Since the SQL Server Database is selected in the previous step, the options that appear in this window are related to the options for the SQL Server connection. If other options are selected in the previous step, different windows will appear. Specifies the subscriber's system name and account authentication mode. Figure 26-40 "Registered SQL Server Properties" window If you want to select more SQL Server systems, press the [...] button next to the server to enter the selection server dialog, as shown in Figure 26-41. Here you can choose the server you want to set as a subscriber. Figure 26-41 "Select Server" dialogue After the setting of step four is complete, press OK and press OK in the registered SQL Server property window, the issuer and the distributor property window will appear again, and the new increase in the previous step will occur in the list of subscribers. Subscriber, as shown in Figure 26-42. The newly added system can be used as a subscriber of the specified issuer. Figure 26-42 Display the "Publisher and Transport Attribute" window of the new subscriber Settings Subscribe Settings You can start setting subscriptions after setting the issuer, distributors, and distribution sets, as well as enabling the subscriber. You can set a subscription is from the subscriber or the issuer. If it is from the subscriber, you can set the extraction subscription; if it is from the issuer, you can set the send subscription. Set the settings and management of the subsequent subscription and managing the subscriber, therefore, select the subscriber system in Enterprise Manager before starting the subscription wizard. Typically, subscribers are not often connected to the network to use extraction subscription. For example, the business person can set an extraction subscription, and each time you use the notebook to update your subscription. Refer to the following steps to set the extraction subscription: 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 the welcome to use extraction subscription wizard, as shown in Figure 26-43. Figure 26-43 Welcome screen "using extraction subscription fake" Select Next to enter the search selection screen, as shown in Figure 26-44. Here you can choose to find the issuer's way, it can be a registration server (preset value), or in Activity Directory. Here you can decide how to search for the issuer, the decision here influence the result of the next picture. Figure 26-44 "Looking for the issuance set" screen Press Next to enter the selection issuing set screen, as shown in Figure 26-45. 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, as shown in Figure 26-45. If you need to register a server, press the registration server to enter the registration server properties window. When the subscriber is enabled in the previous section, we used this window. After selecting the issuance set, press the next step to enter the specified synchronous agent login surface, as shown in Figure 26-46. Here you can specify how the agent is wired to the issuer and login account. The preset value option is analog SQL Server Agent account, which is usually a ideal option. If the SQL Server Agent in the setting system uses special logins, you must be specified here. Figure 26-45 "Select the issuance set" screen Figure 26-46 "Specify Synchronous Agent Login" screen Select the next step to enter the selection target screen, as shown in Figure 26-47, where you can specify which database is to be specified. Figure 26-47 shows that we select the Pubs database for the subscription set. If you want to create a new database, enter the database properties window by adding. Figure 26-47 "Select the Database" screen If the database properties window is opened, you can return to the selected target library window after the new completion is complete. Then press the next step to enter the initial subscription screen, as shown in Figure 26-48. The choice is that the initialization structure describes the database structure and data to start the subscriber end. Figure 26-48 "Initialization Subscription" screen Press Next to enter the snapshot collection screen, as shown in Figure 26-49. Here you can choose where the snapshot set is transmitted. It can usually accept the position of the preset value. Figure 26-49 Snapshot Collection Transfer Screen Press Next to enter the set distribution agent schedule screen, as shown in Figure 26-50. This screen can choose a continuous update, updated according to the schedule, or updated only when needed. Remember that in this case we set a snapshot, so when the update is generated, all the issuance items will be updated to the subscriber. The options here can be determined according to the frequency updated, and the need to maintain the data synchronization. Pressing the Change button to enter the editing duplicate job scheduling dialog (mentioned before this chapter), here you can customize the schedule of the job. Press the next step to enter the necessary service screen, as shown in Figure 26-51. 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 prompt you to start the agent, if you want to manually start the agent, expand the management folder in Enterprise Manager, press the right button in the SQL Server agent, select Start in the quick function table Or stop the SQL Server agent program. Figure 26-50 "Setting the Agent Program Screen" screen Figure 26-51 "Start the necessary service" screen If the SQL Server Agent is not set to automatically start, you will see the set SQL Server Agent screen, press OK. If the SQL Server agent has been set to automatically start, you will not see this screen. Press Next to enter the completion of the subsequent subsequent screen, as shown in Figure 26-52. Select to complete the setting job of the subscription set. Figure 26-52 "Complete Extraction Subscription Elf" Now the issuance will be set by the subscriber, and the subscriber is docked. 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 at the subscriber. Please refer to the following steps to send subscription wizards: Use one of the following methods to enter the send subscription spirit. 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 26-53. Then select Send a new subscription. Figure 26-53 "Establishing the Dip Set" Dialogue in Management Server Or use the second method, select the elves in the tool, expand in the selection of the wizard dialogue, select the establishment sending subscription wizard, then press the send new subscription, any kind of issuance can enter the welcome to send subscription wizard screen As shown in Figures 26-54. Figure 26-54 "Welcome to send subscription wizard" screen Press Next to enter the selection subscriber screen, as shown in Figure 26-55. Here you can specify which subscriber end to send to the issuance set. In this example, the system we choose is B6S8A9. These subscribers must be enabled, such as eTlealexy Figure 26-55 "Select Subscriber" screen Select Next to enter the selected target database screen, as shown in Figure 26-56. Here you can specify the subscriber's database, you can select the existing database, or create a new database. Enter the name of the database you want, or select it from the list after booking or establish a button. To establish a new database, select the new item after pressing the browsing or establishment button, you can enter the database properties window settings. When you complete the new database, press OK to return to the selected database screen. Figure 26-56 "Select Subscriber" screen Press Next to enter the set distribution agent schedule screen, as shown in Figure 26-57. Here you can choose a continuous update subscription or select the scheduled schedule. Since snapshot, select continuous updates is not reasonable. If you want to change the schedule, press the change button to enter the previously explained editing repeated execution job schedule, change the schedule. Figure 26-57 "Setting the Agent Schedule" screen The schedule set in the Elf is used to update the schedule of the snapshot user, and the schedule of the update snapshot set must be coordinated. If the snapshot set itself is not updated, the information transmitted to the client is the old snapshot data. Press Next to enter the initial subscription screen, as shown in Figure 26-58. 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, the subscriber already has structural descriptions and information that this option will not be available. 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. Setting the snapshot agent schedule, making it a consistent consistency and sending subscriptions, and can be referred to this chapter in the Figure 26-58 "Initialization Subscription" screen Press Next to enter the necessary service screen, as shown in Figure 26-51, as early as possible, you can specify the SQL Server Agent program. Press Next to enter the completion of the subscription sprite screen, as shown in Figure 26-59. Check if the setting is correct, press the completion to start copying the snapshot to the subscriber 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 26-59 "Complete Send Subscription Elf" Managing Subwrites After learning the SQL Server 2000 environment, you can use the monitoring function in Enterprise Manger, manage the replication environment or find any problems that cannot be worked in the Enterprise Manger. Surveillance and Management of the Complication Program Operation in Enterprise Manager can find the agent program. Please refer to the following steps to access the proxy program: Expand the servo group, your server, and reply monitor data clips. If the publisher server server, the issuer, and the agent program folder will appear in the reploy monitor. The issuer's data clip contains all the issuers belonging to this server. The agent program includes a snapshot set agent program, distributing agency program, merge agency, and other agents used to clear history login data. Although the agent usually does not need to be started or stopped, you can use a replication monitor when needed. If the replication system is not executed according to the setting, it is probably because there is no start-up agent program. (This is why we recommend that the initial express collection is performed immediately before the setting process. Press a proxy program folder in Enterprise Manager, check the status of the agent, and reserved information about the agent program in the right pane, as shown in Figure 26-60. Here you can decide whether to perform an agent program, or you can see if the agent is in use. When a proxy is launched, the agent is executed until the task is completed, then stopping the job. At the terminal, the SQL Server agent will be divided into the agent. Figure 26-60 Snapshot Agent Program in Enterprise Manager Press the right button in the agent program to call the quick selection function, there are several options that can provide monitoring and management agents, as shown in Figure 26-61. Figure 26-61 Replication Agent Options Summary of these options is as follows: Error Details: List any wrong lists. Agent program history: List the agent program activity. Agent Properties: You can modify the scheduling of the copy agent program, the data inventory method, the proxy program, and the execution action after the job is completed, and if you can choose the email notification agent program to complete the completion of the event. Agent program settings: viewable and modify the agent program parameters, such as logging in over time, the batch size, and the setting time of the query time is over time. Start a proxy program and stop agency: You can start or stop the agent program. Remove the speed and set value: Modify the frequency of reintegration to perform monitoring data. Select the field: You can specify the result of the data that you want to see. Description: Provides explanation information about this window. Note The monitoring of the issuance agent program will be discussed in the next chapter. Deactivate Duwold Use Enterprise Manager to deactivate all or part of the replication, as long as the selected replication component is removed in the Enterprise Manger's reputation. This section will learn how to simply perform this job. Removing Send Subscription In the radiator system, send subscriptions using Enterprise Manager to send subscriptions. After starting the Send Subscription, the establishment and management issuance set dialog will appear. After selecting the subscription to delete, press the button to delete the date of the issued set, the prompt block, ask if you confirm the deletion of this subscription. The subscription is removed. Removing the extraction subscription In the subscriber system, you can remove the extraction subscription using the ENTERPRISE Manager's Send Subscribe. After starting the extraction subscription, the establishment and management issuance set dialog will appear. After selecting the subscription to delete, press the button to delete the issued set button, the prompt block, ask if you confirm the deletion of this subscription. The subscription is removed. Removing and issue to remove distribution and distribution, you must open the disabled distribution and distribution wizard. In deactivating the first picture of the wizard, you must specify whether all emission and release, or only remove the issuance. If the first option is selected, all the issues, subscriptions, and distributors on the server will be removed. If the second option (preset option) is selected, only the issuer will be removed. After the option is determined, a confirmation screen will appear, and the selected replication component will be removed after pressing the button. Snapshot Sub-Autodity This section will learn how to set up and install the fast-scale replication system to optimization performance. The setting and command of the snapshot replication system, which is simpler than the trading and merged replication system. In trading and merged complications, the snapshot set is transferred to the subscriber end at the beginning, and then the small amount of data is constantly transmitted into these systems. However, in snapshots, all databases will be reinited, so there is a simple operation. In this section, let's discuss the properties of the snapshot, and then discuss the focus of its setting, monitoring, and school adjustment. Snapshot Docked Due to snapshot replica first copies existing libraries, then to subscribers, the execution efficiency of snapshot replications depends on whether there is a specific system component to transmit large amounts of data. The following is a few factors affecting the performance of snapshot, if it is correct specified snapshot size and system settings, it can reduce the impact of these factors. Publisters' I / O effect: whether the entire database or some database is replicated from the issuer, the performance of the sub-issuer I / O subsystem is one of the factors affecting the snapshot. . The establishment of the snapshot set and I / O is related, usually and the CPU has nothing to do. I / O effect of radiator: The transmitted person receives and exudes a lot of information at the same time, so the I / O subsystem of the distributor is not fast enough to affect the efficiency of the snapshot set. Subscriber's I / O performance: The distributor will deliver all or part of the database to the subscriber at the same time. If the execution efficiency of the subscriber I / O subsystem is not good enough, it will affect the execution of the replication. Network bandwidth between issuers, distributors, and subscribers: Since the amount of data between the three is very large, it is easy to produce bottlenecks on the Internet. Confirm that the network band is sufficient to perform execution of load. Setting Snapshot Document This section provides some points about snapshots. Since the snapshot rewriting uses the issued fund-raising material for the information status classification of the copy program, it is not a replication material itself (in a folder), so it usually does not need to call SQL Server in the distributed side. These points can help you use the snapshot replication system to optimize, set the points: In the issuer, distributor, and subscriber, set enough I / O capacity to set the issuer, let the snapshot set in the issuer system The distributor and issuers set up to add BCP execution in the same system to now take care of these settings. Set enough I / O capacity As mentioned earlier, the snapshot is replicated at the same time to replicate a large amount of information, so the slow-speed disk subsystem reduces the speed of the entire program. Therefore, the performance of the specific I / O subsystem can be increased, and the replication program can be improved. In a system with a copy function (such as a SQL Server system), the transaction record file should be placed in its own RAID 1 disk area to protect the information. The data clip is placed in one or several RAID 10 to the RAID 5 disk area. Which RAID hierarchy is used depends on the issuer, distributed, or subscriber. Setting the issuer's I / O subsystem regardless of the RAID 5 or RAID 10 disk area regardless of the distributor's data clip, it depends on the proportion of the data in the discharge data of the disk area. As mentioned in Chapter 5, if the data is written more than 10%, RAID 10 is applied than RAID 5. Regardless of which RAID hierarchy is selected, the I / O subsystem size should be appropriately assigned according to the ways mentioned in Chapter 5 and Chapter 6. Setting the I / O subsystem of the distributor is required to exist in the SQL Server Database, so you must determine that the location of the quick-illumination set has a large number of write information. Therefore, the location of the snapshot set is preferably placed in the RAID 10 disk area. It will be seen later in this section, in some cases, the snapshot set is more efficient. Setting the subscriber's I / O subsystem will put the subscriber data folder in the RAID 10 disk area to enhance the performance of the snapshot, because the subscriber is a large amount of data encountered when the snapshot is written. Note When setting the I / O subsystem of a snapshot, you should consider the size of the snapshot set. If the amount of data replicated in the snapshot is small, it is convenient to put it in any RAID level. Select the snapshot set location Since the snapshot replication will be copied first to the sender, then to the subscriber, you can set this extra step by removal. The distributor is still used, but now sets the snapshot to the subscriber. This allows you to avoid excess network replication operations. Please refer to the following steps to set the distributed person, let the quick shot in the issuer: On the distributed vendor, the set distribution and distribution wizard, and select the issuer tab, as shown in Figure 26-62. Figure 26-62 "Publisher" tab Press the [...] button to enter the publisher property window, as shown in Figure 26-63. Figure 26-63 "Publisher Properties" Window In this window, you can set the location where the snapshot set is in the system. The I / O capacity of this location is sufficient to deal with the load of the system before setting. Note When the snapshot set is set in the radiator, it is also equal to setting all the issued positions, so if there is not only one issuer system, it is not possible. Setting the distributors and issuers in the same system If the replication type used is recorded, you can simply set the issuer and distributor to the same system. Since such settings can copy the snapshot set to the radiator end through the network, the network traffic can be reduced. However, if the issuer is very important, it is best to retain the snapshot set in the distributed person, allowing the distributed problem. Increasing the BCP execution is to increase the replication efficiency through the BCP used by the Fast Rejoice program, please refer to the following steps: Expand the DC monitor folder in Enterprise Manager, expand the agent program folder, then select the snapshot set agent program folder. In the right pane, click the selected release set to press the right button to select the agent program setting file in the quick function table, and enter the dialog box, as shown in Figures 26-64. Figure 26-64 "Snapshot Agent Program Settings" dialogue Press New Settings button to enter the Detail Dialog of the Documentary Settings to modify or add setting files, which are shown in Figure 26-65. Change the parameters of Maxbcpthreads here. Figure 26-65 Details of the Details of the Agent Program Settings "Dialogue This setting file can be stored when the setting is naming after the setting is named. Then select this setting file in the dialog block of the snapshot set agent program. Monitoring ExpressTes You can use the Microsoft Windows 2000 Efficvisory Monitor to monitor fast illumination. When using SQL Server to reply, several items are added to the Efficvation Monitor. In addition, standard objects with some performance monitors are also useful for monitoring snapshots. These objects include: 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 Snapshot: Provides information about snapshots. PhysicalDisk: Provides information about I / O activities, there are many effective issues related to I / O, so this information is very useful for snapshot. Processor: Allows the monitoring system processor. System: Provides information about the entire system. These counters allow you to know how to execute the copy programs, if the system settings and calibrations are appropriate, the execution process of the snapshot set will be very fast. To optimize the execution of the replication, pay attention to the following places that may generate problems: I / O performance bottleneck: Is I / O too high? Note that the execution of I / O per second and the number of seconds required for each I / O. Network performance bottleneck: This is not an easy to discover bottleneck, but you can try the time comparison of network transportation (NETWORK THROUGHPUT) and snapshot. The school-regulatory restroom system snapshot system is usually used as a simple setting. The most obvious problem that affects reproach is nothing more than I / O and Internet performance. You must see if your network performance is enough to load. Now look at the following example. Suppose you have a 5GB database. If you use a 10BASET network, the maximum frequency wide of the network is 10Mbps per second, almost 1 megabyte (Mbps) per second. Therefore, a 5-GB library will take 5120 seconds to transfer, or spend 1.4 hours on this network. The calculation method is as follows: (5 GB * 1024 (MB / GB)) / 1 (Mbps) = 5120 seconds, conversion of 1.4 hours. Compared, a 100BASET network only needs to be 8.3 minutes to complete the same copy program. A Gigabit Ethernet requires 51 seconds to complete the same action, and the results of the calculation are in Table 26-1: Table 26-1 Network comparison table Online speed execution a 5-GB database fast illumination time 10Baset5120 seconds 100Baset516 second Gigabit Etotacle 51 seconds
