Configuring the Transactional Replication System

3 4

Configuring transactional replication is similar to configuring snapshot replication. First you must configure the publication, and then you configure that publication to be pushed to the subscriber or to be pulled by the subscriber.

NOTE


Before you configure any type of SQL Server replication, you must first configure publishing and distribution. See Chapter 26 for instructions.

Configuring Publications

The process of creating a transactional publication is nearly identical to the process of creating a snapshot publication. To configure a transactional publication, follow these steps:

  1. In Enterprise Manager, click the Tools menu. Next either point to Replication and then choose Create And Manage Publications, or choose Wizards, expand the Replication folder in the Select Wizard dialog box that appears, and select Create Publication Wizard. Performing either procedure will display the Create And Manage Publications dialog box, shown in Figure 27-1. This dialog box allows you to select a database or table containing the data you want to publish.

    click to view at full size.

    Figure 27-1. The Create And Manage Publications dialog box.

    If publications already exist, the following buttons will be available in addition to the Create Publication button:

    • Push New Subscription Enables you to create a new push subscription for an already existing publication. This process is described in the section "Configuring Subscriptions" later in this chapter.
    • Properties And Subscriptions Enables you to modify both publication and subscription properties.
    • Script Publication Enables you to create a SQL script that can be used to create more publications.
    • Delete Publication Enables you to delete an already configured publication.
  2. Select the database you want to use for the publication (in Figure 27-1, Northwind is selected), and then click Create Publication to invoke the Create Publication Wizard. The wizard's welcome screen appears, as shown in Figure 27-2. Select the Show Advanced Options In This Wizard check box.
  3. Click Next to display the Choose Publication Database screen, shown in Figure 27-3. This screen allows you to (again) select the database containing the data that you want to publish. By default, the database that you chose in step 2 is selected.

    click to view at full size.

    Figure 27-2. The Create Publication Wizard welcome screen.

    click to view at full size.

    Figure 27-3. The Choose Publication Database screen.

    NOTE


    If the system you had chosen in step 1 does not already have a distributor defined for it, you will be prompted to select a distributor in the Select Distributor screen. Remember, a publisher can only have one distributor, regardless of the number of publications. If you already have a Distributor defined, you will see the Choose Publication Database screen as described.

  4. Click Next to display the Select Publication Type screen, as shown in Figure 27-4.

    click to view at full size.

    Figure 27-4. The Select Publication Type screen.

    This screen allows you to choose one of three replication types. The options on the Select Publication Type screen are described here:

    • Snapshot Publication Creates a snapshot publication that periodically copies a snapshot of the article to the subscriber. A snapshot publication can be created from any table.
    • Transactional Publication Creates a transactional replication publication that updates the subscription with changes made to the publisher, based on transactions. Articles can be created only from tables with primary keys.
    • Merge Publication Creates a merge replication publication that allows two-way replication between the publisher and subscriber. Articles can be created from any tables.
  5. Click Transactional Publication and click Next to display the Updatable Subscriptions screen, shown in Figure 27-5. This screen appears because we selected the Show Advanced Options In This Wizard check box, shown in Figure 27-2. (If this check box had not been selected, the Specify Subscriber Types screen would have appeared instead.)

    click to view at full size.

    Figure 27-5. The Updatable Subscriptions screen.

    In this screen, you specify how changes made at subscribers are replicated to publishers. The screen's check boxes are described here:

    • Immediate Updating Enables immediate-updating subscriptions. This means that the replication agents will use Microsoft Distributed Transaction Coordinator (MS DTC) to perform two-phase commits on the transactions that update subscribers so that changes can be made on the subscriber and immediately replicated on the publisher. (MS DTC and two-phase commits are described in Chapter 25.) The default is to not allow immediate-updating subscriptions.
    • Queued Updating Enables queued-updating subscriptions. This means updates on the subscriber will be queued until they can be applied to the publisher. This option allows the subscriber to update the database but does not require two-phase commits with the publisher.

    NOTE


    Immediate-updating replication is useful when having identical systems is a requirement, but be aware that the overhead involved in performing a two-phase commit is high. And if both systems are not immediately available, the transaction will not be able to commit. Immediate-updating replication should be used only when absolutely necessary.

  6. Click Next to display the Transform Published Data screen, shown in Figure 27-6. The option to transform data is a new SQL Server feature. Microsoft Data Transformation Services (DTS) is used to transform the replicated data. DTS allows the following transformations to data:

    • Converting data values or types
    • Changing text case
    • Data merging
    • Data splitting

    click to view at full size.

    Figure 27-6. The Transform Published Data screen.

  7. Click Next to display the Specify Subscriber Types screen, shown in Figure 27-7. This screen lets you specify whether all of the subscribers will be running SQL Server. If possible, accept the default setting, which specifies that all subscriber servers are running SQL Server 2000. If you accept this setting, you are configuring replication to use native SQL Server 2000 data types. If SQL Server 7 systems are in the replicated configuration, select the second check box. If you have non-SQL Server systems in the configuration, you should select the third check box, which causes replication data to be converted to character format. This conversion of complex native data types causes additional overhead.

    click to view at full size.

    Figure 27-7. The Specify Subscriber Types screen.

  8. Click Next to display the Specify Articles screen, shown in Figure 27-8. In this screen, you can specify the tables, stored procedures, and views that will be replicated as articles. These articles will make up the publication that you are creating. Select as many tables, stored procedures, and views as you want in the right-hand box, or select one or more check boxes in the Publish All column to select all items of one or more object types in the database. Remember, each object is considered an article, and a publication is a set of articles that are logically grouped together.

    click to view at full size.

    Figure 27-8. The Specify Articles screen.

    NOTE


    If stored procedures exist on the subscriber, replication can be configured to replicate the calls to the stored procedures rather than the results of the stored-procedure calls.

  9. Click Next. At this point, SQL Server checks the publication, and if it finds errors, you will see a screen such as the one shown in Figure 27-9.

    click to view at full size.

    Figure 27-9. The Article Issues screen.

  10. After the publication analysis is completed (and after you click OK to return to the wizard if the informational dialog box appears), click Next to display the Select Publication Name And Description screen, shown in Figure 27-10. In this screen, you specify a publication name and a description.

    click to view at full size.

    Figure 27-10. The Select Publication Name And Description screen.

  11. Click Next to display the Customize The Properties Of The Publication screen, shown in Figure 27-11. In this screen, you specify whether you want to define data filters and customize other properties. Click Yes.

    click to view at full size.

    Figure 27-11. The Customize The Properties Of The Publication screen.

  12. Click Next to display the Filter Data screen, shown in Figure 27-12. In this screen, you specify whether you want to filter the data vertically (filter columns) or horizontally (filter rows). Select both check boxes, and click Next.

    click to view at full size.

    Figure 27-12. The Filter Data screen.

  13. The Filter Table Columns screen, shown in Figure 27-13, appears. This screen allows you to exclude columns from replication. First select the table from the Tables In Publication list, and then, in the Columns In Selected Table list, clear the check boxes beside the columns that you don't want to be replicated. This allows you to vertically filter the article, which will create the replicated table with fewer columns than the table on the publisher has.

    NOTE


    Primary key columns cannot be filtered out because the primary key columns are used in transactional replication, as described earlier in this chapter.

    click to view at full size.

    Figure 27-13. The Filter Table Columns screen.

  14. Click Next to display the Filter Table Rows screen, shown in Figure 27-14. This screen allows you to select tables in which you want to filter the data by rows. Select a table and click the Build […]button to set up the filter.
  15. The Specify Filter dialog box appears, as shown in Figure 27-15. This dialog box allows you to add a WHERE clause to an SQL statement that will filter the row data. When you are finished specifying which rows will be replicated, click OK to return to the wizard.

    click to view at full size.

    Figure 27-14. The Filter Table Rows screen.

    Figure 27-15. The Specify Filter dialog box.

  16. Click Next to display the Allow Anonymous Subscriptions screen, shown in Figure 27-16. This screen allows you to specify whether anonymous subscribers or only known subscribers can access the replication data. Your choice should be based on your configuration and security needs.

    click to view at full size.

    Figure 27-16. The Allow Anonymous Subscriptions screen.

  17. Click Next to display the Set Snapshot Agent Schedule screen. For information about using this screen, see steps 15 and 16 in the subsection "Configuring Snapshot Replication" in Chapter 26. Because in transactional replication the snapshot is used only to initially create the subscriber database, you will probably not need to set a regular schedule for snapshot creation; instead you can create the snapshot manually.
  18. After setting the schedule, click Next to display the Completing the Create Publication Wizard screen, review the summary of your publication, and then click Finish. A dialog box will inform you when the creation of your publication is complete.

