Setting up replication involves a number of different steps. This lesson describes each of the different parts of the configuration process.
First you must set up a Distributor and a Publisher; they are both set up during the same process. After these are set up, you can define publications and subscriptions.
When you use SQL Server Enterprise Manager to configure replication, you will often be configuring more than one part at the same time. For example, it is possible to create a publication using the Create Publication Wizard before you have set up the server as a Publisher. This does not mean that you do not have to set up the Publisher; rather, the wizard goes ahead and sets up the server as a Publisher for you.
After this lesson, you will be able to
- Identify the tasks that must be performed to configure SQL Server for replication
- Set up and configure a distribution server
- Set up publication and subscription servers
Estimated lesson time: 115 minutes
You must set up the Distributor before you create dependent Publishers. System administrator permissions are necessary to create a Distributor.
Prepare servers using the Configure Publishing and Distribution Wizard in SQL Server Enterprise Manager. All servers that participate in the replication scenario must be registered in SQL Server Enterprise Manager.
The following topics give more information about some of the options that you will specify when enabling the Distributor.
The distribution database, a store-and-forward database that holds all transactions that are waiting to be distributed to Subscribers, is installed automatically when you set up a Distributor.
You also have options to
During various stages of the different replication processes, the Snapshot Agent creates files that are used by the Distribution Agent or the Merge Agent. These files are created in the distribution working folder. You must ensure that the distribution working folder is available to these replication agents when necessary.
By default, the distribution working folder is created in C:\Mssql7\Repldata and is accessed by other servers on the default Windows NT administrative share (C$) as \\computer_name\C$\Mssql7\Repldata. You can create your own custom share if you prefer not to use the default administrative share and update the configuration to use it. The share must be accessible to the SQL Server Agent service account or to the replication agents' account if you configure another account for the replication agents.
IMPORTANT
On Windows 95 and Windows 98 computers configured as Distributors, you will need to create a share because the C$ share is not created by default, as it is on Windows NT. If you want to use the default Publisher configuration, create a share called C$ in the root of the C: drive and make it accessible to the SQL Server Agent service account or to the replication agents' account.
You also must ensure adequate memory for the distribution server, especially if the Distributor and the Publisher are located on the same computer. Base the memory requirement on the anticipated amount of data and the number of Subscribers; it is a good idea to allocate generous resources to the Distributor.
After you have enabled the Distributor, you can open the Publisher and Distributor Properties dialog box to configure the Distributor.
NOTE
To open the Publisher and Distributor Properties dialog box, run the Configure Publishing and Distribution Wizard or select Tools/Replication/Configure Publishers, Subscribers And Distribution. This menu selection is grayed if you have not selected a server and is called Tools/Replication/Configure Publishing and Subscribers if you have not enabled the Distributor.
In the Publisher and Distributor Properties dialog box, you can perform the following Distributor-related tasks:
Other options in the Publisher and Distributor Properties dialog box allow you to configure the Publisher.
Ensure that you have enough storage space for the distribution working folder and the distribution database:
Regardless of the type of replication you use, you should consider the following factors to determine the size of the distribution database:
Transaction information is retained at the Distributor until it is applied to all Subscribers. If some Subscribers are offline, the database may grow large until the Subscribers are able to connect and retrieve their transactions.
For transactional replication, consider these additional factors:
It is possible to delete a distribution database without disabling the Distributor by first disabling all Publishers that use that particular distribution database.
You can uninstall a Distributor by using the Disable Publishing and Distribution Wizard. This completely removes the replication components from the server. If you do this and you later want to use replication again, you will have to reconfigure replication from the start. The effects of uninstalling a Distributor are as follows:
A server will often be configured as both a Publisher and a Distributor. You use the same dialog box for managing both Publisher and Distributor properties. If you are managing more than one server in SQL Server Enterprise Manager, make sure that you have the correct server selected before you open the Publisher and Distributor Properties dialog box. After you configure the Distributor, you can set the following options for the Publisher with the Publisher and Distributor Properties dialog box:
You will learn how to create publications in Lesson 3, "Publishing."
NOTE
If you use a remote Distributor, make sure that the Snapshot Agent that runs at the Distributor can access the Publisher as well as the distribution working folder for replication. It is easiest to use the same SQL Server Agent domain user account on both the Publisher and the Distributor.
There are two types of Subscribers: registered Subscribers and anonymous Subscribers. For registered Subscribers, information about each Subscriber is stored at the Publisher, and performance information about each Subscriber is kept at the Distributor. The Publisher and the Distributor do not store detailed information about an anonymous Subscriber.
If you will have a large number of Subscribers, or you don't want the overhead of maintaining extra information, you can allow anonymous subscriptions to a publication. This can be especially useful if you want to allow Subscribers to connect using the Internet.
Setting up a registered Subscriber involves enabling the Subscriber at the Publisher and creating either a push or a pull subscription. Anonymous Subscribers do not have to be enabled at the Publisher, and anonymous subscriptions can only be pulled from the Subscriber.
Enabling a Subscriber at the Publisher involves
If you pull subscriptions from a remote Distributor, you must also make sure that the Distribution Agent or Merge Agent that runs at the Subscriber can access the distribution working folder.
You will learn how to create subscriptions in Lesson 4 of this chapter.
You can disable a Subscriber at the Publisher. When you do so, subscriptions to all publications are automatically deleted. However, the administrator of the Subscriber has responsibility for deleting the subscription database or any of its objects.
NOTE
The only time you need to do anything on the Subscriber is when you pull subscriptions. Nothing needs to be set up on the Subscriber before you can pull subscriptions.
There are a number of settings that you can configure for each replication agent, such as replication batch sizes, time-outs, and polling intervals. Because each publication and subscription has one or more agents associated with it, there may be a large number of agents to configure.
Different subscriptions may require different settings; for example, a subscription that is performed across a slow link may need a longer time-out and a less frequent polling interval than a subscription across a fast network link.
To make administration simpler, agent settings are stored in profiles. You can configure each agent to use a default profile or create custom profiles for individual agents.
In the replication exercises, your server will be the Publisher, Distributor, and Subscriber. You will create publications in the StudyNwind database (representing the Publisher) and subscriptions in other databases or tables (representing the Subscribers).
You will learn more about publishing and subscribing in Lessons 3 and 4. These exercises give you an opportunity to enable replication on your server and see replication at work for the first time. Replication uses many dialog boxes and processes, so do not expect to see or understand them all right away. You may find it useful to refer back to the lesson text as you perform the exercises.
In this exercise, you will create a new publication of the Products table in the StudyNwind database. In doing so, you will automatically enable publishing and distribution.
Option | Value |
---|---|
Distributor | Your server |
Publication type | Transactional publication |
Allow Immediate-Updating Subscriptions | No |
Subscriber Types | All Subscribers will be servers running SQL Server |
Articles for publication | Check dbo.Products, click the ellipsis to set the Article name and the Destination table name |
Article name | ReplProducts_Article |
Destination table name | ReplProducts Click OK to return to the wizard |
Publication name | StudyNwind_Products_Publication |
No, create a publication without data filters and with the following properties | Selected |
In this exercise, you will create a new database that will be used to create a pull subscription in the next exercise.
In this exercise, you will create a subscription to pull the publication that you published in a previous exercise.
Option | Value |
---|---|
Choose Publication | Expand your server, then select StudyNwind_ Products_Publication: StudyNwind |
Destination Database | PullSubs |
Initialize Subscription | Yes, initialize the schema and data at the Subscriber |
Distribution Agent Schedule | Continuously |
Start Required Services | SQLServerAgent (on SQLSERVER) |
The job called SQLSERVER-StudyNwind-StudyNwind_Products_Pub-SQLSERVER-PullSubs- 0, with a category of REPL-Distribution, is the job that starts the Distribution Agent for your new subscription.
Which server's system resources will this job consume? Would a push subscription change the resources being used?
Answer
In this exercise, you will start the Snapshot Agent to create a snapshot of the data and schema of the publication. Once the snapshot has been created, the Distribution Agent will use it to create the ReplProducts table and populate it in the PullSubs database.
View the agent history and action messages by right-clicking the agent in the details pane and selecting Agent History. The Distribution will say that there are no replicated transactions available because no new transactions have occurred since it replicated the snapshot.
USE PullSubs SELECT * FROM ReplProducts |
In this exercise, you will use SQL Server Enterprise Manager to see some of the changes that are made to a server when replication is enabled.
If distribution (and other system databases such as master) is not listed, right-click on your server and click Edit SQL Server Registration properties. On the property sheet, make sure that the Show System Databases And System Objects option is checked.
You must neither delete the distribution database nor add objects to or remove objects from the distribution database. It is used to store replication information and replication data from the Publisher that is to be sent to or retrieved by the Subscribers.
In this exercise, you will update the Products table (on the Publisher) and verify that the updated information is replicated to the Subscriber.
USE StudyNwind SELECT * FROM Products WHERE ProductID = 1 |
USE StudyNwind UPDATE Products SET ReorderLevel = 20 WHERE ProductID = 1 SELECT * FROM Products |
Within a few moments, the Status of the Log Reader Agent entry should change to Running, and the Last Action should indicate that 1 transaction with 1 command was delivered.
USE PullSubs SELECT * FROM Products WHERE ProductID = 1 |
When you implement replication, at least one SQL Server becomes a distribution server, and the distribution database is created on this server. Some special considerations are necessary for the distribution server, such as configuring the distribution database, making the distribution share accessible to other SQL Servers, and possibly adding extra memory. Replication agents are enabled on the relevant servers as you create publications and subscriptions. To simplify the configuration of these agents, they can all use common agent configuration profiles.