For improved scalability Configuration Manager 2007 sites, it is supported to configure the management point or server locator point site systems to access a site database replica. Configuring site systems to use a site database replica allows read activity to be scaled across multiple SQL Server computers and decrease the processing load on the site database server.
Replication administration is typically handled by a Database Engine administrator, who plans and runs daily operations in such areas as system availability, performance monitoring and optimization, deployment, upgrades, troubleshooting, and configuration. Small and medium organizations may have only one administrator; larger organizations may subdivide the administrator role into multiple jobs.
 Note |
|---|
| Configuration Manager 2007 sites support transactional replication between the site database server and remote SQL Server site database replica computers. The procedures in this section can be used to configure transactional replication by configuring the site database server as a publisher using a local distributor. |
SQL Server Replication Components
Microsoft SQL Server replication uses a publishing industry metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions. It is helpful to think of Microsoft SQL Server replication in terms of a magazine:
- A magazine publisher produces one or more
publications
- A publication contains articles
- The publisher either distributes the magazine
directly or uses a distributor
- Subscribers receive publications to which
they have subscribed
- Publisher
- The Publisher is a database instance that makes data available to other locations through replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.
- Distributor
- The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.
- Subscribers
- A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.
- Article
- An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.
- Publication
- A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.
- Subscription
- A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when.
Transactional Replication Overview
Transactional replication typically starts with a snapshot of the publication database objects and data. As soon as the initial snapshot is taken, subsequent data changes and schema modifications made at the Publisher are usually delivered to the Subscriber as they occur (in near real time), but you can configure a schedule to control the network bandwidth used during site database replication. When configuring a replication schedule for site database replication, you should plan for the latency between the time changes are made at the Publisher and arrive at the Subscriber.
While transactional replication does offer options that allow updates at the Subscriber to replicated database information, by default, Subscribers to transactional publications should be treated as read-only, because changes are not propagated back to the Publisher.
Additional Disk Space Requirements for Transactional Replication
Transaction Log Disk Space
For each database that will be published using transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database might require more space than the log of an identical unpublished database, because the log records are not truncated until they have been moved to the distribution database.
If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been delivered to the distribution database. A SQL Server recommendation is to set the transaction log file to auto grow so that the log can accommodate these circumstances.
Another SQL Server recommendation is to set the sync with backup option on the distribution database to delay the truncation of the log on the publication database until the corresponding transactions in the distribution database have been backed up. This can result in a larger transaction log in the publication database.
Distribution Database Disk Space
Ensure that you have enough disk space to store replicated transactions in the distribution database. If you do not make a snapshot file available to Subscribers immediately (which is the default) transactions are stored until they have been replicated to all Subscribers or until the retention period has been reached, whichever is shorter.
If you create a transactional publication and make the snapshot files available to Subscribers immediately: transactions are stored until they have been replicated to all Subscribers or until the Snapshot Agent runs and creates a new snapshot, whichever is longer. If the elapsed time between Snapshot Agent runs is greater than the maximum distribution retention period for the publication, which has a default of 72 hours, transactions older than the retention period are removed from the distribution database.
Although making the snapshot available to Subscribers immediately improves the speed with which new Subscribers have access to the publication, the option can result in increased disk storage for the distribution database. It also means that a new snapshot is generated each time the Snapshot Agent runs. If the option is not used, a new snapshot is generated only if there is a new subscription.