Now that you've established how to get the publication to the Subscriber server, you're ready to set the Distribution Agent's schedule. The Distribution server through the Distribution Agent ensures that SQL Server replicates updates made at the Publisher server to the Subscriber database. You can schedule the Distribution Agent to run at specified times or let it run continuously. The default is to let the Distribution Agent run continuously. If the Distribution server and Publisher server are the same, if you expect a large volume of user connections and transactions, and if you have multiple Subscriber servers, then the overhead that the Distribution Agent requires, in terms of memory and CPU utilization, could cause performance problems. However, with only one Subscriber server, you shouldn't have any performance problems.
Accept the default—to let the Distribution Agent run continuously—and click Next to display the Initialize Subscription window, which asks whether you want to use the Snapshot Agent to initialize the destination database's schema and data. For this example, choose No, the subscriber already has the schema and data. This option requires that you restore the Northwind database from your Publisher server backup. Click Next to have SQL Server check the SQLServerAgent service's status. The SQLServerAgent must be running because this agent monitors and schedules the replication agents. Click Next, Finish to complete the Push Subscription Wizard. Now, you have your transactional replication implementation ready to push the Northwind_Pub1 publication's subscription from the Northwind publication database to the Subscriber server.
Troubleshooting
Now that you've disabled publishing for the Subscriber database, you can start the Distribution Agent on the Distribution server to begin replication. The Distribution Agent automatically creates a table named msreplication_subscriptions in the Subscriber database. The table contains information about when the Distribution database's transaction batch began. In some cases, you might need to modify this table's entry_time column value. For example, if you decided to enable a push subscription to the Subscriber database before your database backup and the Publisher server was in multiuser mode during the Subscriber database restore, data modifications might have occurred. These data modifications would result in entering transactions into the Distribution database through the Log Reader Agent. After the restore has completed, SQL Server wouldn't yet have committed these transactions to the Subscriber database because the backup wouldn't reflect these transactions, so the Subscriber database wouldn't reflect them either after the restore. Also, the time at which SQL Server put these transactions in the Distribution database will be earlier than the time at which the restore completed. When you start the Distribution Agent, the entry_time column value reflects only the time at which SQL Server created the msreplication_subscriptions table. The difference between the entry_time column value in the msreplication_subscriptions table and the time at which SQL Server copied the first batch of transactions into the Distribution database could cause data synchronization problems between the Publisher and Subscriber databases.
To update the entry_time column in the msreplication_subscriptions table with the value corresponding to the time at which the Publisher database backup completed, execute the following query against the Distribution database.
SELECT * FROM msrepl_transactions
WHERE entry_time >= Backup Completion Time
In the result, which Figure 1 shows, the Xact_SeqNo column contains a hexadecimal value that corresponds to the entry_time column value. Next, update the timestamp in the Subscriber database's msreplication_subscriptions table by using the following code; this update lets you restart the Distribution Agent with the next available transaction ID:
UPDATE msreplication_subscriptions
SET transaction_timestamp = 0x00000DB70001D9B10032
Last, because replication replays some transactions that already exist in the Subscriber database, you might experience some primary key violations. Conversely, if you start the Distribution Agent and receive no primary key violations, replication might have missed some transactions. You can make sure you have all the transactions and no duplicates by using the stored procedure sp_publication_validation to validate the Subscriber data by both rowcount and checksum.
Virtually Maintenance Free
Many IT shops have large SQL Server 7.0 databases supporting mission-critical OLTP applications. These applications might also support business functions almost continuously and have limited database maintenance time. Often, companies also require that IT provide reporting capabilities and maintain a reporting server.
If time doesn't permit you to use the Snapshot Agent for transactional replication, you might resort to either log shipping (restoring to a secondary server each transaction log backup from a primary server) or a daily restore from the previous full backup to maintain a copy of the reporting server's database. Either way, the reporting server's data lags significantly behind the production server's data. In addition, the reporting server might be unavailable because of an attempted restore of corrupted transaction logs or because of how long a full database restore requires. These scenarios could require much of your time and attention.
We've shown you how to set up transactional replication without the Snapshot Agent. Although this setup process can require significant time and effort—especially for databases with many tables—the completed replication implementation should be virtually maintenance free. In addition, if problems occur that cause the Subscriber and Publisher databases to fall out of sync, the time required to reinitialize the data will be minimal; you'll already have completed all the work involved in creating the stored procedures required for replication, and you can devote your DBA resources to other important tasks.
End of Article
Prev. page
1
2
[3]
next page -->
