Configuring Snapshot, Merge, and Transactional Replication

Article
01/28/2010

Updated : July 30, 2001

Data replication allows you to distribute data from a source database to one or more destination databases. The source and destination databases can be on other Microsoft SQL Servers or on other databases as long as an OLE DB provider is available for each destination database. You have precise control over when replication occurs, what data is replicated, and how other aspects of replication are handled. For example, you can configure replication to happen continuously or periodically. Before we examine how to implement replication, let's look at why you'd want to use replication and review its key concepts.

Note: Replication is a complex subject. The discussion in this chapter is not meant to be exhaustive. Instead, as in other chapters, I focus on core administration—the replication concepts and tasks you'll use most often. In the field, I've used the tasks discussed in this chapter to configure replication on hundreds of servers. The tasks work well in most environments but they don't cover every nitty-gritty detail. See the Microsoft SQL Server 2000 Administrator's Companion (Microsoft Press, 2000) for more detailed information and alternative scenarios on replication.

An Overview of Replication

You use replication to copy data on one server and distribute it to other servers. You can also use replication to copy data, transform it, and then distribute the customized data to multiple servers. You generally use replication when you need to manage data on multiple servers on a recurring basis. If you need to create a copy of a database just once, you don't need replication and instead you should copy the database as discussed in the section of Chapter 4 entitled "Manipulating Databases" or in the section of Chapter 11 entitled "Restoring a Database to a Different Location." If you need to copy and transform data from one server to another server, you don't need replication either, and instead you should use the Data Transformation Services (DTS) discussed in Chapter 7. Some reasons to use replication include:

To synchronize changes to remote databases with a central database. For example, if the sales team uses remote laptops, you may need to create a copy of data for their sales region on the laptop. Later, a salesperson in the field may add information or make changes while disconnected from the network. Through replication these modifications would then need to be synchronized with the central database.
To create multiple instances of a database so that you can distribute the workload. For example, if you have a central database that's updated regularly, you may want to push changes out to departmental databases as they occur. Employees can then access data through these departmental databases instead of trying to connect to the central database.
To move specific data sets from a central server and distribute them to several other servers. For example, you'd use replication if you had a central database and needed to distribute sales data to all the databases in your company's department stores.
To customize data and distribute it to multiple subscribers. For example, if your company sold subscriptions to your consumer credit database, you could replicate the data with subscribers, performing customization on the data as necessary for each subscriber.

Replication is designed to meet the needs of a wide variety of environments. Replication architecture is broken down into several different processes, procedures, and components, each of which is used to tailor replication for a given situation. The replication architecture includes

Replication components The server and data components used in replication
Replication agents Applications that assist in the replication process
Replication variants The types of replication you can configure

Replication Components

Before working with replication, you'll need to know the key terms and how you use them. Servers in the replication model can have one or more of the following roles:

Publisher Publishers are servers that make data available for replication to other servers. Publishers also track changes to data and maintain other information about source databases. Each data grouping has only one publisher.
Distributor Distributors are servers that distribute replicated data. Distributors store the distribution database, metadata, historical data, and (for transactional replication) transactions.
Subscriber Subscribers are the destination servers for replication. These servers store the replicated data and receive updates. Subscribers can also make changes to data. You can publish data to multiple subscribers.

The data being published for replication are referred to as articles and publications. Articles are the basic unit for replication and can consist of a table, a subset of a table, or other database objects. Publications are collections of articles that subscribers can receive. You should associate articles with a publication and then publish the publication. Articles can contain

An entire table
Only certain columns from a table, obtained by using a vertical filter
Only certain rows from a table, obtained by using a horizontal filter
A table subset containing certain rows and columns
A view, indexed view, or user-defined function
A stored procedure

You can also specify whether schema objects are replicated. Schema objects include constraints, indexes, triggers, collation, and extended properties. You can't publish any of the following for replication:

The model, tempdb, and msdb databases
System tables in the master database

In the publication and subscription model, replication involves the following steps:

Selecting a replication type and model
Performing any necessary preliminary tasks
Configuring a distributor and enabling publishers, publication databases, and subscribers
Creating a publication
Creating subscriptions to the publication

Replication Agents

SQL Server uses various helper applications to assist in the replication process. These applications are called replication agents and they include

Snapshot agent Creates snapshots of data. It includes schema and data, which are stored for distribution. The snapshot agent is also responsible for updating status information in the distribution database. The snapshot agent runs on the distributor. Each published database has its own snapshot agent that runs on the distributor and connects to the publisher. Snapshot agents are used with all types of replication.
Distribution agent Responsible for applying data from snapshot replication or transactions from transaction replication to subscribers. The distribution agent can run on the distributor or on subscribers. This agent isn't used with merge replication.
Merge agent Used to synchronize changes that occur after the initial snapshot is created. If any conflicts occur when the changes are being synchronized, the conflicts are resolved using the rules set with the conflict resolver. Depending on the configuration, merge agents run on the publisher or on subscribers. Merge agents are used only with merge replication.
Log reader agent Moves transactions marked for replication from the transaction log on the publisher to the distributor. Each database that's published using transactional replication has its own log reader agent that runs on the distributor and connects to the publisher. Log reader agents are used only with transactional replication.
Queue reader agent Stores database changes in a queue where the updates can be asynchronously propagated to the publisher. This allows subscribers to modify published data and synchronize those changes without having an active network connection to the publisher. Queue reader agents are used only with snapshot and transactional replication.
Clean up agent Performs replication maintenance tasks. There are several different types of clean up agents. Each clean up agent has a very specific purpose, such as removing replication agent history from distribution databases or detecting and removing expired subscriptions from distribution databases.

Replication Variants

SQL Server supports several different types of replication. These replication variants are

Snapshot replication Takes a snapshot of current data and replaces the entire copy of the data on one or more subscribers. With subsequent snapshots, the entire copy of the data is again distributed to subscribers. While exact copies are a strong point of snapshot replication, this technique increases the amount of overhead and traffic on the network. Another weak point of snapshot replication is that it only runs periodically, which usually means that subscribers don't have the most current information.
Transactional replication Uses transactions to distribute changes. When replication is first started, a snapshot of the data is sent to subscribers. After that, selected transactions in the publisher's transaction log are marked for replication and then distributed to each subscriber separately. Snapshots are then taken on a periodic basis to ensure that the databases are synchronized. Distributed transactions are used to ensure that incremental changes are applied consistently. A benefit of transactional replication is that you replicate individual transactions rather than an entire data set. Transactional replication can also occur on a continuous or periodic basis, which makes the procedure more versatile than snapshot replication by itself.
Merge replication Allows subscribers to make changes to replicated data independently. Later, you can merge these changes into all of the related source and destination databases. Merge replication doesn't use distributed transactions and can't guarantee transactional consistency. Instead, merge replication uses a conflict resolver to determine which changes are applied.

In snapshot and transactional replication, subscribers normally don't change data. However, you have several options for allowing subscribers to change data:

Immediate updating Allows subscribers to make changes and then immediately update the publisher. The publisher then replicates these changes to other subscribers.
Queued updating Allows subscribers to make changes and then store those changes in a queue until they can be applied to the publisher. The publisher then replicates the changes to other subscribers. Immediate and Queued updating is only supported in Snapshot and Transactional publications.

Queued updating provides fault tolerance that may be needed when databases are geographically separated. While immediate updating requires an active connection to the publisher, queued updating doesn't. With queued updating subscribers can asynchronously apply changes, which means that they can store changes when a link is inactive and then, when the link is active, they can submit the changes to the publisher.

You can also use immediate updating with queued updating as a failover when you expect publishers and subscribers to be connected but don't want to lose the ability to make updates if a link fails. Here, you configure both updating options, using immediate updating as the primary update mechanism and then switching to queued updating when needed. You can invoke failover at any time. However, you can't fail back afterward until the subscriber and publisher are connected and the Queue Reader agent has applied all pending updates in the queue.

