alteryx server Knowledge Base

If you are upgrading your Alteryx Server from an older version to 2018.1 or greater, your MongoDB will be migrated from Mongo 3.0 to Mongo 3.4 as part of the process. This article details how to troubleshoot a potentially failed migration. 

This is the second article in a series on Alteryx Server backup and recovery. You can find Part 1 at:   Alteryx Server Backup & Recovery Part 1: Best Practices   As long as a backup of the Mongo database is available, you can get Alteryx Server back up and running. Luckily, backing up the embedded MongoDB is pretty simple, and can be done with a few console commands. I would recommend creating a batch file or script to perform the process. Doing so will allow you to schedule the backup using Windows Task Scheduler. The actual steps to perform a MongoDB backup are covered in detail in the online help under the server configuration section or at this direct link. I will also outline the steps below for completeness.   To create a backup of the MongoDB:   Stop AlteryxService. Execute the following command to save a backup of the database in the specified folder:   alteryxservice emongodump=<path to backup location> Restart AlteryxService   You can easily script this to a batch file with a few simple console commands. Keep in mind that paths may vary on your server, but it should look something like this.   Example:     "C:\Program Files\Alteryx\bin\AlteryxService.exe" stop "C:\Program Files\Alteryx\bin\AlteryxService.exe" emongodump=Z:\Path\MongoBackup "C:\Program Files\Alteryx\bin\AlteryxService.exe" start     You can add additional features, such as logging and date/time stamps, to the backups.  As an example of additional useful features to include with your backups, I have included the code for a batch script I created that adds the following information: logging with date/time stamping, a backup that is also date/time stamped, automated archival of the backup, copying the archive to a network location, and cleanup of the temp files.   Once you have a batch file or other script to perform your backups, you need to test the script to ensure it works properly. Once testing is done, the next step is to schedule the backup. The easiest way to do this is to use Windows Task Scheduler. To create a scheduled task on Windows 2012 Server, follow these steps:   Create a scheduled task:   Open Task Scheduler and click on “Create Task”   On the General tab, enter “Name”, “Description”, select “Run whether user is logged in or not", and select "Run with highest privileges"   On the Triggers tab, click “New”   A dialogue box will appear. Define the schedule (daily, weekly, etc...) on which you want the backup to run and click “OK”   On the Actions tab click “New”   On the dialogue window, make sure “Start a Program” is selected and click “Browse”. Select the batch file you created and click “Open”. Then click “OK”.   Click “OK” on the Create Task window to finalize the creation of the backup task.   Now that you have successfully implemented backup procedures and scheduled a task to automate the backups, it is time to discuss database restoration from a backup. The good news is that restoring the database is just as simple as backing it up. Assuming that 1) the server is functioning, 2) Alteryx Server is installed, and 3) you have a valid backup available, you can follow these simple steps outlined below.   To restore a backup of the MongoDB:   Stop AlteryxService Execute the following command to restore the backup:   alteryxservice emongorestore=<path to backup location>,<path to restore to>   Restart AlteryxService   This simplicity and same focus on command line statements means that we can also script recovery. However, since recovery actions are much less frequent, it probably isn't necessary. Instead, you would just connect to the server, open a command prompt and, following our backup example above, execute the following commands:   Example:     "C:\Program Files\Alteryx\bin\AlteryxService.exe" stop "C:\Program Files\Alteryx\bin\AlteryxService.exe" emongorestore=Z:\Path\MongoBackup,C:\ProgramData\Alteryx\Service\Persistence\MongoDB "C:\Program Files\Alteryx\bin\AlteryxService.exe" start     For Alteryx Server we also recommend backing up the controller token and some settings files. While the server can be recovered without these files. Having a backup of them can expedite the recovery process, and they will also ensure you will be able to decrypt any sensitive data in the database. The settings files we recommend backing up are:   C:\ProgramData\Alteryx\RuntimeSettings.xml C:\ProgramData\Alteryx\Engine\SystemAlias.xml C:\ProgramData\Alteryx\Engine\SystemConnections.xml   Again, please keep in mind the exact paths may vary depending on the server configuration and where the backup is located. This example also assumes the backup isn't compressed/archived. If you are using a backup script that archives the backup and copies it to network storage, you will need to copy the backup file to the server and decompress the archive before running the recovery commands above.     Below is the code for my sample batch script:   ::----------------------------------------------------------------------------- :: :: AlteryxServer Backup Script v.2.0.2 - 01/04/19 :: Created By: Kevin Powney :: :: Service start and stop checks adapted from example code by Eric Falsken :: ::----------------------------------------------------------------------------- @echo off ::----------------------------------------------------------------------------- :: Set variables for Log, Temp, Network, and Application Paths :: :: Please update these values as appropriate for your environment. Note :: that spaces should be avoided in the LogDir, TempDir, and NetworkDir paths. :: The trailing slash is also required for these paths. ::----------------------------------------------------------------------------- SET LogDir=C:\ProgramData\Alteryx\BackupLog\ SET TempDir=C:\Temp\ SET NetworkDir=\\ServerName\SharePath\ SET AlteryxService="C:\Program Files\Alteryx\bin\AlteryxService.exe" SET ZipUtil="C:\Program Files\7-Zip\7z.exe" :: Set the maximium time to wait for the service to start or stop in whole seconds. Default value is 2 hours. SET MaxServiceWait=7200 ::----------------------------------------------------------------------------- :: Set Date/Time to a usable format and create log ::----------------------------------------------------------------------------- FOR /f %%a IN ('WMIC OS GET LocalDateTime ^| FIND "."') DO SET DTS=%%a SET DateTime=%DTS:~0,4%%DTS:~4,2%%DTS:~6,2%_%DTS:~8,2%%DTS:~10,2%%DTS:~12,2% SET /a tztemp=%DTS:~21%/60 SET tzone=UTC%tztemp% echo %date% %time% %tzone%: Starting backup process... > %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Stop Alteryx Service ::----------------------------------------------------------------------------- echo %date% %time% %tzone%: Stopping Alteryx Service... >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log SET COUNT=0 :StopInitState SC query AlteryxService | FIND "STATE" | FIND "RUNNING" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO StopService SC query AlteryxService | FIND "STATE" | FIND "STOPPED" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO StopedService SC query AlteryxService | FIND "STATE" | FIND "PAUSED" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO SystemError echo %date% %time% %tzone%: Service State is changing, waiting for service to resolve its state before making changes >> %LogDir%BackupLog%datetime%.log SC query AlteryxService | Find "STATE" timeout /t 1 /nobreak >NUL SET /A COUNT=%COUNT%+1 IF "%COUNT%" == "%MaxServiceWait%" GOTO SystemError GOTO StopInitState :StopService SET COUNT=0 SC stop AlteryxService >> %LogDir%BackupLog%datetime%.log GOTO StoppingService :StopServiceDelay echo %date% %time% %tzone%: Waiting for AlteryService to stop >> %LogDir%BackupLog%datetime%.log timeout /t 1 /nobreak >NUL SET /A COUNT=%COUNT%+1 IF "%COUNT%" == "%MaxServiceWait%" GOTO SystemError :StoppingService SC query AlteryxService | FIND "STATE" | FIND "STOPPED" >> %LogDir%BackupLog%datetime%.log IF errorlevel 1 GOTO StopServiceDelay :StopedService echo %date% %time% %tzone%: AlteryService is stopped >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Backup MongoDB to local temp directory. ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Starting MongoDB Backup... >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log %AlteryxService% emongodump=%TempDir%ServerBackup_%datetime%\Mongo >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Backup Config files to local temp directory. ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Backing up settings, connections, and aliases... >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log copy %ProgramData%\Alteryx\RuntimeSettings.xml %TempDir%ServerBackup_%datetime%\RuntimeSettings.xml >> %LogDir%BackupLog%datetime%.log copy %ProgramData%\Alteryx\Engine\SystemAlias.xml %TempDir%ServerBackup_%datetime%\SystemAlias.xml copy %ProgramData%\Alteryx\Engine\SystemConnections.xml %TempDir%ServerBackup_%datetime%\SystemConnections.xml %AlteryxService% getserversecret > %TempDir%ServerBackup_%datetime%\ControllerToken.txt ::----------------------------------------------------------------------------- :: Restart Alteryx Service ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Restarting Alteryx Service... >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log SET COUNT=0 :StartInitState SC query AlteryxService | FIND "STATE" | FIND "STOPPED" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO StartService SC query AlteryxService | FIND "STATE" | FIND "RUNNING" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO StartedService SC query AlteryxService | FIND "STATE" | FIND "PAUSED" >> %LogDir%BackupLog%datetime%.log IF errorlevel 0 IF NOT errorlevel 1 GOTO SystemError echo %date% %time% %tzone%: Service State is changing, waiting for service to resolve its state before making changes >> %LogDir%BackupLog%datetime%.log SC query AlteryxService | Find "STATE" timeout /t 1 /nobreak >NUL SET /A COUNT=%COUNT%+1 IF "%COUNT%" == "%MaxServiceWait%" GOTO SystemError GOTO StartInitState :StartService SET COUNT=0 SC start AlteryxService >> %LogDir%BackupLog%datetime%.log GOTO StartingService :StartServiceDelay echo %date% %time% %tzone%: Waiting for AlteryxService to start >> %LogDir%BackupLog%datetime%.log timeout /t 1 /nobreak >NUL SET /A COUNT=%COUNT%+1 IF "%COUNT%" == "%MaxServiceWait%" GOTO SystemError :StartingService SC query AlteryxService | FIND "STATE" | FIND "RUNNING" >> %LogDir%BackupLog%datetime%.log IF errorlevel 1 GOTO StartServiceDelay :StartedService echo %date% %time% %tzone%: AlteryxService is started >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: This section compresses the backup to a single zip archive :: :: Please note the command below requires 7-Zip to be installed on the server. :: You can download 7-Zip from http://www.7-zip.org/ or change the command to :: use the zip utility of your choice as defined in the variable above. ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Archiving backup... >> %LogDir%BackupLog%datetime%.log %ZipUtil% a %TempDir%ServerBackup_%datetime%.7z %TempDir%ServerBackup_%datetime% >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Move zip archive to network storage location and cleanup local files ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Moving archive to network storage >> %LogDir%BackupLog%datetime%.log echo. >> %LogDir%BackupLog%datetime%.log copy %TempDir%ServerBackup_%datetime%.7z %NetworkDir%ServerBackup_%datetime%.7z >> %LogDir%BackupLog%datetime%.log del %TempDir%ServerBackup_%datetime%.7z >> %LogDir%BackupLog%datetime%.log rmdir /S /Q %TempDir%ServerBackup_%datetime% >> %LogDir%BackupLog%datetime%.log ::----------------------------------------------------------------------------- :: Done ::----------------------------------------------------------------------------- echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Backup process completed >> %LogDir%BackupLog%datetime%.log GOTO :EOF :SystemError echo. >> %LogDir%BackupLog%datetime%.log echo %date% %time% %tzone%: Error starting or stopping service. Service is not accessible, is offline, or did not respond to the start or stop request within the designated time frame. >> %LogDir%BackupLog%datetime%.log