Configuring the Log Reader Agent

Once you have created the publication, you might want to modify the behavior of the Log Reader Agent. For example, you can specify how the Log Reader Agent is invoked by selecting which mode it runs in. In continuous mode, which is the default mode, the Log Reader Agent is started when the SQL Server Agent is started. It then connects to the transaction log on the publisher and continually reads the log. In scheduled mode, the Log Reader Agent starts according to a schedule you specify, and it becomes inactive after it reads all of the replicated transactions from the transaction log. By changing the mode and other properties, you can improve performance and reduce the amount of overhead on the publisher. To configure the Log Reader Agent, follow these steps:

  1. In Enterprise Manager, expand a server, expand the Replication Monitor folder, expand the Agents folder, and then click the Log Reader Agents folder.
  2. In the right-hand pane of Enterprise Manager, right-click the publication. The shortcut menu shown in Figure 27-17 appears.

    Figure 27-17. The shortcut menu for a publication.

  3. Choose Agent Properties from the available options. The Log Reader Agent's Properties window appears, as shown in Figure 27-18.

    click to view at full size.

    Figure 27-18. A Log Reader Agent's Properties window.

  4. Click the Steps tab, as shown in Figure 27-19. On this tab, you will see the steps that the Log Reader Agent performs whenever it is invoked. The three steps are listed and described here:

    • Log Agent Startup Message Logs a message into the Log Reader Agent history table (the MSLogreader_history table in the distribution database).
    • Run Agent Starts the agent according to the specified schedule. When running in continuous mode, the agent will run until the system is shut down.
    • Detect Nonlogged Agent Shutdown Puts a message into the Log Reader Agent history table in the event of an agent failure.

    click to view at full size.

    Figure 27-19. The Steps tab of a Log Reader Agent's Properties window.

  5. Select the Run Agent step, and click Edit to display the Edit Job Step dialog box, shown in Figure 27-20. This dialog box allows you to configure how the Log Reader Agent is invoked.

    click to view at full size.

    Figure 27-20. The General tab of the Edit Job Step dialog box.

    Many options can be configured for the Log Reader Agent. The default Log Reader Agent parameters can be modified in the Command box of the Edit Job Step dialog box and in the Replication Agent Profile Details dialog box (Figure 27-22). Two of the parameters you can modify in the Edit Job Step dialog box are described here:

    • Continuous Specifies whether the Log Reader Agent runs in continuous mode or in scheduled mode. To specify scheduled mode, remove this parameter.
    • DistributorSecurityMode Specifies whether the Log Reader Agent uses SQL Server or Microsoft Windows 2000 authentication mode.

    In addition, you can specify other parameters in the Edit Job Step dialog box, such as AsynchLogging, Buffers, DefinitionFile, distributor and publisher information, and MessageInterval.

    MORE INFO


    An explanation of these parameters can be found in SQL Server Books Online. Look up "Log Reader Agent, starting" in the Books Online index.

  6. Once you have finished modifying the Log Reader Agent's properties, click OK to save your changes.

