Lesson 2: Preparing the Servers

[Previous] [Next]

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

Setting Up a Distributor

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.

Configure the Distribution Database

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

  • Specify an existing remote distribution server or create a new distribution database on a server that has been configured as a Distributor
  • Define one or more distribution databases, each of which can support one or more publications
  • Specify the location of the distribution database data and log files

Ensure the Accessibility of the Distribution Working Folder

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.

Ensure Sufficient Memory

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.

Configuring a 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:

  • Create and configure distribution databases.?For each distribution database, you can specify the name and file specifications and set the retention properties for history and transaction records.
  • Set the password to be used when Publishers connect.
  • Configure agent profiles for agents associated with the Distributor.
  • Specify the Publishers that will be allowed to use this server as their (remote) distribution server. When you add a Publisher, you specify which distribution database (if there is more than one) the Publisher must use. Changing the distribution database that a Publisher uses involves disabling the Publisher, dropping all publications and subscriptions, and then enabling the publishing server as a new Publisher with a different distribution database.

Other options in the Publisher and Distributor Properties dialog box allow you to configure the Publisher.

Ensuring Sufficient Space

Ensure that you have enough storage space for the distribution working folder and the distribution database:

  • For snapshot and merge replication, data is stored in the distribution working folder; the distribution database tracks only status.
  • For transactional replication, the distribution database must be able to store replicated information for all publishing and subscribing servers. Because the distribution database contains all transactions that are waiting to be distributed, the database can grow large.

Regardless of the type of replication you use, you should consider the following factors to determine the size of the distribution database:

  • The total number of tables that are published
  • The number of columns and text and image data types in an article
  • The length of articles
  • The maximum retention time for transactions and history
  • 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:

  • The number of INSERT and UPDATE statements, because each contains data
  • The estimated transaction rate
  • The average transaction size

Deleting a Distribution Database

It is possible to delete a distribution database without disabling the Distributor by first disabling all Publishers that use that particular distribution database.

Use Caution when Uninstalling a Distributor

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:

  • All distribution databases on that server are deleted.
  • All Publishers that use the Distributor are disabled. All publications on those servers are deleted.
  • All subscriptions to the publications are deleted (although subscribed data on the Subscribers is not deleted).

Setting Up a Publisher

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:

  • Specify the databases that will publish data using transactional or merge replication.
  • Enable Subscribers and set Subscriber security and default scheduling options.
  • Manage the publication access list of logins that are able to access the Publisher in order to set up pull and immediate-updating subscriptions. Individual publications can have their own custom list of accounts.

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.

Setting Up a Subscriber

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 and Configuring a Subscriber

Enabling a Subscriber at the Publisher involves

You will learn how to create subscriptions in Lesson 4 of this chapter.

Disabling a Subscriber

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.

Agent Profiles

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.

Practice: Replication Exercises

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.

Exercise 1: Enabling Publishing and Distribution and Creating a Publication

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.

  1. In the console tree of SQL Server Enterprise Manager, click your server.
  2. On the Tools menu, point to Replication and then click Create And Manage Publications.
  3. In the Create And Manage Publications dialog box, click StudyNwind, and then click Create Publication.
  4. Use the Create Publication Wizard and the information in the following table to create your publication. Accept defaults for options not specified.
  5. 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

  6. If you are prompted to have the SQL Server Agent start automatically, click Yes. You will see a dialog box that informs you that the Replication Monitor has been added to the console tree. Click Close to close this dialog box. You will learn about the Replication Monitor in Lesson 1 of Chapter 17.
  7. After the publication has been created, click Close to close the Create and Manage Publications dialog box.
  8. On the Tools menu, point to Replication and then click Configure Publishers, Subscribers And Distribution.
  9. In the Publisher and Distributor Properties dialog box, on the Distributor tab, select Distribution in Databases, and then click Properties.
  10. In the Store The Transactions, At Least option, type 24 to have the distributor store the transactions for at least 24 hours.
  11. Click OK twice to close the Distribution Properties and the Publisher And Distributor Properties dialog boxes.
  12. In the console tree, expand your server, and then expand Replication Monitor.
  13. If prompted, click Yes to turn polling on. Click OK to save the refresh and rate settings.
  14. Expand Publishers, expand your server, and then click StudyNwind_Products_ Publication:StudyNwind. The status of the Snapshot and Log Reader Agents for your new publication is shown in the details pane.

Exercise 2: Creating a New Database

In this exercise, you will create a new database that will be used to create a pull subscription in the next exercise.

  1. In the console tree, right-click Databases, and then click New Database.
  2. In Name, type PullSubs, and then click OK to close the Database Properties dialog box and create the new database.

Exercise 3: Creating a Subscription

In this exercise, you will create a subscription to pull the publication that you published in a previous exercise.

  1. Click your server. On the Tools menu, point to Replication, and then click Pull Subscription To 'SQLSERVER' (or your server name if different).
  2. Select PullSubs, and then click Pull New Subscription.
  3. Use the Pull Subscription Wizard and the information in the following table to pull StudyNwind_Products_Publication from the Publisher.
  4. 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)

  5. When you have completed all of the steps in the wizard, close the Pull Subscription dialog box.
  6. After the subscription has been created, in the console tree, expand your server, expand Management, expand SQL Server Agent, and then click Jobs.
  7. 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

  8. In the console tree, expand Replication Monitor, expand Agents, and then click Distribution Agents. The agent listed in the details pane is the Distribution Agent for your new subscription being monitored by the Replication Monitor. Note that the Last Action for the agent says, "The initial snapshot for article 'ReplProducts_Article' is not yet available." This is because the Snapshot Agent has not yet been run. It is scheduled to run, by default only, at 11:30 p.m. each day. In the next exercise, you will start the Snapshot Agent manually.