This is a very common error that can occur if the AlteryxService shuts down unexpectedly. Most commonly the error is caused by MongoDB not shutting down properly and the lock file does not get released. This prevents MongoDB from starting the next time you try to start the AlteryxService and returns an error message.

Alteryx Server provides a fully scalable architecture that allows an organization to scale Alteryx to automate data analytics, tackle bigger projects, process larger datasets and put self-service data analytics into the hands of more decision makers. From scaling Worker nodes to Gallery nodes to the MongoDB persistence layer, Alteryx Server allows organizations to efficiently manage their automated and self-service data analytics needs.

One common reason why the Alteryx Service appears stuck in the 'Stopping' state is when the service is trying to stop but the AlteryxEngineCmd.exe process is running. In other words, a workflow is running. The Alteryx Service cannot be stopped when a workflow is running due to a schedule or a Gallery run.

Now, find all your Server and Gallery questions and answers in one place!  The new Gallery Admin Help Page has your Server Installation Guide, Configuration instructions, and the much-requested Administer Gallery management features - Subscriptions and Studios defined!  Manage your user permissions!  Edit user accounts!   

One of the three database options when setting up the Alteryx Server is to connect into a User-Managed MongoDB instance.  Why would you want to set up your own implementation of MongoDB?  The main benefits are to take advantage of the features of MongoDB that are not included with our embedded instance.