You can modify additional options via the Log Reader Agent's profile. To modify the profile, follow these steps:

  1. In the right-hand pane of Enterprise Manager, right-click the Log Reader Agent and choose Agent Profiles from the shortcut menu that appears. This will invoke the Log Reader Agent Profiles dialog box, shown in Figure 27-21.
  2. Click New Profile in order to create a new profile. The current profile cannot be modified. This will invoke the Replication Agent Profile Details dialog box, shown in Figure 27-22.
  3. In this dialog box, you can modify the following parameters:
    • HistoryVerboseLevel Specifies how much information is logged. The default level is usually sufficient, unless you are experiencing problems.
    • LoginTimeout Specifies the number of seconds that the Log Reader Agent will wait before timing out.
    • PollingInterval Specifies how often the transaction log on the publisher is polled for new transactions.
    • QueryTimeout Specifies how many seconds a query waits before timing out.
    • ReadBatchSize Specifies the number of transactions to be read out of the transaction log in one batch.

click to view at full size.

Figure 27-21. The Log Reader Agent Profiles dialog box.

Figure 27-22. The Replication Agent Profile Details dialog box.

NOTE


As mentioned, if the Log Reader Agent is running in scheduled mode rather than continuous mode, it will be invoked by SQL Server Agent and will read all transactions from the transaction log that have been marked for replication. The Log Reader Agent will read the number of transactions or commands from the transaction log specified by the ReadBatchSize parameter and insert them into the distribution database. Once all of the transactions that have been marked for replication have been read, the Log Reader Agent will become inactive until it is next scheduled to run.

Configuring Subscriptions

