Initial Replication Setup

No matter what type of replication you're going to configure, you will need to perform an initial setup on the publisher, which creates the environment for the publisher and the distributor. To enable replication, simply open Enterprise Manager and connect to the server. Once you're connected, launch the Configure Publishing and Distribution Wizard by selecting Tools | Replication | Configure Publishing And Subscribers.

Note 

You must be a member of the sysadmin fixed server role to configure replication.

In this section, I'll cover the default installation, which works for most people, including ways to customize it. Most people can buzz through the installation without looking at a screen. However, there are some consequences to your decisions in this wizard, although many problems can be fixed later. The Configure Publishing and Distribution Wizard does the following:

• Sets one of the servers as a distributor

• Configures the distributor and loads it with the distribution database

• Configures the publisher

The first step in the wizard is to specify which server will act as the distributor, choosing a server from the list of available distributors. To add to the list of available servers, click the Add Server button.

By default, the publisher also acts as the distributor. If the SQL Server Agent is not configured properly, when you click Next you receive a message that gives you an opportunity to configure it.

You must then provide the snapshot folder, no matter what type of replication you're going to deploy. After you specify the folder, click Next. If you've specified the default administrative shared drive (C$), you receive a warning:

If the account that starts the subscriber's SQL Server services has enough access to use the administrative shares, or if you do not plan to deploy pull subscriptions, you can ignore this warning. Otherwise, create a share for the subscribers to pull from.

In the Trenches

The most common error I see is that the SQL Server Agent complains about using a local system account. For replication to work, you must use a Windows account to start the SQLServerAgent service.

After acknowledging the error, the wizard asks if you'd like to accept the defaults by selecting the No, Use The Following Default Settings option. (I cover customizing this in the next section.) If you select the defaults, the wizard automatically configures the following:

• Configures the earlier specified server as a distributor.

• Enables and configures all registered servers in Enterprise Manager as subscribers.

• Configures SQL Server Agent to start automatically at SQL Server's startup.

• Configures the snapshot folder to be the folder you specified earlier.

• Sets the name of the distribution database on the distributor. By default, this is named distribution and is located in the standard \Program Files\Microsoft SQL Server\MSSQL\data subdirectory.

Customizing the Initial Installation

The first screen of the customization process asks you what you'd like to call the distribution database, and where you'd like to locate the data and log files. After clicking Next, you are asked to enable the publisher. You can click the Properties button to display the Publisher Properties screen, shown in Figure 12-6. This screen allows you to specify how the various replication agents will log into the publisher. You can either specify a standard SQL Server account or use a trusted connection to connect to the publisher.

In the Trenches

When configuring the replication folder that the snapshot agent uses, you need to be sure that the SQL Server accounts have proper permissions for it. If this is not configured properly, you receive the following error from the snapshot agent:

The process could not read file 'C:\Program Files\Microsoft SQL Server\MSSQL\REPLDATA'. Access is denied

To fix the problem, ensure that the account that starts the MSSQLServer and the SQLServerAgent services on the distributor have access to the share or directory.

Figure 12-6: Publisher Properties screen

Tip 

Don't accept the default location for the distribution database. In merge and transactional replication, this database is written to quite often. If you have the resources, locate the log and the data files on separate drive arrays. If you have the equipment, place the files on a RAID 1 or RAID 10 array.

The next screen allows you to enable the user-defined databases on the publisher for merge and transactional replication. By enabling the databases for each type of replication, you automatically enable them for snapshot replication. After the database is enabled, you can create publications in the database.

In the Trenches

Pay special attention to this screen before accepting the defaults. This screen does simplify installation, but it also configures every server you have registered on your workstation as a subscriber. If you're running the wizard remotely from your desk, you will enable all the servers registered in your Enterprise Manager, not those registered on the target SQL Server. Some of these servers that are accessible from your remote workstation may not be viewable from the target SQL Server, though.

Note 

SQL Server Personal Edition is not licensed to run transactional replication as a publisher. Instead, you are forced to do snapshot or merge replication. Personal Edition servers can act as subscribers of a transactional replication publication.

After you enable the databases on the publisher, you are ready to enable any of the subscribers for replication. To enable each subscriber, check the corresponding box next to the name. You can also go to the Subscriber Properties screen by clicking on the Properties button next to the subscriber's name.

In the General tab, specify how the distributor will log in to the subscriber. Under the Schedules tab (see Figure 12-7), specify how often the distribution or merge agents run. By default, the distribution agent runs continuously and the merge agents run once an hour every day.

Figure 12-7: Configuring the schedules

Once you click the Finish button, the wizard enables and configures all the servers and databases based on your specifications. If any errors occur, the wizard allows you to go back and correct the problem. After the process finishes, the wizard creates a new group in Enterprise Manager called Replication Monitor. This allows you to:

• View a list of publishers, publications, subscribers, and subscriptions and quickly configure them

• View and configure the replication agents

• Set up replication alerts and view errors

• See replication performance numbers

  Tip 

  You can hide this group by selecting Tools | Replication | Hide Replication Monitor Group in Enterprise Manager.

In the Trenches

When you first view the Replication Monitor group in Enterprise Manager, you are prompted to specify how often you want to refresh the contents of the window. If you set it to manual refresh, Enterprise Manager caches the contents. You may miss a problem unless you remember to manually refresh the contents of the window. If you select the option to allow the system to refresh your screen every ten seconds, you incur a small performance hit. It's such a minimal hit that ten seconds is usually a fine refresh rate.

After you finish this wizard, a group named Publications is created under each database that is configured to publish data. Each publication you add is listed here. You can quickly determine which databases are participating in replication by viewing the database icons in Enterprise Manager. Icons of participating databases have a hand under the graphic, like the hand in Windows that designates a shared folder.