Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Setup through Wizards
Replication setup consists of three distinct parts:
Defining the distributor
Defining publications
Defining subscriptions
The third part can have two variations: pull subscriptions or push subscriptions. We'll examine both variations because each is served by a separate set of wizard screens.
All replication wizards can be found by selecting Tools, Replication or by clicking the Wizards button in the Enterprise Manager. If we choose the latter, we see a screen that lets us select the wizard we want to run, as shown in the following figure.
)
Figure
1If we choose the other option (Tools, Replication), we won't see this screen; instead, we see five menu options that activate the same wizards.
Defining the Distributor
The first step in configuring replication is defining a distributor. It is recommended to use a server other than publisher or subscriber as a distribution server; in other words, if at all possible try to have a dedicated server acting as a distributor. The distribution server has to store every transaction replicated from the publisher to subscriber in the distribution database. Clearly, because you already experience problems with heavy traffic on your publisher, you should avoid adding the unnecessary load to that server.
However, if you don't have the budget for a separate server, you can configure either a publisher server or subscriber server to also act as the distributor.
NOTE
Replication setup works the same way whether you use a separate server as a distributor or not.
For this article, I will be using two instances of SQL Server 2000: D10ZF411 is the publisher and D10ZF411/Subscription_srv is the subscriber. The first instance will also act as the distributor.
Without further ado, let's activate the Configure Publishing and Distribution Wizard to see how we can configure D10ZF411 as its own distributor. I will omit the first screen in this and all subsequent wizards—they all welcome us to the wizard and give us a brief overview of what they can help us accomplish. The second screen of the wizard lets me configure the server on which I'm running the wizard as its own distributor or use another server for distribution, as shown in the following figure.
)
Figure
2.For this example, let's use D10ZF411 as its own distributor. I simply click Next. The following figure allows me to pick a location for storing snapshot files.
)
Figure
3.By default, all snapshots are stored in the ReplData folder under the SQL Server installation directory. However, sometimes we might want to specify a different folder. For example, we might change the default location of the snapshot folder if we anticipate snapshot files needing more space than we have on the disk in which SQL Server is installed or if the account that runs SQL Server Agent does not have permission to a particular share where the ReplData folder is located.
For this example, I'm using an account that has administrative privileges on the computer to run SQL Server Agent; therefore, I leave the snapshot location at default and click Next. Note that we immediately see the following warning from Enterprise Manager.
)
Figure
4.SQL Server gives us one more opportunity to ensure that SQL Server Agent has sufficient permissions to the folder that will be used for storing snapshot files. Because I've already taken care of this, we can simply ignore this warning and continue with the following figure.
)
Figure
5.This screen is somewhat tricky; if we choose the default option of not customizing publishing and distribution settings, the wizard will create a distribution database in the SQL Server installation directory. Both data and log files for the distribution database will be created on the same drive. Furthermore, we won't have an opportunity to set up publication databases or customize subscriber settings—the wizard will simply take us to the final screen that lets us click the Finish button.
It might be tempting to choose the default option and take a "shortcut," but I highly recommend not doing so for a couple of reasons. First, on a production server, the SQL Server installation directory isn't likely to reside on the drive where we store data. To achieve the best possible performance, we should always store data and log files on separate physical drives. We should put the data file(s) of the distribution database on the data drive and the log files on the drive where we store transaction logs. Furthermore, if we're using the same distribution server for multiple replication configurations, we might want to change the default name of the distribution database (Distribution) to something more descriptive. Last but not least, choosing the non-default option lets us customize publisher and subscriber settings from the same wizard. Therefore, I will choose "Yes, let me set the distribution database properties, enable Publishers, or set publishing settings" and then click Next.
The following figure lets us customize the name of the distribution database and the paths for its data and transaction log files.
)
Figure
6.Notice the warning that data and log must reside on local drives. Although we can't use mapped drive letters for storing distribution database files, we can use any drives that the server computer sees as local—that is, drive arrays that might physically reside outside the server. We might want to change the default paths to the data and log files and the distribution database name.
The next figure lets us enable publishers and customize properties for each publisher.
)
Figure
7.Notice that this screen allows us to enable all servers currently registered in the Enterprise Manager for publishing or to click New to register a new server. We can also specify the distribution database that we want a particular publisher to use. Clicking the ellipsis button (...) next to the distribution database brings up the following screen.
)
Figure
8.Here I have another opportunity to customize the location of the snapshot folder. Furthermore, I can specify the security credentials to be used by the replication agent when connecting to the publisher. Notice that by default SQL Server uses the SQL Server Agent account (Windows login) to connect to the publisher. Alternatively, we could use a SQL Server login for the same task. For our purposes, the default setting will suffice because publisher and subscriber are running on the same physical computer.
Next, the wizard allows us to enable publication databases. Before we can set up publication(s), we must enable the databases for transactional (includes snapshot) or merge replication.
)
Figure
9.I will enable the PUBS database for transactional replication because that's the one I want to replicate. The following screen lets us enable and customize subscribers.
)
Figure
10.This figure also enables all servers registered in my Enterprise Manager for subscribing. In addition, I can click New to register another server that I can also enable as a subscriber. Clicking the ellipsis button next to the subscriber allows us to customize subscriber options as shown in the following figure.
)
Figure
11.The General tab lets us specify the security credentials for a replication agent's connection to the subscriber. By default, SQL Server attempts using an SA account; however, we can opt to use the SQL Server Agent (Windows login) account to perform the same task. The Schedules tab lets us specify default schedules for Distribution and Merge Agents, as follows.
)
Figure
12.The wizard also informs me that I can override the default schedules when using the Push Subscription Wizard. Replication Agent schedules specify how often each agent executes. Recall that replication agents are implemented as SQL Server jobs. For transactional replication, the log reader agent reads the transaction log of the published database and moves published transactions to the distribution database; the Distribution Agent, on the other hand, runs on the distribution server—it reads transactions in the distribution database and applies them to the subscribers.
Let's leave the agent schedules at their defaults and click Next. We've made it to the final screen of the wizard, as follows.
)
Figure
13.Here, we simply get a summary of what the wizard is about to do: Configure the distribution database and the snapshot folder, enable the PUBS database for publishing, and enable subscribers that can receive transactions from the current server. This is our last chance to make any changes by clicking the Back button. We click Finish.