Satellite Database as a GeoSync Subscriber

This procedure addresses a situation where a personalization server database has been created by exporting some or all of the configuration from a larger, primary database (using import/export functionality) and is used remotely for a smaller number of users. The customer wants to use GeoSync to automatically synchronize a subset of users between the primary and satellite by making them GeoSync publisher and subscriber respectively. This wasn't possible using the existing merge replication scripts as they sync all of the database.

The import/export of a configuration alters the database IDs (GUIDs) that the GeoSync feature relies on to match the publisher and subscriber. This problem is solved by modifying all the GUIDs in the subscriber to match the publisher. To do this, downtime is needed at the subscriber - no affected user can be logged on, and any offline caches on user machines will be invalidated as they cannot be saved back to the modified database on the next connection. Also, if any Windows Settings Groups 'OriginalName' columns don't match on the publisher and subscriber, a sync cannot be performed.

The GUID mapping is performed by a PowerShell script (PrepareGeoSubscriber.ps1), which uses the sqlcmd utility and two SQL scripts (CheckMapping.sql and MapUgs.sql) which are required to be in the same folder.

The scripts are located here: :%systemdrive%\Program Files\AppSense\Environment Manager\Personalization Server\Support

Limitations

If the publisher and subscriber databases were not created using the same software version, the following issues may arise:

  • Due to the introduction of Windows 10 and Server 2016, some Windows Settings Group (WSG) names changed between database versions. If groups are exported and imported to the subscriber, and the subscriber has both old and new names, synchronization of WSGs is not possible as the OriginalName columns in the DesktopSettings.[Group] table will differ. Original names cannot be easily changed as it would require a scan of both the ApplicationData and ApplicationDataArchives tables. The mapping process described in this topic does not support this.
  • If the same users to be synchronized exist in both the publisher and subscriber databases and the users were initially created with versions of Personalization Server prior to 8.4, they cannot be synchronized. This is because, prior to 8.4, user identities (UserPK column) were randomly allocated according to the usual GUID algorithm. From 8.4, the GUIDs are derived from the users' SIDs and will be consistent across databases. The solution to this problem is to physically delete the affected users from one of the databases using SQL DELETE on the dbo.[User].table.

Prerequisites

  • The user running the scripts has sysadmin access to both databases via Windows Integrated Authentication.
  • The scripts are run on a machine which has the sqlcmd.exe utility in the current path. One of the database servers involved might be the most convenient but sqlcmd.exe can be installed on a different machine by downloading the appropriate MSI from Microsoft.
  • The publisher and subscriber databases must be accessible to each other and the script over the network. Normal SQL server connections (usually on port 1433) are used by both sqlcmd and the GeoSync software itself.

Initial Setup

The script is applied to a single personalization group at a time. The personalization group (identified by name) must appear on both publisher and subscriber before starting. It is assumed that GeoSync is not yet set up, and the terms 'publisher' and 'subscriber' refer to their intended use.

Normally it is assumed that a personalization group is exported from the publisher and imported into the subscriber using the import/export functionality. But if a new personalization group is created on the subscriber using application groups and windows settings groups imported from the publisher, it is possible to sync it by exporting the group from the subscriber and importing it back into the publisher.

In either case the script assumes that the personalization group to be synchronized initially exists on both databases.

Running the Script

The PowerShell script and the two SQL scripts must be in the same directory. Run the PowerShell script from a PowerShell command prompt - elevation is not required. It prompts for the details of both databases and the name of the personalization group to be synchronized. Checks are made that the specified databases exist, then the two SQL scripts are run. These scripts run on the subscriber but contact the publisher by creating a linked server entry, which is deleted afterwards.

The two scripts do the following:

  • CheckMapping.sql - Compares all the details of the group to identify possible problems. These problems come in three categories:
    • Warnings - This is where the personalization group on the publisher contains extra entities, such as application groups, that are not present on the subscriber. After syncing the subscriber may therefore receive additional entities.
    • Errors - The personalization group on the subscriber has extra entities not present on the publisher. These may result in data disappearing from the subscriber and should be investigated.
    • Fatals - Windows settings groups 'originalname' fields don't match on the publisher and subscriber, usually due to similar data already present on the subscriber. Mapping cannot be performed.
  • MapUGs.sql - Performs the mapping. This cannot be run if 'Fatals' have been discovered. The user is prompted first before this is run so he can make the required changes and restart.

Setting up GeoSync

After syncing the personalization group, GeoSync can be enabled between the two databases as described here. After setup, the synchronized personalization group must be set as synchronized on its GeoSync tab in the Environment Manager console. If this group has existing data on the publisher, setup of the GeoSync conditions might be required to ensure that not all of the publisher's data is transmitted to the subscriber.

CheckMapping Actions

CheckMapping.sql checks the following on the publisher and subscriber databases:

Checks

Problem category

The personalization group does not exist in both databases.

Fatal

Checkbox items on the Settings tab are not the same between the personalization groups.

Warning

Affected users exist on both databases, with the same SID but different GUIDs.

Fatal

The setting of the Advanced Property UpgradeFbrToHive differs between databases.

Fatal

Applications group assignments differ.

Publisher has more Application Groups: Warning

Subscriber has more Application Groups: Error

Applications assigned to Application Groups differ.

Publisher has more: Warning

Subscriber has more: Error

Application definitions differ - EXE, OS version, file version.

Publisher has more: Warning

Subscriber has more: Error

Registry, file, and folder paths assigned to application groups differ.

Publisher has more: Warning

Subscriber has more: Error

Application group managed folders differ.

Publisher has more: Warning

Subscriber has more: Error

Windows Settings Groups assignments differ between databases.

Publisher has more: Warning

Subscriber has more: Error

Windows Settings Groups components, settings, custom settings, conditions, differ.

Publisher has more: Warning

Subscriber has more: Error

OriginalName columns of WSGs to be synced differ.

This can occur during import if existing WSGs on the subscriber had matching original names. It cannot be easily resolved as the 'original name' is used in the data and the archives and is not fixed by the MapUGs.sql script.

Fatal