I have encountered a few situations recently that resulted in data loss because a backup of the Alteryx Server wasn't available. I can't stress enough the importance of regularly backing up your server. This article, the first of two-part series, will cover some options and best practices to ensure you have the necessary backups available in the event they are needed. Part II will focus on the embedded MongoDB instance  provided with the Alteryx Server install. If you are utilizing a user-managed MongoDB instance, please refer to MongoDB's documentation for backup and recovery procedures at https://docs.mongodb.org/manual/administration/backup/.   Let's go over some widely accepted best practices for backing up servers and databases:   Schedule Regular Backups - Backing up consistently on a scheduled basis is key to ensure you minimize data loss and downtime. I recommend backing up nightly during non-peak hours to minimize potential impact to users and for the minimal amount of data loss in the case of a crash or another failure. If backing up nightly isn't feasible, scheduling weekly backups is also an option. The important thing is to set a regular schedule.   Keep Historic Backups for a Defined Period - Sometimes the unexpected happens and a backup fails, becomes corrupt, gets lost/deleted, or the issue isn't noticed immediately (causing the problem state to be present in the backups). Having historic backups available helps ensure you have a backup available, and allows you to choose one created before the issue began.   Store Backups on Network or SAN Storage - Storing your backups on the same server where the data resides runs the risk of those backups not being available during a failure event. Let's consider what happens when your server suffers a disk failure. If the backups are stored locally on that disk, then they are gone too, making recovery impossible.  However, if your backups are stored on the network they would not be impacted by a failure event on the server.   Keep a Copy of the Backup Off-Site - This falls along the same lines as above. If the only backups are on a file server in the same data center as the Alteryx Server, and that data center suffers a disaster, you will lose both your server and your backups. Keeping an additional copy offsite will allow you to bring the server back up in the Cloud or at another data center if need be.   Validate your Backup Files - You should periodically check to ensure your backups are occurring successfully, and confirm that the backups are valid and usable. There is nothing worse than putting a backup process in place and then finding out after a failure occurs that the backups stopped working 6 months ago or that all of your backups aren't usable.    Practice your Recovery Procedures Regularly - Recovery drills allow you to become familiar with the restoration process and the amount of time needed to return to a fully functional state in the event of a disaster. Practice is also proven to reduce the occurrence of mistakes, and can save valuable time. I recommend running a recovery drill quarterly or bi-yearly.   Keep in mind that, in most cases, backing up the entire server including the OS and all data isn't necessary. In fact, it can actually significantly increase the average time to restoration. Instead, I would recommend backing up only the critical data and configuration files for the server. This is because it is significantly faster to clean install the server and necessary software, and then restore the backed up data/configuration than it is to restore the entire server. This is especially true in the case of virtual servers, as deploying a new virtual server takes minutes in most cases. These limited backups can also reduce the time it takes to complete and validate the backups and reduce the storage needs/costs involved in keeping those backups.   Part 2 - Alteryx Server Backup & Recovery Part 2: Procedures    

