Microsoft SQL Server 2005 Replication Step by Step

Nội dung text: Microsoft SQL Server 2005 Replication Step by Step

1. Page 1 of 85 Microsoft SQL Server 2005 Replication Step by Step Author Name Account/Business Group Author(s) Mahesh Kumar Chudamani Microsoft/TMTS Employee ID:-11003447 Email ID:-mahesh.chudamani@wipro.com Satish Kumar Vemulakonda Employee ID:-11003436 Email ID:-satish.vemulakonda@wipro.com Reviewer(s) Microsoft/TMTS Table of Contents Introduction 3 Eager Replication 3 Lazy Replication 4 Components in Replication 4 Distributor 5 Publisher 5 ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
2. Page 2 of 85 Subscriber 6 Publication 6 Article 6 Subscriptions 6 Agents 7 Snapshot Agent 8 Log Reader Agent 8 Distribution Agent 8 Merge Agent 8 Queue Reader Agent 9 Purpose of Replication 9 Replication in SQL Server 9 Snapshot Replication 10 Transactional Replication 12 Merge Replication 13 Physical Replication Models 15 Publisher/Distributor–Subscriber Model 15 Central Publisher–Multiple Subscribers Model 15 Central Subscriber–Multiple Publishers Model 17 Multiple Publishers–Multiple Subscribers Model 18 Configuring Replication 19 Step 1 Configuring Distribution 20 Step 2 Configuring Publication 33 Step 3 Verifying the Status of Replication Agents 52 Step 4 Configuring Permissions for the Distribution Agent 56 ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
3. Page 3 of 85 Step 5 Creating a Subscription 60 Best Practices in Replication 74 Best Practices in Snapshot Replication 74 Best Practices in Transactional Replication 77 Best Practices in Merge Replication 82 Conclusion 85 References 85 Introduction Replication is a ―set of technologies‖ that can move data and database objects from one database to another and across different platforms and geographic locales. Using replication you create copies of the Database and share the copy with different users so that they can make changes to their local copy of database and later synchronize the changes to the source database. The advantages of replicating databases are the physical separation of the databases and the normal latency of the data. For example, sales staff working in the field can enter their orders or changes on their portable devices and transfer the data automatically to the head office while maintaining database consistency at each site. The question arises is how to distribute data using replication, and the answer depends on when, where, and how data is propagated. There are two kinds of replication: Eager Replication. Lazy Replication. Eager Replication Eager replication is also known as synchronous replication. In this method, an application can update a local replica of a table, and within the same transaction it can also update other replicas of the same table. No concurrency anomalies occur, since synchronous replication gives serializable execution. Any anomaly in concurrency is detected by the locking method. If any of the nodes are disconnected, eager replication prevents the update from taking ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
4. Page 4 of 85 place. However, there is a compromise in performance as a result of all the updates being carried in a single transaction. Eager replication consists of the following steps: execute, transmit, notify, and either commit or rollback. An executed transaction is transmitted to different nodes, and in the event of failure in one node, the transaction is rolled back and all the other nodes are notified of the failure. The transaction is then aborted in all nodes. If replication is successful in all the nodes, a commit is broadcast and a copy of the committed transaction is then sent to all the nodes. You may wonder why you would want to use Eager replication. Suppose you want to have a real-time copy of the master database so that you have a ready backup in the event of a failure. The synchronous nature of data transfer in eager replication facilitates real-time data transfer and in this situation it would be useful. However, eager replication is not a good choice for a remote or mobile environment, since it reduces update performance. Also, in a mobile environment the nodes are not always connected. Lazy Replication Lazy replication is also known as asynchronous replication. In this case, if the transactions are committed, they are sent to the different sites for the updates to occur. However, if they are rolled back, the changes will not be transmitted to the different sites. Thus, the very nature of asynchronous replication allows the updates of committed transactions to be sent to disconnected sites, as in the case of handheld sets or mobile devices. With this type of replication, it is possible for two different sites to update the same data on the same destination site. This will lead to a conflict in the updating of the data. Such update conflicts need to be resolved in lazy replication and this is done by associating timestamps with each of the transaction objects. Each object carries the timestamp associated with the previous update of that data. So when a transaction is sent to the destination site, it first checks to see whether the timestamp associated with the local copy of the replicated data matches the incoming transaction‘s old timestamp for that data. Only if they match will the changes, including the transaction‘s new timestamp, be applied. If the timestamps do not match at the initial stage, the updated transaction is rejected. SQL Server has a conflict resolution viewer that deals with updates, inserts, and deletes. Components in Replication The below are different components in SQL Server Replication.  Distributor  Publisher  Subscriber  Publication ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
5. Page 5 of 85  Article  Subscriptions  Agents Distributor The Distributor server is the common link that enables all the components involved in replication to interact with each other. It contains the distribution database, and it is responsible for the smooth passage of data between the Publisher servers and the Subscriber servers. If the Distributor server is located on the same machine as the Publisher server, it is Known as the local Distributor server, but if it is on a separate machine from the Publisher server, it is called the remote Distributor server. In large-scale replication, it is better to house the Distributor server on a remote server. This will not only improve performance but will also reduce I/O processing and reduce the impact of replication on the Publisher server. The role of the Distributor server varies depending on the type of replication: In snapshot and transactional replication, the distribution database in the Distributor server stores the replicated transactions temporarily and also stores the metadata and the job history. The replication agents are also stored in the Distributor server, except in cases where the agents are configured remotely or pull subscriptions are used. (A pull subscription is one in which the Subscriber server asks for periodic updates of all changes made at the publishing server). In merge replication, unlike in snapshot and transactional replication, the distribution database in the Distributor server stores the metadata and the history of the synchronization. It also contains the Snapshot Agent and the Merge Agent for push subscriptions. (A push subscription is a subscription in which the Publisher server propagates the changes to the subscribing servers without any specific request from the subscribing server). The distribution database is a system database that is created when the Distributor server is configured. You should not drop the distribution database unless you want to disable it. It not only stores information about replication but also the metadata, job history, and transactions. Publisher While the Distributor server manages the data flow, the Publisher server ensures that data is available for replication to other servers. The Publisher is the server that contains the data to be replicated. It can also identify and maintain changes in data. Depending on the type of ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
6. Page 6 of 85 replication, changes in data are identified and periodically time-stamped. You can see the list of Publisher servers on the machine in the Replication Monitor. Subscriber The Subscriber server stores replicas and receives updates from the Publisher server. Periodic updates made on the Subscriber server can then be sent back to the Publisher server. It may also be necessary for the Subscriber server to act as a Publisher server and republish the data to other subscribing servers. Publication The Publisher server contains a collection of articles in the publication database. This database tells the Publisher server which data needs to be sent to other servers or to the subscribing servers. In other words, the publication database acts as the data source for replication. Any database that is used as a source of replication therefore needs to be enabled as a Publisher server. In SQL Server you can achieve this by using the Create Publication Wizard, the Configure Publishing and Distribution Wizard, or the sp_replicationdboption system stored procedure. The database that is published can contain one or more publications. A publication is a unit that contains one or more articles that are sent to the subscribing servers. Article An article is any grouping of data to be replicated; it is a component of a publication. It may contain a set of tables or a subset of tables. Articles can also contain a set of columns (vertical filtering), a set of rows (horizontal filtering), stored procedures, views, indexed views, or user defined functions (UDFs). Subscriptions Subscriber servers must define their subscriptions for a particular set of publications in order to receive the snapshot from the Publisher server. For all three types of replication, snapshot files are made of the schema and initial data files of the publication and are stored in the snapshot folder. Subsequent changes to the data or the schema are transferred from the Publisher server to the Subscriber server. This process is known as synchronization. The subscriptions map the different articles to the corresponding tables in the Subscriber Server. They also specify when the Subscriber servers should receive the publications from publishing servers. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
7. Page 7 of 85 There are two methods by which data changes made on the publication can be sent to Subscriptions in SQL Server: Anonymous subscriptions and Named subscriptions. In an anonymous subscription, no information about the subscribing server or the subscription is stored on the Publisher server. It is the responsibility of the subscribing servers to keep track of the history of the data and the subscriptions. These details are then passed on to the Distribution Agent at the time of the next synchronization. Named subscriptions are those in which the Subscriber servers are explicitly enabled in the Publisher server. There are two kinds of named subscriptions: push subscriptions and pull subscriptions. (In fact, anonymous subscription is a kind of pull subscription.) Which subscription type you use depends on where you want the administration of the subscription and the agent processing to take place. Push subscriptions are created at the Publisher server, The Publisher server retains control of the subscriptions and can propagate the changes either on demand, or continuously, or at scheduled intervals. However, synchronization in push subscriptions is typically transmitted continuously, whenever changes occur in the publication, without waiting for the Subscriber server to make a request. In this case, there is no need to administer individual subscribing servers—the Distribution or the Merge Agent that resides on the Distributor server implements the scheduling. The Subscriber server must be explicitly enabled in the Publisher server for this type of replication to function. For Pull subscriptions, the Subscriber servers must be enabled explicitly in the Publisher server, just as for push subscriptions. In pull subscriptions, however, the subscriptions are created at the Subscriber server. The Subscriber server requests changes in the publication from the Publisher server, and the data is synchronized either on demand or at a scheduled time. Agents Where do the agents fit in? What purpose do they serve? They are the workhorses in the group. The agents collate all the changes and perform the necessary jobs in distributing the data. These agents are the executables, which, by default, run as jobs under the SQL Server Agent folder in the SQL Server Management Studio (SSMS). Keep in mind, though, that the SQL Server Agent needs to be running in order for the jobs to do their work. The executables are located under Program Files\Microsoft SQL Server\90\COM, and they can be run from the Command prompt. There are five different types of agents:  Snapshot Agent  Log Reader Agent  Queue Reader Agent ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
8. Page 8 of 85  Distribution Agent  Merge Agent There are also other miscellaneous jobs that perform maintenance and servicing for replication. The Distribution clean up job is one such example. Snapshot Agent The name of the Snapshot Agent executable is snapshot.exe. This agent usually resides in the Distributor server. The Snapshot Agent is used in all replications, particularly at the time of initial synchronization. It makes a copy of the schema and the data of the tables that are to be published, stores them in the snapshot file, and records information about synchronization in the distribution database. Log Reader Agent The name of the Log Reader Agent executable is logread.exe. This agent is used in transactional replication. The Log Reader Agent monitors the transaction logs of all databases that are involved in transactional replication. The agent copies any changes in the data that are marked for replication in the transaction log of the publication database and sends them to the Distributor server where they are stored in the distribution database. The transactions are held there until they are ready to be sent to the Subscriber servers. Distribution Agent The name of the Distribution Agent executable is distrib.exe. Both snapshot and transactional replication use this agent. The Distribution Agent is responsible for moving the snapshot and the transactions held in the distribution database to the subscribing servers. In the case of push subscriptions, the Distribution Agent resides on the Distributor server, In the case of pull Subscriptions; it resides on the Subscriber server. Merge Agent The name of the Merge Agent executable is replmerg.exe. This agent is used with merge replication. The Merge Agent applies the initial snapshot to the Subscriber servers. Incremental changes subsequent to the initial synchronization are monitored and merged to the Subscriber servers by the Merge Agent. The agent also resolves the update conflicts. Each of the databases taking part in the process has one Merge Agent. Like the Distribution Agent, the Merge Agent runs on the Distributor server in push subscriptions, and on the Subscriber server in the case of pull subscriptions. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
9. Page 9 of 85 Queue Reader Agent The name of the Queue Reader Agent executable is qrdsvc.exe. In transactional replication, there is an option to either immediately update the messages or store them in a queue, using either the SQL Server queue or the Microsoft Messaging queue. If the updated messages need to be sent immediately, there needs to be a constant connection between the Publisher and the Subscriber servers. However, if you are going to store the messages in the queue, you do not need a constant connection; you can send the messages whenever the connection is available. In such cases, the Queue Reader Agent takes the messages from the queue and applies them to the publishing server. The Queue Reader Agent is multithreaded and runs on the Distributor server. There is only one instance of this agent for a given distribution database, and it services all the publications and Publisher servers. Purpose of Replication The purpose of this document is to show most skilled ways to replicate your database from one server to other and also to show the benefits of replication of database. The following advantages users can avail by using replication process. Users working in different geographic locations can work with their local copy of data thus allowing greater independence. Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations. You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online. You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic. Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files. Replication in SQL Server Microsoft SQL Server follows asynchronous (lazy) replication. It permits three different kinds of asynchronous replication. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
10. Page 10 of 85  Snapshot Replication (SQL Server 6.5, 7.0, 2000).  Transactional Replication (SQL Server 6.5, 7.0, 2000).  Merge Replication (SQL Server 7.0, 2000). Snapshot Replication Snapshot replication makes a copy of the data and propagates changes for the whole set of data rather than individual transactions, thereby making it a discontinuous process and entailing a higher degree of latency. For example, suppose a bookstore chain offers discounts once or twice a year. The regional bookstores only need to be aware of the price changes occasionally, so you could use snapshot replication to transfer the changes from the head office to the regional bookstores. Using snapshot replication by itself is most appropriate when one or more of the following is true: Data changes infrequently. It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time. You are replicating small volumes of data. Many changes occur over a short period of time. Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots might also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot. Snapshot replication has a lower continuous overhead on the Publisher than transactional replication because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to use snapshot replication. The below figure 1 shows how snapshot replication will works. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
11. Page 11 of 85 Fig 1: Snapshot Replication–Work Model Work Model Steps:- 1. Publication Database Contains articles in publisher server. 2. Snapshot agent transfers articles from publication database and stores in snapshot folder. 3. Snapshot agent writes history data and adds rows in MSrepl_commands table in the Distribution database. 4. Distribution database transfers articles from Distribution database to Subscription database. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
12. Page 12 of 85 Transactional Replication Transactional replication allows incremental changes to data to be transferred either continuously or at specific time intervals. Transactional replication is normally used where there is a high volume of inserts, updates, and deletes. This type of replication is usually used in a Server-to-server environment. For example, auto repair shops need to have real-time data about inventory in their warehouses and other shops. By using transactional replication across all stores, it is possible for each of the shops to know the current inventory, and stock shortages can be anticipated ahead of time. Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases: You want incremental changes to be propagated to Subscribers as they occur. The application requires low state between the time changes are made at the Publisher and the changes arrive at the Subscriber. The application requires access to intermediate data states. For example, if a row changes five times, transactional replication permits an application to respond to each change, not only the net data change to the row. The Publisher has a very high volume of insert, update, and delete activity. The Publisher or Subscriber is a non-SQL Server database, such as Oracle. By default, Subscribers to transactional publication should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that permit updates at the Subscriber. The below figure 2 shows how snapshot replication will works ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
13. Page 13 of 85 Fig 2: Transactional Replication–Work Model Work Model Steps:- 1. Transactions are written in the transaction log of publication database. 2. Log reader agent reads only committed transactions from publication database. 3. Log reader agent writes committed transactions in the distribution database. 4. Distribution agent reads from MSrepl_transactions. 5. Distribution agent transfers data to the subscription database. Merge Replication Merge replication permits a higher degree of autonomy. It allows the subscribing servers to make changes and then it propagates those changes to the publishing servers, which in turn transfer the changes to other subscriber servers. For example, Sales people working in the field can enter their orders or changes once the transactions are complete. The updated data from different sales people can lead to conflicts, which can be resolved by setting up a conflict policy in merge replication. The Conflict Policy Viewer in SQL Server 2005 helps you track the conflicts. Point of sales applications, like sales force automation, are situations where you can use merge replication. Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations: Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
14. Page 14 of 85 Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers. Each Subscriber requires a different partition of data. Conflicts might occur. When they do, you need the ability to detect and resolve them. The application requires net data change instead of access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher, to the fifth value, to reflect the net data change. Merge replication enables various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one server, the same data might have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged. Merge replication provides several ways to handle conflicts. Fig 3: Merge Replication-Work Model ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
15. Page 15 of 85 Work Model Steps:- 1. Publication database contains articles in the publisher server. 2. Conflict tables, change- tracking tables, and triggers present in publication and subscription databases. 3. Merge agent transfers data. 4. Merge agent writes history in MSmerge_history table in the distribution database. Physical Replication Models There are four physical models for replication in SQL Server, They are:  Publisher/Distributor–Subscriber model.  Central Publisher–Multiple Subscribers model.  Central Subscriber–Multiple Publishers model.  Multiple Publishers–Multiple Subscribers model. Publisher/Distributor–Subscriber Model In the Publisher/Distributor–Subscriber model, you place the Distributor server along with the Publisher server in one physical server, and the Subscriber server in another physical server, as shown in Figure 4. This is the simplest of all the models. Data is replicated from the Publisher server to the Subscriber server. Fig 4: Simple Publisher/Distributor–Subscriber model Central Publisher–Multiple Subscribers Model The Central Publisher–Multiple Subscribers model is actually an extension of the previous Model. In this one, you have one Publisher server publishing data that is sent to several Subscriber servers. For example, the publisher publishes a book, and you and others want to read several chapters. In this case, each of you buys the book, and each of you is a subscriber. The Publisher ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
16. Page 16 of 85 server is a central Publisher that is distributing data to multiple Subscriber servers, as shown in Figure 5. The Distributor server can reside on the same physical server as the Publisher server, or on a different one. Fig 5: Central Publisher/Distributor–Multiple Subscribers model Typically this model is used when you want the Subscribers to have read-only permission. The Distributor server processes the changes in data from the Publisher server, and sends them to the Subscriber servers. As such, the administrator needs to ensure that SELECT permissions have been granted to the Subscriber servers. When you put the Distributor server on another physical server, you are essentially Offloading the work of replication this may be done to take advantage of a high-speed network Connection or to optimize the performance of replication either way, you want to take advantage of the powerful network at your disposal. You might implement this model if, For example, the regional sales people working in the field do not have access to retrieve data from the central publishing server. The Publisher server partitions the data and only sends out data that is relevant to the subscribing servers. The distributing server receives all the data ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
17. Page 17 of 85 from the publishing server and transmits the subscriptions to the subscribing servers at the regional level. Instead of logging on to the main publishing server, the sales people log on to the local Subscriber server and retrieve the data set. This is shown in Figure 6, Fig 6: Central Publisher–Multiple Subscribers model with Remote Distributor Central Subscriber–Multiple Publishers Model The Central Subscriber–Multiple Publishers model is commonly used in situations where data from multiple sites needs to be consolidated at a central location while providing access to the local site with local data. Data warehousing is a typical example. A diagram of this model is shown in Figure 7. The multiple Publisher servers replicate their data to the same subscription table, and this poses a unique problem. For example, suppose regional product orders are tracked by consolidating them in an inventory database at a central location. The Publisher servers publish a table called Products, which contains the following columns: productid, product_name, and quantity. Because data from the Products table on each Publisher server is being published to the same subscription ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
18. Page 18 of 85 table, data from one Publisher server runs the risk of being overwritten by another. One way to resolve this issue is to create a unique local identifier for each Publisher server. This can be done by adding a column called region_id in the Products table and assigning each Publisher server a unique number for region_id. This means the two columns, productid and region_id, are a composite key. Fig 7: Central Subscriber-Multiple Publishers model Multiple Publishers–Multiple Subscribers Model The Multiple Publishers–Multiple Subscribers model allows the Publisher in one server to also act as a Subscriber. When using such a model, you need to consider consistency in data and any potential update conflicts. For example, suppose three or more shops belong to the same manufacturer of refrigerator parts. Each of the shops keeps a record in the Parts table of ice-maker kits, water inlet valves, o-rings, and so on. If a shop owner knows the inventory of the parts in the other stores, they will be able to get inventory from the other shops if they run out of the parts in any store. This is made possible by implementing the Multiple Publishers–Multiple Subscribers model using transactional or merge replication. Each shop is a Publisher server of the Parts table to the other two subscribing servers, and it is also a Subscriber server to the same table. Any time the data in the table is updated, it can be replicated by transactional replication. And if you ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
19. Page 19 of 85 want to ensure that other shops know each other‘s inventory, you can use transactional replication with updateable subscriptions. This is shown diagrammatically in Figure 8. Fig 8: Multiple Publishers–Multiple Subscribers model Configuring Replication Now I will show you how to configure replication using the GUI method. Note: Here in the following screenshots I used the below as Distributor & Publisher: MAHESH-PC\Mahesh Subscriber: MAHESH-PC\Keerthi ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
20. Page 20 of 85 Step 1 Configuring Distribution First, you must connect to the SQL Server Distributor: 1. Open SQL Server 2005 Management Studio (SSMS). 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. Fig 9: Connecting to 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
21. Page 21 of 85 Fig 10: Starting the Configure Distribution Wizard 4. The Configure Distribution Wizard introduction page is displayed. Click Next ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
22. Page 22 of 85 Fig 11: Starting the Configure Distribution Wizard Page 5. Verify that the instance that you are connected to is selected as the Distributor and click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
23. Page 23 of 85 Fig 12: Specifying the Distributor 6. Specify Snapshot Folder This snapshot folder does not support pull subscriptions created at the Subscriber. It is not a network path or it is a drive letter mapped to a network path. To support both push and pull subscriptions, use a network path to refer to this folder. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
24. Page 24 of 85 Fig 13: Specifying the Snapshot Folder 7. Specify Distribution Database, Locate the distribution database and log files. Click Next ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
25. Page 25 of 85 Fig 14: Specifying the Distribution Database 8. Specify Publisher, Enable servers to use this distributor when they become publisher. Click Next ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
26. Page 26 of 85 Fig 15: 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
27. Page 27 of 85 Fig 16: Scripting and Creating the Distribution 10. Specify the distributor of script, Click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
28. Page 28 of 85 Fig 17: Specify the Distributor Script 11. On the Complete the Wizard page, verify that all options are correct. Click Finish ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
29. Page 29 of 85 Fig 18: Completing & Verifying the Wizard 12. The Distribution Wizard now saves the configuration information that you have entered and creates the distribution. Click Close after the steps are complete. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
30. Page 30 of 85 Fig 19: The successful completion of the Distribution process 13. Specifying the connection properties. Right-click the Replication folder and then click Distribution Properties ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
31. Page 31 of 85 Fig 20: Specifying the connection properties ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
32. Page 32 of 85 Fig 21: Saving the Distributor Configuration 14. To set the security for a Queue Reader Agent, Click the properties button ( ) from the distribution database associated with the agent. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
33. Page 33 of 85 Fig 22: Saving the Properties for Distributor Configuration Step 2 Configuring Publication 1. Right-click on the Replication folder in the left pane, and select Publisher Properties from the menu. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
34. Page 34 of 85 Fig 23: Specifying the connection properties 2. Specify the Publisher Properties and select Transactional Databases at this article. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
35. Page 35 of 85 Fig 24: Saving the Publisher Properties 3. For selecting database names for Transactional or Merge replications, click on Publications Database object under the Select a page on the left pane and after selecting databases, then click on OK button. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
36. Page 36 of 85 Fig 25: Specifying the Publication Databases 4. After you have connected to the instance, start the New Publication Wizard. a. Expand the server node. b. Expand the Replication folder in the left pane. c. Right-click the Local Publications folder and then click New Publication from menu, the wizard for configuring new publications will open. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
37. Page 37 of 85 Fig 26: Creating New Publication ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
38. Page 38 of 85 Fig 27: Starting the New Publication Wizard 5. This wizard guides you through the creation of the publication. Click Next in the first page of the New Publication Wizard, and you will see the page in below figure 28. Choose the database that contains the data or objects you want to publish, and click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
39. Page 39 of 85 Fig 28: Selecting the database whose data and objects you want to publish 6. In the next page, shown in below Figure 29, the wizard lists the different types of publications, such as snapshot publication, transactional publication, transactional publication with updatable subscriptions, and merge publication. At the bottom of the page, it describes the different publication types. 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 and click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
40. Page 40 of 85 Fig 29: Selecting Publication Type 7. Now you can select the tables and other database objects, such as views and stored procedures that you want to publish as articles, as shown in below Figure 30. For now, I select only the tables. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
41. Page 41 of 85 Fig 30: Selecting the articles that you want to publish 8. Click the plus sign next to a table to see its objects, and click in the check box to select a table. In below Figure 31, you can see a circle with a line through it on the icon for the table‘s discounts, roysched this means that the table is not allowed to be published. If you select the table, the reason will be displayed at the bottom of the window. As you can see in below figure 31, for a table to be published it must contain a primary key, and the table‘s discounts, roysched does not have one. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
42. Page 42 of 85 Fig 31: Displaying the reason the table cannot be published 9. Now, highlight the authors table, click the Article Properties button, and select Set Properties of the Highlighted Table from the drop-down list. This will open the Article Properties window shown in below figure 32. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
43. Page 43 of 85 Fig 32: Setting the Item article’s properties Scroll down to the bottom of the Properties pane, and you will see the Statement Delivery section, which lists the methods of transmitting data changes to the Subscriber server in transactional replication. By clicking on the drop-down list for the delivery format, you can select the kind of propagation method you want from the different methods: Specify that you do not want to replicate the DML statements; in this case, the DML Statements will not be replicated to the Subscriber server. Call a custom stored procedure; in this case you can write a custom stored procedure to Replicate the DML operations. Call a stored procedure; this is the default. Transactional replication will generate the Stored procedures for the necessary DML operations. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
44. Page 44 of 85 Use insert, update, or delete statements to perform the DML operations; this method Can be used with non-SQL Server Subscriber servers. In this case, I choose to call stored procedures for the insert and delete statements, while the SCALL method was used for the update delivery format. Transactional replication creates the following stored procedures by default when the table article is created:  sp_MSins_ : stored procedures used in insert statements.  sp_MSupd_ : stored procedure used in update statements.  sp_MSdel_ : stored procedures used in delete statements. This is done for all tables that are selected for publication in transactional replication. 10. Click OK in the Article Properties window to return to the wizard, and click Next. You will see the Filter Table Rows page shown in below figure 33. Fig 33: Filtering table rows from published tables 11. We are not going to filter the table now, so click Next again. This will take to the Snapshot ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
45. Page 45 of 85 Agent page shown in below figure 34. Uncheck the ―Schedule the Snapshot Agent to run at the following times‖ box. Since we are using transactional replication and are not going to change the properties of the publication, we do not need to use this feature. We just want to use the Snapshot Agent now to generate the initial snapshot. Click Next. Fig 34: Scheduling the Snapshot Agent 12. You will now see the Agent Security page shown in below figure 35, in which you can configure security for both the Snapshot and Log Reader Agents. Click the Security Settings button for the Snapshot Agent, and you‘ll see the Snapshot Agent Security window as shown Figure 35 contains title as ‗Snapshot Agent Security‘, Select the security settings under which the Snapshot Agent will run. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
46. Page 46 of 85 Fig 35: Specifying accounts for the Snapshot and the Log Reader Agents ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
47. Page 47 of 85 Fig 36: Setting the Snapshot Agent process to run under the SQL Server Agent service Normally you should select the domain account for the Snapshot Agent, but in this case, since I am the only person using the machine, I chose the SQL Server Agent service account. In order to connect to the Publisher server, I chose to impersonate the process account (Windows account). Click OK to return to the Agent Security page, and repeat the process for the Log Reader Agent. 13. Click Next in the Agent Security page, and you‘ll see the Wizard Actions page shown in below figure 37. Check both boxes to create the publication and generate a script file. Click Next to continue. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
48. Page 48 of 85 Fig 37: Setting the wizard to create the publication 14. Now the wizard will ask you to specify a location for the script file, as shown in below figure 38. Set the location and whether you want it to be appended to or to overwrite any existing file, and then click Next. It is a good practice to keep a script for creating publications and subscriptions. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
49. Page 49 of 85 Fig 38: Setting the script file properties 15. The final page of the wizard asks you to specify the publication name and summarizes the settings for the publication, as shown in below Figure 39. You can scroll down to view the full summary of the settings. If you are not happy with the results, you can always go back to the previous pages and make any necessary changes. If you‘re satisfied, click Finish. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
50. Page 50 of 85 Fig 39: Completing the wizard 16. The wizard will now create the publication and show its success or failure as shown in below figure 40. You can click the Report button to view, save, or email the report. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
51. Page 51 of 85 Fig 40: The successful completion of the process 17. The publication that you created appears under the Replication folder in SQL Server Management Studio. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
52. Page 52 of 85 Fig 41: Verifying whether Publication created successfully in SSMS or not Step 3 Verifying the Status of Replication Agents 1. If you configured the Snapshot Agent to start immediately as recommended, you can now verify 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
53. Page 53 of 85 Fig 42: Launching 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
54. Page 54 of 85 Fig 43: Viewing the new publication in Replication Monitor 4. Verify the status of the Log Reader Agent and the Snapshot Agent. If the status of either agent is incorrect, verify that SQL Server Agent is running and that the Windows user and password you specified for each agent are correct before you continue. a. In the lower pane, titled Agents and jobs related to this publication, verify that the status of the Log Reader Agent is Running. b. In the same pane, verify 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
55. Page 55 of 85 Fig 44: 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, verify that the status is Completed. b. Close the Snapshot Agent dialog box c. Close Replication Monitor. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
56. Page 56 of 85 Fig 45: Viewing details of Snapshot Agent history Step 4 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 user on the Distributor computer under which the Distribution Agent can run. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
57. Page 57 of 85 1. The snapshot files are stored in a snapshot folder. By default the snapshot folder is located at :\SQLServer2005\Replication\ (This is the location where I mentioned the Snapshot Folder). I recommend that you configure a Windows share for this folder. The Windows user that runs the Distribution Agent must have read permissions on the share. For more information about how to configure shares and assigning 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 and Type a name and description for the share. Fig 46: Configuring snapshot folder properties ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
58. Page 58 of 85 2. Configure the snapshot share so that the Windows user under which the Distribution Agent runs has read permissions to the folder: a. Click Permissions. b. In the Permissions for Replication dialog box, click Add and locate the Windows user under which the Distribution Agent runs. c. Select the Read check box. Fig 47: Configuring permissions to the snapshot folder 3. The next step is to add the Windows user to the Publication Access List (PAL). The PAL contains all Windows users and groups that have permission to synchronize with the publication. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
59. Page 59 of 85 a. In SQL Server Management Studio (SSMS), in Object Explorer, expand Replication. b. Expand Local Publications, and right-click the publication. c. Click Properties. Fig 48: Accessing the Publication Properties dialog box 4. In the Publication Properties dialog box, make sure that the Windows user is listed in the PAL. a. Click the Publication Access List page. b. If the Windows user is not in the Publication access list pane, click Add. c. Locate the user and click OK to add the user to the PAL. d. Click OK to close the Publication Properties dialog box. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
60. Page 60 of 85 Note: The Windows user must be a valid SQL Server login that is already associated with a database user in the publication database. Fig 49: Adding a user to the Publication Access List Step 5 Creating a Subscription This section of the article describes how to create a subscription to the publication you created with the New Subscription Wizard. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
61. Page 61 of 85 1. In SQL Server Management Studio, right-click the publication that created. 2. Click New Subscriptions. Fig 50: Starting the New Subscription Wizard 3. The New Subscription Wizard introduction page is displayed. Click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
62. Page 62 of 85 Fig 51: New Subscription Wizard introduction page 4. On the Publication page, the publication you created is displayed and selected by default. Click Next ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
63. Page 63 of 85 Fig 52: 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
64. Page 64 of 85 Fig 53: 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
65. Page 65 of 85 Fig 54: 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
66. Page 66 of 85 Fig 55: Connecting to 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. Here I will select pubs_Repl 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
67. Page 67 of 85 Fig 56: Specifying 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. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
68. Page 68 of 85 Fig 57: Reviewing Connection Properties 10. In the Distribution Agent Security dialog box, select the Windows user that you configured before you started the New Subscription Wizard. The Windows user that you select must meet the following conditions: a. The user is included in the PAL. b. The user is a member of the db_owner fixed database role in the subscription database. c. The user has read permissions on the snapshot share. Specify the Windows user under which the Distribution Agent will run. a. Type the SQL login account info. b. Click OK to save changes and close the dialog box. c. On the Distribution Agent Security page, click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
69. Page 69 of 85 Fig 58: 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. Verify that Run Continuously is selected in the Agent Schedule column. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
70. Page 70 of 85 b. Click Next. Fig 59: 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. Click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
71. Page 71 of 85 Fig 60: Setting Initialize 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. I recommend that you select the check box for scripting so that you will have a copy of the replication configuration for future reference and it is the best practice to keep the script. a. Select both check boxes. b. Click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
72. Page 72 of 85 Fig 61: Scripting and Creating the Subscription 14. Now the wizard will ask you to specify a location for the script file, as shown in below figure 62. Set the location and whether you want it to be appended to or to overwrite any existing file, and then click Next. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
73. Page 73 of 85 Fig 62: Specify the Subscription Script 15. The final page of the wizard Complete the Wizard, summarizes the settings for the Subscription, as shown in below Figure 63. You can scroll down to view the full summary of the settings. If you are not happy with the results, you can always go back to the previous pages and make any necessary changes. If you‘re satisfied, click Finish. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
74. Page 74 of 85 Fig 63: Completing the Subscription Wizard Best Practices in Replication There are some best practices for replication types which will yield‘s us a healthy and fruitful results. Best Practices in Snapshot Replication  Store configuration scripts: Once the publication and subscriptions are configured for snapshot replication using the GUI, generate a script and store it for later use. The script can be tested using either the SSMS or the SQLCMD utility.  Create backups: Back up the publication, subscription, msdb, and distribution databases right after you add a new publication, or make schema changes on the publication. You should back up the msdb databases on the Publisher, Distributor, and Subscriber servers ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
75. Page 75 of 85 separately. Ensure that you have a backup and restoration script, and that you test it periodically.  Generate performance baselines: Develop a baseline for the performance of snapshot replication on the server side for each of the server and hardware resources, like processors and memory, and the configuration of the Distributor. You should also develop a performance baseline for the client side for each of the filtering options, the agent parameters, the subscription options, the generation of the snapshot, and the settings of thresholds and warning.  Use data-modeling tools to design the database: The physical data model (PDM) should at least be up to the third normal form. In designing the publication, add only the articles that are necessary. If you add articles the subscribing servers do not need, the Snapshot Agent will require more resources than necessary every time it runs. Consequently, use caution when adding indexes to the Subscriber server, as the creation of indexes other than the one on the primary key column can affect DML operations.  Reduce contention: Since latency is not an issue with snapshot replication, consideration must be made to reduce contention. This is particularly so between the activities of the user and the replication agent. Turning on the READ_COMMITTED_SNAPSHOT option for both the publishing and subscribing databases can reduce this.  Consider using anonymous or pull subscriptions: The Distribution Agent will reside on the Subscriber server, and not on the Distributor server, so using anonymous or pull subscriptions will reduce the workload on the Distributor server.  Schedule the snapshot carefully: Generate the snapshot during off-peak hours and not during peak business hours, particularly if you have a slow network or low bandwidth.  Plan data locations: Consider saving the data and the log files for the distribution database on a separate drive from where the snapshot is generated to reduce contention between the disks. Have the snapshot folder local to the Distributor server, since the Distribution Agent needs to access it to transmit the messages to the appropriate subscribing servers.  Assign permissions: Ensure the right permissions are given to the snapshot folder on the Distributor server. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
76. Page 76 of 85  Use one snapshot folder: Generate the snapshot only to one folder and not simultaneously to an alternate folder, as this requires the Snapshot Agent to write first on the default folder and then to the alternate folder. Instead, you could save the snapshot on some storage media and then transfer it physically to the subscribing server.  Consider compressing the snapshot: Compressing the snapshot will make the transfer of files across the network easier and faster. Bear in mind, though, that more work needs to be done by the Snapshot Agent to generate the compressed snapshot and by the Distribution Agent to process the files, and this might have an adverse impact on performance.  Consider manual initialization: Consider manually initializing subscriptions for snapshot publications containing a lot of data.  Minimize logging for initial synchronization: During the initial phase of the subscription synchronization, have the subscribing database set either to the Simple or Bulk Recovery model. This will ensure that bulk inserts are logged minimally. Once the configuration is done, set it to Full Recovery. Do not set any of the databases to autogrowth mode, as this will lead to the fragmentation and can degrade performance.  Adjust agent parameters: Once you have configured snapshot replication, change the parameters for both the Snapshot and Distribution Agents, particularly - HistoryVerboseLevel and -OutputVerboselevel. Set -OutputVerboselevel to 0 so that only the error messages are logged. Set -HistoryVerboseLevel to 1 so that minimal history of the data is logged. Increase the -MaxBcpThreads parameter for the Snapshot Agent to 2 so that the bcp utility can run faster using parallel processing.  Use bulk inserts: For the Distribution Agent use the -UseInprocLoader parameter, as this will cause the agent to use the BULK INSERT property instead.  Use the System Monitor: System Monitor can trace the data for server-wide operations. Have separate counters for each of the operations that are monitored. For example, have separate counters for locks, Snapshot Agents, and Distribution Agents.  Use the SQL Server Profiler: SQL Server Profiler can create a template to monitor the trace for snapshot replication. Save the trace in a file or a table in a separate database.  Set thresholds: Use the Replication Monitor to set a threshold to warn when the subscriptions will expire. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
77. Page 77 of 85 Best Practices in Transactional Replication  Save the scripts: Once a publication and subscription are configured for transactional replication, store the script for later use.  Make regular backups: Back up the publication, subscription, msdb, and distribution databases right after you add a new publication or schema changes are made on the publication. If you are using updatable subscriptions, back up the subscription database along with the msdb database on the Subscriber server. Periodically test the backup and restoration script. You should also back up the transaction log, since the Log Reader Agent transmits committed transactions from the log to the distribution database.  Monitor disk space: For each database involved in transactional replication, ensure that you have enough space for the transaction log. The log file is used not only as a write- ahead log; it is also being read sequentially by the Log Reader Agent to transmit committed transactions.  Consider autogrowth mode: If you have situations where either the Log Reader Agent is not running or the distribution database is temporarily down, the transaction log will continue to grow until the transactions have been delivered. In such situations, it is better to set the log file to autogrowth mode.  Consider sync with backup: Consider using the sync with backup option for the distribution database. This will ensure that the log file on the publication database is not truncated until the corresponding transactions have been backed up in the distribution database.  Validate the subscriptions: After the backup and restoration of the databases used in transactional replication, you should validate the subscriptions using either the SSMS or the validate subscription script.  Check primary keys: For tables used as publications in transactional replication, check to see that they have primary keys.  Avoid truncating data: For articles involved in transactional replication, be careful not to truncate the data, as truncated operations are not logged. Those changes will not be replicated to the subscribing databases. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
78. Page 78 of 85  Generate performance baselines: Develop a baseline for the performance of transactional replication on the server side for each of the server and hardware resources like processors, memory, and the configuration of the Distributor. You should also develop a performance baseline on the client side for each of the filtering options, the agent parameters, the subscription options, the locks, the transaction log, and the settings of tracer tokens using the Replication Monitor. At the development stage of transactional replication, you should configure the publication design such that the business requirements are met. Ensure that you do not publish unwanted data, as this will have an impact on the distribution database, and keep the transactions to a minimum. You should also check that the right kinds of indexes are created for the DML operations that you are going to use on the publication database. Keep row filters to a minimum, as they affect the throughput of the Log Reader Agent when it scans the transaction log of the publication database.  Save scripts for profiles: Keep a script for the profiles for each of the agents used in transactional replication.  Use tracer tokens: Since latency is an issue in transactional replication, monitor the latency using tracer tokens in the Replication Monitor.  Use data-modeling tools to design the database: As with snapshot replication, design the database at least up to the third normal form. In designing the publication, add only the articles that you need.  Plan your replication use: Transactional replication creates stored procedures on the database to perform DML operations, so do not use transactional replication as a stopgap solution to provide real-time data for third-party applications. Plan well ahead, because insert, update, and delete operations use these system-generated stored procedures to monitor the changes in the database and then transmit them to the subscribing servers.  Plan for high-volume changes: If you are going to use a high volume of inserts or updates (batch operations), write a stored procedure that will perform these operations. The stored procedure will be executed once to deliver inserts or updates. Otherwise, a system-generated stored procedure will be executed for each insert or update operation, which will have an impact on performance, since there will be a considerable amount of throughput involved. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
79. Page 79 of 85  Minimize the use of row filters: Use row filters for articles involved in transactional replication with caution, since the Log Reader Agent will have to apply the filter to each row affected by the update. The Log Reader Agent will have to scan the log, which will slow down the performance of transactional replication.  Use the SQL Server queue: When using queued updating subscriptions, it is better to use the SQL Server queue and not the message queue, as the latter will slow down the performance for queued updates.  Plan for how tables are published: Remember that tables used for merge publication cannot be used for transactional replication with queued updating subscriptions.  Be careful updating keys: Do not update the primary keys for transactional replication with queued updating subscriptions. This is because the primary keys are used to locate the records and updating them can lead to conflicts.  Consider resources required for subscriptions: For immediate updating subscriptions, check to see that the MS DTC is installed and running. Remember that immediate updating subscriptions need to use the 2PC protocol, and this can be a drain on resources. Use it if it is a must for your business requirements.  Minimize conflicts: Conflicts can occur in transactional replication with updatable subscriptions and peer-to-peer replication. Wherever possible, use data-partitioning strategies to minimize the level of conflicts and thus reduce the processing overhead associated with conflict detection and resolution.  Monitor conflicts: If you need to set a conflict policy for updatable subscriptions, ensure that it meets the business requirements. Monitor the Conflict Policy Viewer to view the conflicts, and use them for troubleshooting purposes.  Prevent activity when altering schema: When altering schema in updatable subscriptions, stop all the activities in the publishing and subscribing databases by putting them in quiescent mode. Ensure that all prior changes have been propagated before implementing the schema change.  Monitor delays in replication: If there are delays in the delivery of transactions to the subscribing servers, consider reinitializing the subscriptions. Use the Replication Monitor to monitor the Undistributed Commands tab, which will show you how many transactions are still waiting to be delivered from the distribution database to the subscription database. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
80. Page 80 of 85  Match the schema for peer-to-peer transactional replication: All the databases involved in peer-to-peer transactional replication must contain the same schema. The object names and the object schema, along with the publication name, must be the same in all nodes.  Use multiple distribution databases: To eliminate a potential point of failure in peer- to-peer transactional replication, it is better to have the distribution database on each node.  Avoid identity columns in peer-to-peer replication: Do not add identity columns to publications involved in peer-to-peer replication. If you do, you will have to manually manage the identity range.  Reduce contention: As with snapshot replication, you can reduce contention by setting the READ_COMMITED_SNAPSHOT option on.  Monitor performance: Use the SQL Trace function to trace the data and monitor the performance of transactional replication.  Schedule the traces with jobs: You can create a job and schedule the traces. Create a separate job for each of the traces. Select only those event classes that you need, or the amount of information generated by the trace will be overwhelming. Check to see whether the C2 audit option is enabled on the server; enabling this option will cause the engine to capture everything.  Use the fn_trace_gettable function: Do not save the trace directly from the SQL Profiler to a table in the database, as this will take an enormous amount of time.  Use the DTA: Use the DTA (Database Tuning Advisor) to tune the trace data as a workload, and save the tuning sessions. Also tune the publication and the subscription databases using the DTA. Save the tuning session for the workload that meets the performance baseline on a testing environment and then export the requirements to other production servers.  Test the tuning: Test the tuning of transactional replication on a test server before you export the requirements to a production server.  Monitor the DTA tuning: Monitor the progress of the DTA (Database Tuning Advisor) so that if you are not satisfied with the tuning, you can stop before it eats into your valuable production time. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
81. Page 81 of 85  Review the DTA reports: Use the DTA‘s Report tab to check the different kinds of tuning usage, like the index usage. Select the recommended reports to see the recommended and current costs for the tuning.  Assign a dedicated Distributor server: Having the distribution database on a separate Distributor server will reduce the processing overhead on the Publisher server.  Consider anonymous and pull subscriptions: As with snapshot replication, using either anonymous or pull subscriptions will reduce the workload on the Distributor server while shifting the processing to the Subscriber server.  Change agent parameters: After the configuration is finished, change the parameters for the both the Log Reader and Distribution Agents. Set -HistoryVerboseLevel to 1 so that minimal history of the data is logged, and set -OutputVerboselevel to 0 so that only the error messages are logged. Increase the -MaxBcpThreads parameter for the snapshot agent to 2 so that the bcp utility can run faster using parallel processing.  Monitor the Log Reader Agent’s latency: Use the System Monitor to monitor the Log Reader: Delivery Latency and Dist: Delivery Latency counters.  Adjust the batch size for the Log Reader Agent: Change the ReadBatchSize parameter for the Log Reader Agent. The default is 500 transactions, and the Log Reader Agent will process this number of transactions from the publication database in one processing cycle until all of them have been read. You should change this number of transactions to suit your needs.  Adjust the polling interval for the Log Reader Agent: Increase the PollingInterval parameter from the default value of 5 seconds for the Log Reader Agent. Increasing the value for this parameter boosts performance by reducing the interference of the reads with the sequential writes in the transaction log. However, decreasing the polling interval will reduce the latency, as the Log Reader Agent will have to poll the log more frequently to deliver transactions from the publication database to the distribution database. You will have to balance latency and server processing by adjusting the PollingInterval parameter.  Use the System Monitor: As with snapshot replication, use the System Monitor to trace the data for server-wide operations, and have separate counters for each of the operations that are used to monitor the trace. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
82. Page 82 of 85 Best Practices in Merge Replication  Save the configuration script: Once the publication and subscription are configured for merge replication, store the script for later use.  Maintain regular backups: Back up the publication, the subscription, and the msdb databases right after you add a new publication, or when schema changes have been made on the publication.  Optimize publication retention time: Wherever possible, adjust the publication retention time to the minimum appropriate for your business requirements. This setting determines how long the tracking metadata is retained for the synchronization of the subscription, and hence how much disk storage is needed to hold the metadata.  Set the compatibility level: Set the publication compatibility level to SQL Server 2005 so that the new features of merge replication are enabled.  Consider indexing when using filters: When using row or join filters, consider indexing those columns involved in the filters. Indexing will speed up the merge replication process, since the engine will not need to read each of the rows involved in the filtration.  Use join filters when possible: When using row filters for articles involved in merge replication, avoid complicated sub queries, since the Merge Agent will have to process data for each of the partitions. Use join filters instead, to make this process more efficient.  Minimize join filters: Try to keep the number of join filters to fewer than five. Use join filters only for those tables that need to be partitioned among the subscribing servers.  Monitor synchronization performance: Use the Synchronization History tab in the Replication Monitor to display statistics regarding uploading and downloading changes, history, error messages, and status.  Validate subscriptions after restoring: After backing up and restoring databases used in merge replication, you should validate the subscriptions using either the SSMS or the validate subscription script.  Consider indexing metadata: For optimizing purposes, consider indexing the MSmerge_genhistory,MSmerge_contents,MSmerge_tombstone, ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
83. Page 83 of 85 MSmerge_past_partition_mappings, and MSmerge_current_partition_mappings system tables, which store the metadata for merge replication.  Create performance baselines: Develop baselines for the performance of merge replication on the server side for each of the server and hardware resources, like processors and memory. You should also develop a performance baseline for the client side for each of the filtering options, the agent parameters, the subscription options, the locks. You should also set up thresholds and warnings for each of the publication settings using the Replication Monitor.  Keep scripts for the agent profiles: Keep scripts for the profiles of the different agents used in merge replication.  Use data-modeling tools to design the database: As with snapshot and transactional replication, design the database at least up to the third normal form. In designing the publication, add only the articles that are necessary.  Plan for high-volume changes: If you are going to use a high volume of inserts or updates (i.e., batch operations) write a stored procedure that will perform these operations. The stored procedure will be executed once to deliver inserts or updates. Otherwise, the system-generated stored procedure will be executed once for each insert or update operation, which will have an impact on performance because of the considerable amount of throughput involved.  Create conflict policies: In merge replication, conflicts can occur. Set up an appropriate conflict policy to suit your business requirements. Use data partitioning strategies to minimize the number of conflicts and thus reduce the processing overhead associated with detecting and resolving conflicts.  Monitor conflicts: Use the Conflict Policy Viewer to monitor whether the Merge Agent has detected any conflicts and resolved them to meet your business requirements.  Reduce contention: As with snapshot replication, set the READ_COMMITED_SNAPSHOT parameter to on to reduce contention.  Prepare tables for replication: For large tables, prior to the generation of the snapshot, create a ROWGUID column.  Use trace to monitor performance: Use the SQL Trace function to trace the data in merge replication to monitor performance. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
84. Page 84 of 85  Save the trace: Save the trace in a table on a separate database using the fn_trace_gettable function. Do not save the trace directly from the SQL Server Profiler to a table in the database, as this will take an enormous amount of time.  Use the DTA: Use the DTA (Database Tuning Advisor) to tune the trace data as a workload and then save the tuning sessions. Also use the DTA to tune the publication and the subscription databases. Monitor the progress of the DTA, and cancel it if you are not satisfied with the tuning, before it eats into your valuable production time.  Save and test the tuning sessions: Save the tuning session for workloads that meet the performance requirements, and then export the requirements on other servers. Test the tuning of merge replication on a test server before you export the requirements to a production server.  Review tuning reports: Use the Report tab in the Database Engine Tuning Advisor to review the different tuning usage reports, like the Index usage report. Select the recommended reports to see what changes are recommended and the current costs for the tuning.  Adjust agent parameters: After the configuration is finished, change the Merge Agent parameters. As discussed earlier in transactional replication, set -OutputVerboselevel to 0 so that only the error messages are logged. Set -HistoryVerboseLevel to 1 so that minimal history of the data is logged. Increase the -MaxBcpThreads parameter for the Snapshot Agent to 2 so that the bcp utility can run faster using parallel processing.  Monitor performance counters: Monitor the counters for the Merge Agent by using the System Monitor. You can monitor the Merge: Conflicts/sec, Merge: Downloaded Changes/sec and Merge: Uploaded Changes/sec counters.  Adjust the batch size parameter: Change the ReadBatchSize parameter for the Merge Agent. The default is 500 transactions, and the Merge Agent will process all these transactions from the publication database in one processing cycle until all of them have been read. Like the Log Reader Agent, you should change the number of transactions to suit your needs.  Trace with System Monitor: As with snapshot replication, use the System Monitor to trace the data for server-wide operations, and have separate counters for each of the operations that are used to monitor the trace. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step
85. Page 85 of 85 Conclusion In a nutshell, replication is the capability to reliably duplicate data from a source database to one or more destination databases. SQL Server 2005 gives you the power for replication design, implementation, monitoring, and administration. This gives you the functionality and flexibility needed for distributing copy of data and maintaining data consistency among the distributed. You can automatically distribute data from one SQL Server to many different SQL Servers through ODBC (Open Database Connectivity) or OLE DB (Object Linking and Embedding Database). SQL Server replication provides update replication capabilities such as Immediate Updating Subscribers and merges replication. With all the new enhancements to SQL Server replication, the number of possible applications and business scenarios is mind-boggling. References Book: SQL Server 2005 Replication by Sujoy Paul. ©Wipro Technologies Confidential Microsoft SQL Server 2005 Replication Step by Step