The wizard then asks you to select servers to subscribe to publications from the current Publisher; select TANYA and REBA. The last screen in this wizard gives you an overview of what you've instructed SQL Server to do: enable TANYA as a Publisher and Distributor, enable the Northwind database for publishing, and let the TANYA and REBA servers subscribe to publications on the TANYA server. When you click FINISH, SQL Server runs the code to set up these servers. When SQL Server is finished, Enterprise Manager pops up a message that says you've successfully configured TANYA for replication.
Creating a Publication
Now you need to run the Create Publication Wizard to create publications for replication. For this example, let's replicate Northwind's Customers table on the TANYA server. Figure 6 shows the wizard screen that lets you choose the type of replication you want to use with the publication. Select Transactional publication. Figure 7, page 34, shows the wizard's next screen, which lets you choose whether to allow immediate-updating subscriptions; select Yes to guarantee data consistency on both servers. By activating immediate-updating subscriptions, you instruct SQL Server to apply committed transactions at the Subscribers immediately after applying them at the Publisher. The wizard then asks whether all Subscribers are SQL Servers.
The next wizard screen lists tables that can't participate in transactional replication. To uniquely identify each modified row for replication, SQL Server requires primary keys on each table participating in transactional replication. So, in a transactional replication solution, you can't replicate tables that don't have a primary key. Another catch with SQL Server replication is that you can replicate identity column values but not the identity property. So if you want to set up two-way (or immediate-updating subscription) replication, you can't use tables with identity columns.
When you choose the Customers table, the wizard pops up the Enterprise Manager message in Figure 8, page 34, which says SQL Server is adding a timestamp column to the table. Contrary to common belief, the timestamp column isn't a date-and-time field; it's a field that tracks the sequence of modifications. Thus, if a table doesn't already have a timestamp column—as is the case with the Customers table—SQL Server adds the column as a way to identify the sequence of modifications that occur on the same table but on different servers.
The remaining wizard steps configure the Northwind publication on TANYA and let you define data filters to specify which data you want to replicate. For simplicity's sake, let's replicate all the data in the Customers table.
Configuring a Subscriber
With your Publisher, Distributor, Subscribers, and publications set up, you can now configure the type of subscriptions you want on the REBA Subscriber. You can select either pull or push subscriptions. Push subscriptions tend to work best for replicating frequently changed values; pull tends to work best for retrieving infrequent data updates. For this example, choose Push New Subscription from the Create and Manage Publications screen in Figure 9. The resulting Push Subscription Wizard screen lets you select which database you want to push the Customers table to; from a drop-down list of databases, select the Test database on REBA. The Push Subscription Wizard then asks you whether you want this to be an immediate-updating subscription; select Yes.
The next wizard screen lets you specify the acceptable latency—how often you want to deliver transactions from one server to another—by setting the Distribution Agent schedule. Choose continuously updating subscribers, which guarantees minimal latency. The wizard then lets you specify whether the Subscriber needs to receive table schema and data. Because REBA's Test database doesn't have any user tables, select the option to transfer table schema and data. You can now start the following required services on the participating servers: Microsoft Distributed Transaction Coordinator (MSDTC) on both the Publisher and the Subscriber, and SQL Server Agent on the Publisher. The last wizard screen lets you review the options you've chosen, then sets up the subscription on REBA.
Viewing Your Handywork
From Enterprise Manager, you can view the results of the replication wizards' work. First, notice in Figure 10 that the Northwind database is now available in the hierarchy of Replication Monitor, Publishers, TANYA. If you expand the Northwind database folder and look at the stored procedures, you'll see sp_MSsync_ins_customers_1, sp_MSsync_upd_customers_1, and sp_MSsync_del_customers_1. These procedures forward committed transactions from TANYA to REBA. Now look at the stored procedures in REBA's Test database. You'll notice similar procedures sp_MSins_customers, sp_MSupd_customers, and sp_MSdel_customers, which forward committed transactions from REBA to TANYA.
If you look at the tables on REBA, you'll see that the replication process has added the Customers table and system tables MSreplication_objects, MSreplication_subscriptions, and MSsubscription_properties to REBA. These tables track the Publisher and Subscriber, as well as replication type and frequency information.
The SQL Server Agents folder under TANYA shows that the replication process has added several replication jobs. Extended and system stored procedures handle these replication jobs, but how does SQL Server know which parameters to pass to the stored procedures? Notice in Figure 10 that TANYA now has a distribution database. When you configured TANYA as a Distributor, the replication wizard added to this distribution database 21 replication-specific tables—along with other system tables present in every database by default. These tables contain all the information the replication agents and replication-specific stored procedures need.
To test whether this replication process works, you can run the following query on the TANYA server:
Use NORTHWIND
update customers set contactname = "maria anderson" where customer_id = 'alfki'
This query modifies the top row of the Customers table. From Enterprise Manager, you can then look at TANYA's Log Reader Agents folder to see that 1 transaction(s) with 1 command(s) were delivered. The Status is Running. If you check the top row of REBA's Test database, you'll see that the replication process has set ContactName to maria anderson.
To investigate the cleanup jobs that the replication wizards created on TANYA, open the Miscellaneous Agents folder. Cleanup jobs remove the replicated transactions from the appropriate tables in the distribution database.
How does SQL Server implement this replication process? First, SQL Server generates table schema (.sch) files, which the database system uses to create tables. SQL Server also generates table index creation (.idx) files, which it uses to create indexes on Subscribers, and bcp files for all replicated tables. The database system then generates stored procedures for each insert, update, and delete action on the Publisher. SQL Server applies schema files on each Subscriber and applies index files to appropriate tables on each Subscriber. SQL Server then uses bcp to copy data into the Subscriber tables. Finally, SQL Server creates stored procedures for insert, update, and delete actions only on immediate-updating subscriptions.
If SQL Server automatically generates these replication tasks, why should you be interested in the behind-the-scenes details? Even though SQL Server 7.0 dramatically improves and simplifies replication setup and implementation, problems still crop up. The causes might have nothing to do with SQL Server. Perhaps an inexperienced DBA didn't answer a wizard question appropriately or didn't use the right security settings. Whatever the cause, when you're trying to troubleshoot replication problems, every piece of information you can bring to the investigation is valuable.
Without a doubt, replication is an advanced feature, but it offers exciting capabilities—especially if your organization needs to keep data on different servers in synch. SQL Server 7.0's new functionality makes replication even more powerful and flexible, and its wizards make setting up replication a simple matter of answering questions—just make sure you give the right answers.