Both immediate updating and queued updating use transactions and the standard two-phase commit process to apply updates to the publisher. Transactions ensure that the update can be committed if it's successfully applied or rolled back if there's a problem. The transactions are applied from a specific subscriber to the publisher. After changes are made to the publisher, the publisher replicates the changes to other subscribers.

Transactions are completed automatically through the update process and managed by the Distributed Transaction Coordinator. Custom applications that modify subscriber data can be written as though they're updating a single database. To prevent changes that can't be replicated, updates to the subscriber are applied only when they can be replicated through a transaction. If the update can't be replicated through a transaction, the subscriber won't be able to modify the subscription data.

SQL Server detects subscriber changes that would conflict with changes on the publisher. If it detects a conflict, it rejects the transaction and doesn't allow the data changes. Usually, a rejection means that the subscriber needs to synchronize with the publisher before attempting to update the data locally.

When you include stored procedures as articles in a snapshot publication, SQL Server replicates the entire stored procedure from the publisher to the subscribers. Changes caused by the execution of those stored procedures are replicated with new snapshots. With transactional replication, on the other hand, you can replicate execution of the stored procedure instead of replicating the changes that the execution causes. By sending an execute command rather than data changes, you reduce the amount of data that needs to flow across the network and improve the performance of SQL applications.

If you replicate the execution of stored procedures, you have two configuration settings. You can use standard procedure execution, or you can use serialized procedure execution. With standard procedure execution, procedure execution is replicated to all subscribers, even if those procedures are executed in different transactions. Because multiple transactions may be executing at a particular time, subscribers' data can't be guaranteed to be consistent with the publisher's. With serialized stored procedures, procedures are executed in sequence as long as they are referenced within serialized transactions. If the procedures are executed outside of serialized transactions, changes to the data are replicated instead. This behavior guarantees that the subscribers' data is consistent with the publisher's.

Planning for Replication

As you've seen, the replication architecture is fairly extensive. The reason for that is to ensure that the architecture is versatile enough to meet the needs of just about any replication situation. Unfortunately, this versatility also makes replication tricky to configure. To make the replication go smoothly, you should do a bit of planning, which involves selecting a specific replication model and performing any necessary preliminary tasks before you start configuring replication.

Replication Models

The key decision you make when you select a replication model involves the physical layout of the publisher, distributor, and subscriber databases. Replication models you may want to use are

Central publisher model A central publisher maintains the publisher and distributor databases on the same server, with one or more subscribers configured on other servers. The advantages of this model are manageability and ease of maintenance. The disadvantages are the extra workload and resource usage on the publication server.

Tip The central publisher model is the most commonly used replication model. Unfortunately, you'll often find that the extra load on the publication server hampers server performance. To reduce the server load, you'll want to put the distributor on its own server. Keep in mind, though, that doing so won't entirely eliminate the workload on the publication server. The publisher and distributor still need to communicate, and they still need to pass data back and forth.
Central publisher with remote distributor model A remote distributor maintains the publisher and distributor databases on different servers, with one or more subscribers configured on other servers. The advantage of this model is that you distribute the workload. The disadvantage is that you have to maintain an additional server.
Central subscriber model A central subscriber is a single subscriber database that collects data from several publishers. For example, if you have ServerA, ServerB, and ServerC, ServerA and ServerB would act as central publishers and ServerC would act as the central subscriber. Now, when updates are distributed from ServerA and ServerB, they are collected on ServerC. A central subscriber could then republish the combined data to other servers. To use this model, all tables used in replication must have a unique primary key. Otherwise the replication model won't work properly.
Publishing subscriber model A publishing subscriber relays the distribution of data to other subscribers, and you can use it with any of the other models. For example, if you have two geographically separated sites, a publisher could replicate data to servers at site A and then have a publishing subscriber at site B that distributes the data to servers at site B.

Preliminary Replication Tasks

After selecting the replication type and model you want to use, you prepare for the replication by performing preliminary tasks. The following sections describe key tasks you should consider, according to replication type.

Preparing for Snapshot Replication

With snapshot replication, the data being replicated is copied in full to data files on the distributor. Normally these snapshot files are the same size as the data you're replicating and are stored in the SQL Server Repldata folder by default. You should make sure that the drive on which the replication data is stored has enough free space. For example, if you're using snapshot replication to distribute publication A with 5 MB of data, publication B with 42 MB, and publication C with 9 MB, you'll need at least 56 MB of free space.

You can also store snapshot data in an alternate location where subscribers can retrieve it at a later time. If you use an alternate location you have the option of compressing the snapshot file. Compressing a snapshot file reduces the disk space requirements only for the files you're compressing. It doesn't change the overall space requirements, and it doesn't always reduce the initial and final space requirements. With compression, the snapshot agent generates the necessary data files and then uses the Microsoft CAB utility to compress the files. When the subscriber receives compressed snapshot files, the files are written to a temporary location, which is either the default client working directory or an alternate location specified in the subscription properties. The subscriber uses the CAB utility to decompress the files before reading them.

Real World When you create snapshot files in the default location and in an alternate location on different drives, the files are created separately. This means that the total disk space requirements are typically what you would expect. However, when you create snapshot files in the default location and in an alternate location on the same drive, both files are initially created in the default location and then the alternate location file is copied to its final destination. This means that the total disk space requirements in the default location are twice what you would expect. Compression doesn't help. The snapshot agent generates the necessary data files and then compresses them.

Replication timing is another important consideration in snapshot replication. When the snapshot agent creates a snapshot of a published table, the agent locks the entire table while it bulk copies the data from the publisher to the distributor and, as a result, users can't update any data in the table until the lock is released. To reduce the impact on operations, you should carefully schedule when replication occurs. Some actions that may help include

Identifying times when operations are at their lowest levels or users don't need write access to the tables you're replicating
Identifying times when snapshots must be made and scheduling users to do work that doesn't require write access to the tables you're replicating

Preparing for Transactional Replication

Because transactional replication builds on the snapshot replication model, you'll want to prepare for both snapshot and transactional replication. With transactional replication, an initial snapshot is sent to the distributor and this snapshot is then updated on a periodic basis, such as once a week. In between snapshots, transactions are used to update subscribers. These transactions are logged in the distributor's database and are cleared out only after a new snapshot is created.

Transaction logs for published databases are extremely important to successful replication. As long as replication is enabled, pending transactions can't be cleared out of a published database until they've been passed to the distributor. Because of this, you may need to increase the size of a published database's transaction log. Furthermore, if the publisher can't contact the distributor or if the log reader agent isn't running, transactions will continue to build up in the publisher's transaction logs.

With transactional replication, all published tables must have a declared primary key. You can add a primary key to an existing table using the ALTER TABLE statement (see Chapter 6). Additionally, if a publication uses text or image data types, you must make sure that you keep the following limitations in mind:

With WRITETEXT and UPDATETEXT statements, you must use the WITH LOG option on tables that are published for replication. You can't use the WITH NO_LOG option. While WRITETEXT is supported with any compliant database, UPDATETEXT is supported only on subscribers that are running SQL Server version 6.0 or later. Furthermore, you must retrieve text pointers immediately prior to executing the UPDATETEXT or WRITETEXT statement in a transaction.
The MAX TEXT REPL SIZE SQL Server configuration option controls the maximum byte size of the text and image data that can be replicated. Operations that exceed this limit will fail. Set the maximum text replication size with the sp_configure system stored procedure.

Preparing for Merge Replication

With merge replication, all published tables must have primary keys. If a table contains foreign keys or is used in validation, you must include the reference table in the publication. Otherwise update operations that add new rows will fail because SQL Server can't find the required primary key. Additionally, merge replication affects timestamp column usage. Timestamps are generated automatically and are guaranteed to be unique only in a specific database. Because of this, SQL Server replicates timestamp columns but doesn't replicate the literal timestamp values contained in the columns. These values are regenerated when the initial snapshot rows are applied at the subscriber.

