The replication process begins with SQL Server marking for replication in the Publisher server's database transaction log the transactions against all articles in the publication. The Distribution server's Log Reader Agent clears the transactions marked for replication from the transaction log and copies them into the Distribution database. After the Distribution Agent starts, it replicates these transactions to the Subscriber database. However, the Distribution Agent can begin only after you've initialized the Subscriber database's schema and data. Therefore, you initialize schema and data by restoring the Publisher database at the Subscriber server. If the Publisher database were available to users for updating during the restore period, some job process or scheduled application might log in to the server and make data modifications. This action would make the data immediately out of sync between the Publisher database and the Subscriber database. After the restore, you enable a push subscription to the Subscriber database and restart the Publisher server in multiuser mode. The Subscriber database can now accept replicated transactions.
Creating the stored procedures. In addition to synchronizing the database schema and data, the Snapshot Agent also creates special stored procedures that the Subscriber database needs to process replicated transaction commands. Because this example bypasses the Snapshot Agent, you need to create these stored procedures manually.
Each insert, update, or delete operation against a publication article must have a corresponding insert, update, or delete stored procedure on the Subscriber database. Begin by using sp_helppublication to obtain the names of all publications in the Publisher database. The following shows the code you need to obtain this information:
USE Northwind
GO
EXEC sp_helppublication
GO
This query returns Northwind_Pub1, which is the name you chose when you created the publication.
Next, you use the sp_helparticle stored procedure to obtain the article_id value associated with each publication, as follows:
USE Northwind
GO
EXEC sp_helparticle 'Northwind_Employees'
GO
For the publication Northwind_Pub1's Employees article, the article_id value is 1. For subsequent articles in this publication, SQL Server increments the article_id values by 1.
You can now create the insert, update, and delete stored procedures for the Subscriber database. First, execute the commands that Listings 1 and 2 show to create the Subscriber database's insert and delete stored procedures, respectively. (To create the update stored procedure, execute the code in Web Listing 1. For download instructions, see the More on the Web box, page 57.) Note that the Employees table contains an IDENTITY column. To verify that the IDENTITY property is active, execute the following command from Query Analyzer:
SELECT name FROM Northwind..sysobjects
WHERE type = 'U'
AND (objectproperty(object_id(name),
'tablehasidentity') = 1)
The Employees table will appear among the table names in the result set. If you had used the Snapshot Agent to synchronize the schema and data, the Employees table wouldn't appear in the result set (i.e., the Snapshot Agent doesn't transfer the IDENTITY property as part of the schema synchronization process). You must add the SET IDENTITY_INSERT statement to the insert stored procedure to override the auto-incrementing action of the IDENTITY property because any rows inserted into the Publisher database's Employees table should also be inserted into the Subscriber database's Employees table.
The IDENTITY column's auto-incrementing action in the Publisher database increments the IDENTITY value, but you must explicitly insert this row into the Subscriber database's Employees table. Thus, we added the statement SET IDENTITY_INSERT Employees ON before the INSERT statement and SET IDENTITY_INSERT Employees OFF after the INSERT statement, as Listing 1 shows. In addition, because SQL Server disallows updating an IDENTITY column's value, we modified the update stored procedure by commenting out the line that would cause SQL Server to update the IDENTITY column. For this example, the IDENTITY column is the Employees table's EmployeeID column.
In addition to the IDENTITY property, the Subscriber database might also have triggers on database tables. You should disable all triggers because you don't want SQL Server to fire the trigger on the Subscriber database when the database receives an insert, update, or delete operation. Because the trigger has already fired on the Publisher database, firing the trigger on the Subscriber database might repeat the transaction.
Disabling publishing on the Subscriber database. You've manually synchronized schema and data by restoring the Publisher database at the Subscriber. In addition, recall that in the sequence of steps outlined here, you created the publication on the Publisher before backing up the database. Once a publication exists, SQL Server updates the row in the sysdatabases system table and sets its category value to 1. For this example, this action indicates that SQL Server has enabled the Northwind database for publishing. Because you restored the database on the Subscriber server, the Northwind database would be enabled for publishing on the Subscriber server also. To verify that this statement is true, execute the following query from Query Analyzer on the Subscriber server:
SELECT * FROM master..sysdatabases WHERE name = 'Northwind'
The categoryid is equal to 1. You should disable this property because you don't want SQL Server to create any publication or enable any subscriptions for the Subscriber database. To disable publishing for the Subscriber database, execute the commands in Listing 3 on the Subscriber server.
Pushing or pulling the subscriptions. Pushing a subscription means letting the Subscriber server passively accept transaction commands from the Distribution server; pulling a subscription requires that the Subscriber server establish a connection with the Distribution server and retrieve the replicated transaction commands. Thus, a subscription involves propagating data and updates to a Subscriber server, which is the server on which the copy of the production database is maintained and updated.
To push a new subscription, start Enterprise Manager, then select Tools, Replication, Push Subscription to Others. Under the Northwind database, highlight the publication Northwind_Pub1, click Push New Subscription to access the Push Subscription Wizard, then click Next. On the Choose Subscriber window, highlight the Subscriber server among all enabled Subscriber servers that you want to receive the replicated transactions, and click Next. In the Choose Destination Database window, click Browse to preview all the available databases on the Subscriber server. Highlight the Northwind database (i.e., the database to which you want to replicate the transactions against articles in your publication), and click OK. The Northwind database should now appear in the Subscription database name text box. If the database's name doesn't appear, enter it into the text box and click Next.
Prev. page
1
[2]
3
next page 
