Active Roles 7.2.1 - Replication: Best Practices and Troubleshooting

Windows authentication

If the Administration Service uses Windows authentication, Replication Agents connect to SQL Server in the security context of the SQL Server Agent service. Therefore, the SQL Server Agent logon account must have sufficient permissions for replication to work properly (see the “SQL Server permissions” section in the Active Roles Quick Start Guide.

If the SQL Server Agent logon account does not have the appropriate permissions, is deleted, or has the password changed, Active Roles replication fails. To resolve this problem, give the required permissions to the logon account, or configure the SQL Server Agent service to log on with a different account that has the appropriate permissions. For instructions on how to configure the SQL Server Agent service to log on with a given account, see Changing the SQL Server Agent logon account earlier in this document.

You can use the following instructions to verify that the Replication Agents are configured properly. The instructions vary depending on whether the SQL Server holds the Publisher or Subscriber role. In both cases, you should connect to the Publisher SQL Server using SQL Server Management Studio.

Replication Agent connection to Publisher

If the Administration Service connects to the Publisher SQL Server using Windows authentication, follow these steps to verify that the Replication Agents are configured properly:

1. With SQL Server Management Studio, connect to the Publisher SQL Server.
2. In the Object Browser, under the Publisher SQL Server, right-click the Replication folder, and then click Distributor Properties.
3. In the left pane of the Distributor Properties window, click Publishers.
4. In the Publishers list, select the entry representing the Publisher SQL Server, and click the button in that entry to display the Publisher Properties dialog box.
5. In the Publisher Properties dialog box, under Agent Connection to the Publisher, verify that the Agent Connection Mode property is set to Impersonate the agent process account.

Replication Agent connection to Subscriber

If the Administration Service connects to the Subscriber SQL Server using Windows authentication, follow these steps to verify that the Replication Agents are configured properly:

1. 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.

2. In the Object Browser, under the Publisher SQL Server, expand Replication | Local Publications | AelitaReplica.
3. In the list under AelitaReplica, right-click the entry corresponding to the Subscriber SQL Server and click Properties.
4. In the Subscription Properties window, in the Security section, expand the Subscriber connection entry.
5. Verify that the Subscriber connection property is set to Impersonate agent process account (Windows Authentication).

SQL Server authentication

If the Administration Service uses SQL Server authentication, the Replication Agents connect to SQL Server in the security context of the SQL Server login specified for the Administration Server connection to SQL Server.

If the login does not have sufficient rights, is deleted, or has the password changed, Active Roles replication fails. To resolve this problem, do the following:

1. Choose a SQL Server login with sufficient rights (see the “SQL Server permissions” section in the Active Roles Quick Start Guide).
2. Configure the Administration Service to use that login (see Viewing database connection settings earlier in this document).
3. 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:

1. With SQL Server Management Studio, connect to the Publisher SQL Server.
2. In the Object Browser, under the Publisher SQL Server, right-click the Replication folder, and then click Distributor Properties.
3. In the left pane of the Distributor Properties window, click Publishers.
4. In the Publishers list, select the entry representing the Publisher SQL Server, and click the button in that entry to display the Publisher Properties dialog box.
5. In the Agent Connection to the Publisher area, click Login, and type the login name.
6. Click Password, and then click the button in the Password entry.
7. In the Enter Password dialog box, type and confirm by retyping the password of that login.
8. Click OK to close the Enter Password dialog box.
9. Click OK to close the Publisher Properties dialog box.

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:

1. 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.

2. In the Object Browser, under the Publisher SQL Server, expand Replication | Local Publications | AelitaReplica.
3. In the list under AelitaReplica, right-click the entry corresponding to the Subscriber SQL Server and click Properties.
4. In the Subscription Properties window, in the Security section, expand the Subscriber connection entry.
5. Click the button in the Subscriber Connection entry.

This displays the Enter Connection Information dialog box.

6. In the Login box, type the login name.
7. In the Password and Confirm password boxes, type and confirm by retyping the password of that login.
8. Click OK to close the Enter Connection Information dialog box.
9. Click OK to close the Subscription Properties dialog box.

Moving the Publisher role

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 may 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. Given that 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. 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 whose database server currently holds the Publisher role (Publisher Administration Service).

Open the Active Roles console and connect to the Publisher Administration Service:

1. Look for the Active Roles Console application, and then click to start that application.
2. 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:

1. In the console tree, expand Configuration | Server Configuration, and select Configuration Databases.
2. In the details pane, right-click a Subscriber, and click Delete.
3. In the confirmation message box, click Yes.
4. Repeat Steps 2–3 for each Subscriber.
5. In the details pane, right-click the Publisher, and click Demote.
6. In the confirmation message box, click Yes.
7. Wait while Active Roles demotes the Publisher.

After these steps, 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.

To configure the new replication group, perform the following steps using the Active Roles console:

1. Right-click the console tree root, click Connect, and then select the Administration Service whose SQL Server you want to hold the Publisher role.
2. In the console tree, expand Configuration | Server Configuration, and select the Configuration Databases container.
3. In the details pane, right-click the database and click Promote.
4. In the confirmation message box, click Yes.
5. Wait while Active Roles performs the operation.
6. In the details pane, right-click the Publisher, and click Add Replication Partner.
7. On the Welcome page in the New Replication Partner wizard, click Next.
8. On the Database Selection page, click Browse.
9. In the Connect to Administration Service dialog box, select the Administration Service whose SQL Server is to be configured as a Subscriber to this Publisher. Click OK.
10. In the New Replication Partner wizard, click Next, click Next, and then click Finish.
11. Repeat Steps 6–10 for each SQL Server you want to make a Subscriber.

Recovering replication if Publisher is not available

Once the Publisher becomes unavailable, Subscribers cannot synchronize configuration data. The only way that replication can be recovered is by restoring the current Publisher or making another SQL Server the Publisher.

If the current Publisher cannot be restored, you need to transfer the Publisher role to SQL Server that holds the Subscriber role, and reconfigure the other Subscribers to use the new Publisher. This requires that you first remove all Subscribers from the replication group.

Given that the Publisher is unavailable, you can remove a Subscriber from the replication group by using the Active Roles console as follows:

1. Right-click the console tree root, click Connect, and then select the Administration Service that uses the Subscriber SQL Server.
2. In the console tree, expand Configuration | Server Configuration, and select Configuration Databases.
3. In the details pane, right-click the Subscriber and select All Tasks | Advanced Properties.
4. In the Advanced Properties window, select both the Show all possible attributes and Include attributes with empty values check boxes.
5. In the list of attributes, double-click the attribute edsvaReplicationForceStandalone.
6. In the Edit Attribute window, type TRUE in the Value box. Click OK.
7. In the Advanced Properties window, click OK.

Once you have removed all Subscribers from the replication group, you can promote one of the former Subscribers to Publisher and add Subscribers to the new Publisher by using the Active Roles console as follows:

1. Right-click the console tree root, click Connect, and then select the Administration Service whose SQL Server you want to hold the Publisher role.

   TIP: After you add a Subscriber, the configuration data stored on the Publisher will be replicated to the Subscriber, overriding the data on that Subscriber. Therefore, in order to retain your existing Active Roles configuration, assign the Publisher role to one of the former Subscribers. This ensures that each Administration Service in the new replication group inherits the configuration that was in place when you removed the Subscribers from the replication group.

2. In the console tree, expand Configuration | Server Configuration, and select Configuration Databases.
3. In the details pane, right-click the database and click Promote.
4. In the confirmation message box, click Yes.
5. Wait while Active Roles performs the operation.
6. In the details pane, right-click the Publisher, and click Add Replication Partner.
7. On the Welcome page in the New Replication Partner wizard, click Next.
8. On the Database Selection page, click Browse.
9. Use the Connect to Administration Service dialog box to specify the Administration Service whose SQL Server is to be configured as a Subscriber to this Publisher. Click OK.
10. In the New Replication Partner wizard, click Next, click Next, and then click Finish.
11. Repeat Steps 6–10 for each SQL Server you want to make a Subscriber.