Snow System User Migrator

Development • 14 April 2021

If you’ve ever had to migrate a customer from one platform to another, or from on-premise to hosted, you’ll know from a technical side the worst two things to migrate are Organisation Structure with Autoconnect Rules and large amounts of System Users, more so if you’re moving more than one customer at once. So… 😊

# The Problem

Moving large amounts of System Users is a pain, it’s even more of a pain when you then have to tell the users their new password, potentially have to change the Configuration for SIM Connectors because of API passwords. An Official Snow Solution doesn’t exist here unfortunately, you need to manually re-create each user as there is no way of importing a spreadsheet of users and permissions, nor is there a way of migrating the data from one database to another due to there being more than one table that stores System User data, for example although the User itself is stored in dbo.tblSystemUser, there are other tables that they’re added to also.

# The Solution

I’m lazy, I’d rather spend 2 days trying to make a tool that does something for me than spend 2 days doing what this tool would do for me, so that’s what I did, I spent 2 days making a tool that migrates system users from one platform to another, the following methods are supported:

• Platform to Platform (SPE > SPE for example, db access required)
• Spreadsheet Migration (Export old data via SQL, import via tool)

It works, surprisingly well for something that was made in 2 days, and you can migrate around 60 Users in about 10 seconds which I’d say is a lot better than manually re-creating each user.

When creating the user, it checks for these things and has a few defaults if this data doesn’t exist in the platform already.

• Does the user have any organisation restrictions?
• Does the user have a non-standard role that doesn’t exist in the new platform?
• Does the user have an expiry date?
• Does the user have more than one role?
• Does the user exist in the new platform already?

There’s a few things I do here, but it’ll be easier to outline each one and explain how it works…

Does the user have any organisation restrictions? As a dependency before doing the import, one of the checklist items is to ensure that the existing organisation structure exists in the new platform. If it doesn’t, the user will be assigned to the entry in the database against their CID that is set to 1 for IsRoot.

Does the user have a non-standard role that doesn’t exist in the new platform? I’ll be honest when I say, I completely forgot about custom roles! So instead of making a migrator for it (sorry!), if the user is in a role in the old platform that doesn’t exist in the new platform, they’re assigned to the Viewers role to ensure they still have access, but not too much access.

Does the user have an expiry date? This is pulled from the old platform and added when the user is imported into the new one.

Does the user have more than one role? Similar to non-standard roles, I forgot about this, but I forgot about it before I was too far doing what I was doing so I looped back through my code and accounted for this. It will pull a list of roles the user is assigned to in the old platfor, and if that Role Name exists in the new platform, they’ll be assigned to each of them, if one of the roles exist but not all do, they’ll be assigned to the one that exists but nothing else.

Does the user exist in the new platform already? To prevent weird duplicate accounts this is done based on UserName only, and not based on email address as you can have accounts with the same email address in Snow. If the username already exists in the platform, the user will not be added and they will be skipped over entirely, however this is tracked in the relevant log file for the tool itself (thanks log4net)

I’ve used this already and not seen any problems, but I’l likely be doing it in a TEST environment before using it in a production environment each time to ensure that Snow haven’t updated or removed any of the procedures it uses.

The big benefit of this is now Softcat, my employer, has the ability to migrate system users across platforms without needing to manually re-create. This is the first in a migration toolkit that I’m creating, so keep your eyes out for future posts regarding these types of things.

P.S. I’ve started adding random songs I’m addicted to at the moment to the bottom of my posts. I’m currently addicted to this: drivers license - Our Last Night.

This tool is not/will not be released to the public.