Sync users and groups between QB servers #2297

JustPassingThrough · 1 decade ago

We currently have two build servers - one live and one for DR - and we need all of the users and group membership to stay in sync between the two of them. Both servers are only using local groups and local users, so simply pointing both of them to shared / synced LDAP groups would not be an option.

What would be the best way to sync the QB users and groups between these two environments? Is there a built-in function, or can this be scripted via the API?

replies 7
views 2665
stars 0

robinshen ADMIN · 1 decade ago

This can be scripted by pulling user XML from one server and push it into another. Refer to below links on how to retrieve/save users/groups/memberships:
http://wiki.pmease.com/display/QB50/Interact+with+Users
http://wiki.pmease.com/display/QB50/Int ... ith+Groups
http://wiki.pmease.com/display/QB50/Int ... emberships

JustPassingThrough · 1 decade ago

While that is helpful information, the major complication with that method is that users, groups, memberships and authorizations are all keyed to the Group IDs, and it does not appear that those IDs can be modified or specifically chosen when modifying or creating new groups on the destination server.

Here's an example of how this creates a problem:
- Assume we want to completely sync all security from server A to server B
- Assume server A initially has 5 groups, with IDs 1, 2, 3, 4, and 5
- Assume server B initially has 3 groups with IDs 1, 2, and 4. There is no group 3 because it was previously deleted
- There are Memberships, Authorizations, and a Group defined on server A with Group ID=3

As Group ID=3 previously existed on server B, when trying to create a new group it will actually receive ID=5 (since that is the next sequential ID that has not yet been used). Since you can't specify ID # when creating a group (by any method that I have found), you're now stuck with a set of data from Server A pointing to an ID that will never again exist on Server B. I suppose one could script a hash table to line up all of the IDs from one server to the next for the sync, but that seems like a lot of unnecessary work, and it still wouldn't result in having truly matching / synchronized security data on both of the servers (since the membership and authorization maps will still be completely different). I have a feeling I'm going to encounter exactly the same issue with the User IDs when trying to recreate users on Server B (though I haven't tested that yet)...

Is there any way to directly migrate the users, groups, memberships and authorizations across servers without having to fight with this issue?

robinshen ADMIN · 1 decade ago

Since QB servers are separate systems, there is no easy way to do the sync. At sync time, one should always check group name instead of group id. For instance, if a group with certain name exists in A but not in B, you will need to remove ID element from group definition XML of A and get it posted to B to create a new group with that name; on the other hand, if A and B contains group of the same name, you will then need to change ID element from group definitin XML of A to use ID of B and post it to B.
In this way groups, users can be synced, then you may retrieve memberships from A to see how they set up user/group relations based on names, and again to create the same relationship based on name in B.

JustPassingThrough · 1 decade ago

Is there a specific table (or number of tables) that could be copied across from one DB to the other to achieve this? We don't want to do a full backup and restore, just the security data (users, groups, authorizations and memberships).

robinshen ADMIN · 1 decade ago

QB_USER, QB_MEMBERSHIP, QB_GROUP, QB_AUTHORIZATION are relevant tables here, but copying table has two disadvantages:
1. The destination QB instance has to be restarted after copying as it won't realize underline data change.
2. You might encounter strange issues if ID of rows in these table conflicts with destination database.

JustPassingThrough · 1 decade ago

Needing to restart doesn't bother me because the destination server is for disaster recovery only.

Regarding the row IDs conflicting, as long as we're completely dropping the destination tables before restoring from the other server how would / could that happen?

robinshen ADMIN · 1 decade ago

Database might prevent you from dropping the table if some of its keys are referenced from other tables (such as build, configuration, etc.). I still recommend to go with the hard approach of updating based on names.