SQL Server Agent
SQL Server Agent hosts and schedules the agents used in replication, and provides a way to run replication agents. SQL Server Agent also controls and monitors several other operations outside of replication, including monitoring the SQL Server Agent service, maintaining error logs, running jobs, and starting other processes.
Replication Agents
Replication Agents used with Microsoft SQL Server replication carry out the tasks associated with copying and distributing data. The Active Roles replication model employs the Snapshot Agent and Merge Agents.
Snapshot Agent
The Snapshot Agent prepares schema and initial data files of published tables and stored procedures, stores the snapshot files, and records information about synchronization in the distribution database. In the Active Roles replication model, the Snapshot Agent runs at the Publisher.
Merge Agent
The Merge Agent applies the initial snapshot to the Subscriber, and moves and reconciles incremental data changes that occur. Each Subscriber has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
In the Active Roles replication model, the Merge Agents run continuously at the Publisher. Each Merge Agent uploads data changes from its Subscriber to the Publisher, and downloads data changes from the Publisher to the Subscriber.
Replication model overview
Understanding the Replication model
NOTE: Operations related to replication are not supported by the Azure SQL databases.
Active Roles replication propagates the changes to configuration data to all replication partners whenever the data is modified on any one of replication partners. To achieve this goal, Active Roles relies on the merge replication provided by Microsoft SQL Server. For details on merge replication, refer to the content indexed under the Merge Replication topic in SQL Server Books Online.
In the Active Roles environment, the SQL Server replication function is used to propagate changes to configuration data to all the replication partners, as soon as data is modified on one of the replication partners. The replication process is initiated immediately after changes are committed to a replication partner. Active Roles does not offer the facility to change this behavior.
As there is usually a moderate volume of changes, and since replication only propagates modified data (merge replication model), the amount of replication traffic is manageable. Therefore, you do not need to schedule or manually force replication in Active Roles.
A merge replication model normally requires a means of resolving conflicts that could result from changing the same data on different replication partners. In the Active Roles replication model, the outcome of the conflict is decided on a “later wins” basis, that is, the last to modify the data wins the conflict.
In the Active Roles replication model, each Administration Service database server can have one of the following roles:
This section briefly discusses the following elements of the Active Roles replication model:
- Replication group management
- Data synchronization and conflict resolution
Replication group management
The tasks performed when managing a replication group include the Publisher-related tasks, such as Promote or Demote, and the Subscriber-related tasks, such as Add or Delete.
Promote
This task assigns the Publisher role to the Administration Service database server, thereby creating a replication group. When performing the Promote task, SQL Server creates the AelitaReplica publication, and starts the Snapshot Agent. The Agent creates an initial snapshot of schema and data, and saves it to the snapshot folder.
Active Roles automatically specifies and passes to SQL Server all replication settings, such as filters, type of replication, and retention period for subscriptions. For details, see Viewing replication settings later in this document.
Add
This task adds the Administration Service database server to the replication group, thus assigning the Subscriber role to the database server. When performing the Add task, SQL Server starts the Merge Agent. The Agent copies data from the Publisher’s snapshot folder to the Subscriber SQL Server. This process is referred to as applying the initial snapshot (see "Create and Apply the Snapshot" in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms151785.aspx).
Delete
This task removes the Subscriber from the replication group, causing the database server to revert to the standalone state. When performing the Delete task, SQL Server deletes the subscription at the Publisher. The database of the former Subscriber retains the replicated data.
Demote
This task removes the Publisher from the replication group, causing the database server to revert to the standalone state. The Publisher can only be demoted after all of its Subscribers are deleted. When performing the Demote task, SQL Server deletes the AelitaReplica publication, and erases data in the snapshot folder.