Setting Up Replication

In general, SQL Server 2000 data replication is exceptionally easy to set up via Enterprise Manager/Wizards. However, please be warned : If you use the wizards, be sure to generate SQL scripts for every phase of replication configuration. In a production environment, you most likely will rely heavily on scripts and will not have the luxury of much time to set up and break down production replication configurations via wizards.

You always will have to define any data replication configuration in the following order:

1. Create or enable a distributor to enable publishing.

2. Enable publishing. (A distributor must be designated for a publisher.)

3. Create a publication and define articles within the publication.

4. Define subscribers and subscribe to a publication.

In Figure 22.23, you can see a shot of Enterprise Manager without data replication configuration defined yet. This picture will change dramatically as we build up a typical data replication configuration and allow it to begin replicating data.

Figure 22.23. Enterprise Manager.

Enable Publishing

Before setting up a publisher, you will have to designate a distribution server to be used by that publisher. As has already been discussed, you can either configure the local server as the distribution server, or choose a remote server as the distributor. You can create a distributor in one of two ways. You can configure the server as a distributor and publisher at the same time, or you can configure the server as a dedicated distributor. You can do this using the Configure Publishing, Subscribers, and Distribution Wizard. After the distributor has been set up, you can finish enabling publishing.

You must be a member of the sysadmin server role to use this wizard. Use the following steps to configure a server as a distributor:

1. From Enterprise Monitor, choose the Tools, Replication, Configure Publishing, Subscribers, and Distribution selection. This will start you through the wizard to accomplish three tasks :

   • Specify and create a distributor

   • Configure the properties of the distribution server

   • Configure the properties of a publisher that will use that distributor

     Figure 22.24. Select Tools, Replication, Configure Publishing, Subscribers, and Distribution.

     

2. You must either select or install a distributor for this publisher to use. We will choose to have the distributor on the same machine as the publisher.

3. You'll be asked to specify a snapshot folder. Give it the proper network full pathname. Remember: Much data will be coming here, and it should be on a drive that can support the snapshot concept without filling up the drive.

4. You are then asked to configure the distribution database. The default settings will cover most of your needs. Figure 22.25 shows all of the things that will be configured using this default setting option.

   Figure 22.25. Summary of default settings.

   

5. The end result will yield a distribution database being created, the distribution server set up, Replication Monitor being added to Enterprise Manager, and Publishing enabled. In fact, if you look a bit closer at Figure 22.26, you will see some replication agents being set up as well.

   Figure 22.26. Replication agents are set up.

   

Creating a Publication

Now that the distribution database has been created and publishing has been enabled on the server, you can create and configure a publication. Again, select the Replication option from the Tools menu:

1. From Enterprise Monitor, choose the Tools, Replication, Create and Manage Publications selection.

2. You will be immediately prompted to select the database on which you are going to set up a publication. We chose the Northwind database.

3. You must now choose the type of replication method for this publication. This will be Snapshot replication, Transactional replication, or Merge replication. We have selected Transactional replication.

4. In the next screen, you will be asked to select the Updatable Subscriptions method you want to use. If you have not decided to allow this, just skip this screen by clicking the Next button.

5. In addition, you now can choose to utilize Data Transformation Services (DTS) as part of this publication if you have extremely complex data transformations that you want to be part of this process. Otherwise, just click No.

6. Now, you must indicate what the possible subscribers are going to be. Will they be MS SQL Server 2000 servers, MS SQL Server 7.0, or Heterogeneous data sources (Oracle, Access, or earlier versions of MS SQL Server 6.5 and below)?

7. From the Specify Articles screen, you are prompted to create articles in your publication (see Figure 22.27). You must include at least one article in your publication. These can be tables, views, or stored procedures. After you select an article, a button with an ellipsis () appears after the article name . If you click this button, you are able to select options for your article. For snapshot and transactional replication, you can determine how the snapshot portion of the replication will occur. If you have selected Merge replication, you will be able to select the conflict resolver that you are going to use. You can either select the default SQL Server resolver or create your own stored procedure or COM objects.

   Figure 22.27. The Specify Articles screen allows you to choose which tables you are going to publish.

   

