Replication Architecture
Replication is a set of technologies that allows you to keep copies of the same data on multiple sites, sometimes covering hundreds of sites.
Replication uses a publish-subscribe model for distributing data: 
•	A Publisher is a server that is the source of data to be replicated. The Publisher defines an article for each table or other database object to be used as a replication source. One or more related articles from the same database are organized into a publication. Publications are convenient ways to group related data and objects that you want to replicate together.

•	A Subscriber is a server that receives the data replicated by the publisher. The Subscriber defines a subscription to a particular publication. The subscription specifies when the Subscriber receives the publication from the Publisher, and maps the articles to tables and other database objects in the Subscriber.

•	A Distributor is a server that performs various tasks when moving articles from Publishers to Subscribers. The actual tasks performed depend on the type of replication performed. 
Microsoft® SQL Server™ 2000 also supports replication to and from heterogeneous data sources. OLE DB or ODBC data sources can subscribe to SQL Server publications. SQL Server can also receive data replicated from a number of data sources, including Microsoft Exchange, Microsoft Access, Oracle, and DB2.
Replication Types
SQL Server 2000 uses three types of replication:
Snapshot replication
Snapshot replication copies data or database objects exactly as they exist at any moment. Snapshot publications are typically defined to happen on a scheduled basis. The Subscribers contain copies of the published articles as they existed at the last snapshot. Snapshot replication is used where the source data is relatively static, the Subscribers can be slightly out of date, and the amount of data to replicate is small.
Transactional replication
In transactional replication, the Subscribers are first synchronized with the Publisher, typically using a snapshot, and then, as the publication data is modified, the transactions are captured and sent to the Subscribers. Transactional integrity is maintained across the Subscribers by having all modifications be made at the Publisher, and then replicated to the Subscribers. Transactional replication is used when data must be replicated as it is modified, you must preserve the transactions, and the Publishers and Subscribers are reliably and/or frequently connected through the network.
Merge replication
Merge replication lets multiple sites work autonomously with a set of Subscribers, and then later merge the combined work back to the Publisher. The Subscribers and Publisher are synchronized with a snapshot. Changes are tracked on both the Subscribers and Publishers. At some later point, the changes are merged to form a single version of the data. During the merge, some conflicts may be found where multiple Subscribers modified the same data. Merge replication supports the definition of conflict resolvers, which are sets of rules that define how to resolve such conflicts. Custom conflict resolver scripts can be written to handle any logic that may be needed to resolve complex conflict scenarios properly. Merge replication is used when it is important for the Subscriber computers to operate autonomously (such as a mobile disconnected user), or when multiple Subscribers must update the same data.
Configuring and Managing Replication
SQL Server 2000 provides several mechanisms for defining and administering replication: 
•	SQL Server Enterprise Manager supports configuring and monitoring replication.

•	SQL-DMO interfaces for programmatically configuring and monitoring replication.

•	Programmatic interfaces for replicating data from heterogeneous data sources.

•	Microsoft ActiveX® controls for embedding replication functionality in custom applications.

•	Scripting replication using Transact-SQL system stored procedures.

============================================================================
Log Shipping
In Microsoft® SQL Server™ 2000 Enterprise Edition, you can use log shipping to feed transaction logs from one database to another on a constant basis. Continually backing up the transaction logs from a source database and then copying and restoring the logs to a destination database keeps the destination database synchronized with the source database. This allows you to have a backup server and also provides a way to offload query processing from the main computer (the source server) to read-only destination servers.
Log Shipping Model
The illustration shows the log shipping model.

