All servers that participate in a replication must be registered. (Server registration is described in Chapter 3.) After registering servers, the distribution server, publishing server(s), and subscription server(s) must be set up. The following sections describe configuration of these processes using the corresponding wizards.
1. Configuring the Distribution and Publication Servers
Before you install publishing databases, you must install the distribution server and configure the distribution database. You can set up a distribution server by using the Configure Distribution Wizard. This wizard allows you to configure the distributor and the distribution database and to enable publisher(s). With the wizard you can
- Configure your server to be a distributor that can be used by other publishers
- Configure your server to be a publisher that acts as its own distributor
- Configure your server to be a publisher that uses another server as its distributor
This section shows a scenario for data replication of the sample database using the following servers: LAPTOP-TVUM0CNL and NTB01112. The former will be used as a publisher and distributor, while the latter will be the subscriber. The first step is to use the Configure Distribution Wizard to set up the LAPTOP-TVUM0CNL server to be a publisher that acts as its own distributor. (Additionally, the wizard will create the distribution database.)
NOTE You can also use the system procedures sp_adddistributor and sp_adddistributiondb to set up the distribution server and the distribution database. sp_adddistributor sets up the distribution server by creating a new row in the sysservers system table. sp_adddistributiondb creates a new distribution database and installs the distribution schema.
To start the wizard, start SQL Server Management Studio, expand the instance, right-click Replication, and select Configure Distribution. The Configure Distribution Wizard appears.
On the Distributor page, choose the LAPTOP-TVUM0CNL server as the distribution server and click Next. After that, select the folder in which snapshots from publisher(s) that use the distribution server will be stored and click Next. On the Distribution Database page, select the name of the distribution database and log files and click Next. On the Publishers page, enable the publisher(s) (the LAPTOP-TVUM0CNL server in this example), choose whether to finish the configuration process immediately or generate the script file to start the distribution configuration later, and then click Next. Figure 18-4 shows the summary of all steps that you have made to configure the LAPTOP-TVUM0CNL server as the distributor and publisher.
NOTE The existing publishing and distribution on a server can be disabled using the Disable Publishing and Distribution Wizard. To start the wizard, right-click Replication and choose Disable Publishing and Distribution.
After you configure the distribution and publishing servers, you must set up the publishing process. This is done with the New Publication Wizard, explained in the following section.
2. Setting Up Publications
You can use the New Publication Wizard to:
- Select the data and database objects you want to replicate
- Filter the published data so the subscribers receive only the data they need
Assume that you want to publish data of the employee table from the LAPTOP- TVUM0CNL server using the snapshot replication type. In this case, the entire employee table is the publication unit.
To create a publication, expand the server node of the publishing server (LAPTOP- TVUM0CNL), expand the Replication folder, right-click the Local Publications folder, and choose New Publication. The New Publication Wizard appears. On the first two pages, choose the database to publish (sample) and the publication type (in this case, the snapshot publication) and click Next. Then select at least one object for publication and click Next (in this example, select the entire employee table). The New Publication Wizard also allows you to filter (horizontally or vertically) the data that you want to publish. The next page, Snapshot Agent, allows you to create the snapshot of the selected data immediately and/or to run periodically. (For our example, we will create the snapshot immediately.)
On the Agent Security page, specify the security settings for the Snapshot agent. To do this, click the Security Settings button and type the Windows user account under which the Snapshot agent process will run. (The user account must be entered in the form domain_name\ account_name) Click OK on the Security Setting dialog box and click Next. In the Wizard Actions page, you can decide to finish the configuration process immediately or generate the script file to start the publication creation later. Figure 18-5 shows the summary of all steps made to set up the employee table as a publication unit.
The last step is to configure the subscription servers, discussed in the following section.
3. Configuring Subscription Servers
A task that concerns subscribers but has to be performed at the publisher is enabling the publisher to subscribe. Use Management Studio to enable a subscriber at the publishing server. First expand the publishing server, expand Replication, right-click Local Subscriptions, and choose New Subscriptions. The New Subscription Wizard appears. You can use the wizard to:
- Create one or more subscriptions to a publication
- Specify where and when to run agents that synchronize the subscription
On the Publication page, choose the publication for which you want to create one or more subscriptions and then click Next. (In this example, choose the publication already generated with the New Publication Wizard.)
On the Distribution Agent Location page, you must choose between the push and pull subscriptions. A push subscription means that the synchronization of subscriptions is administered centrally. For this replication, check Run All Agents at the Distributor. To specify the pull subscription, check Run Each Agent at Its Subscriber. Click Next. (Because our subscription is pushed from the central publisher, we choose the first option.)
On the Subscribers page, you must specify all subscription servers. If the subscription servers have not been added, click Add Subscriber, and after selecting all servers to which data will be replicated, click Next. Before you finish the process, the wizard shows you the summary of the subscription configuration.
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.