8. If article issues exist, they will be presented to you here. Identity columns might be an issue because of the way they will be treated in replication.

9. You can now name your publication something meaningful.

10. You are given a chance to customize the properties of the publication. This includes adding data filters and allowing anonymous subscribers. If you select that you do want to create anonymous subscribers, SQL Server will allow any server to connect to and receive data from your publication.

11. As you finish this wizard, it displays what it is doing in a nice dialog box, as shown in Figure 22.28. When this finishes, you will have a valid publication that simply needs to be subscribed to.

    Figure 22.28. Wizard summary.

    

Creating Subscriptions

Now that you have installed and configured the distributor, enabled publishing, and created a publication, you need to create subscriptions. As you can see from Enterprise Manager in Figure 22.29, after the publication has been defined, the snapshot agent is created along with the Log Reader Agent (because we chose transactional replication). Also notice that a shared hand is on the database that we are publishing.

Figure 22.29. Enterprise Manager with publication agents.

Remember that two types of subscriptions can be created: push and pull. Pull subscriptions allow remote sites to subscribe to any publication that they are allowed to, but you must be confident that the administrators at the other sites have properly configured the sub-scriptions at their sites. Push subscriptions are easier to create because all of the subscription processes are performed and administered from one machine. This also makes them the most common approach taken. Following are the steps to create a push subscription:

1. From Enterprise Manager, choose the Tools, Replication, Create and Manage Publi-cations selection. You will now see, in Figure 22.30, the Push New Subscription option active. This will allow you to do the following:

   • Select one or more subscribers

   • Specify where the data is to be replicated into for each subscriber

   • Set/configure the initialization and synchronization process schedule so that they happen when they need to

     Figure 22.30. Create and manage publications.

     

2. Specify the subscriber from the list that has been registered to SQL Server and has been enabled for subscribing.

3. From the Choose Destination Database screen, you are prompted to choose to which database you will publish. If you click the Browse Database button, you can see a list of all the databases on the destination server (see Figure 22.31). If you want to create a new database on the destination server, click the Create New button.

   Figure 22.31. The Choose Destination Database screen.

   

4. Next, we will configure how the distribution agent will run. If you want to provide the shortest latency, select the Run Continuously option. Otherwise, configure the distribution agent to run at specific times during the day. By default, the distribution agent runs once an hour every day.

5. You then are prompted to set the initialization of the database schema and data. You will have an option to create the schema and data at the subscriber (and also to do it immediately) or to skip this initialization altogether because you have already created the schema and loaded the data manually.

6. The next part of the process, the Start Required Services dialog box, checks whether the required services are running on the server.

7. The last screen, shown in Figure 22.32, is a summary screen that outlines the options you selected and the steps SQL Server will perform to create the subscription. After you click the Finish button, SQL Server will create the subscription according to your specification.

   Figure 22.32. Push Subscription Wizard summary.

   

Now that replication is set up, the only thing left to do is wait. If you have specified that the schema and data be created immediately, things will start happening quickly.

You will first see the snapshot agent start up and begin creating schema files (.SCHs), extracting the data into .BCP files (.BCPs), and putting everything in the snapshot folder on the distribution server. Figure 22.33 shows the last part of the snapshot tasks in its log.

Figure 22.33. Snapshot task details.

In addition, in Figure 22.34, we took a quick peek, via Windows Explorer, at the contents of the snapshot working directory to see all of our .SCHs and .BCPs being created. This is often where trouble is encountered because of the lack of space on the disk drive.

Figure 22.34. Using Windows Explorer to verify files.

Now that the snapshot agent is essentially finished with its tasks, the distribution agent finishes the job. As you can see from Figure 22.35, the distribution agent is applying the schemas to the subscriber. The bulk copying of the data into the tables on the subscriber side will follow accordingly . After this bulk copying is done, the initialization step is completed and active replication begins.

Figure 22.35. Applying schemas to the subscriber.