In this example, an enterprise has five servers: server A, server B, server C, server D, and server E. Server B is the source server, the server on which log backups and restores are performed and copied. Server C, server D, and server E contain the destination databases on which the log backups from server B are restored, keeping these servers in synchronization with server B. Server A is the monitor server on which the enterprise-level monitoring of log shipping occurs. Each destination or source server is maintained by only one monitor server. The Database Maintenance Plan Wizard is used to define an appropriate delay between the time server B backs up the log backup and the time server C, server D, and server E must restore the log backup. If more time elapses than defined, then server A generates an alert using SQL Server Agent. This alert can aid in troubleshooting the reason the destination server has failed to restore the backups.
Do not use the monitor server as the source server, because the monitor server maintains critical information regarding the log shipping system. The monitor server should be regularly backed up. Keeping the monitor server independent is also better for performance, because monitoring adds unnecessary overhead. Also, as a source server supporting a production workload, it is most likely to fail, which would disrupt the monitoring. The source and destination servers can be on the same computer. However, in this case, SQL Server 2000 failover clustering may provide better results. For more information, see Failover Clustering.
Configuring Log Shipping with the Database Maintenance Plan Wizard
To easily configure log shipping, use the Database Maintenance Plan Wizard. With this wizard, you can: 
•	Define how often the logs are generated, the time between a backup and a restore operation, and when a destination server is out of synchronization with a source server. 

•	Register any new servers.

•	Create the source databases on all destination servers. When adding a destination database through the Database Maintenance Plan Wizard, you have the option of creating the databases on the destination server or using existing databases. Any existing databases must be in standby mode before you can configure them for log shipping.

•	Specify which destination servers might assume the role of the source server.

•	Set a restore delay. This delay defines how old a transaction log must be before it is restored. If something goes wrong on the source server, this delay provides an extra time before the corrupted log is restored onto the destination server.

•	Create a schedule that sets the backup schedule. 
Before using the Database Maintenance Plan Wizard, consider the following: 
•	The user configuring log shipping must be a member of the sysadmin server role in order to have permission to modify the database to log ship. 

•	You can configure log shipping only on one database at a time. If you select more than one database, the log shipping option on the wizard is disabled. 

•	The login used to start the MSSQLServer and SQLServerAgent services must have access to the log shipping plan jobs, the source server, and the destination server.

•	When you use the Database Maintenance Plan Wizard to configure log shipping, you can log ship only to disks. The backup-to-tape option is not available. 
Configuring Log Shipping Manually
SQL Server 2000 supports manual log shipping from a SQL Server version 7.0 Service Pack 2 (SP2) transaction log if the pending upgrade option is enabled on the computer running SP2. 
To enable this option, execute the following code:
EXEC sp_dboption 'database name', 'pending upgrade', 'true'
However, when you are restoring the database after log shipping, you can recover only with the NORECOVERY option.

Note When you manually configure log shipping between a computer running SP2 and a computer running an instance of SQL Server 2000, you cannot use SQL Server replication.
For more information, see the SP2 documentation.
To configure log shipping
Enterprise Manager
How to configure log shipping (Enterprise Manager)
To configure log shipping with the Database Maintenance Plan Wizard

Note Before you configure log shipping, you must create a share on the primary database to make the transaction logs available. This is a share off of the directory that the transaction logs are dumped to. For example, if you dump the logs to the directory e:\data\tlogs\, you could create the \\logshipping\tlogs share off the directory.
In the Select Databases screen, select the These databases check box, and then select the database to log ship. 
If you select more than one database, log shipping will not work, and the log shipping option will not be available. You are not allowed to select a database that is already configured for log shipping.
Select the Ship the transaction logs to other SQL Servers (Log Shipping) check box.

Continue through the wizard, specifying the rest of the database maintenance options, until you get to the Specify the Log Shipping Destinations screen.

Click Add to add a destination database. 
For this option to be available, you must have selected to use log shipping earlier in the wizard.
In the Add Destination Database screen, select a server name. 
The server must be registered and running Microsoft® SQL Server™ 2000 Enterprise Edition to appear in the drop-down list. If you want this destination to become an available source destination, you must select the Allow database to assume primary role check box. If this box is not selected, this destination database will not be able to assume the source destination role in the future. If you have selected the Allow database to assume primary role check box, you must also specify the Transaction Log Backup Directory on the destination database to which the logs will be backed up.
To change the transaction log destination database from the default location, enter a location in the Directory box.

