Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
SteveA
Alteryx
Alteryx

Introduction and Motivation

The Alteryx Server relies on MongoDB as its back-end data persistence layer, storing Server-related data such as Gallery users and sessions, Schedules, Modules and execution results.  By default, the Alteryx Server ships with its own "Embedded" MongoDB, a standalone MongoDB instance bootstrapped silently on first usage.  The self-contained instance facilitates near-zero configuration of the Alteryx Server, enabling users to stand up a Server quickly and efficiently without concern about the back-end storage details.  Many clients continue to use the single-node setup beyond the initial Server deployment, adding a proper backup strategy but otherwise leaving the instance as-is.

 

With a backup in place, the single-node MongoDB setup is indeed suitable for smaller Server deployments.  As an organization's Server usage grows, however, scaling MongoDB, first by moving the database to a dedicated host and then by adding redundancy, are natural points of expansion.  In fact, a number of clients have reached out to us recently (both directly and via their Alteryx support contacts) inquiring about guidelines for migrating their standalone, "Embedded" MongoDB instance to a more robust, "User-managed" MongoDB instance, improving the resilience and redundancy of their Alteryx Server installation.


This post is intended to address these recent user requests directly, providing general recipes for scaling MongoDB, first by migrating the standalone database to a dedicated host, and then adding redundancy via a MongoDB Replica Set.  This process relies on some basic Server administration concepts, particularly using the Server command line as discussed in a previous post.

 

References

In the spirit of up-front documentation and addressing the possibility of TL;DR, here are some key references for concepts in this post:

 

Preparing for a Migration

Before making any modifications to the Alteryx Server it's important to schedule Server downtime, take the Server offline completely, and perform a backup of the existing MongoDB database.  Backing up the MongoDB database is straightforward and we provide a command-line option to dump the existing database via the AlteryxService emongodump command.  For reference, complete instructions are available in an older post under the section "Backing up and restoring Embedded Mongo."

 

You will also need the current MongoDB credentials.  To collect the credentials, open an Administrator command shell, navigate to the Alteryx installation directory, and use the AlteryxService getemongopassword command-line option.  This retrieves both the administrative and non-administrative credentials for the database: 

getemongopassword.png

In an Embedded MongoDB instance, both the administrative and non-administrative user have the username "user".

 

Migrating to a new MongoDB host

There are several ways of migrating the Alteryx Server database to a new MongoDB host and we'll examine a few of the techniques.  The first is copying the database directly to the target server, and is applicable to a MongoDB instance hosted in a Windows environment.  The second is creating a database backup and restoring on the target server, applicable to a MongoDB instance hosted in either a Linux or Windows environment.

 

After migrating the database to a new MongoDB host, updating the MongoDB instance to become a full-fledged Replica Set is suprisingly straightforward and follows the same general process regardless of the host operating system.  Similarly, configuring the Alteryx Server to consume the new standalone MongoDB instance, be it a single node or Replica Set, is identical irrespective of the MongoDB hosting environment.

 

Copying to a new Server (Windows)

The first technique for migrating a standalone instance to a new Windows-based server is by copying the source Mongo folder itself to the new target system.  This is arguably the easiest method, requiring just a few short steps.


First, locate the MongoDB folder on the source system.  By default, the folder is located in C:\ProgramData\Alteryx\Service\Persistence.

source_mongo_folder.png

Note that the default persistence folder location may be overridden in the System Settings dialog on the Controller | Persistence page.

 

Copy that folder in its entirety from the source system to the target system.  In this example, we're using xcopy on the command line to move the database contents to a shared folder on the new host:

copy_database_folder.png

Depending on how large the folder is you may want to compress its contents prior to copying the folder to the new host.

 

Now, on the new host, you should be able to start MongoDB using that new folder as the database source folder.  On the command-line, start mongod.exe using the --auth and --dbpath command-line options, specifying the target folder containing the copied DB for the latter:

start_standalone_server.png

 

After the new MongoDB server starts, connect to the instance using mongo.exe and authenticate using the credentials collected from the host system.  Remember, the username for both the administrative and non-administrative users is "user": 