As when you configure snapshot replication, the final step when you configure transactional replication is to set up the subscribers. You must first enable subscribers in the distribution database; this process was outlined in the section "Enabling Subscribers" in the previous chapter. Then you configure subscriptions from either the subscriber or the publisher. From the subscriber, you can configure a pull subscription; from the publisher, you can configure a push subscription.

Configuring Pull Subscriptions

Pull subscriptions are controlled by and configured from the subscriber. Thus, you must configure the pull subscription using Enterprise Manager on the subscriber system. To configure a pull subscription, follow these steps:

  1. In Enterprise Manager, click the Tools menu. Next, either point to Replication, choose Pull Subscription To, and then click Pull New Subscription in the Pull Subscription To dialog box that appears; or, from the Tools menu choose Wizards, expand the Replication folder in the Select Wizard dialog box that appears, and then select Create Pull Subscription Wizard and click OK. Either way, the Pull Subscription Wizard welcome screen appears, as shown in Figure 27-23. Note the check box that lets you specify whether advanced options are shown in the wizard. For this example, we'll select the check box. This will allow data transformation to be enabled.
  2. Click Next to display the Look For Publications screen shown in Figure 27-24. This screen prompts you to determine where you want to look for a publication. You have the option to select from standard Windows 2000 networking or the Active Directory service. Select the default, which specifies that you should look at publications from registered servers.

    click to view at full size.

    Figure 27-23. The Pull Subscription Wizard welcome screen.

    click to view at full size.

    Figure 27-24. The Look For Publications screen.

  3. Click Next to display the Choose Publication screen, shown in Figure 27-25. This screen is used to identify the publication that will be used in the replication. Servers that are registered with your SQL Server system are listed here. Select the publication that you want to replicate.

    click to view at full size.

    Figure 27-25. The Choose Publication screen.

  4. Click Next to display the Specify Synchronization Agent Login screen, shown in Figure 27-26. In this screen, you specify the SQL Server login ID and password for the distributor.

    click to view at full size.

    Figure 27-26. The Specify Synchronization Agent Login screen.

  5. Click Next to display the Choose Destination Database screen, shown in Figure 27-27. In this screen, you specify which database you want the replicated articles to be placed in. If you want to create a new database, click New to open the Database Properties window.

    click to view at full size.

    Figure 27-27. The Choose Destination Database screen.

  6. Click Next to display the Initialize Subscription screen, shown in Figure 27-28. Click Yes to initialize the database schema and data at the subscriber. If you have previously created the schema, click No.

    click to view at full size.

    Figure 27-28. The Initialize Subscription screen.

  7. Click Next to display the Snapshot Delivery screen, shown in Figure 27-29. Here you can specify a snapshot folder different from the default snapshot folder. If you have not modified the snapshot folder, accept the default.

    click to view at full size.

    Figure 27-29. The Snapshot Delivery screen.

  8. Click Next to display the Set Distribution Agent Schedule screen, shown in Figure 27-30. This screen enables you to select continuous updates, scheduled updates, or updates on demand. In most cases, the scheduled updates option is the preferred option.

    click to view at full size.

    Figure 27-30. The Set Distribution Agent Schedule screen.

    When deciding how updates will be handled on your system, remember that the more often the Distribution Agent runs, the more overhead there is on both the distributor and subscriber. Run the agent as much as necessary, but don't run it excessively.

    In order to change the schedule for the Distribution Agent, click Change and modify the schedule in the Edit Recurring Job Schedule dialog box that appears.

    NOTE


    If you had chosen to transform the publication, you would have been presented with the Specify DTS Package screen at this point. In order to proceed from this point you must have created a DTS package. If you do not have a package available, you must stop, create one, and then run the wizard again. For our example we are not transforming data.

  9. Click Next to display the Start Required Services screen, shown in Figure 27-31. You can start the SQL Server Agent from this screen, if it is not running already. This screen shows you whether the SQL Server Agent is running on the subscriber. If the SQL Server Agent is not running, you will be prompted to start it. If you want to start the SQL Server Agent manually, expand the Management folder in Enterprise Manager, right-click on SQL Server Agent and use the options on the shortcut menu that appears to start and stop the SQL Server Agent.

    click to view at full size.

    Figure 27-31. The Start Required Services screen.

  10. Click Next to display the Completing The Pull Subscription Wizard screen, shown in Figure 27-32. Click Finish to complete the tasks of setting up the subscriber.

    click to view at full size.

    Figure 27-32. The Completing The Pull Subscription Wizard screen.

