InTrust 11.6 - Replication of the InTrust Configuration Database

Benefits of Configuration Database Replication

Replication of the InTrust configuration database involves keeping a regularly synchronized replica of the database on additional SQL servers.

This gives you the following benefits:

• InTrust configuration consistency across the enterprise
  In enterprises that operate globally, InTrust deployments in all the locations cannot use the same configuration database. A partial solution is to have multiple configurations, but it is better to keep a replica of the master configuration database in each location.
• Increased fault tolerance
  In the event that the SQL server with your configuration database fails, your configuration is not lost. Furthermore, if InTrust is configured to automatically switch databases in such circumstances, then the impact of the failure is minimized.

This document outlines two possible scenarios, which illustrate these points:

• WAN Link Scenario
• SQL Server Failover Scenario

The configuration described is supported for SQL Server 2005 or later.

Before You Set Up Replication

Prepare InTrust for Replication

Make sure you have an up-to-date backup of your InTrust configuration database before you start configuring its replication.

Prepare SQL Servers for Replication

1. Verify that the name of each SQL Server participating in the replication matches the network name of its local machine.

2. Make sure that the Microsoft SQL Server version on the Subscriber side is less than or equal to that of the Publisher. To get SQL Server version information, use the following query:
   SELECT SERVERPROPERTY('productversion')
3. Create a new empty InTrust configuration database on the Subscriber SQL server using the following query:
   CREATE DATABASE <new database name>

4. Set up rights and permissions for accounts to be used by the Snapshot Agent and Merge Agent as described in the Permissions Required by Agents topic.

Setting Up Replication

1. Configure a distribution for the SQL Server that will be publishing your InTrust configuration database (Publisher). For more information, see the To configure a distribution procedure in the Technical Details and Procedures topic.
2. Create a publication for the InTrust configuration database on the Publisher SQL Server. For more information, see the To create a publication procedure in the Technical Details and Procedures topic.
3. On the SQL Server that hosts the new InTrust configuration database, create a subscription for this publication, as described in the To create a subscription procedure in the Technical Details and Procedures topic.
4. Wait for initial synchronization to complete successfully. To monitor the synchronization status, right-click the name of the subscription you have created and select View Synchronization Status from the shortcut menu to see a message in the Status pane reading:
   Waiting 60 second(s) before polling for further changes.
5. Connect to the Subscriber SQL server (the one where you created the subscription on the previous step) using credentials with the db_owner role for the new InTrust configuration database. Run the configdb.sql script (find it in the Scripts\Database Scripts folder in your InTrust distribution) on that database. You may receive some errors while the script is running, such as:
   • Updating columns with the rowguidcol property is not allowed.
   • GETMAXVERSION: The parameter 'lineage' is not valid.
   • The statement has been terminated.

These errors can be safely ignored.

Using the Configuration Database Replica

Before you can use the database replica as a full-featured InTrust configuration database, you need to perform an additional initialization step. You can do it in one of two ways:

• Install a new instance of InTrust Server that uses the replica as the configuration database.
  If you were planning to deploy new InTrust servers for this configuration replica anyway, this is a good time to do so. During InTrust Server installation, the database will be initialized automatically.
• Run the special-purpose configdb.sql script on the database to perform the initialization.
  This script is located in the Scripts\Database Scripts folder in your InTrust distribution. Use the script if you have no plans to deploy new InTrust servers that use the configuration replica.

Technical Details and Procedures

To configure a distribution

1. In Microsoft SQL Server Management Studio, connect to the SQL server that you want to configure as the Distributor (if unsure, use the Publisher server that publishes your InTrust configuration database), and expand the node of that server.
2. Right-click the Replication folder and then click Configure Distribution.
3. Follow the Configuration Wizard steps.

When you configure the Distributor, you specify the following:

• A snapshot folder, which is used, by default, for all Publishers that use this Distributor. Ensure that this folder is already shared and has the appropriate permissions set.
• A name and file location for the distribution database. The distribution database cannot be renamed after it is created. To use a different name for the database, you must disable distribution and reconfigure it.
• Any Publishers authorized to use the Distributor. If you specify Publishers other than the instance on which the Distributor runs, you must also specify a password for the connections that the Publishers make to the remote Distributor.

In addition, you need to make sure the "Agent XPs" stored procedure is enabled. Otherwise, you will get an error like the following:

SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server.

To enable this stored procedure, run the following SQL query:

sp_configure
sp_configure ‘show advanced options’,1
reconfigure
sp_configure 'Agent XPs',1
reconfigure

To create a publication

1. Connect to the Publisher SQL server with Microsoft SQL Server Management Studio using credentials with the sysadmin role for that SQL server.
2. On the InTrust configuration database to be published, execute the AdcCfgPublication.sql script with no parameters.

3. Verify that a publication named AdcCfgPublication has been created on the Publisher SQL Server for that database.
4. On the Publication Access page of the publication Properties dialog, add the account you plan to use for the Merge Agent.
5. On the Agent Security page, create a Snapshot Agent (if not yet created). Verify that the account specified for this agent meets the requirements stated in the Permissions Required by Agents topic.
6. Verify that the snapshot of the published database is created. To do so, right-click AdcCfgPublication and select View Snapshot Agent Status in the shortcut menu. If the last message there is “The agent has never been run.”, start the agent by clicking the Start button and wait for a message such as “[100%] A snapshot of 146 article(s) was generated.”.

To create a subscription

1. Connect to the Subscriber SQL server with Microsoft SQL Server Management Studio using credentials with the sysadmin role for that SQL server.
2. Expand the Replication node under the node of Subscriber server, right-click Local Subscriptions and then select New Subscription.
3. Follow the steps of the Configuration Wizard:
   • Select the Publisher and the publication you have created (see the To create a publication procedure earlier in this document).
   • Select Push subscription.
   • Select the new InTrust configuration database you have created as a target database for the replication.
   • Specify an account that the Merge Agent will use to connect to the Publisher and Subscriber (see the Permissions Required by Agents topic).
   • Select Run continuously for the synchronization schedule.
   • Unless you have your reasons for not doing so, select the option to initialize the subscription immediately.
   • Select Client as the subscription type.

Important: After you have created a subscription for a new database, wait until the initial replication between the publisher and subscriber databases completes. After that, you can safely proceed with configuration.