test_mongo_connection_1.png

 

Backing up and restoring to a new server (Windows and Linux)

Another technique for migrating to a new MongoDB server is creating a backup on the source system and then restoring from that backup on the target system.  If you haven't already created a backup of the source system, do so now using the AlteryxService emongodump command as described in this post under the section "Backing up and restoring Embedded Mongo":

emongo_dump.png

 

The process may take a while to complete, but in the end it will create a directory containing the contents of the databases plus a few log files:

mongo_dump_folder.png

 

As with the previous example, copy the contents of the backup to the target system (you may want to compress the contents of this folder first).  As before, we're using xcopy here to copy the backup to a shared folder on the new host:

copy_mongo_dump.png

 

Now, we'll restore the database on the new host using the Mongo tool mongorestore, for which full documentation is available on Mongo's website.  The mongorestore tool will restore data into a running database instance, so first we'll start mongod.exe in an empty target directory with authentication disabled (note the lack of the --auth flag):

empty_mongod.png

 

Then, use mongorestore to populate the new database.  Unless specified explicitly, mongorestore will connect to mongod.exe running locally on the default port of 27017:

mongorestore.png

 

After mongorestore completes its job, the database contents on the new host will be equivalent to that from the old host.  As in the previous example, [re]start mongod.exe with auth enabled and test the connection using mongo.exe using the same user and credentials as the original system:

restored_db.png

 

Other methods for migrating data to an existing MongoDB instance (Windows and Linux)

In addition to the two basic techniques presented above, there are several other ways to move data from an Alteryx Server MongoDB instance to a pre-existing MongoDB server.  For example, mongorestore can be used to restore only certain databases from a backup into an existing MongoDB instance, creating new databases/collections as necessary.  Similarly, the MongoDB copydb command can be executed from the mongo.exe command-line to copy databases from a source instance to a target instance.

 

Configuring Alteryx Server to use User-managed MongoDB

Regardless of the method you choose to migrate data from the Embedded MongoDB instance to a User-managed MongoDB instance, configuring Alteryx Server to connect to the new instance is trivial.

 

In the System Settings dialog, navigate to the Controller | Persistence page and choose "User-managed MongoDB," updating the connection information with the new MongoDB host and credentials.  Note that the password is for the non-administrative user from the Embedded MongoDB instance:

user_managed_single.png

 

MongoDB Replica Sets

Conceptually, a MongoDB Replica Set is a collection of MongoDB servers configured to provide High Availability (HA) and automatic failover.  A single Replica Set comprises one or more MongoDB servers working in unison, and best practices state that the Replica Set should contain at least three nodes (and an odd number of nodes in total).  At all times, one of the members of the Replica Set acts as the Primary, or the node responsible for servicing read/write requests from external clients such as the Alteryx Server.  The other nodes in the Replica Sets are called Secondaries, and they are responsible for maintaining exact copies of data hosted by the Primary and/or otherwise contributing to the overall health of the Replica Set.


In the unlikely event that the Primary fails, a process called an Election occurs, and one of the Secondaries clicks its heels three times and becomes the Primary.  Ideally, the election and subsequent failover to a secondary is transparent to the client, although in practice there may be small "blips" in service.  

 

Replica Sets may also contain other, non-electable nodes including Arbiters and dedicated backup nodes.  Arbiters are Replica Set members that do not store any data, but whose sole responsibility is to provide a tie-breaking vote (if needed) when electing a new Primary.  Backup nodes are Replica Set members whose sole responsibility is to provide read-only data access for rolling backups, and they may be termed "hidden" or "priority zero" nodes because they are configured to only ever act as a Secondary, contributing to the election process but never being electable to the Primary themselves.

 

Mongo has excellent documentation available on their website, and I encourage starting with their overview of replication in MongoDB and continuing with the collection of replication-related tutorials.  We'll be using several of the basic techniques covered in their tutorials, referring back to specifics from the MongoDB references as needed.

 

Updating User-managed MongoDB with a Replica Set

