Data synchronization and conflict resolution
After applying the initial snapshot to Subscribers, SQL Server tracks changes to published data at the Publisher and at the Subscribers:
- When data is modified at a Subscriber, the data changes are sent to the Publisher. Then, the Publisher propagates the data changes to the other Subscribers.
- When data is modified at the Publisher, the data changes are propagated to the Subscribers.
These operations are performed by the Merge Agents running on the Publisher SQL Server.
The Merge Agents are configured so that once data changes are made at a given replication partner, it normally takes two minutes or less for SQL Server to start synchronizing the data changes with other replication partners. The time required for the synchronization process to be completed depends on SQL Server load and on the bandwidth of network connections. As there is normally a moderate volume of data changes, the replication traffic is manageable.
The synchronization process tracks data changes on both the Subscribers and the Publisher. At the Publisher, 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.
.Any conflict between the arrived values is automatically resolved based on the Microsoft SQL Server DATETIME (Later Wins) Conflict Resolver: The winner of the conflict is chosen according to a “later wins” solution, with the last to modify the data winning the conflict. For information about conflict resolvers, see Microsoft COM-Based Resolvers in SQL Server Books Online at http://msdn.microsoft.com/en-us/library/ms152573.aspx.
SQL Server-related permissions
The health of Active Roles replication heavily depends on the access permissions that the Administration Service and SQL Server Agent has on SQL Server. The required permissions are listed in the “SQL Server permissions” section in the Active Roles Quick Start Guide.
Configuring SQL Server
To ensure that SQL Server is properly configured for Administration Service replication, ensure that the SQL Server Agent service is started and configured properly.
The SQL Server Agent service must be up and running on SQL Server that holds the role of the Publisher database server (Publisher SQL Server). It is recommended that the startup type for this service be set to Automatic.
The SQL Server Agent service should be configured to log on with a domain user account. The service logon account must have sufficient rights to connect to the Publisher SQL Server and to the Subscriber SQL Server (see “Replication agent permissions” in the Active Roles Quick Start Guide).
Configuring replication
Active Roles uses the replication functionality of Microsoft SQL Server to copy and distribute configuration data from one Administration Service database to another, and to synchronize data among the databases for consistency.
Administration Service database servers synchronized by using the SQL Server replication function are referred to as replication partners. Each replication partner maintains a writable copy of the Service’s configuration and Management history data. Whenever changes are made to one replication partner, the changes are propagated to the other replication partners.