Exercise 4: Running the Snapshot Agent

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.

  1. In the console tree, expand Replication Monitor, expand Publishers, expand your server, and then click StudyNwind_Products_Publication:StudyNwind.
  2. In the details pane, right-click Snapshot, and then click Start.
  3. Wait for the Status column to say, "Succeeded," indicating that the Snapshot Agent has generated a snapshot successfully.
  4. Right-click Snapshot, and then click Agent History. In the history list, there will be a single entry indicating the successful generation of a snapshot of 1 article.
  5. Click Session Details. Review the list of actions that were performed when the snapshot was generated. Click Close twice to close both the Snapshot and Agent History dialog boxes.
  6. Use Windows Explorer to review the folders and files that were created below C:\Mssql7\Repldata\Unc. Note the ReplProducts_Article.bcp, ReplProducts_ Article.idx, and ReplProducts_Article.sch files that the Snapshot Agent created and that were referenced in the session details you reviewed in the previous step.
  7. With Notepad, open the ReplProducts_Article.sch file and review the script that was generated. This script contains the table schema creation statements.
  8. With Notepad, open the ReplProducts_Article.idx file and review the script that was generated. This script contains index creation statements.
  9. Switch to SQL Server Enterprise Manager. In the console tree, expand Replication Monitor, expand Agents, and then click Distribution Agents.
  10. 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.

  11. Open SQL Server Query Analyzer. Verify that the article was published to the ReplProducts table by executing the following statement:
  12.  USE PullSubs SELECT * FROM ReplProducts 

Exercise 5: Exploring Changes Due to Replication

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.

  1. Right-click your server in the console tree, and click Disconnect. Many of the steps in this exercise require data to be refreshed in SQL Server Enterprise Manager; by disconnecting before you begin, you force all of the information to be refreshed.
  2. In the console tree, expand your server, and then expand Databases. Note that the distribution database has been added to the list of databases.
  3. 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.

  4. Notice that the StudyNwind database icon has a hand below it, indicating that it has been published. Expand the StudyNwind database. A node called Publications has been added.
  5. Expand Publications, and then click StudyNwind_Products_Publication. In the details pane, all subscriptions to the selected publication are listed.
  6. Expand the PullSubs database. Notice that a node called Pull Subscriptions has been added.
  7. Click Pull Subscriptions. In the details pane, the subscription to StudyNwind_ Products_Publication is listed.
  8. In the details pane, right-click the subscription and then click Properties. Review the properties of your pull subscription.
  9. Click Cancel to close the Pull Subscription Properties dialog box.

Exercise 6: Updating the Published Article

In this exercise, you will update the Products table (on the Publisher) and verify that the updated information is replicated to the Subscriber.

  1. In SQL Server Query Analyzer, type and execute the following:
  2.  USE StudyNwind SELECT * FROM Products WHERE ProductID = 1 

  3. Review the result set. Make a note of the ReorderLevel for the product (it should be 10).
  4. Switch to SQL Server Enterprise Manager, and then expand Replication Monitor. Expand Agents, and then click Log Reader Agents. You must now switch to SQL Server Query Analyzer and type and execute the following script. Immediately after executing the script, switch back to SQL Server Enterprise Manager and watch the Status of the Log Reader Agent.
  5.  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.

  6. In the console tree, click Distribution Agents. Within a few moments, the Status of the Distribution Agent entry should change to Running, and the Last Action should indicate that 1 transaction with 1 command was delivered.
  7. In the console tree, click Log Reader Agents. Right-click SQLSERVER (or your server name, if different), and then click Agent History. Click Session Details to see the details for the current session. You may still see the action indicating that 1 transaction with 1 command was delivered; otherwise, the action will now indicate that no replicated transactions are available. The detail above the action messages includes various statistics for the current session for the Log Reader Agent.
  8. In the console tree, click Distribution Agents. Right-click the publication in the details pane, and then click Agent History. Click Session Details to see the details for the current session. You may still see the action indicating that 1 transaction with 1 command was delivered; otherwise, the action will now indicate that no replicated transactions are available. The detail above the action messages includes various statistics for the current session for the Distribution Agent.
  9. If you wish to see the agents running again, execute the query from step 3 with a different reorder level. You must use a different reorder level or the Distribution Agent will not run, because the Log Reader will detect that no data has actually changed.
  10. Switch to SQL Server Query Analyzer. Execute the following statement to view the reorder level in the ReplProducts table in the PullSubs database and verify that the data was replicated:

 USE PullSubs SELECT * FROM Products WHERE ProductID = 1 

Lesson Summary

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.



Microsoft Press - Microsoft SQL Server 7. 0 System Administration Training Kit
Microsoft SQL Server 7.0 System Administration Training Kit
ISBN: 1572318279
EAN: 2147483647
Year: 1999
Pages: 100
Authors: Microsoft Press
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net