| Replicating a database is a long process. The first step is to set up the distribution server, which is described in this section. Step by Step 11.2 shows you how to implement replication: STEP BY STEP 11.2 Configuring the Publishing and Distribution Mechanism -
Open the Enterprise Manager from the Start menu in Programs, SQL Server 2000. -
Select the default instance of SQL Server and on the Tools menu. Point to Replication and then select Configure Publishing, Subscribers and Distribution, which initiates the Configure Publishing and Distribution wizard. -
On this screen, you are prompted to select a Distributor. Make sure that the Distribution working folder and Distribution database have enough hard disk space. Select the default setting and click Next . If you want to choose some other server as your Distributor, that Distributor must be configured as such. NOTE SQL Server Agent To continue the wizard, you have to have the SQL Server agent configured to start automatically. If, however, you do not have the SQL Server agent service configured to AutoStart, you get an extra screen at this point that prompts you to set it to AutoStart. You may also be prompted to Specify Snapshot Folder. -
The third screen asks you whether you want to customize your Distributor or you want to stick with the default settings provided by SQL Server. In this case, select the first option (Yes, Let Me . . . ). Choosing this enables you to change Distributor-specific information; this is used for example purposes so that you may know how to configure all steps in the wizard. -
Because you selected the option specifying that you would configure your Distributor, you are faced with the Provide Distribution Database Information selection screen as displayed in Figure 11.10. This screen enables you to name the Distribution database, set the folder of the distribution database, and set the folder of the distribution log. When placing the distribution database and log, make sure you have enough hard disk space. In this case, leave the default settings as they are and click Next to continue. Figure 11.10. Distribution database information.  -
This wizard also helps enable your Publishers, and this is the screen on which you do it. This screen lists all the registered SQL Servers. Only one listed registered server is on the list (to install a second server, you have to install another instance of SQL Server 2000; it is, however, not necessary to install a second server), so select it as the Publishing server and click Next to continue. -
Use this screen to enable publication databases to be replicated. This screen lists the databases that can be replicated under Databases. You can select for merge or transactional replication. In this case, select the Pubs database and check the box under Trans, as shown in Figure 11.11, to set up transactional replication. Figure 11.11. Setting up transactional replication.  -
Similar to the selection screen in step 6, this screen enables you to select the Subscriber or Subscribers. Remember that this example uses a single instance of SQL Server 2000 so all three roles (Publisher, Distributor, and Subscriber) will be played by one server. Select the Subscriber, and click Next. -
The final screen displays the configurations you have made throughout this wizard. If everything is correct, click Finish. After you click finish, SQL Server has adequate information to implement the choices you have made. | You have now implemented the first part of replication. To see for yourself, expand your server in the left pane of the Enterprise Manager and then expand Databases. You can see a new Distribution database. Also, notice that the icon beside the Pubs database has a small hand beside it which shows that it is configured for replication. The Replication Monitor icon also signifies replication. After you implement the Publisher, Subscriber, and Distributor, you have to implement a publication for the Subscriber to receive. When setting up a publication, you have to decide on the replication type you want to use. Step by Step 11.3 shows you how to add a publication. STEP BY STEP 11.3 Setting Up a Publication -
Open the Enterprise Manager from the Start menu in Programs, SQL Server 2000. NOTE Quick Recap on Immediate Updating Immediate updating enables the Subscribers to change their local copies of data; these changes made by Subscribers are sent back to the Publisher (using MS DTC). Queued updating is when the Subscribers are enabled to change their local copy of replicated data. These changes are not sent directly to the Publisher but are stored at an intermediate state until they are sent. Therefore, if you select none of these, the replicated data is read-only. Also, if the Subscriber tries to change the data, that data might change at the Subscriber, but those changes are not propagated back to the Publisher and the other Subscribers. -
Select the current server and then choose Create and Manage Publications from the Tools menu. This initiates the Create and Manage Publications dialog box. -
Select Pubs and then click on Create Publication. This starts the Create Publication Wizard. -
If the Show Advanced Options check box is selected, you can choose whether Subscribers are updateable and data is transformable. Select Show Advanced Options and then click Next to continue. -
On the second screen, you can choose the database you want to be published. Select Pubs and then click Next to proceed. -
On the third screen, you are asked which type of publication you want to implement (transactional, merge, snapshot). In this case, select Transactional Publication, as shown in Figure 11.12, and click Next to proceed. Figure 11.12. Setting the replication type.  -
This screen asks whether you want queued updating, immediate updating, none, or both for your Subscribers. -
This screen asks whether you want to send slightly different data to the Subscribers in your enterprise. This is used when slightly different data is needed at different Subscribers and when more than one Subscriber is involved. Transformable subscriptions can be enabled only in a snapshot or transactional replication strategy. Because you have only one Subscriber and don't need different data at the Subscriber, select the second radio button, to Bypass Selection, and click Next. -
The next screen asks whether the Subscriber is running Microsoft SQL Server 2000, SQL Server 7, or a totally different Database Management System (DBMS) altogether. In this case, you run only SQL Server 2000, so select SQL Server 2000 and click Next. -
From this screen, you choose what to publish as an Article, whether it is a table, a stored procedure, or a view, but in this case you will publish a table. Publishing a table requires that the table include a Primary Key. On the right pane, select the Authors table and display its Properties page by clicking the ellipsis ( . . . ) button as shown in Figure 11.13. Some of the tables can't be replicated, because they probably don't include a Primary Key. This requirement is only for a transactional publication. Figure 11.13. Article selection.  NOTE Selecting Subscriber Type When you select SQL Server 7 as the subscription type, only Subscribers running version 7 of SQL Server may subscribe to it. Also, the options thereafter in the wizard are shaped so that they are compatible to SQL Server 7 Subscribers. If you select Heterogeneous Subscribers, you are specifying that the Subscriber is either Microsoft SQL Server 7 or earlier, or that the Subscriber is using a different DBMS. Subscribers classified as running heterogeneous data sources can subscribe to only transactional or snapshot publications. -
This opens the Table Article Properties dialog box. On the General tab, change the Destination Table name to rtblAuthors and Destination owner to Dbo . Because you are using the Publisher also as the Subscriber, naming the destination table with the prefix rtbl (rtblAuthors) is done to avoid confusion that could occur on the Subscriber with the existing Authors table. The Snapshot tab is where the setting for the initial Snapshot is configured. Leave all properties to their default, and then click OK. -
When you get back to the Specify Articles screen, click Next. -
This leads to the Choose Publication Name screen. In the Publication Name box, enter Publishers Data. As the description, type an appropriate name for your publication and click Next to continue. -
This screen asks whether you want to add more complexity to the publication. Selecting Yes enables data filters, anonymous subscriptions, and other properties to be set. Select Yes and then click Next. -
The next screen asks whether you're going to filter data using vertical partitioning or horizontal partitioning. With vertical partitioning, also known as vertical filtering, you select only certain columns of the published data. With horizontal partitioning, a certain number of rows are selected from the published data. Selecting Horizontal Partitioning produces one screen and selecting Vertical Partitioning results in another screen. Select Vertical Paritioning and click next. NOTE Anonymous Subscription An anonymous Subscriber is a type of pull Subscriber where the Subscriber and subscription information is not stored. -
As shown in Figure 11.14, you can now select and deselect columns for your publication. For this example, deselect the Address column and click Next. Figure 11.14. Column selection.  -
This screen asks whether you would like to allow anonymous Subscribers to access your publication. In a scenario where there are numerous Subscribers and pull subscriptions would be desired, a Yes choice would be appropriate. Select Yes and then click Next. -
This is the last selection screen for the Create Publication Wizard. This screen is the Snapshot Agent Schedule screen. The Snapshot agent schedule determines when a snapshot is created to refresh the data at the Subscribers. Remember that before setting replication, a Subscriber must receive a Snapshot of your data. Select the Create the First Snapshot Immediately check box and click Next. -
This is where the wizard ends. Click Finish to end the wizard. This leads back to the Create and Manage Publications dialog box. | NOTE Anonymous Subscriptions An anonymous subscription is a type of pull subscription where the Subscriber and subscription information is not stored and registered. To create a pull subscription (see Step by Step 11.4), select Yes. Selecting No would mean that all Subscribers would need to be registered in the Enterprise Manager.
After creating a publication, you have to pull or push it to the Subscriber. This example pulls it. Follow the steps in Step by Step 11.4 to do this. REVIEW BREAK: Updating Subscribers When a Subscriber is said to be updateable, it means that the Subscriber is able to make changes to the replica, as opposed to its read-only default. Updating Subscribers can be implemented when data is replicated with either transactional or snapshot replication. There are two types of updateable Subscribers: immediate updating and queued updating. The big difference in these two is that in queued updating an active network connection is not absolutely necessary; it allows snapshot replication and transactional replication Subscribers to modify published data without an active network connection to the Publisher. STEP BY STEP 11.4 Pulling a Subscription -
Open the SQL Server Enterprise Manager from the Start menu and connect to your default server. NOTE Active Directory Services Active Directory is a topic that can span a couple hundred pages; so, I'll try summing it up in a couple of sentences. The Active Directory is a central component found in Windows 2000 that provides storage for information about network-based entities, such as applications and files. With the Active Directory, you can view the different replication objects available, and also even subscribe to publications. -
Highlight your server and then select Replication, Pull Subscription to Your Server from the Tools menu. This initiates the Pull Subscription dialog box. Select Pubs and then click Pull New Subscription. -
On the first screen, check the Show Advanced Options check box and then click Next. -
The second screen offers you a choice to either search through registered servers or through the Active Directory for the publication you want to subscribe to. Select the Look at Publications from Registered Servers radio button and click Next. -
Expand your server and then select Publishers Data: Pubs and then click Next. (You may have an extra screen to Specify Synchronization Agent Login.) -
Now you choose where you want the replica to reside. Click New and then type Replica as the name of the new database. Leave all properties as they are and then click OK. Coming back to the wizard, select Replica and click Next to continue. -
This screen asks whether the subscription is anonymousextra information about the subscription and Subscriber is not needed; thus it can be used for Internet-based sites with FTP download involved. Select No, This Is a Named Subscription and click Next. -
To initialize the subscription, select the default option, Yes, Initialize the Schema and Data. The Distribution Agent then runs and applies the snapshot files to the Subscriber. If you are planning to manually load the schema and data at the Subscriber, the subscription does not need to be initialized and you should select No. In this case, select Yes and then click Next. -
This screen asks you to specify where the snapshot folders reside at the time of subscription initialization. This can be the distribution working folder, an FTP server for downloading, or the Internet or any other media. Select the default option (which is the distribution folder) and then click Next. -
This screen enables you to select the update frequency of the Distribution Agent. The first option specifies that it updates continuously, providing minimized latency in modifying at the Publisher. The second offers the choice of setting the updating time: after hours, days, or between certain times. The last option tells SQL Server that you will manually replicate changes (best suited for Merge Replication). Select the last option (On Demand Only), the type of replication that you are doing does not need continuous updating, and click Next. -
Review the desired options on the finishing screen and then click Finish to end the wizard. | IN THE FIELD: CONTINUOUS REPLICATION Actually, in a real-world situation, transactional replication would most probably be implemented with continuous updating, but because we're just testing replication, we'll stick with On Demand. | Now you have to start the replication process manually. First, synchronize your subscription. To do this, expand your default server and then click the Replication folder. Click the Subscriptions folder, then right-click your subscription, and then select Start Synchronizing. Great! That was your final step in setting up a complete replication process. Now to convince yourself that the replication was successful, follow Step By Step 11.5. After your replication process has been configured, you need to perform a few tasks to ensure that the replication has been set up and is ready for operation. To confirm replication success, you should perform the following check: STEP BY STEP 11.5 Confirming Success of Replication -
Open the SQL Server 2000 Enterprise Manager from the Start menu in Programs, Microsoft SQL Server. -
Connect to your default server, expand it, and expand Databases. -
Expand the Replica database and then click on Tables. -
Locate RtblAuthors and then right-click it. -
Point to Open Table and then select Return All Rows. -
If you followed the steps correctly, you should see a result-set returning all columns from the replicated Authors table. Notice that the address column is not there. Vertical partitioning removed it. |  |
