As the name indicates, you use distributors to distribute replicated data. When you work with distributors, the core set of administration tasks you will perform includes setting up a new distributor, updating an existing distributor, and deleting distributors.
Setting up a new distributor is the first major step in configuring replication. Before you get started, you should prepare as follows:
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 are ready to proceed, configure the distributor by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Tip
If you cannot successfully connect to a remote server, the server might not be configured to accept remote connections. As discussed in Chapter 3, you will need to use the SQL Server 2005 Surface Area Configuration tool to allow remote connections.
Right-click the Replication folder, and then select Configure Distribution. This starts the Configure Distribution Wizard.
Click Next to move past the Welcome screen. On the next page of the wizard, you can select a distributor, as shown in Figure 12-1.
Because you want to set up a new distributor, accept the default to allow the current server to act as its own distributor, and then click Next.
If the SQL Server Agent is not already started and configured to start automatically, you will see the SQL Server Agent Start page. Select Yes, Configure The SQL Server Agent Service To Start Automatically, and then click Next.
On the Snapshot Folder page, set the location of the folder used to store snapshots, and then click Next. The default path is to the %ProgramFiles% Microsoft SQL ServerMSSQL.1MSSQLReplData folder on the server designated as the distributor. To guarantee that Distribution and Merge Agents running on subscribers can access snapshots of their push and pull subscriptions, you should place the snapshot folder on a network share and specify the network path by typing it in the text box provided, such as \CorpSvr09 ReplData.
On the Distribution Database page, provide information for the distribution database using the dialog box shown in Figure 12-2. Enter a name for the distribution database, and then set folder locations for the corresponding data and log files. Click Next when you are ready to continue.
As shown in Figure 12-3, you need to enable publishers for this distribution database. Only registered servers in the current domain are shown. If you want to add a server so that it can use this distributor when it becomes a publisher, click Add, and then:
Choose Add SQL Server Publisher to configure a connection to a SQL Server using the Connect To Server dialog box. Registered servers are listed in the Server Name drop-down list, and you can browse for others. The default authentication is Windows Authentication, which uses your current login and password. Click Connect.
Choose Add Oracle Publisher to configure a connection to an Oracle server using the Connect To Server dialog box. Registered servers are listed in the Server Name drop-down list, and you can browse for others. The default authentication is Oracle Standard Authentication, which requires a user login and password. Click Connect.
To the right of registered publisher entries, you will see the Properties button (...). Click this button to open the Properties dialog box to set publisher options for the related server. As Figure 12-4 shows, the following options are available:
Agent Connection Mode. Distributors use SQL Server Agent to handle replication tasks. The SQL Server Agent must be configured to start automatically. By default, synchronization agents log in to the publishers using the SQL Server Agent account (determined by selecting the Impersonate The Agent Process Account option). If you want synchronization agents to use a specific login when connecting to publishers, select SQL Server Authentication from the drop-down list, and then enter the login and password to use.
Default Snapshot Folder. 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.
Click Next. If you specified a remote server as a possible publisher, you next must specify and confirm the password that remote publishers will use to connect to the distributor. This password must be provided when a remote publisher connects to the distributor to perform replication administrative operations. The password must meet the Windows policy requirements for length and complexity. Click Next.
By default, the wizard will configure the distributor immediately when you click Finish. If you would rather have the wizard generate a script that you can run at a later time or can schedule to be run, select the Generate A Script File... option.
Click Next, and then click Finish. The wizard will configure the distributor or generate the script according to the option you specified. The success or failure of each step is shown in the related dialog box. If a step fails, click the link provided to display the error details. Click Close.
When you establish a server as a distributor, many areas of the server are updated. You may note that there is a new distribution database; additional jobs, alerts, and proxies for replication may now be listed; and other updates may be evident. The Replication Monitor tool also becomes available. To learn how to work with this SQL Server feature, see Chapter 13.
Configure publications and subscriptions as explained in the subsections titled "Enabling and Updating Publishers," "Enabling Publication Databases," and "Creating Subscriptions" later in this chapter.
When you configure a new distributor, you can set up a new distribution database, as discussed in the subsection titled "Setting Up a New Distributor" earlier in this chapter. If you have already configured a distributor, you can update the distributor and create additional distribution databases by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Distributor Properties. This opens the dialog box shown in Figure 12-5.
You can use the options in the dialog box to change publisher and distributor data properties for the currently selected distributor. Use the dialog box pages as follows:
General. Configure distribution databases, agent profiles, and properties for retention and queuing.
Publishers. Enable and disable publishers for the distributor, set publisher properties, and configure passwords for administrative links.
On the General page, you will see a list of current distribution databases and their settings for transaction and history retention. By default, new distribution databases store transactions only as long as they are needed [determined by the retention setting of at least zero (0) hours] but not more than 72 hours, and they retain replication performance history data for at least 48 hours.
To view the current location of database and log files for a selected distribution database or change the retention settings, click the Properties button (...) in the third column to the right of the database name. This displays the Distribution Database Properties dialog box shown in Figure 12-6. Use the text boxes and option buttons provided to manage the retention settings as necessary. Click OK when you are finished to apply the changes.
On the Publishers page, you will see a list of current publishers for this distributor and the distribution databases they use. A check mark next to a publisher’s name indicates that the publisher is enabled.
Click OK to close the dialog box and apply any changes.
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 also can create additional databases as necessary. If you have already configured a distributor, you can create additional distribution databases by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Distributor Properties. This opens the dialog box shown previously in Figure 12-5.
On the General page, 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 cannot 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 twice to close the open dialog boxes and create the distribution database.
You can assign the new distribution database to any new publishers you configure.
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 SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Distributor Properties.
In the Distributor Properties dialog box, select the Publishers page. Use the check boxes provided to enable or disable publishers. Only registered publishers are shown. If you want to add a server so that it can use this distributor when it becomes a publisher, click Add, and then:
Choose Add SQL Server Publisher to configure a connection to a SQL Server using the Connect To Server dialog box. Registered servers are listed in the Server Name drop-down list, and you can browse for others. The default authentication is Windows Authentication, which uses your current login and password. Click Connect. If there is more than one distribution database available, the Distribution Database box will have a drop-down list allowing you to choose the database to use.
Choose Add Oracle Publisher to configure a connection to an Oracle server using the Connect To Server dialog box. Registered servers are listed in the Server Name drop-down list, and you can browse for others. The default authentication is Oracle Standard Authentication, which requires a user login and password. Click Connect. If there is more than one distribution database available, the Distribution Database box will have a drop-down list allowing you to choose the database to use.
To the right of registered publisher entries, you will see the Properties button (...). Click this button to set publisher options for the related server. The following options are available:
Agent Connection Mode. Distributors use SQL Server Agent to handle replication tasks. The SQL Server Agent must be configured to start automatically. By default, synchronization agents log in to the publishers using the SQL Server Agent account (determined by selecting the Impersonate The Agent Process Account option). If you want synchronization agents to use a specific login when connecting to publishers, select SQL Server Authentication in this box, and then enter the login and password to use. (This is the Administrative Link Password, which also can be set by selecting the publisher on the Publishers page and then using the boxes and options provided in the lower-right area of the dialog box.)
Default Snapshot Folder. 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.
After you configure distributors and publishers, you can enable publication databases by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Publisher Properties. This opens the Publisher Properties dialog box.
To enable publication databases, select the Publication Databases page. Then select entries under the Transactional column to enable a database for snapshot or transactional replication, 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 Transactional and Merge check boxes.
Before you can delete a distribution database, you must remove all publications and disable all the publishers using the distribution database. Once you have done this, you can delete distribution databases by completing the following steps:
Start SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Distributor Properties. This opens the Distributor Properties dialog box.
On the General page, select the distribution database you want to delete, and then click Delete.
Click OK to close the dialog box and perform the delete operation.
By using the Disable Publishing And Distribution Wizard in SQL Server Management Studio, 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 SQL Server Management Studio. In Object Explorer view, connect to the server you want to use, and then work your way down to the Replication folder.
Right-click the Replication folder, and then select Disable Publishing And Distribution. This starts the Disable Publishing And Distribution Wizard.
Click Next to skip the Welcome screen, and then choose Yes, Disable Publishing On This Server.
Click Next. Review the publishers that will be disabled.
Click Next twice, and then click Finish.