The articles will now be replicated on the subscriber and will be regularly updated according to the schedule you have set up. You might have to verify the schedule that the publication agents are running on before the replication can begin. Because the Snapshot Agent runs on its own schedule, if you did not configure it to immediately propagate the snapshot to the distributor, the data might take some time to reach the distributor. Even though replication is working, the actual data will not reach the subscriber until the Snapshot Agent has done its job.

Configuring Push Subscriptions

A push subscription is initiated on the publisher. You configure a push subscription by using the Push Subscription Wizard. When a push subscription is used, the schedule on which the replication occurs is determined on the distributor. To run the Push Subscription Wizard, follow these steps:

  1. Invoke the Push Subscription Wizard by using either of two methods. To use the first method, in Enterprise Manager, point to Replication on the Tools menu, and then choose Push Subscription To Others. The Create And Manage Publications dialog box appears, as shown in Figure 27-33. Select a publication in the Databases And Publications list box, and then click Push New Subscription. To use the second method, choose Wizards from the Tools menu, expand the Replication folder in the Select Wizard dialog box that appears, select Create Push Subscription Wizard, and click OK. Select a publication in the Create And Manage Publications dialog box that appears, and then click Push New Subscription.

    click to view at full size.

    Figure 27-33. A publication selected in the Create And Manage Publications dialog box.

    The Push Subscription Wizard welcome screen appears, as shown in Figure 27-34.

    click to view at full size.

    Figure 27-34. The Push Subscription Wizard welcome screen.

  2. Click Next to display the Choose Subscribers screen, as shown in Figure 27-35. In this screen, you select the system that will be the recipient of the publication you just selected.

    click to view at full size.

    Figure 27-35. The Choose Subscribers screen.

  3. Click Next to display the Choose Destination Database screen, shown in Figure 27-36. In this screen, you specify the database that will accept the publication on the subscriber. You can choose to use a database that already exists, or you can create a database, depending on your system configuration and your needs.
  4. Click Next to display the Set Distribution Agent Location screen, shown in Figure 27-37. Here you can choose to run the distribution agent on the distributor (default and recommended) or to run it on the subscriber. For those of you familiar with transactional replication under SQL Server 7, this is a new option.

    click to view at full size.

    Figure 27-36. The Choose Destination Database screen.

    click to view at full size.

    Figure 27-37. The Set Distribution Agent Location screen.

  5. Click Next to display the Set Distribution Agent Schedule screen, shown in Figure 27-38. Here you can choose to have the subscription continually updated or to have it updated based on a schedule you specify. Click the Using The Following Schedule option, and then click Change to display the Edit Recurring Job Schedule dialog box. Here you can easily configure a recurring schedule. When deciding how often the subscription should be updated, keep in mind that continuous updates use a lot of overhead. Note that the replicate-on-demand option is not available for push subscriptions.

    click to view at full size.

    Figure 27-38. The Set Distribution Agent Schedule screen.

  6. Click Next to display the Initialize Subscription screen, shown in Figure 27-39. In this screen, you specify whether the subscription needs to be initialized. The option to initialize the schema and data set on the subscriber is selected by default. In this screen, you can also start the Snapshot Agent if it is not already started. It's a good idea to start the Snapshot Agent when you initialize the snapshot; otherwise, you must start the agent by hand. Once the snapshot has been initialized and replication begins, you don't need to use a snapshot until the next time you create a subscription. Each time you create a subscription, create a new snapshot, and don't bother creating snapshots on a regular schedule unless you plan on resynchronizing the subscriber database using the snapshots.

    click to view at full size.

    Figure 27-39. The Initialize Subscription screen.

  7. Click Next to display the Start Required Services screen, shown in Figure 27-40, You can specify that SQL Server Agent start automatically if it is not already started.

    click to view at full size.

    Figure 27-40. The Start Required Services screen.

  8. Click Next to display the Completing The Push Subscription Wizard screen, shown in Figure 27-41. Review your settings and then click Finish to begin the copying of the snapshot to the subscriber. Once you have completed this wizard, the push subscription is created and will be updated on a regular basis.

    click to view at full size.

    Figure 27-41. The Completing The Push Subscription Wizard screen.

MORE INFO


Please refer to "Managing Replication" in Chapter 26 for information about managing and troubleshooting replication; monitoring and managing replication agents; disabling replication; and removing subscriptions, distribution, and publications.



Microsoft SQL Server 2000 Administrator's Companion
Microsoft SQL Server 2000 Administrators Companion
ISBN: B001HC0RPI
EAN: N/A
Year: 2005
Pages: 264

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