If the Administration Service uses SQL Server authentication, the Replication Agents connect toSQL Server in the security context of the SQL Server login that is specified for the Administration Service connection to SQL Server.
If the login does not have sufficient rights, it has been deleted, or had the password changed, the Active Roles replication fails. To resolve this problem, do the following:
To avoid replication fail
-
Choose an SQL Server login with sufficient rights. For more information, see the “SQL Server permissions” section in the Active Roles Quick Start Guide.
-
Configure the Administration Service to use that login. For more information, see Viewing database connection settings.
-
Configure the Replication Agents to use that login.
The following sections elaborate on how to configure the Replication Agents to use a given SQL Server login. The instructions vary depending on whether SQL Server in question is the Publisher or a Subscriber.
Replication Agent connection to Publisher
If you have changed the SQL Server login for the Administration Service connection to the Publisher, use the following steps to configure the Replication Agents with that login:
To configure the Replication Agent to connect to the Publisher
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
-
In the Object Browser, under the Publisher SQL Server, right-click the Replication folder, and then click Distributor Properties.
-
In the left pane of the Distributor Properties window, click Publishers.
-
In the Publishers list, select the entry representing the Publisher SQL Server, and click
in that entry to display the Publisher Properties dialog.
-
In the Agent Connection to the Publisher area, click Login, and type the login name.
-
Click Password, and then click
in the Password entry.
-
In the Enter Password dialog, type and confirm by retyping the password of that login.
-
To close the Enter Password dialog, click OK.
-
To close the Publisher Properties dialog, click OK.
Replication Agent connection to Subscriber
If you have changed the SQL Server login for the Administration Service connection to a Subscriber, use the following steps to configure the Replication Agents with that login:
To configure the Replication Agent to connect to the Subscriber
-
With SQL Server Management Studio, connect to the Publisher SQL Server.
NOTE: You must have Management Studio connected to the Publisher SQL Server, regardless of whether you are managing Replication Agents for the Publisher or for a Subscriber.
-
In the Object Browser, under the Publisher SQL Server, expand Replication > Local Publications > AelitaReplica.
-
In the list under AelitaReplica, right-click the entry corresponding to the Subscriber SQL Server and click Properties.
-
In the Subscription Properties window, in the Security section, expand the Subscriber connection entry.
-
Click
in the Subscriber Connection entry.
This displays the Enter Connection Information dialog.
-
In the Login box, type the login name.
-
In the Password and Confirm password boxes, type and confirm by retyping the password of that login.
-
To close the Enter Connection Information dialog, click OK.
-
To close the Subscription Properties dialog, click OK.
In the Active Roles replication model, a replication group includes the Publisher and may include several Subscribers. The Publisher plays a special role in the replication group: it synchronizes data changes between Subscribers. In some scenarios, you might want to move the Publisher role to another SQL Server.
For example, you might need to move the Publisher role to a different SQL Server if the service level becomes insufficient. Because the Publisher receives and synchronizes data changes from all Subscribers, the volume of requests being serviced by the Publisher increases as the number of Subscribers grows. The increased volume of requests respectively increases the workload for SQL Server that holds the Publisher role so its performance can suffer. To resolve this problem, you can transfer the Publisher role to another, more powerful server.
This section provides instructions on how to reconfigure the existing replication group so that the Publisher role is assigned to SQL Server other than the current Publisher. You can perform this task using the Active Roles Console connected to the Administration Service which has a database server currently holding the Publisher role (Publisher Administration Service).
NOTE: The Publisher Administration Service must be up and running. If the Publisher is unavailable, you can assign the Publisher role to a different SQL Server. For more information, see Promoting an SQL Server to Publisher.
To connect to the Publisher Administration Service via the Active Roles Console
-
Look for the Active Roles Console application, and then click to start that application.
-
Right-click the Console tree root, click Connect, and then select the Administration Service whose database server currently holds the Publisher role.
Use the Active Roles Console to remove all Subscribers and to demote the Publisher as follows.
To remove all Subscribers and demote the Publisher
-
In the Console tree, expand Configuration > Server Configuration, and select Configuration Databases.
-
In the Details pane, right-click a Subscriber, and click Delete.
-
In the confirmation message box, click Yes.
-
Repeat the deletion steps for each Subscriber.
-
In the details pane, right-click the Publisher, and click Demote.
-
In the confirmation message box, click Yes.
-
Wait while Active Roles demotes the Publisher.
After demoting the previous Publisher, you can promote the appropriate SQL Server to Publisher and designate the other SQL Servers as Subscribers to the new Publisher, thus configuring the new replication group.
TIP: After you add a Subscriber, the configuration data stored on the Publisher is replicated to the Subscriber, overriding the data on that Subscriber. Therefore, in order to retain your existing Active Roles configuration, it is advisable to assign the Publisher role to SQL Server that belonged to the old replication group. This ensures that each Administration Service in the new replication group inherits the configuration that was in place when you removed the Subscribers and demoted the Publisher.
To configure the new replication group using the Active Roles Console
-
Right-click the Console tree root, click Connect, and then select the Administration Service the SQL Server of which you want to hold the Publisher role.
-
In the Console tree, expand Configuration > Server Configuration, and select Configuration Databases.
-
In the Details pane, right-click the database and click Promote.
-
In the confirmation message box, click Yes.
-
Wait while Active Roles performs the operation.
-
In the Details pane, right-click the Publisher, and click Add Replication Partner.
-
On the Welcome page in the New Replication Partner wizard, click Next.
-
On the Database Selection page, click Browse.
-
To configure the SQL Server of an Administration Service as a Subscriber to this Publisher, specify the corresponding Administration Service in the Connect to Administration Service dialog. Click OK.
-
In the New Replication Partner wizard, click Next, click Next, and then click Finish.
-
Repeat the steps for adding a replication partner for each SQL Server you want to make a Subscriber.