Mssql Server 2005 Replication Step By Step 3v5350
This document was ed by and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this report form. Report 3i3n4
Overview 26281t
& View Mssql Server 2005 Replication Step By Step as PDF for free.
More details 6y5l6z
- Words: 3,671
- Pages: 66
MSSQL Server 2005 Replication Step by Step 收藏 For distributed databases, SQL Server 2005 provides comprehensive schema change (DDL) replication, next-generation monitoring capabilities, built in replication from Oracle to SQL Server, merge replication over https, and significant merge replication scalability and performance improvements. Additionally, the new peer-to-peer transactional replication feature improves for data scale out using replication. Replication is designed to increase data availability by distributing the data across multiple database servers. Availability is increased by allowing applications to scale out the SQL Server read workload across databases. SQL Server 2005 offers enhanced replication using a new peer-to-peer model that provides a new topology in which databases can be synchronized transactionally with any identical peer database.
Note Distributor&Publisher:WIN2003R2[192.168.5.100] Subscriber:ZHY[192.168.5.200]
Step I. Configure Distribution First, you must connect to the SQL Server Distributor: 1. Open SQL Server 2005 Management Studio. 2. If you are not already connected to the instance of SQL Server that will serve as the Distributor, click Object Explorer and select the instance.
Figure 1. Connecting to the Distributor After you have connected to the instance, start the New Publication Wizard. 1. Expand the server node. 2. Expand the Replication folder. 3. Right-click the Local Publications folder and then click Configure Distribution
Figure 2. Starting the Configure Distribution Wizard
4.The Configure Distribution Wizard introduction page is displayed. Click Next
Figure 3. the Configure Distribution Wizard page 5. that the instance that you are connected to is selected as the Distributor and click Next.
Figure 4. Specifying the Distributor 6.Specify Snapshot Folder This snapshot folder does not pull subscriptions created at the Subscriber. It is not a network path or it is a drive letter mapped to a network path. To both push and pull subscriptions, use a network path to refer to this folder.
Figure 5.Specify the Snapshot Folder 7.Specify Distribution Database .Locate the distribution database and log files.Click Next
Figure 6. specifying distribution database 8.Specify Publisher.Enable servers to use this distributor when they become publisher.click Next
Figure 7. Specifying publisher and distribution database 9. On the Wizard Actions page, you specify the distribution will be created, and whether the distribution configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the distribution configuration for future reference. click Next.
Figure 8. Scripting and creating the distribution
10. specify the distributor of script ,Click Next.
Figure 9. Specify the distributor script 11. On the Complete the Wizard page, that all options are correct.Click Finish
Figure 10. ing wizard options 12. The Distribution Wizard now saves the configuration information that you have entered and creates the distribution. Click Close after the steps are complete
Figure 11. Creating the distribution 13. Specifying the connection properties Right-click the Replication folder and then click Distribution Properties
Figure 12. Specifying the connection properties
Figure 13. Saving the Distributor configuration
Step II. Create Publication 1.Specify the Publisher Properties and select Transactional Databases at this artical
Figure 15 staring the publisher properties
Figure 16.specifing the publication databases. For more information about Transactional and merge,pls see "Troubleshooting Oracle Publishers" in SQL Server Books Online 2. After you have connected to the instance, start the New Publication Wizard. A.Expand the server node. B.Expand the Replication folder. C.Right-click the Local Publications folder and then click New Publication The rest of this section covers the steps in the wizard in more detail
Figure 17. Starting the New Publication Wizard 3. The New Publication Wizard introduction page is displayed.then click Next
Figure 18. New Publication Wizard introduction page 4. Choose the database U wanna to publish. Select the publication type and click Next
Figure 19.specifing the publication database 5. On the Publication Type page, you select the type of replication to use. Select Snapshot publication if you want all the published data to be copied every time replication executes. Select Transactional publication if you want to start by copying all the published data and then continuously stream subsequent data changes to SQL Server in near real time.
Note
For more information, see the topic "Transactional Replication Publishers" in SQL Server Books
Online. Select the publication type and click Next. The rest of this article assumes that you selected Transactional publication.
Figure 20. Specifying the publication type 6.On the Articles page, select the tables that you want to publish from the Objects to publish list. If there are columns of data that you do not want to replicate, you can remove the columns from the published table by clearing the check box next to each column. Note A table can appear among the Objects to publish only if SELECT permissions for the table have been granted directly to the replication istrative .
Figure 21. Selecting the tables to replicate Note If a column contains an MSSQL data type that might result in data loss when it is converted to a SQL Server data type, a yellow warning sign is displayed next to the column. In the following illustration, the HIREDATE column contains date data that might result in data loss. You can optionally select an alternative to the default SQL Server data type. First, access the properties for the published table. A. Select the table in the Objects to publish list and then click Article Properties. B. Click Set the Properties of Highlighted Table Article. In the next step, you can select an alternative data type.
Figure 22. Accessing properties of published tables
Figure 23. Changing article types for Subscriber article 7.You can optionally specify that you require only a subset of the data to be published. To specify a filter for a table, you specify a WHERE clause using SQL-compliant syntax. A. On the Filter Table Rows page, click Add. B. In the Add Filter dialog box, select a table to filter from the list. C. In the Filter statement text area, type a WHERE clause. D. Click OK. E. Click Next.
Figure 24. Filtering data 8.On the Snapshot Agent page, select whether you want to start creating snapshot data files immediately after the wizard finishes, or at a later time. A. Select the option to create a snapshot immediately. B. Click Next.
Figure 25. Scheduling the snapshot 9.The Snapshot Agent and Log Reader Agent pull data from the Publisher. On the Agent Security page, you specify the Windows under which each agent should run. Each must be a member of the sys fixed server role on the SQL Server Distributor. Additionally, the Windows under which the Snapshot Agent runs must have write permissions on the snapshot folder. By default, the snapshot folder is located at
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\.
A.Click Security Settings to specify settings for each agent. If you want to specify the same settings for the Log Reader Agent and Snapshot Agent, specify the Snaphot Agent settings first and then select Use the security settings from the Snapshot Agent. B. Click Next. Figure 26. Setting security options for replication agents 10.On the Wizard Actions page, you specify when the publication will be created, and whether the publication configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. A. Select both check boxes. B. Click Next.
Figure 27. Scripting and creating the publication 11. Create Publication Script File.click Next
Figure 28. CreatePublication sql script 12.On the Complete the Wizard page, you can that all options are correct, and provide a name for the publication. A. Type a name for the publication. B. options, and click Back to change your selections if necessary. C. Click Finish.
Figure 29. Completing the New Publication Wizard 13.The New Publication Wizard now saves the configuration information that you have entered and creates the publication. Click Close after the steps are finished.
Figure 30. Creating the publication The publication that you created appears under the Replication folder in SQL Server Management Studio.
Figure 31. Viewing the new publication Step III.ing the Status of Replication Agents
1.If you configured the Snapshot Agent to start immediately as recommended, you can now the progress of the Snapshot Agent by using Replication Monitor. This section of the article describes how to configure Replication Monitor and how to view the status of the Snapshot Agent. 2.In SQL Server Management Studio, right-click the Local Publications folder and then click Launch Replication Monitor.
Figure 32. Starting Replication Monitor
3.The Publisher is now listed under the My Publishers node of Replication Monitor. The right pane of Replication Monitor is populated, but does not show any subscriptions to the publication at this time. You will create a subscription later in this article. A. Expand the Publisher node ( in the diagram), and then select the newly created publication. B. Click the Warnings and Agents tab.
Figure 33. Viewing the new publication in Replication Monitor
4. the status of the Log Reader Agent and the Snapshot Agent. If the status of either agent is incorrect, that SQL Server Agent is running and that the Windows and you specified for each agent are correct before you continue. A. In the lower pane, titled Agents and jobs related to this publication, that the status of the Log Reader Agent is Running. B. In the same pane, that status of the Snapshot Agent status is Running or Completed. C. Double-click the Snapshot Agent row to open a dialog box that shows the history of the snapshot process.
Figure 34. Viewing status of the Snapshot Agent and Log Reader Agent 5.In the Snapshot Agent dialog box, view the history of the snapshot process. A. In the pane titled Sessions of the Snapshot Agent, that the status is Completed. B. Close the Snapshot Agent dialog box C. Close Replication Monitor.
Figure 35. Viewing details of Snapshot Agent history
Step IV.Configuring Permissions for the Distribution Agent Now that the publication has been created and the Snapshot Agent has been configured to pull data from the MS SQL database into a set of snapshot files, you will configure permissions for the Distribution Agent, which delivers changes to the SQL Server Subscriber. Make sure that there is a Windows on the Distributor computer under which the Distribution Agent can run.
1.The snapshot files are stored in a snapshot folder. By default the snapshot folder is located at
\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\repldata\. We recommend that you configure a Windows share for this folder. The Windows that runs the Distribution Agent must have read permissions on the share. For more information about how to configure shares and asg permissions to shares, see the Windows documentation. The following illustration shows the default folder used by SQL Server 2005 replication for storing snapshots.
a. In My Computer, right-click the snapshot folder, and click Sharing and Security. b. Select Share this folder. c. Type a name and description for the share.
Figure 36. Configuring snapshot folder properties
Configure the snapshot share so that the Windows under which the Distribution Agent runs has read permissions to the folder: a. Click Permissions. b. In the Permissions for repldata dialog box, click Add and locate the Windows under which the Distribution Agent runs. c. Select the Read check box.
Figure 37. Configuring permissions to the snapshot folder 2.The next step is to add the Windows to the Publication Access List (PAL). The PAL contains all Windows s and groups that have permission to synchronize with the publication. a. In SQL Server Management Studio, in Object Explorer, expand Replication. b. Expand Local Publications, and right-click the publication.
c. Click Properties.
Figure 38. Accessing the Publication Properties dialog box
In the Publication Properties dialog box, make sure that the Windows is listed in the PAL. a. Click the Publication Access List page. b. If the Windows is not in the Publication access list pane, click Add. c. Locate the and click OK to add the to the PAL. d. Click OK to close the Publication Properties dialog box. Note The Windows must be a valid SQL Server that is already associated with a database in the publication database.
Figure 39. Adding a to the PAL Step V.Creating a Subscription This section of the article describes how to create a subscription to the publication you created with the New Subscription Wizard. 1. In SQL Server Management Studio, right-click the publication you created. 2. Click New Subscriptions.
Figure 40. Starting the New Subscription Wizard 3.The New Subscription Wizard introduction page is displayed.Click Next
Figure 41. New Subscription Wizard introduction page 4.On the Publication page, the publication you created is displayed and selected by default. Click Next
Figure 42. Selecting the publication 5.On the Distribution Agent Location page, select the location at which the Distribution Agents will run. If there are less than fifty Subscribers, we recommend the default of running the Distribution Agent at the Distributor. a. Select Run all agents at the Distributor (push subscriptions) b.click Next
Figure 43 Specifying the Distribution Agent location 6.On the Subscribers page, select the Subscriber instance that will receive the replicated data from the Oracle Publisher. You must also specify whether the Subscriber is another instance of SQL Server or a different type of database provider. a. Click Add Subscriber. b. Select Add SQL Server Subscriber. c.Click Next
Figure 44. Adding a Subscriber 7.Specify the connection information for the SQL Server Subscriber. a. In the Connect to Server dialog box, select the instance of SQL Server that will be the Subscriber. b. Specify the authentication method that is used to connect to the Subscriber. c. Click Connect to save the connection information.
Note In the configuration used for this paper, the Distributor and Subscriber are on the same instance of SQL Server. Therefore the connection settings in the following illustration are the same as those shown earlier in the paper.
Figure 45. Specifying connection properties for the Subscriber 8.Next, you select the database on the Subscriber that will receive the replicated data. If the subscription database does not exist, you can create it by selecting New database. a. In the Subscription Database list, select a database, or click New database. b. (Optional) To add other Subscribers, click Add Subscriber and repeat the process. For each Subscriber, you must connect to a Subscriber and specify a subscription database. c. Click Next.
Figure 46. Specifying a subscription database 9.The Distribution Agent Security page of the wizard lets you configure Agent security for each Subscriber that you have added, to ensure that the Distribution Agents have the correct permissions. Click the browse button (...) for each Subscriber to open the Distribution Agent Security dialog box
Figure 47. Reviewing connection properties 10.In the Distribution Agent Security dialog box, select the Windows that you configured before you started the New Subscription Wizard. The Windows that you select must meet the following conditions: a. The is included in the PAL. b. The is a member of the db_owner fixed database role in the subscription database. c. The has read permissions on the snapshot share. Specify the Windows under which the Distribution Agent will run. a. Type the SQL info. b. Click OK to save changes and close the dialog box c. On the Distribution Agent Security page, click Next.
Figure 48. Specifying Distribution Agent security
11.By default, the Distribution Agent runs continuously and delivers changes from the Publisher to the Subscribers as changes become available. a. that Run Continuously is selected in the Agent Schedule column. b. Click Next.
Figure 49. Scheduling synchronization 12.Use the Initialize Subscriptions page of the wizard to specify how the publication is initialized. When the Distribution Agent first runs for a subscription, by default it delivers the complete snapshot of initial data to the Subscriber. This initialization occurs immediately after the New Subscription Wizard finishes.
a. If the Subscriber already contains all the initial tables and data, clear the Initialize option on the Initialize Subscriptions page. b. Select Immediately in the Initialize When list to specify when initialization will occur. c. Click Next.
Figure 50. Setting initialization options
13.On the Wizard Actions page, you specify when the subscription will be created, and whether the subscription configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. a. Select both check boxes. b. Click Next.
Figure 51, 52. Generating the publication and script file
14.On the Complete the Wizard page, that all options are correct. Click Finish
Figure 53. ing wizard options
15.The New Subscription Wizard now saves the configuration information that you have entered and creates the subscription. Click Close after the steps are complete
Figure 54. Creating the subscription
16.After the wizard has completed, you can view the subscriptions you created in SQL Server Management Studio.
a. In Object Explorer, expand Replication.
b. Expand Publications.
c. Right-click the publication you created and click Refresh.
d. Expand the publication.
Figure 55. Viewing the new subscription
Step VI. Monitoring the Publication and Subscription
This section of the article describes how to use Replication Monitor to monitor the publication and subscription you created. 1. In SQL Server Management Studio, expand Replication.
2. Expand Local Publications. 3. Right-click the Local Publications folder and then click Launch Replication Monitor.
Figure 56. Starting Replication Monitor Subscription status is displayed on the All Subscriptions tab in the right pane. The time it took for data to replicate from the Oracle Publisher to the Subscriber is listed in the Latency column, and a relative rating of this performance is listed in the Performance column. Because initial snapshots take more time to generate and deliver than incremental changes, the initial latency is typically much greater than the latency for incremental changes.
Figure 57. Viewing subscription status in Replication Monitor
4.Latency between the Publisher and Subscriber is calculated by measuring two intervals in replication processing: a. The time that is required from when a change is made at the Publisher until the Log Reader Agent delivers the change to the distribution database. b. The time that is required for the Distribution Agent to deliver the change from the distribution database to the subscription database. 5.To view the details of the replication processing involved in these steps, you can double-click the subscription row in the right pane. The Distributor To Subscriber History tab shows the history for the selected Distribution Agent.
Figure 58. Viewing Distribution Agent history 6.The Publisher To Distributor History tab displays the history for the Log Reader Agent.
Figure 59. Viewing Log Reader Agent history
7.The Undistributed Commands tab displays information about the number of commands in the distribution database that have not been delivered to the selected Subscriber, and the estimated time to deliver those commands. Close the dialog box and return to the main Replication Monitor interface.
Figure 60. Viewing the number of commands in the distribution database
8.Tracer tokens are used to diagnose the current performance of transactional replication. A token, which is a small amount of data, is written to the transaction log of the publication database, marked as if it were a typical replicated transaction, and then sent through the system. The elapsed time is then measured. You can create and monitor tracer tokens by using Replication Monitor. a. In Replication Monitor, click the Tracer Tokens tab. b. Click Insert Tracer.
Figure 61. Adding a tracer token for a publication
9.After a short time, Replication Monitor displays three numbers as indicators of transactional replication performance: the time to replicate the tracer token from the publication database to the distribution database; the time to replicate the tracer token from the distribution database to the subscription database; and the total elapsed time. If one of the agents is stopped and the tracer cannot be replicated, Replication Monitor displays Pending until the agent is restarted. a. that a value is displayed in the Total Latency column. b. Close Replication Monitor.
Figure 62. Viewing elapsed time for the tracer token
Step VII. ing That Changes at the Publisher are Replicated to the Subscriber
You have now successfully configured replication from SQL to SQL Server, and you have used Replication Monitor to the status and performance of the system. If you created a transactional publication, you can make changes to data at the rep_2 db Publisher and then observe the successful replication of those changes to the SQL Server Subscriber. To test replication, make changes to one or more published tables by using Transact-SQL command line statements. The example in the illustration updates one row in the info table
Figure 63,64. Making changes in the publish database
To that these changes are replicated, you can view the new data in the subscription database. a. Open SQL Server Management Studio. b. Click New Query and connect to the Subscriber.
Figure 65. ing changes in the subscription database
发表于 @ 2006 年 06 月 09 日 16:38:00 | 评论( 1
) | 举报| 收藏
旧一 篇 :how to reinstall MS DTC on windows server 2003 R2 | 新一 篇 : 本周技 术关注 [面向 webapps 运 维 ]:Preventing DDoS Attacks: The Community's Center for Security 查看最新精华文章 请访问博客首页相关文章 The code that does not work JXTA JXSE In-Memory Database Systems Beginning VB 2005 Databases: From Novice to Professional Beginning Hibernate: From Novice to Professional (Paperback) Overview of ACE 使用 sqlCacheDependency 使用 sqlCacheDependency 匿名用户 发表于 Saturday, February 20, 2010 4:41:26 AM 举 报回复
Thanks Man •
发表评论 •
表 情：
• •
评论内容：
• • •
用 户 名： 登 录 注册 匿名用户
•
验 证 码：
• • •
重新获得验证码
Copyright © longrujun Powered by CSDN Blog
近期 热点文章推荐
近期热点文章推荐 • • • • • • •
1、 Meego:阿汤学 QT--PyQT mo
•
8、 【正式版】Flash Builder4
2、 以太网巨人 3Com 退出历史舞台 3、 移动的其实是“应用” 4、 百度的青春期 5、 亚马逊中国慢跑 6、 马云：未来的创新要靠 80 后、90 后 7、 Facebook 正全面启动虚拟货币计划
更 多新闻、精彩评论敬请关注 CSDN 新闻频道
Note Distributor&Publisher:WIN2003R2[192.168.5.100] Subscriber:ZHY[192.168.5.200]
Step I. Configure Distribution First, you must connect to the SQL Server Distributor: 1. Open SQL Server 2005 Management Studio. 2. If you are not already connected to the instance of SQL Server that will serve as the Distributor, click Object Explorer and select the instance.
Figure 1. Connecting to the Distributor After you have connected to the instance, start the New Publication Wizard. 1. Expand the server node. 2. Expand the Replication folder. 3. Right-click the Local Publications folder and then click Configure Distribution
Figure 2. Starting the Configure Distribution Wizard
4.The Configure Distribution Wizard introduction page is displayed. Click Next
Figure 3. the Configure Distribution Wizard page 5. that the instance that you are connected to is selected as the Distributor and click Next.
Figure 4. Specifying the Distributor 6.Specify Snapshot Folder This snapshot folder does not pull subscriptions created at the Subscriber. It is not a network path or it is a drive letter mapped to a network path. To both push and pull subscriptions, use a network path to refer to this folder.
Figure 5.Specify the Snapshot Folder 7.Specify Distribution Database .Locate the distribution database and log files.Click Next
Figure 6. specifying distribution database 8.Specify Publisher.Enable servers to use this distributor when they become publisher.click Next
Figure 7. Specifying publisher and distribution database 9. On the Wizard Actions page, you specify the distribution will be created, and whether the distribution configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the distribution configuration for future reference. click Next.
Figure 8. Scripting and creating the distribution
10. specify the distributor of script ,Click Next.
Figure 9. Specify the distributor script 11. On the Complete the Wizard page, that all options are correct.Click Finish
Figure 10. ing wizard options 12. The Distribution Wizard now saves the configuration information that you have entered and creates the distribution. Click Close after the steps are complete
Figure 11. Creating the distribution 13. Specifying the connection properties Right-click the Replication folder and then click Distribution Properties
Figure 12. Specifying the connection properties
Figure 13. Saving the Distributor configuration
Step II. Create Publication 1.Specify the Publisher Properties and select Transactional Databases at this artical
Figure 15 staring the publisher properties
Figure 16.specifing the publication databases. For more information about Transactional and merge,pls see "Troubleshooting Oracle Publishers" in SQL Server Books Online 2. After you have connected to the instance, start the New Publication Wizard. A.Expand the server node. B.Expand the Replication folder. C.Right-click the Local Publications folder and then click New Publication The rest of this section covers the steps in the wizard in more detail
Figure 17. Starting the New Publication Wizard 3. The New Publication Wizard introduction page is displayed.then click Next
Figure 18. New Publication Wizard introduction page 4. Choose the database U wanna to publish. Select the publication type and click Next
Figure 19.specifing the publication database 5. On the Publication Type page, you select the type of replication to use. Select Snapshot publication if you want all the published data to be copied every time replication executes. Select Transactional publication if you want to start by copying all the published data and then continuously stream subsequent data changes to SQL Server in near real time.
Note
For more information, see the topic "Transactional Replication Publishers" in SQL Server Books
Online. Select the publication type and click Next. The rest of this article assumes that you selected Transactional publication.
Figure 20. Specifying the publication type 6.On the Articles page, select the tables that you want to publish from the Objects to publish list. If there are columns of data that you do not want to replicate, you can remove the columns from the published table by clearing the check box next to each column. Note A table can appear among the Objects to publish only if SELECT permissions for the table have been granted directly to the replication istrative .
Figure 21. Selecting the tables to replicate Note If a column contains an MSSQL data type that might result in data loss when it is converted to a SQL Server data type, a yellow warning sign is displayed next to the column. In the following illustration, the HIREDATE column contains date data that might result in data loss. You can optionally select an alternative to the default SQL Server data type. First, access the properties for the published table. A. Select the table in the Objects to publish list and then click Article Properties. B. Click Set the Properties of Highlighted Table Article. In the next step, you can select an alternative data type.
Figure 22. Accessing properties of published tables
Figure 23. Changing article types for Subscriber article 7.You can optionally specify that you require only a subset of the data to be published. To specify a filter for a table, you specify a WHERE clause using SQL-compliant syntax. A. On the Filter Table Rows page, click Add. B. In the Add Filter dialog box, select a table to filter from the list. C. In the Filter statement text area, type a WHERE clause. D. Click OK. E. Click Next.
Figure 24. Filtering data 8.On the Snapshot Agent page, select whether you want to start creating snapshot data files immediately after the wizard finishes, or at a later time. A. Select the option to create a snapshot immediately. B. Click Next.
Figure 25. Scheduling the snapshot 9.The Snapshot Agent and Log Reader Agent pull data from the Publisher. On the Agent Security page, you specify the Windows under which each agent should run. Each must be a member of the sys fixed server role on the SQL Server Distributor. Additionally, the Windows under which the Snapshot Agent runs must have write permissions on the snapshot folder. By default, the snapshot folder is located at
A.Click Security Settings to specify settings for each agent. If you want to specify the same settings for the Log Reader Agent and Snapshot Agent, specify the Snaphot Agent settings first and then select Use the security settings from the Snapshot Agent. B. Click Next. Figure 26. Setting security options for replication agents 10.On the Wizard Actions page, you specify when the publication will be created, and whether the publication configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. A. Select both check boxes. B. Click Next.
Figure 27. Scripting and creating the publication 11. Create Publication Script File.click Next
Figure 28. CreatePublication sql script 12.On the Complete the Wizard page, you can that all options are correct, and provide a name for the publication. A. Type a name for the publication. B. options, and click Back to change your selections if necessary. C. Click Finish.
Figure 29. Completing the New Publication Wizard 13.The New Publication Wizard now saves the configuration information that you have entered and creates the publication. Click Close after the steps are finished.
Figure 30. Creating the publication The publication that you created appears under the Replication folder in SQL Server Management Studio.
Figure 31. Viewing the new publication Step III.ing the Status of Replication Agents
1.If you configured the Snapshot Agent to start immediately as recommended, you can now the progress of the Snapshot Agent by using Replication Monitor. This section of the article describes how to configure Replication Monitor and how to view the status of the Snapshot Agent. 2.In SQL Server Management Studio, right-click the Local Publications folder and then click Launch Replication Monitor.
Figure 32. Starting Replication Monitor
3.The Publisher is now listed under the My Publishers node of Replication Monitor. The right pane of Replication Monitor is populated, but does not show any subscriptions to the publication at this time. You will create a subscription later in this article. A. Expand the Publisher node ( in the diagram), and then select the newly created publication. B. Click the Warnings and Agents tab.
Figure 33. Viewing the new publication in Replication Monitor
4. the status of the Log Reader Agent and the Snapshot Agent. If the status of either agent is incorrect, that SQL Server Agent is running and that the Windows and you specified for each agent are correct before you continue. A. In the lower pane, titled Agents and jobs related to this publication, that the status of the Log Reader Agent is Running. B. In the same pane, that status of the Snapshot Agent status is Running or Completed. C. Double-click the Snapshot Agent row to open a dialog box that shows the history of the snapshot process.
Figure 34. Viewing status of the Snapshot Agent and Log Reader Agent 5.In the Snapshot Agent dialog box, view the history of the snapshot process. A. In the pane titled Sessions of the Snapshot Agent, that the status is Completed. B. Close the Snapshot Agent dialog box C. Close Replication Monitor.
Figure 35. Viewing details of Snapshot Agent history
Step IV.Configuring Permissions for the Distribution Agent Now that the publication has been created and the Snapshot Agent has been configured to pull data from the MS SQL database into a set of snapshot files, you will configure permissions for the Distribution Agent, which delivers changes to the SQL Server Subscriber. Make sure that there is a Windows on the Distributor computer under which the Distribution Agent can run.
1.The snapshot files are stored in a snapshot folder. By default the snapshot folder is located at
a. In My Computer, right-click the snapshot folder, and click Sharing and Security. b. Select Share this folder. c. Type a name and description for the share.
Figure 36. Configuring snapshot folder properties
Configure the snapshot share so that the Windows under which the Distribution Agent runs has read permissions to the folder: a. Click Permissions. b. In the Permissions for repldata dialog box, click Add and locate the Windows under which the Distribution Agent runs. c. Select the Read check box.
Figure 37. Configuring permissions to the snapshot folder 2.The next step is to add the Windows to the Publication Access List (PAL). The PAL contains all Windows s and groups that have permission to synchronize with the publication. a. In SQL Server Management Studio, in Object Explorer, expand Replication. b. Expand Local Publications, and right-click the publication.
c. Click Properties.
Figure 38. Accessing the Publication Properties dialog box
In the Publication Properties dialog box, make sure that the Windows is listed in the PAL. a. Click the Publication Access List page. b. If the Windows is not in the Publication access list pane, click Add. c. Locate the and click OK to add the to the PAL. d. Click OK to close the Publication Properties dialog box. Note The Windows must be a valid SQL Server that is already associated with a database in the publication database.
Figure 39. Adding a to the PAL Step V.Creating a Subscription This section of the article describes how to create a subscription to the publication you created with the New Subscription Wizard. 1. In SQL Server Management Studio, right-click the publication you created. 2. Click New Subscriptions.
Figure 40. Starting the New Subscription Wizard 3.The New Subscription Wizard introduction page is displayed.Click Next
Figure 41. New Subscription Wizard introduction page 4.On the Publication page, the publication you created is displayed and selected by default. Click Next
Figure 42. Selecting the publication 5.On the Distribution Agent Location page, select the location at which the Distribution Agents will run. If there are less than fifty Subscribers, we recommend the default of running the Distribution Agent at the Distributor. a. Select Run all agents at the Distributor (push subscriptions) b.click Next
Figure 43 Specifying the Distribution Agent location 6.On the Subscribers page, select the Subscriber instance that will receive the replicated data from the Oracle Publisher. You must also specify whether the Subscriber is another instance of SQL Server or a different type of database provider. a. Click Add Subscriber. b. Select Add SQL Server Subscriber. c.Click Next
Figure 44. Adding a Subscriber 7.Specify the connection information for the SQL Server Subscriber. a. In the Connect to Server dialog box, select the instance of SQL Server that will be the Subscriber. b. Specify the authentication method that is used to connect to the Subscriber. c. Click Connect to save the connection information.
Note In the configuration used for this paper, the Distributor and Subscriber are on the same instance of SQL Server. Therefore the connection settings in the following illustration are the same as those shown earlier in the paper.
Figure 45. Specifying connection properties for the Subscriber 8.Next, you select the database on the Subscriber that will receive the replicated data. If the subscription database does not exist, you can create it by selecting New database. a. In the Subscription Database list, select a database, or click New database. b. (Optional) To add other Subscribers, click Add Subscriber and repeat the process. For each Subscriber, you must connect to a Subscriber and specify a subscription database. c. Click Next.
Figure 46. Specifying a subscription database 9.The Distribution Agent Security page of the wizard lets you configure Agent security for each Subscriber that you have added, to ensure that the Distribution Agents have the correct permissions. Click the browse button (...) for each Subscriber to open the Distribution Agent Security dialog box
Figure 47. Reviewing connection properties 10.In the Distribution Agent Security dialog box, select the Windows that you configured before you started the New Subscription Wizard. The Windows that you select must meet the following conditions: a. The is included in the PAL. b. The is a member of the db_owner fixed database role in the subscription database. c. The has read permissions on the snapshot share. Specify the Windows under which the Distribution Agent will run. a. Type the SQL info. b. Click OK to save changes and close the dialog box c. On the Distribution Agent Security page, click Next.
Figure 48. Specifying Distribution Agent security
11.By default, the Distribution Agent runs continuously and delivers changes from the Publisher to the Subscribers as changes become available. a. that Run Continuously is selected in the Agent Schedule column. b. Click Next.
Figure 49. Scheduling synchronization 12.Use the Initialize Subscriptions page of the wizard to specify how the publication is initialized. When the Distribution Agent first runs for a subscription, by default it delivers the complete snapshot of initial data to the Subscriber. This initialization occurs immediately after the New Subscription Wizard finishes.
a. If the Subscriber already contains all the initial tables and data, clear the Initialize option on the Initialize Subscriptions page. b. Select Immediately in the Initialize When list to specify when initialization will occur. c. Click Next.
Figure 50. Setting initialization options
13.On the Wizard Actions page, you specify when the subscription will be created, and whether the subscription configuration is saved in a script. We recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference. a. Select both check boxes. b. Click Next.
Figure 51, 52. Generating the publication and script file
14.On the Complete the Wizard page, that all options are correct. Click Finish
Figure 53. ing wizard options
15.The New Subscription Wizard now saves the configuration information that you have entered and creates the subscription. Click Close after the steps are complete
Figure 54. Creating the subscription
16.After the wizard has completed, you can view the subscriptions you created in SQL Server Management Studio.
a. In Object Explorer, expand Replication.
b. Expand Publications.
c. Right-click the publication you created and click Refresh.
d. Expand the publication.
Figure 55. Viewing the new subscription
Step VI. Monitoring the Publication and Subscription
This section of the article describes how to use Replication Monitor to monitor the publication and subscription you created. 1. In SQL Server Management Studio, expand Replication.
2. Expand Local Publications. 3. Right-click the Local Publications folder and then click Launch Replication Monitor.
Figure 56. Starting Replication Monitor Subscription status is displayed on the All Subscriptions tab in the right pane. The time it took for data to replicate from the Oracle Publisher to the Subscriber is listed in the Latency column, and a relative rating of this performance is listed in the Performance column. Because initial snapshots take more time to generate and deliver than incremental changes, the initial latency is typically much greater than the latency for incremental changes.
Figure 57. Viewing subscription status in Replication Monitor
4.Latency between the Publisher and Subscriber is calculated by measuring two intervals in replication processing: a. The time that is required from when a change is made at the Publisher until the Log Reader Agent delivers the change to the distribution database. b. The time that is required for the Distribution Agent to deliver the change from the distribution database to the subscription database. 5.To view the details of the replication processing involved in these steps, you can double-click the subscription row in the right pane. The Distributor To Subscriber History tab shows the history for the selected Distribution Agent.
Figure 58. Viewing Distribution Agent history 6.The Publisher To Distributor History tab displays the history for the Log Reader Agent.
Figure 59. Viewing Log Reader Agent history
7.The Undistributed Commands tab displays information about the number of commands in the distribution database that have not been delivered to the selected Subscriber, and the estimated time to deliver those commands. Close the dialog box and return to the main Replication Monitor interface.
Figure 60. Viewing the number of commands in the distribution database
8.Tracer tokens are used to diagnose the current performance of transactional replication. A token, which is a small amount of data, is written to the transaction log of the publication database, marked as if it were a typical replicated transaction, and then sent through the system. The elapsed time is then measured. You can create and monitor tracer tokens by using Replication Monitor. a. In Replication Monitor, click the Tracer Tokens tab. b. Click Insert Tracer.
Figure 61. Adding a tracer token for a publication
9.After a short time, Replication Monitor displays three numbers as indicators of transactional replication performance: the time to replicate the tracer token from the publication database to the distribution database; the time to replicate the tracer token from the distribution database to the subscription database; and the total elapsed time. If one of the agents is stopped and the tracer cannot be replicated, Replication Monitor displays Pending until the agent is restarted. a. that a value is displayed in the Total Latency column. b. Close Replication Monitor.
Figure 62. Viewing elapsed time for the tracer token
Step VII. ing That Changes at the Publisher are Replicated to the Subscriber
You have now successfully configured replication from SQL to SQL Server, and you have used Replication Monitor to the status and performance of the system. If you created a transactional publication, you can make changes to data at the rep_2 db Publisher and then observe the successful replication of those changes to the SQL Server Subscriber. To test replication, make changes to one or more published tables by using Transact-SQL command line statements. The example in the illustration updates one row in the info table
Figure 63,64. Making changes in the publish database
To that these changes are replicated, you can view the new data in the subscription database. a. Open SQL Server Management Studio. b. Click New Query and connect to the Subscriber.
Figure 65. ing changes in the subscription database
发表于 @ 2006 年 06 月 09 日 16:38:00 | 评论( 1
) | 举报| 收藏
旧一 篇 :how to reinstall MS DTC on windows server 2003 R2 | 新一 篇 : 本周技 术关注 [面向 webapps 运 维 ]:Preventing DDoS Attacks: The Community's Center for Security 查看最新精华文章 请访问博客首页相关文章 The code that does not work JXTA JXSE In-Memory Database Systems Beginning VB 2005 Databases: From Novice to Professional Beginning Hibernate: From Novice to Professional (Paperback) Overview of ACE 使用 sqlCacheDependency 使用 sqlCacheDependency 匿名用户 发表于 Saturday, February 20, 2010 4:41:26 AM 举 报回复
Thanks Man •
发表评论 •
表 情：
• •
评论内容：
• • •
用 户 名： 登 录 注册 匿名用户
•
验 证 码：
• • •
重新获得验证码
Copyright © longrujun Powered by CSDN Blog
近期 热点文章推荐
近期热点文章推荐 • • • • • • •
1、 Meego:阿汤学 QT--PyQT mo
•
8、 【正式版】Flash Builder4
2、 以太网巨人 3Com 退出历史舞台 3、 移动的其实是“应用” 4、 百度的青春期 5、 亚马逊中国慢跑 6、 马云：未来的创新要靠 80 后、90 后 7、 Facebook 正全面启动虚拟货币计划
更 多新闻、精彩评论敬请关注 CSDN 新闻频道
Related Documents 3h463d
Mssql Server 2005 Replication Step By Step 3v5350
November 2019 35
Step By Step Guide To Q Replication i2i2d
April 2022 0
Korean Step By Step 6b632z
October 2021 0
Html Step By Step 3126v
January 2021 0
Bps Step By Step 3tz50
November 2019 100