Adding resilience and redundancy to our new User-managed MongoDB instance is also reasonably straightforward, following a basic recipe as set forth in Mongo's guidelines for converting a standalone instance.  We'll execute the following four steps in sequence, each of which is expanded upon below in more detail:

  1. Connect to DB and enable cluster administration
  2. Create keyfile and deploy
  3. Start Replica Set members
  4. Connect to Primary and create the Replica Set

Step 1: Connect to DB and enable cluster administration

The first step is connecting to the existing database as the administrative user and enabling this user to deploy and administer Replica Sets.  Alternatively, a new user can be created with this privilege, but to keep things simple let's use our existing administrator and add the new capability.

 

Using mongo.exe, we'll connect to the database, authenticate as the administrative user, and then invoke the method "grantRolesToUser" with the new role "clusterAdmin" to give our administrative user the power to control Replica Sets:

grantrolestouser.png

 

Step 2: Create keyfile and deploy

Since we're using a Replica Set with authentication enabled, the Replica Set members must know how to communicate securely with each other.  There are several techniques for achieving this goal, and to keep things simple here we're going to use keyfiles.  Conceptually, a keyfile contains a bit of pre-shared information that is copied to each member of a Replica Set, and as long as the contents of the files are identical, the Replica Set members can talk to each other.

 

Following the general suggestions from Mongo, we'll use the openssl command line in Cygwin to generate the keyfile.  Contrary to the MongoDB docs, however, we'll specify the -hex option to correctly generate exactly 1k of data in the keyfile (note, however, that for testing purposes any text in the keyfile will work just fine, so open up Notepad and bang away!)

$ openssl rand -hex 512 > /cygdrive/C/mongo/keyfile

 

After generating the keyfile, copy it onto each node destined to be part of the Replica Set.  Since the keyfile contains sensitive information, it should be deployed to a secure location where only the mongod.exe user will have access.  Additionally, on Linux, this file must be owned by the same user that runs the mongod process, with 600 file permissions.

 

Step 3: Start Replica Set members

With a keyfile in place on each member, it's now time to start each one in turn.  We'll add two new flags to the mongod.exe command line including --keyFile to specify the key file we just generated, and --replSet to specify the name of the Replica Set, which in this example is arbitrarily "rs0":

startup_member_0.png

 

The other two members are started similarly.  Note how the Replica Set name is specified explicitly via the --replSet flag, as is the port via the --port flag in this example from the second member:

startup_secondary.png

 

Step 4: Connect to Primary and create the Replica Set

Now that each member is started, we'll log onto the Primary, authenticate, and initialize the Replica Set using the rs.initiate() command.  When this command completes, we'll add the other two members using the rs.add() command, explicitly specifying each node and port.  The entire sequence of three commands may take a minute or two to complete:

rs_initiate.png

 

Congratulations!  You've just created a three-member Replica Set, and any transaction will now be mirrored across all three nodes instead of just one.

 

Configuring Alteryx Server to connect to the Replica Set

In the System Settings dialog, navigate to the Controller | Persistence page and choose "User-managed MongoDB."  Instead of choosing a single MongoDB node as the Host, however, the Host will comprise the Replica Set name followed by a comma-separated list of the members (as <host1>:<port1>,<host2>:<port2>,<host3>:<port3>).

 

For our example Replica Set "rs0", the connection string will be as follows:

user_managed_rs0.png

Note, as with the single-node connection, enter the non-administrative credentials here.

 

Closing

As shown, the default "Embedded" MongoDB instance used by the Alteryx Server is portable and easily migrated to a standalone instance.  Furthermore, updating that standalone instance to support HA and failover via a MongoDB Replica Set requires just a few well-documented steps and perhaps a bit of practice.

 

Thanks for reading and stay tuned for more Server-related posts.  As always, feedback is greatly appreciated so please don't hesitate to reach out directly or via your Alteryx support contact.

 

Kudos to @TaraM@GaryS and @DougD (our resident MongoDB Giant!) for assistance.

Steve Ahlgren