Like transactional replication, merge replication has a few limitations when it comes to text and image columns. You must explicitly update text and image columns with an UPDATE statement. If you use WRITETEXT or UPDATETEXT to update text columns, you must follow the WRITETEXT or UPDATETEXT statement with a dummy UPDATE statement within the same transaction. Otherwise the changes may not be distributed.

With merge replication, subscribers can make changes to replicated data independently and these changes can be merged into all of the related source and destination databases. The merge agent watches for changes that conflict with other changes. If it detects a conflict, a conflict resolver is used to determine which change is applied and which change is rolled back. The merge agent can track changes at a column level or at a row level. With column-level tracking, a conflict exists when changes are made to the same column in a table in more than one copy. With row-level tracking, a conflict exists when changes are made to the same row in a table in more than one copy.

Normally, subscribers to merge publications only synchronize updates with the publisher. Subscribers can also synchronize with other servers, and they do this by designating alternate synchronization partners. Having an alternate synchronization partner is useful when you need to ensure that updates can be made even if the primary publisher is offline or otherwise unavailable.

Distributor Administration

As the name indicates, you use distributors to distribute replicated data. When you work with distributors, the core set of administration tasks you'll perform includes setting up a new distributor, updating an existing distributor, and deleting distributors.

Setting Up a New Distributor

Setting up a new distributor is the first major step in configuring replication. Before you get started, you should

Select a replication type—either snapshot, transactional, or merge.
Select a replication model, such as the central publisher model.
Perform any necessary preliminary tasks. To meet certain limitations, you may need to update clients and applications that modify published databases directly.

When you're ready to proceed, configure the distributor by completing the following steps:

Start Enterprise Manager and then double-click the database server instance that you want to use as the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This starts the Configure Publishing And Distribution Wizard.
Click Next to move past the Welcome screen. As shown in Figure 9-1, you can now select a distributor.
Because you want to set up a new distributor, choose Make <ServerName> Its Own Distributor and then click Next.
If SQL Server Agent is currently configured to use the System account, you'll see a dialog box prompting you to configure the agent to use a domain account. Click OK to display the SQL Server Agent Properties dialog box. Select This Account, type a domain account name and password in the fields provided, and then click OK again.

Figure 9-1: Use the Configure Publishing And Distribution Wizard to configure replication. As shown here, the first step is to select a distributor.
Distributors use SQL Server Agent to handle replication tasks. If SQL Server Agent isn't configured to start automatically, you'll need to specify whether to automatically start the SQL Server Agent service for the currently selected database server instance. Choose the Yes option to allow automatic startup. Otherwise choose the No option and then manually start the SQL Server Agent service each time you start the server.
Next, specify where the snapshot folder will be stored. This folder is used to store snapshots from publishers that use the distributor. You must use a network path anytime distributor and merge agents run at subscribers. Otherwise, the agents won't be able to access snapshots.
As shown in Figure 9-2, you can let the wizard configure the remaining setup options for you by selecting the No, Use The Following Default Settings option button. Afterward, click Next and then click Finish. Skip the remaining steps.

Figure 9-2: Choose Yes to configure the distribution options yourself or No to use the default options shown.
To configure the options yourself, choose the Yes option and then continue with the configuration.
Provide information for the distribution database using the dialog box shown in Figure 9-3. Enter a name for the distribution database and then set folder locations for the corresponding data and log files. You can't use mapped network drives.

Note: Be sure to use a descriptive name for the database, such as EmployeeDistribution or EmpDistr.

Figure 9-3: Configure the distribution database name and the location of data and log files.
As shown in Figure 9-4, you need to configure publishers for this distribution database. Only registered servers are shown. If you want to register a new server, click New.

Figure 9-4: Enable publishers and set server properties, if necessary.

To the right of registered server entries, you'll see a button with three dots (…). Click this button to set publisher options for the related server. As Figure 9-5 shows, you have the following options available:
- Data Storage At The Distributor Sets the location of the folder used to store snapshots. The Snapshot folder is stored on the distribution database and can be in a different location for each publisher that uses the distribution database.
- Replication Agent Connection To The Publisher Determines how replication agents on the distributor log in to the publisher. Select Impersonate The SQL Server Agent Account to use a trusted connection. Otherwise select the Use SQL Server Authentication and then type the necessary username and password.
- Administrative Link To The Distributor Determine whether the selected publisher is trusted. If you want to require a password, choose This Publisher Requires A Password To Establish A Link To The Distributor. Otherwise clear this option. (This option is available only if the selected publisher and distributor are located on different servers.)
Figure 9-5: You can set additional properties for each publisher using the distribution database. Use this dialog box to configure the available options.
When you've finished enabling publishers, click Next (see Figure 9-4). As shown in Figure 9-6, you can now enable publication databases for replication. Select entries under the Trans column to enable a database for snapshot or transactional replication or both. Select entries under the Merge column to enable a database for merge replication. Click Next when you're ready to continue.

Tip To enable a publication database for any type of replication, select both of the corresponding Trans and Merge check boxes.

Figure 9-6: Enable publication databases. Database owners can create publications only after you enable the database for publishing.
Enable subscribers to the distribution database as shown in Figure 9-7. Again, only registered servers are shown. If you want to register a new server, click New.

To the right of registered server entries, you'll see a button with three dots (…). Click this button to set subscriber options for the related server. The Properties dialog box has two tabs:
- General Use these options to add a description for the subscriber and to determine how replication agents link to the subscriber. Select the option named Impersonate The SQL Server Agent Account to use a trusted connection. Otherwise select Use SQL Server Authentication and then type the necessary username and password.
- Schedules Use these options to configure when distribution and merge agents run. You can configure these agents to run continuously, which ensures that updates are made continuously, or at specific times, which limits updates to specific time intervals. For detailed information on scheduling jobs, see Chapter 12, "Database Automation and Maintenance."
Figure 9-7: Enable subscribers and set their properties, if desired.

Note: While agents can run updates continuously, the updates aren't immediate. There is always some latency with replication.
When you're ready to continue, click Next, and then click Finish to complete the configuration. The progress of each step of the configuration process is shown in a dialog box. If the process completes successfully, click OK. If errors occur, you'll need to resolve them before you can complete the configuration.