If the source database does not exist on the destination database, select the Create New Database check box. 
The Database Name box will default to the source database name. If you want a different database name on the destination server, specify a new name. If you have chosen to allow this destination database to assume the source role, you cannot change the database name from the default.
If you have selected the Create New Database check box, you must specify the file directories for the data and log on the destination database in the For Data and For Log boxes.

If the source database already exists on the destination database, select the Use Existing Database check box. If the database name on the destination server is different, enter it in the Database Name box. This database must have been restored using the WITH STANDBY option to properly accept logs.

In the Initialize the Destination Databases screen, either: 
•	Click Take full database backup now. 
–or-
•	Click Use most recent backup file to initialize the destination database. 
In the Log Shipping Schedules screen, view the default log shipping schedule. If you would like to alter the schedule, click Change.

In the Copy/Load Frequency box, set the frequency, in minutes, with which you want the destination servers to backup and restore the transaction logs from the source server.

In the Load Delay box, set the delay, in minutes, you want the destination database to wait before it restores the transaction log from the source server. 
The default for this box is 0 minutes, which indicates that the destination database should immediately restore any transaction log backups.
In the File Retention Period box, specify the length of time that must elapse before a transaction log can be deleted.

In the Log Shipping Thresholds screen, set the Backup Alert Threshold. 
This is the maximum elapsed time since the last transaction log backup was made on the source server. After the time exceeds this specified threshold, an alert will be generated by the monitor server.
In the Out of Sync Alert box, specify how long a time has passed between the last transaction log backup on the source server and the last transaction log restore on the destination server. 
After the time exceeds this specified threshold, an alert will be generated by the monitor server.
In the Specify the Log Shipping Monitor Information screen, type the name of the server that will monitor log shipping.

Click either Use Windows Authentication or Use SQL Server Authentication to connect to the monitor server. The log_shipping_monitor_probe login name is fixed and must be used to connect to the monitor server. If this is a new account, choose a new password. If the account already exists on the monitor server, you must specify the existing password. 

Note Using the Database Maintenance Wizard to set up log shipping, you can log ship only to disks; the backup to tape option is not available.
To remove log shipping
Enterprise Manager
How to remove log shipping (Enterprise Manager)
To remove log shipping 
Expand a server group, and then expand the primary server.

Expand Management, and then click Database Maintenance Plans.

In the details pane, right-click the database maintenance plan to delete, and then click Properties.

Click the Log Shipping tab, and then click Remove Log Shipping. 
This stops log shipping on the primary server, removes all secondary servers, and removes the monitor server. You must delete the database maintenance plan to remove additional jobs.
Changing Log Shipping Roles
SQL Server 2000 allows the changing of log shipping roles through system stored procedures. Before roles can be changed, several preliminary tasks must be performed. For more information, see How to set up and perform a log shipping role change (Transact-SQL).
See Also
Failover Clustering
Database Maintenance Plan Wizard

============================================================================
Checkpoints and the Active Portion of the Log
Checkpoints minimize the portion of the log that must be processed during a full recovery of a database. During a full recovery, two types of actions must be performed: 
•	The log may contain records of modifications not flushed to disk before the system stopped. These modifications must be rolled forward.

•	All the modifications associated with incomplete transactions (transactions for which there is no COMMIT or ROLLBACK log record) must be rolled back. 
Checkpoints flush dirty data and log pages from the buffer cache of the current database, minimizing the number of modifications that have to be rolled forward during a recovery.
A SQL Server 2000 checkpoint performs these processes in the current database: 
•	Writes to the log file a record marking the start of the checkpoint.

•	Stores information recorded for the checkpoint in a chain of checkpoint log records. The LSN of the start of this chain is written to the database boot page.