Steve is a Distinguished Engineer at Alteryx on the Architecture team, working largely in C++ but dabbling in other languages and technologies. His contributions include backend components of the Alteryx Server, Hadoop connectors (HDFS/Avro), JavaScript integration in both the Designer and Engine (CEF), Spark Direct functionality, Cloud execution for desktop (CEfD), etc. He currently lives on the fringe of Desktop and Cloud, and is committed to bringing our Desktop/Server customers forward into the Cloud.

Steve is a Distinguished Engineer at Alteryx on the Architecture team, working largely in C++ but dabbling in other languages and technologies. His contributions include backend components of the Alteryx Server, Hadoop connectors (HDFS/Avro), JavaScript integration in both the Designer and Engine (CEF), Spark Direct functionality, Cloud execution for desktop (CEfD), etc. He currently lives on the fringe of Desktop and Cloud, and is committed to bringing our Desktop/Server customers forward into the Cloud.

Comments
lepome
Alteryx Alumni (Retired)

It should be noted that there have been a lot of changes to the Server product in the last few years, so the above procedure is not recommended for current versions.  If absolutely must move the server to a new machine, you're most likely to have success if you do it as a bit-wise clone of the old machine.  If that's not possible, please contact support@alteryx.com for guidance before you make that unsupported change.

jineshnp31
8 - Asteroid

Hi Steve,

 

Can we use Community Edition of Mongo if we decide to have MongoDb on its own nodes with Replica sets? Or do we need to go with Enterprise Edition?

lepome
Alteryx Alumni (Retired)

@jineshnp31 

 

You can use whichever edition of Mongo best suits your needs. The community edition will work fine with our server, but you need to determine if that edition meets your needs and MongoDB’s licensing requirements. Also we currently only support the following Mongo versions depending on the version of Server.

 

Alteryx Server 10.x – 11.8 supports MongoDB 3.0.x (specifically 3.0.4)

Alteryx Server 2018.x+ supports MongoDB 3.4.x (specifically 3.4.11)

jwolf
5 - Atom

Hello,

 

I have a little problem after migration with your guide. When I try to log with original credentials to the Gallery, I am getting: Unknown error. NullReferenceException .

  • I have installed Alteryx Server 2019.1 with 3.4.1 MongoDB
  • I can see all original workflows

 

Any hints?

Thank you!

lepome
Alteryx Alumni (Retired)

@jwolf 

Please clarify "migration with your guide."  Do you mean that you attempted a migration based on the information in the old (August 2016) post above?  Or did you do a bitwise clone of the old machine?  Or did you open a case for guidance on unsupported migration of server versions 2018.x or later?

jwolf
5 - Atom

I migrated according to the 2016 instructions (ok, maybe here could be the 1st problem?), specifically the section "Copying to a new Server (Windows)". So it means that I have 2 servers, both servers Both installations are the same, the database also looks fine, the problem is with logging in with the old credentials.

Thank you for your time.

lepome
Alteryx Alumni (Retired)

@jwolf 

I hope that you got everything worked out more than a month ago.  If not, open a case with support@alteryx.com.

You mention that you installed 2019.1.  Did you migrate an 11.x instance and then upgrade to 2019.1 afterwards?     

Also, what kind of authentication does your gallery use?  

 

lepome
Alteryx Alumni (Retired)

As I've commented in the past, the section of the original blog post above that discusses Migrating to a new MongoDB host, particularly Copying to a new Server (Windows) and Backing up and restoring to a new server (Windows and Linux) are out of date for supported software versions. 

 

I am very pleased to direct you to superior documentation on MongoDB Management.  As always, I recommend you make regular backups of the MongoDB.  Assuming you do that, then you will be well-positioned to use the Alteryx Server Host Recovery Guide should you need to.

 

(The sections above that discuss replica sets are still relevant.)

lepome
Alteryx Alumni (Retired)

With regards to the Server Host Recovery Guide, the order listed there today is incorrect.  You should not restore the database before restoring the RuntimeSettings.xml.  Wait until after you paste the <StorageKeysEncrypted> to restore the database.


Also, the link above is out of date.  Please refer instead to MongoDB Management.