Is it possible to query the data on the "View Schedules" tab of the scheduler? This question has come up a few times recently, and the answer is yes you can but the method depends on whether you are using MongoDB (Server) or SQLite (Designer + Scheduler). Let's go over MongoDB (Server) first: First you'll need some information from your System Settings (Controller - Persistence section): You'll need the Host information, Username, and Password. You'll see two options for password, Admin and regular. For this you want to use the regular Password (which you can just copy/paste into your connector). Once you have this information, configure the MongoDB Input tool (Connectors toolset): The "Host" information goes in the Server input. Username and Password go in their respective boxes. The database you need to query is AlteryxService. The Collection drop down should auto populate with the tables you can pull from, however you may need to run the workflow once to refresh (you will get an error stating that Database and Collection must be specified). Once you've established your connection you can use the rest of the tool configurations to set up any other information you want such as record limits: You can find more information on the specifics of these options in the help file by clicking on the ? icon in the tool configurations. Next we’ll go over how to connect using SQLite (Designer + Scheduler): For this option it’s pretty straight forward. You’ll use your standard Input Data tool. Browse out to: ProgramData\Alteryx\Service\Persistance\AS_Schedules The file you are looking for is called “__TheData.sqlite” Parse the data With both methods you'll get a variety of information back including computer name, username, run dates, etc. There will also be a field called "ServiceData" that is a blob field with a binary object. This field contains additional information about the record you are viewing and can easily be parsed using the ServiceDataParser macro attached below. The ServiceDataParser.yxmc was created by Kory Cunningham and is necessary to make use of the additional field. The original macro is included in the Gallery Usage Report App available on the downloads page ( http://downloads.alteryx.com/downloads.html )  Note that in some cases you may encounter an error when trying to use the macro stating "This tool is not licensed". This is because the macro uses a generic tool that some older licenses don't enable. If you encounter this error, consult with our Fulfillment team to obtain an appropriate license.   Put it all together: The final step here is to join the information from the various Mongo tables back together (if pulling from multiple tables). The key table for this process is the Queue table (AS_Queue) as it holds all of the IDs necessary to join back to the other tables (AS_Results, AS_Schedules, AS_Applications) in order to get all the data together.   You'll need multiple Joins to make this work outlined below: 1) Join the Schedules Table to the Queue Table using the AS_Application_ID field. 2) Join the results of Step 1 to the AS_Results Table using the AS_Queue_ID field. 3) Join the results of Step 2 to the AS_Applications Table using the AS_Application_ID field and the Mongo_id field (from Mongo Input tool)   Note that in order to use the Mongo_id you'll need to parse it out to remove the unneeded information. You can do this using a simple RegEx expression within the RegEx tool: .*"(.*)".*   I've also attached a sample workflow detailing this process. Note that the workflow probably won't run until you update the credentials to match your own as it is set up for my instance of Mongo. You can use the workflow as a template for your own connections.   If you want to take this process a step further, take a look at this article. It will show you how ot use an Alteryx macro to accomplish this (downloadable from the Gallery) and also includes a link to show you how to build a Tableau dashboard to make the information easier to consume! All screen shots and directions are taken from Alteryx version 10.1.6   **Update for 11.0 release** In 11.0, the option to schedule workflows from the Gallery was added. I've updated the sample workflow to show how to bring those in and identify them separately. There's a bit of extra parsing necessary. Take a look at the "PullFromMongo_Updated_v11.yxzp" file attached.