•	One piece of information recorded in the checkpoint records is the LSN of the first log image that must be present for a successful database-wide rollback. This LSN is called the Minimum Recovery LSN (MinLSN) and is the minimum of: 
•	The LSN of the start of the checkpoint.

•	The LSN of the start of the oldest active transaction.

•	The LSN of the start of the oldest replication transaction that has not yet replicated to all subscribers. 
•	Another piece of information recorded in the checkpoint records is a list of all outstanding, active transactions.

•	Deletes all log records before the new MinLSN, if the database is using the simple recovery model.

•	Writes to disk all dirty log and data pages.

•	Writes to the log file a record marking the end of the checkpoint. 
The portion of the log file from the MinLSN to the last-written log record is called the active portion of the log. This is the portion of the log required to do a full recovery of the database. No part of the active log can ever be truncated. All log truncation must be done from the parts of the log before the MinLSN.
This is a simplified version of the end of a transaction log with two active transactions. Checkpoint records have been compacted to a single record.

LSN 148 is the last record in the transaction log. At the time the checkpoint recorded at LSN 147 was processed, Tran 1 had been committed and Tran 2 was the only active transaction. That makes the first log record for Tran 2 the oldest log record for a transaction active at the time of the last checkpoint. This makes LSN 142, the begin transaction record for Tran 2, the MinLSN.
Checkpoints occur: 
•	When a CHECKPOINT statement is executed. The current database for the connection is checkpointed.

•	When ALTER DATABASE is used to change a database option. ALTER DATABASE checkpoints the database when database options are changed.

•	When an instance of SQL Server is stopped by: 
•	Executing a SHUTDOWN statement.

•	Using the SQL Server Service Control Manager to stop the service running an instance of the database engine. 
Either of these methods checkpoints each database in the instance of SQL Server.
•	When an instance SQL Server periodically generates automatic checkpoints in each database to reduce the amount of time the instance would take to recover the database. 
Automatic Checkpoints
SQL Server 2000 always generates automatic checkpoints. The interval between automatic checkpoints is based on the number of records in the log, not time. The time interval between automatic checkpoints can be highly variable. The time interval between automatic checkpoints is long if few modifications are made in the database. Automatic checkpoints occur frequently if a lot of data is modified.
The interval between automatic checkpoints is calculated from the recovery interval server configuration option. This option specifies the maximum time SQL Server should use to recover a database during a system restart. SQL Server estimates how many log records it can process in the recovery interval during a recovery operation. The interval between automatic checkpoints also depends on whether or not the database is using the simple recovery model. 
•	If the database is using either the full or bulk-logged recovery model, an automatic checkpoint is generated whenever the number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.

•	If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values: 
•	The log becomes 70 percent full.

•	The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option. 
Automatic checkpoints truncate the unused portion of the transaction log if the database is using the simple recovery model. The log is not truncated by automatic checkpoints if the database is using the full or bulk-logged recovery models. For more information, see Truncating the Transaction Log.
Long-Running Transactions
The active portion of the log must include every part of all uncommitted transactions. An application that starts a transaction and does not commit it or roll it back prevents SQL Server from advancing the MinLSN. This can cause two types of problems: 
•	If the system is shut down after the transaction has performed many uncommitted modifications, the recovery phase of the subsequent restart can take considerably longer than the amount of time specified in the recovery interval option.

•	The log may grow very large because the log cannot be truncated past the MinLSN. This happens even if the database is using the simple recovery model, in which the transaction log is normally truncated on each automatic checkpoint. 
Replication Transactions
The active portion of the log must also contain all transactions marked for replication, but that have not yet been replicated to a subscriber. If these transactions are not replicated in a timely manner, they can also prevent truncation of the log.
See Also
Backup/Restore Architecture
CHECKPOINT
Freeing and Writing Buffer Pages
Transaction Recovery