Note: The distributor must run the SQL Server Agent service. This service should be configured to run automatically. If it isn't, you'll see an error prompt. Click Yes to configure the service to start automatically. Otherwise click No (but you'll need to remember to start the service each time you restart the distributor).
On the distributor, a Replication Monitor is added to the folder list in Enterprise Manager. To learn how to work with this SQL Server feature, see Chapter 10, "Profiling and Monitoring Microsoft SQL Server 2000."
Configure publications and subscriptions as explained in the sections of this chapter entitled "Enabling Publishers," "Enabling Publication Databases," and "Enabling Subscribers."

Updating Distributors

When you configure a new distributor, you can set up a new distribution database, as discussed in the section of this chapter entitled "Setting Up a New Distributor." If you've already configured a distributor, you can update the distributor and create additional distribution databases by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This opens the Publisher And Distributor Properties dialog box shown in Figure 9-8.

You can use the options in the dialog box to change publisher, subscriber, and distributor data properties for the currently selected distributor. You use the dialog box tabs as follows:
- Distributor Configure distribution databases, agent profiles, and passwords for administrative links
- Publishers Enable and disable publishers for the distributor
- Publication Databases Enable and disable publication databases and specify which types of replication are allowed
- Subscribers Enable and disable subscribers
Figure 9-8: The Publisher And Distributor Properties dialog box controls all the distributor options. You can use it to create new distribution databases and to perform many other administration tasks.

Creating Distribution Databases

Distribution databases are used to store the information being distributed to subscribers. Each publisher that uses a distributor is assigned a distribution database to which it can connect. Publishers can share distribution databases, and you can create additional databases as necessary. If you've already configured a distributor, you can create additional distribution databases by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This opens the Publisher And Distributor Properties dialog box shown previously in Figure 9-8.
In the Distributor tab, click New. You can now configure the distribution database.
Enter a name for the distribution database and then set folder locations for the corresponding data and log files. You can't use mapped network drives.
Use the options in the Transaction Retention and History Retention areas to determine how long transactions and performance history are retained for the distribution database.
Click OK to create the distribution database.

Enabling Publishers

Distributors can work only with servers and databases that are enabled for their use. You can enable publishers when you create a new distributor or by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This opens the Publisher And Distributor Properties dialog box shown previously in Figure 9-8.
To enable publishers, click the Publishers tab. Use the check boxes provided to enable or disable publishers. Only registered publishers are shown. Click New to register a new publisher.

To the right of registered server entries, you'll see a button with three dots (…). Click this button to set publisher options for the related server. You have the following options:
- Data Storage At The Distributor Sets the location of the folder used to store snapshots. The Snapshot folder is stored on the distribution database and can be in a different location for each publisher that uses the distribution database.
- Replication Agent Connection To The Publisher Determines how replication agents on the distributor log in to the publisher. Select Impersonate The SQL Server Agent Account to use a trusted connection. Otherwise select the Use SQL Server Authentication and then type the necessary username and password.
- Administrative Link To The Distributor Determines whether the selected publisher is trusted. If you want to require a password, choose This Publisher Requires A Password To Establish A Link To The Distributor. Otherwise clear this option. (This option is available only if the selected publisher and distributor are located on different servers.)

Enabling Publication Databases

You can enable publication databases when you create a new distributor or by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This opens the Publisher And Distributor Properties dialog box shown previously in Figure 9-8.
To enable publication databases, click the Publication Databases tab. Then select entries under the Trans column to enable a database for snapshot or transactional replication (or both) or select entries under the Merge column to enable a database for merge replication.
To enable a publication database for any type of replication, select both the corresponding Trans and Merge check boxes.

Enabling Subscribers

You can enable subscribers when you create a new distributor or by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, and Distribution. This opens the Publisher And Distributor Properties dialog box shown previously in Figure 9-8.
To enable subscribers, click the Subscribers tab. Use the check boxes provided to enable or disable subscribers.

To the right of registered server entries, you'll see a button with three dots (…). Click this button to set subscriber options for the related server. The Properties dialog box has two tabs:
- General Use these options to add a description for the subscriber and to determine how replication agents link to the subscriber. Select the option named Impersonate The SQL Server Agent Account to use a trusted connection. Otherwise select Use SQL Server Authentication and then type the necessary username and password.
- Schedules Use these options to configure when distribution and merge agents run. You can configure these agents to run continuously, which ensures that updates are made continuously, or at specific times, which limits updates to specific time intervals. For detailed information on scheduling jobs, see Chapter 12, "Database Automation and Maintenance."
If you want to use a subscriber that isn't listed, click New. You can then choose the type of subscriber you want to work with and configure the subscriber, as necessary. The types of subscribers you can choose from are
- SQL server database Select SQL Server Database and then click OK. You can now register a new SQL Server to use as a subscriber.
- Microsoft Jet 4.0 database (Microsoft Access) Select Microsoft Jet 4.0 database (Microsoft Access) and then click OK. Jet 4.0 databases must be registered as linked servers on the distributor. If they aren't, click Add in the Enable Subscriber dialog box to create a new linked server as described in Chapter 8.
- OLE DB data source Select OLE DB Data Source and then click OK. OLE DB data sources must be registered as linked servers on the distributor. If they aren't, click Add in the Enable Subscriber dialog box to create a new linked server.
- ODBC data source Select ODBC Data Source and then click OK. ODBC data sources must be configured as data source names (DSNs) on the distributor. If the data source you want to use isn't available, use the ODBC Data Sources Control Panel utility to create a DSN.

Deleting Distribution Databases

Before you can delete a distribution database, you must remove all publications and disable all the publishers using the distribution database. Once you've done this, you can delete distribution databases by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Configure Publishing, Subscribers, And Distribution. This opens the Publisher And Distributor Properties dialog box.
In the Distributor tab, select the distribution database you want to delete and then click Delete.

Disabling Publishing and Distribution

Through Enterprise Manager's Disable Publishing And Distribution Wizard, you can disable publishing and distribution. When you disable publishing,

All publications on the selected server are dropped
All subscriptions to the affected publications are dropped
The server is disabled as a distributor

You can disable publishing and distribution by completing the following steps:

Start Enterprise Manager and then double-click the server entry for the distributor.
Right-click the Replication folder and then select Disable Publishing And Distribution. This starts the Disable Publishing And Distribution Wizard.
Click Next and then choose Yes, Disable Publishing (And Publishing).
Click Next twice and then click Finish.

Creating and Managing Publications

Once you've configured a distributor and enabled publishers, publication databases, and subscribers, you can create publications. Afterward, you'll need to manage the publications as you would any other SQL Server resource.

Creating Publications

The easiest way to create publications is to use Enterprise Manager. To do this, complete the following steps:

Start Enterprise Manager and then double-click the server entry for the publisher.
Right-click the Replication folder, point to New, and then select Publication. This starts the Create Publication Wizard.
Select Show Advanced Options and then click Next.
Choose the database on the selected server that contains the data or objects you want to publish. You can select user databases only.

The first time you create a publication in a particular database, you'll have to do everything from scratch. Choose the type of replication you want to use for the publication. The options are
- Snapshot publication Creates a publication setup for snapshot replication
- Transactional publication Creates a publication setup for transactional replication
- Merge publication Creates a publication setup for merge replication
The next time you create a publication in a particular database, you'll have the option of using an existing publication as a publication template. To use a template, choose Yes, Use The Following Publication As A Template in the Use Publication Template window, and then select the existing publication to use as a template. Now all the default options are based on the existing publication you've selected (but you can't change the publication type). If you would rather define the publication articles and properties separately, choose No, I Will Define The Articles And Properties.
If you're creating a snapshot or transactional publication, continue using the steps listed in the following section, "Snapshot and Transactional Publications."
If you're creating a merge publication, continue using the steps listed in the section of this chapter entitled "Merge Publications."

Snapshot and Transactional Publications

Snapshot and transactional publications are the most commonly used types of publications. With snapshot publications, the publisher periodically replaces subscriber data with an updated snapshot. With transactional publications, the publisher updates data and changes are sent to subscribers through transactions.

Once you've started a new publication, you can create a snapshot or transactional publication by completing the following steps:

Determine whether immediate updating subscribers or queued updating subscribers are used. These options use the Distributed Transaction Coordinator service to update the publisher. With immediate updating, SQL Server applies updates immediately. With queued updating, SQL Server writes updates to a queue before attempting to update the publisher.

Note: If you want to configure immediate updating with queued updating as a failover, you can select both options. Note also that queued updating uses SQL Server queues by default. After you create the publication, you can choose Microsoft Message Queuing Server as the queue.
Click Next to continue. If you haven't configured updating, you can now elect to use Data Transformation Services. DTS allows you to change the data before it's distributed to subscribers. If you want to allow data transformation, choose Yes, Transform The Data. Otherwise select No, All Subscribers Receive Data Directly.

Select the types of subscribers that will subscribe to the publication. You can choose any or all of the following:
- Servers Running SQL Server 2000
- Servers Running SQL Server Version 7.0
- Heterogeneous Data Sources
Note: If all subscribers are SQL Servers and no transformations are used, snapshots are formatted using Native SQL Server format. Otherwise snapshots are formatted using Character Mode format. Additionally, if you choose SQL Server 7.0 as a data source, you can't choose properties that are new to SQL Server 2000.

Use the dialog box shown in Figure 9-9 to select the objects for replication. You use key elements of this dialog box as follows:
- Object Type Shows the types of objects that are available for replication.
  
  Note: Which objects are available depends on the types of objects in the database and can be tables, stored procedures, and views. Tables without primary keys can't be published for snapshot or transactional replication, and you'll see a key with an X through it in the Specify Articles dialog box.
- Show When selected, displays the related objects in the right-hand pane. For example, select Show for views, and views are displayed for selection in the right-hand pane.
- Publish All When selected, all of the related objects are selected for replication.
- Owner Shows the object owner.
- Object Displays the object name.
- Show Unpublished Objects Determines whether unpublished objects are displayed.
- Article Defaults Allows you to set default article options for each type of database object.
When you select a table, stored procedure, or view, you'll see a Properties button. Click this button to configure property settings for the related article. See the section of this chapter entitled "Setting Article Properties" for details.

Figure 9-9: Select tables to replicate as articles in the publication. With snapshot and transactional replication, you can also select stored procedures.

After you select objects to use in the publication, click Next. If there are any issues that require changes to the publication, you'll see a prompt similar to the one shown in Figure 9-10. Read the description carefully to determine how to resolve the issue and make changes as necessary. Key issues are:
- Tables referenced by views are required and so are objects referenced by stored procedures. If you don't select referenced tables or objects, you must create them manually at the subscriber.
- SQL Server adds Uniqueidentifier columns to any tables you've selected for replication. Adding the Uniqueidentifier column will cause INSERT statements without column lists to fail and increase the time needed to generate the first snapshot.
- IDENTITY columns require the NOT FOR REPLICATION option. If a published IDENTITY column doesn't use this option, INSERT commands may not replicate properly.
Type a name and description for the publication. If you want to list the publication in the Active Directory, select List This Publication In The Active Directory.
You can now determine whether default or custom properties are used. You'll usually want to use custom properties, so select the Yes option and then click Next. If you click No, the wizard uses the default options shown and clicking Next takes you to the final step of the process.
Specify whether and how you want to filter the data. You can filter the data vertically by column, horizontally by row, or both.

Figure 9-10: Whenever there are issues that need your attention, you'll see this dialog box. Read the description carefully to determine how to resolve the outstanding issue.
If you've elected to vertically filter table data, you'll see the Filter Table Columns window shown in Figure 9-11. To filter table data, choose a table in the left pane and then select or clear check boxes for columns in the right pane. By clearing a check box, you're excluding the related column from replication. Repeat this step for each table that you want to filter and then click Next.

Figure 9-11: Select columns to exclude from the publication.
If you elected to horizontally filter data, you'll see the Filter Table Rows window shown in Figure 9-12. By default, all rows are published. To change this behavior, click the build (…) button for the row you want to exclude from the publication and then enter a where clause for the corresponding SELECT published_columns From TableName statement. To extend filtering on a table to a related table by defining a join, select the filtered table and the related table to filter and then define an inner join clause.
Determine whether anonymous subscriptions are allowed. Choose the Yes option to allow them. Otherwise choose the No option. Anonymous subscribers reduce the amount of administration you have to do. They allow any server to receive replicated data without your having to store information about the server in the distribution database.

Note: With anonymous subscribers, only pull subscriptions are allowed. To learn more about pull subscriptions, see the "Subscribing to a Publication" section of this chapter.
The next step lets you set the snapshot schedule. By default, snapshots are made once a day. To change this schedule, click Change and then set a new schedule. You can create a snapshot immediately by selecting Create First Snapshot Immediately.

Figure 9-12: You can also filter table rows using where clauses. To see a list of available column values, click Cancel and then click Back to go back to the previous wizard dialog box.
Click Next and then click Finish. You'll see a dialog box that shows the progress of the creation process. If errors occur, you'll need to resolve any problems before you can continue or restart the publication definition process.

Merge Publications

Once you've started a new publication, you can create a merge publication by completing the following steps:

Select the types of subscribers that will subscribe to the publication. You can choose any or all of the following:
- Servers Running SQL Server 2000
- Servers Running SQL Server Version 7.0
- Heterogeneous Data Sources
Note: If all subscribers are SQL Servers and no transformations are used, snapshots are formatted using Native SQL Server format. Otherwise snapshots are formatted using Character Mode format. Additionally, if you choose SQL Server 7.0 as a data source, you can't choose properties that are new to SQL Server 2000.
Use the dialog box shown previously in Figure 9-9 to select the objects for replication. You use key elements of this dialog box as follows:
- Object Type Shows the types of objects that are available for replication.
  
  Note: Which objects are available depends on the types of objects in the database and can be tables, stored procedures, and views. Tables without primary keys can't be published for snapshot or transactional replication, and you'll see a key with an X through it in the Specify Articles dialog box.
- Show When selected, displays the related objects in the right-hand pane. For example, select Show for views and views are displayed for selection in the right-hand pane.
- Publish All When selected, all of the related objects are selected for replication.
- Owner Shows the object owner.
- Object Displays the object name.
- Show Unpublished Objects Determines whether unpublished objects are displayed.
- Article Defaults Allows you to set default article options for each type of database object.
When you select a table, stored procedure, or view, you'll see a Properties button. Click this button to configure property settings for the related article. See the section of this chapter entitled "Setting Article Properties" for details.

After you select objects to use in the publication, click Next. If there are any issues that require changes to the publication, you'll see a prompt. Read the description carefully to determine how to resolve the issue and make changes as necessary. Key issues are:
- Tables referenced by views are required and so are objects referenced by stored procedures. If you don't select referenced tables or objects, you must create them manually at the subscriber.
- SQL Server adds Uniqueidentifier columns to any tables you've selected for replication. Adding the Uniqueidentifier column will cause INSERT statements without column lists to fail and increase the time needed to generate the first snapshot.
- IDENTITY columns require the NOT FOR REPLICATION option. If a published IDENTITY column doesn't use this option, INSERT commands may not replicate properly.
Type a name and description for the publication. If you want to list the publication in Active Directory Services, select List This Publication In The Active Directory.
You can now determine whether default or custom properties are used. You'll usually want to use custom properties, so select the Yes option and then click Next. If you click No, the wizard uses the default options shown and clicking Next takes you to the final step of the process.
Specify whether and how you want to filter the data. You can filter the data vertically by column, horizontally by row, or both. If you don't want to use filtering, click Next without making any selections and then skip steps 8-15. Steps 8-15 are only used with filtering.
If you've elected to vertically filter table data, you'll see the Filter Table Columns window. To filter table data, choose a table in the left pane and then select or clear check boxes for columns in the right pane. By clearing a check box, you're excluding the related column from replication. Repeat this step for each table that you want to filter and then click Next.
Steps 10-15 are only used for horizontal filtering. If you aren't using hori- zontal filtering, skip steps 8-15.
Horizontal data filtering is handled in one of two ways. You can use a dynamic filter that allows individual subscribers to receive different sections of the published data, or you can use a static filter and all subscribers will receive the same data. For dynamic filters, choose Yes, Enable Dynamic Filters. Otherwise choose No, Use Static Filters.
In the Generate Filters Automatically window, you can specify a filter on a table and have SQL Server automatically extend the filter to other tables based on relationships you define using where clauses. The default setting is None – Do Not Extend Filtering. Use the Table To Filter drop-down list box to select the table you want to filter. Then enter a where clause for the corresponding SELECT *From TableName statement. Repeat this process for any additional tables you want to filter. Click Next when you're finished.
You'll see the Filter Table Rows window, as shown in Figure 9-13. By default, all rows are published. To change this behavior, click the build (…) button for the row you want to exclude from the publication and then enter a where clause for the corresponding SELECT published_columns From TableName statement. To extend filtering on a table to a related table by defining a join, select the filtered table and the related table to filter and then define an inner join clause.
As shown in Figure 9-13, you can extend the row filters to other tables by using join filters. From the list in the Filtered Table column, select a table with an existing filter. Then from the list in the Table To Filter column, select a second table to be filtered. Afterward, click the related properties button (…) to enter a join clause to use as the filter. Repeat this step to create additional filters. To delete a join, click <Delete Join> in the list shown in the Filtered Table column. When you click Next, SQL Server attempts to extend the filter to other tables based on their relationships.
With dynamic filters you can validate information at the Subscriber before each merge and the next window allows you to set related options. To enforce the validation process, select Yes, Validate Subscriber Information and then type a function to use for validation, such as HOST_NAME() + suser_sname().If you do not want Subscriber information to be validated before each merge, select No, Do Not Validate Subscriber Information.

Figure 9-13: Extend filters to other tables using a join clause.
With horizontal filtering you can optimize synchronization by electing to maintain additional information at the publisher and thereby reduce the amount of data sent over the network. If you want to do this, select Yes, Minimize The Amount Of Data. Otherwise select No, Do Not Minimize The Amount Of Data.
Determine whether anonymous subscriptions are allowed. Choose the Yes option to allow them. Otherwise choose the No option. Anonymous subscribers reduce the amount of administration you have to do. They allow any server to receive replicated data without your having to store information about the server in the distribution database.

Note: With anonymous subscribers, only pull subscriptions are allowed. To learn more about pull subscriptions, see the "Subscribing to a Publication" section of this chapter.
The next step lets you set the snapshot schedule. By default, snapshots are made once a day. To change this schedule, click Change and then set a new schedule. You can create a snapshot immediately by selecting Create First Snapshot Immediately.
Click Next and then click Finish. You'll see a dialog box that shows the progress of the creation process.

Updating a Publication

You can change the properties of publications any time you like. To change them, complete the following steps:

Start Enterprise Manager, double-click the server entry for the publisher, and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this database.
Right-click the publication you want to work with and then select Properties.
You can now use the Properties dialog box to configure all the publication options discussed previously in the "Creating Publications" section of this chapter.

Setting Article Properties

Article properties control the behavior of replication. When you create a publication, you can set default article properties based on object type. You can override the default properties at any time. To edit article properties for an existing publication, follow these steps:

Start Enterprise Manager, double-click the server entry for the publisher, and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this database.
Right-click the publication you want to work with and then select Properties.

In the Articles tab, click the build (…) button for an article you want to update. This displays the Article Properties dialog box. The available article properties depend on the replication type, and you may see one or more of the following tabs:
- General Allows you to configure basic options and is available for all replication types. You can view the article name, source table owner, and source table name. You can set the article description, destination table owner, and destination table name. With merge replication you can also specify whether row-level or column-level conflict resolution is used. With DTS and snapshot or transactional replication you can also specify whether the article should support horizontal partitions created by Data Transformation Services.
- Snapshot Sets options for snapshots, which are used with all replication types. You can set options that control what happens when a table exists at the subscriber already, what types of objects are copied, and whether user-defined data types are converted to base data types.
- Resolver Allows you to choose which resolver is used to resolve conflicts that occur in merge replication and to allow subscribers to resolve conflicts interactively during on-demand synchronizations. Merge replication only.
- Merging Changes Controls whether the subscriber verifies that the Merge Agent has permission to use INSERT, UPDATE, and DELETE commands. By default, the agent doesn't verify permissions. Indicates whether Multicolumn updates are applied during merge replication for this article. Merge replication only.
- Commands Controls whether INSERT, UPDATE, and DELETE commands are replaced by customized stored procedures. By default, these commands are replaced and the necessary stored procedures are created automatically when synchronizing with subscribers. Transactional replication only.

Controlling Subscription Access to a Publication

All publications have access control lists (ACLs). For publications, access control lists determine which logins can be used by pull and immediate updating subscribers to access the publication. By default, only the database owner (DBO) and administrators have subscription access to the publication. To add or remove users, complete the following steps:

Start Enterprise Manager, double-click the server entry for the publisher, and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this database.
Right-click the publication you want to work with and then select Properties.
Click the Publication Access List tab. Use the buttons provided to add or remove logins.

Creating a Script for a Publication

To create a script for a publication, complete the following steps:

Start Enterprise Manager, double-click the server entry for the publisher, and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this database.
Right-click the publication you want to work with and then select Generate SQL Script.
Click OK. Click Save and then save the script to a .sql file, which can be executed in Query Analyzer.

Tip In Query Analyzer, you can access scripts by clicking the Load SQL Script button on the toolbar and then entering the location of the script.

Deleting a Publication

When you're finished using a publication, you can delete it to free up resources that it's using. But before you do this, you may want to create a script that allows you to recreate the publication automatically if you have to. After creating the script, you can delete the publication by completing the following steps:

Start Enterprise Manager, double-click the server entry for the publisher, and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this server instance.
Right-click the publication you want to work with and then select Delete.
When prompted to confirm the action, click Yes.

Subscribing to a Publication

The final step in the replication process is having servers subscribe to the publication. You can do this using push or pull subscriptions.

Subscription Essentials

With push subscriptions, the publisher is responsible for replicating all changes to subscribers without subscribers asking for the changes. You'll usually use push subscriptions when you need to send changes to subscribers immediately or when you want to schedule updates periodically. Because the publisher initiates the replication, push subscriptions also offer more security than pull subscriptions. Making the publisher responsible for replicating changes, however, increases overhead on the publisher and may not be the ideal subscription model for a server that's experiencing heavy workloads.

With pull subscriptions, subscribers request periodic updates of all changes from the publisher. You'll usually use pull subscriptions when you have a large number of subscribers or when you need to reduce overhead on the publisher. You'll usually want to use pull subscriptions for independent mobile users as well. A single publication can support a mixture of push and pull subscriptions.

You can also use a special type of pull subscription called an anonymous subscription. With an anonymous subscription, the publisher and distributor don't maintain subscription information. Instead, the subscriber is responsible for maintaining and synchronizing the subscription, which increases the load on the subscriber but reduces the load on the publisher and distributor. Accordingly, anonymous subscriptions are most useful when you have a large number of subscribers or when you allow subscriptions using the Internet.

Note: You create anonymous subscriptions to publications just like pull subscriptions. If the publication is enabled for anonymous subscriptions and the server isn't registered as a subscriber, the Pull Subscription Wizard creates an anonymous subscription automatically.

The Distribution Agent and Merge Agent are responsible for synchronizing subscriptions and resetting their retention period. If these agents aren't running, subscriptions become out of sync with their publications and are marked as deactivated. A deactivated subscription is a subscription that has exceeded the publication retention period. Deactivated subscriptions no longer receive updates during synchronization and you must mark these subscriptions for reinitialization in order to reenable them. If you don't reenable deactivated subscriptions before they expire, the Expired Subscription Clean Up Agent will delete them.

Creating Pull Subscriptions

The subscriber initiates pull subscriptions. You configure pull subscriptions by completing the following steps:

In Enterprise Manager, access the database server instance that will act as the subscriber. Right-click the Replication folder, point to New, and then select Pull Subscription. This starts the Pull Subscription Wizard.
Select Show Advanced Options In This Wizard and then click Next.
You use the next dialog box (see Figure 9-14) to specify where you want to look for a publication. You can look at publications from registered servers, search in Active Directory services for publications, or specify the publication information manually. You can only search the Active Directory for publications or enter the information manually when you are running SQL Server 2000 in a Windows 2000 Active Directory domain. Click Next after you've made your selection.

Figure 9-14: Look For Publications allows you to look for publications from registered servers, search Active Directory for publications, or specify publication information manually.
If you elected to look at publications from registered servers, you can browse available publications from the list of registered publishers in the Choose Publication dialog box (see Figure 9-15). Display a list of available publications by clicking the plus sign (+) next to a registered publisher. Then select the publication to which you want to subscribe. Afterward, click Next. You can also register a new server by clicking the Register Server button.
If you elected to search the Active Directory for publications or to enter information manually, you will see the Specify Publication dialog box as shown in Figure 9-16. To browse an Active Directory for publications, click the Browse button and then use the Find SQL Server Publications dialog box to find the publication you want to use. To specify the publication information, manually type the name of the publisher, the publication database, and the publication. Whether you specify the information manually or select a publication from Active Directory, you must also specify the authentication technique to use for the connection. If you choose SQL Server Authentication, type the SQL login name and password as well.

Figure 9-15: Choose Publication allows you to access a list of currently available publications from registered servers.

Figure 9-16: You can use Specify Publication to enter specific publication information manually, or you can browse the Active Directory for registered publications by clicking Browse.

Note: In some situations you may need to specify the login used by the synchronization agent to connect to the publisher and distributor. Typically this occurs when you're accessing publishers that are members of a domain from a SQL Server that isn't a member of the domain, or you logged in locally to the server and are not an authenticated user of the domain. You specify the login information using the Specify Synchronization Agent Login dialog box. The agent can either impersonate the SQL Server Agent account (recommended) or use a specific SQL Server login name and password. Either way, the account you choose must be listed on the publication access list.
Use the Choose Destination Database window to select an existing database in which to create the subscription or click New to create a new database for the subscription. Click Next when you're ready to continue.
If you selected a publication that does not use immediate updating or queued updating, skip steps 8 and 9. Steps 8 and 9 are only used when immediate updating or queued updating are selected.
If immediate updating, queued updating, or both was specified when the snapshot or transactional publication was created, the next dialog box will be Updatable Subscriptions (see Figure 9-17). This dialog box allows you to enable these options for the subscription. Select None – Changes Are Not Replicated if you don't want to create an updatable subscription at this time. Keep in mind that when you use immediate updating with queued updating as a failover, you must manually switch into queued updating mode and then switch back to immediate updating once the connection is restored and the queue is empty.

Figure 9-17: Updatable subscriptions allow changes made to a subscription to be replicated to the publisher and then to other subscribers.
If you choose to create an updatable subscription using immediate updating, the next dialog box allows you to configure the technique used by the subscriber to access the publisher (see Figure 9-18). You can use an existing linked server or remote server to establish the connection, provided you've already configured these options as described in Chapter 8. Or you can use a SQL Server login id, provided the login is listed on the publication access list.

Figure 9-18: Specify the login method used to access the publisher when making updates. You can use linked servers, remote logins, or SQL Server Authentication.
Use the Initialize Subscription window to determine whether the subscription database should be initialized. The options available are different depending on the type of publication that is being subscribed to. Some options are dimmed and are unavailable. Choose the Yes option to initialize the schema and data. Choose the No option if you're updating an existing database with the same schema.

Note: Keep in mind that initialization is handled by the Snapshot and Distribution Agents. The Snapshot Agent creates the initial view of the schema and data, and then when it next runs, the Distribution Agent applies the snapshot.
If you elect to initialize the schema and data, the next dialog box lets you specify where to access the snapshot files. You can access the snapshot files in the default snapshot folder for the publication or in an alternate location. For an alternate location, type the full folder path, such as C:\data\snapshots.

Set the Distribution Agent or Merge Agent schedule using one of the following options:
- Continuously Select this option to continuously check for updates on the publisher.
- Using The Following Schedule Select this option and then click the Change button to set a periodic schedule, such as once an hour.
- On Demand Only Select this option if you want to update the subscription database manually.
If you chose a snapshot or transactional publication and want to transform the publication data before sending it to subscribers, you must now specify the DTS package that defines the transformations for the subscription. As shown in Figure 9-19, specify the package location on the distributor or on the subscriber and then enter a package name. If required, enter the owner password for the package as well.

Figure 9-19: To transform the data before delivering it, specify the package location, name, and optional owner password.
If you are subscribing to a merge publication, you can choose the priority setting of the publisher to resolve subscriber conflicts or you can set the priority level manually (see Figure 9-20).
If necessary, determine whether required services should be started. To start a service, select the check box to the left of the service name. Services that aren't started automatically will need to be started manually for the subscription to work.
If the SQL Server Agent service isn't configured to start automatically, you'll next need to specify whether the SQL Server Agent should start automatically. Choose Yes for automatic startup or choose No if you plan to manually start the agent.

Figure 9-20: Allow the publisher to set priority to resolve subscriber conflicts or set a priority level manually.
Check the setup and click Finish when you're ready to create the pull subscription.

Updating, Maintaining, and Deleting Pull Subscriptions

To update, maintain, or delete a pull subscription, complete the following steps:

Start Enterprise Manager. Double-click the server entry for the subscriber and then double-click the Replication folder.
Click the Subscriptions folder to see a list of subscriptions for this server instance.

Right-click the pull subscription you want to work with and then choose one of the following options:
- Copy Subscription Database Writes a copy of the subscription database to a subscription file that can be replicated through FTP to remote locations. Subscription files end with the .msf extension and are saved to the REPLDATA folder for the SQL Server instance by default.
- Delete Deletes the subscription. Confirm the action by clicking Yes when prompted.
- Generate SQL Script Creates a SQL script that you can use to recreate or delete the subscription. Generally, it's a good idea to create a script for a subscription before you delete it. This way, you can easily recreate the subscription if necessary.
- Job History Shows a history of all SQL Server Agent jobs that are run to create, maintain, and manage the subscription. If a job fails because SQL Server is unable to determine if the job owner has access to a server, it means the login id for the distributor or publisher isn't listed in the publication's access control list.
- Properties Displays the Subscription Properties dialog box. The dialog box has four tabs: General, Synchronization, Security, and Snapshot File Location. Use the General tab to view subscription information. Use the Synchronization tab to view or change replication agent properties, configure synchronization, and specify where to start the merge agent. Use the Security tab to set distributor and publisher logins. Use the Snapshot File Locations tab to specify alternative snapshot locations and to specify a temporary working folder to use when creating snapshots.
- Reinitialize Reinitialize the snapshot in the subscription database. Use this option to create a new snapshot of the data and restart synchronization for a deactivated subscription.
- Start Synchronization Starts the synchronization process for an active subscription. Use this option to manually update the subscription.
- Stop Synchronization Stops the synchronization process if it's in progress. Use this option if you need to stop the update before it's written to the subscription.
- View Conflicts Starts the Replication Conflict Viewer, which shows current conflicts that are unresolved. If you've configured the subscription so that conflicts are resolved interactively, you use the Replication Conflict Viewer to manually resolve conflicts.

Creating Push Subscriptions

You can initiate push subscriptions from the publisher by completing the following steps:

In Enterprise Manager, access the publisher. Double-click the Replication folder and then double-click the Publications folder.
Right-click the publication you want to configure and then select Push New Subscription. This starts the Push Subscription Wizard.
Select Show Advanced Options In This Wizard. Click Next to access the dialog box shown in Figure 9-21. Select one or more subscribers for the publication. Servers that are currently enabled are listed in the Enabled Subscribers group. If a server you want to use as a subscriber isn't listed, click Cancel, enable the server as a subscriber, and then restart the wizard.
Type the name of the destination database on the subscriber. Click the Browse Or Create button to select an existing database or to create a new database on the subscriber. Click Next when you're ready to continue.

Figure 9-21: Select the subscribers for the publication.
Choose whether the Distribution Agent should run at the distributor or at the subscriber. If you choose to run the agent at the subscriber, you must also enter the computer name of the subscriber.

Note: The Distribution Agent is responsible for applying data from snapshot replication or transactions from transactional replication to subscribers. In most cases, the agent should run on the distributor. However, if you want to move some of the workload off a distributor that is also acting as a publisher, this is one way to do it.

Set the distribution agent schedule using one of the following options:
- Continuously Select this option to have the publisher send updates continuously.
- Using The Following Schedule Select this option and then click Change to set a periodic schedule, such as once an hour.
Determine whether the subscription database should be initialized. Choose the Yes option to initialize the schema and data. Choose the No option if you're updating an existing database with the same schema. If you choose Yes, you can also choose to start the snapshot agent so that it can begin the initialization process.
If you configured merge replication, you can now configure the subscription priority for data changes as shown in Figure 9-22. When a data change at the subscriber conflicts with a change elsewhere, you can use the priority setting of the publisher to resolve the conflict or to set a specific priority between 0.00 (lowest) and 99.99 (highest).

Figure 9-22: Subscription priority determines the winner of conflicting changes.

Tip The priorities of existing subscriptions to the publication are listed for reference. If you want to ensure that changes to a particular subscription have precedence, set the priority of this subscription to be higher than that of other subscriptions and the default priority of the publisher. If you want to ensure that changes to a particular subscription are discarded when there's a conflict, set the priority of this subscription to be lower than that of other subscriptions and the default priority of the publisher.
If you specified immediate updating, queued updating, or both when creating the publication, the next dialog box allows you to enable these options for the subscription. Select None – Changes Are Not Replicated if you don't want to create an updatable subscription at this time. Keep in mind that when you use immediate updating with queued updating as a failover, you must manually switch into queued updating mode and then switch back to immediate updating once the connection is restored and the queue is empty.
If you chose to transform the publication data before sending it to subscribers, you must now specify the DTS package that defines the transformations for the subscription. Specify the package location on the distributor or on the subscriber and then enter a package name. If required, enter the owner password for the package as well.
Determine whether required services should be started. To start a service, if necessary, select the check box to the left of the service name. Services that aren't started automatically will need to be started manually for the subscription to work.
Check the setup and click Finish when you're ready to create the push subscription.

Updating, Maintaining, and Deleting Push Subscriptions

To update, maintain, or delete a push subscription, complete the following steps:

Start Enterprise Manager. Double-click the server entry for the publisher and then double-click the Replication folder.
Double-click the Publications folder. Click the publication whose subscriptions you wish to manage.

Right-click the push subscription you want to work with and then choose one of the following options:
- Delete Deletes the subscription. Confirm the action by clicking Yes when prompted.
- Properties View and change subscription properties. The only property you can change is the run location of the merge agent, which is either at the distributor or at the subscriber.
- Reinitialize Reinitializes the snapshot in the subscription database. Use this option to create a new snapshot of the data and restart synchronization for a deactivated subscription.
- Start Synchronization Starts the synchronization process for an active subscription. Use this option to manually update the subscription.
- Stop Synchronization Stops the synchronization process if it's in progress. Use this option if you need to stop the update before it's written to the subscription.

Transforming Published Data

You can use Data Transformation Services (DTS) to manipulate publication data before sending it to subscribers. This technique is useful if individual subscribers need different views of the data or if you need to map information from one table or data format to another table or data format. You can configure both snapshot and transactional publications for data transformation, provided the publications aren't configured to allow updatable subscriptions.

Configuring data transformation for publications and subscriptions requires several steps. You must create a publication that allows data transformation and then you must create a DTS package that's enabled for replication. After you create a publication that allows data transformation, you use the Transform Published Data Wizard to create the replication-enabled package. You access and use this wizard by completing the steps on the following page.

Start Enterprise Manager. Double-click the server entry for the publisher and then double-click the Replication folder.
Click the Publications folder to see a list of publications for this server instance.
Right-click the publication you want to work with and then select Define Transformation Of Published Data. This starts the Transform Published Data Wizard. Click Next.
As shown in Figure 9-23, use the Destination drop-down list box to select the provider type for the destination database. You use the provider to generate the subscriber data. If the subscriber is a SQL Server database, you'll want to use the Microsoft OLE DB Provider for SQL Server. Other providers are available as well.
Fill in any additional information required to establish a connection to the destination. The destination you select determines what information you need to supply. With SQL Server, be sure to choose the destination database. This database must be the one that was designated as the distribution database when you created the publication.
Now you need to define transformations for the objects in the publication as explained next.

Figure 9-23: Specify a destination and enter information for the provider and database.

Transformation is the process of manipulating the source data and formatting it for the chosen destination. Select a table or view that you want to manipulate and then click the corresponding button in the Transform column. Unless you specify otherwise, the wizard sets default mapping for all objects in the publication. This default mapping

Specifies that every object in the source publication is copied
Maps the original object name and properties to the destination object
Drops any existing object with the same name and creates it

You can override the default mappings using a procedure similar to the one outlined in the section of Chapter 7 entitled "Stage 3: Formatting and Transformation." When you're finished configuring all the necessary transformations, click Next in the Transform Published Data Wizard. The final steps allow you to specify the package location, name, and an optional owner password.

Managing Replication Monitors

SQL Server 2000 provides two ways to monitor replication:

Use replication monitors at the server level.
Use replication monitor groups at the enterprise level.

Once monitors are enabled, you can use the monitor to view the status of replication agents and troubleshoot potential problems with distributors. All replication agents have options that you can set to customize their behavior.

You can set agent profiles that control the exact behavior of the agent as well as agent properties that control other configuration settings.

Configuring Server-Level Monitoring

Server-level monitoring lets you check replication on a specific distributor. To check replication at the server level, follow these steps:

In Enterprise Manager, access the database server instance that is acting as the distributor and then select the Replication Monitor node.
Use the Publishers folder to control replication publishers, publications, and subscriptions.
Use the Agents folder to configure and manage replication agents for the currently selected distributor as described in "Setting Agent Profiles" and "Setting Agent Properties" below.

Configuring Enterprise-Level Monitoring

Enterprise-level monitoring lets you check replication on multiple distributors throughout the enterprise. Before you can view enterprise-level monitors, you must enable the Replication Monitor Group and add distributors to monitor as specified in the following steps:

In Enterprise Manager, right-click Microsoft SQL Servers and then choose Display Replication Monitor Group. The Replication Monitor Group is added to the console root.
Right-click Replication Monitor Group and then choose Add Distributor To Monitor.
In the Add Distributor To Monitor dialog box, select one or more distributors and then click OK. If the distributor you want to use isn't registered, click Register Server and then complete the Registered SQL Server Properties dialog box.

Setting Agent Profiles

Profile settings are useful for updating agents to work with slow connections, to configure verbose logging, and more. You set a profile for any of the available agents by completing the following steps:

In Enterprise Manager, access the distributor whose agent you want to configure in the Replication Monitor Group. If this group isn't displayed, you can display it as described under "Configuring Enterprise-Level Monitoring."
Double-click the distributor's entry and then double-click the related Agents folder. You can now configure profiles for the agents that use this distributor.
Click the folder for the type of agent you want to work with, such as Snapshot Agents. A list of the related agents should be displayed in the right pane.
Right-click the agent you want to work with and then select Agent Profiles. The Agent Profiles dialog box is displayed.
Select an existing profile using the option buttons in the Default column.
Create a new profile by selecting Copy and configuring the Replication Agent Profile Details dialog box.
To force all existing distribution agents to use the new profile, select Change All Existing Agents To Use The Selected Profile.
Click OK.

Setting Agent Properties

The snapshot agent and the distribution agent are the key agents you'll want to set properties for. You configure the snapshot agent properties for publications and the distribution agent properties for pull subscriptions.

To change property settings for the snapshot agent, complete the following steps:

In Enterprise Manager, access the distributor whose agent you want to configure in the Replication Monitor Group. If this group isn't displayed, you can display it as described under "Configuring Enterprise-Level Monitoring."
Double-click the distributor's entry and then double-click the related Agents folder. You can now configure profiles for the agents that use this distributor.
Click the folder for the type of agent you want to work with, such as Snapshot Agents. A list of the related agents should be displayed in the right pane.
Right-click the agent you want to work with and then select Agent Properties. The Agent Properties dialog box is displayed.
Use the related Properties dialog box to configure the agent.

Click to order