Server SQL DB Customer FAQ
This article provides an overview of our support for Alteryx Server with user-managed SQL as the persistence layer and information about migrating from MongoDB to SQL.
We officially support MSSQL Server 2019 and 2022.
The only officially supported versions are MSSQL Server 2019 and 2022. Some cloud SQL versions might work but are not supported by Alteryx.
The SQL DB access user needs full control (admin level) for any SQL DB that Server will be using. This permission can be downgraded after SQL is set up and you have migrated from Mongo to SQL if applicable.
No, at this time we only offer MSSQL as a database option for user-managed databases.
Yes. There were slight schema changes with the new SQL DB. If you were directly querying Mongo, you need to check your queries and possibly update them. For more information, go to the SQL DB Schema Reference help page.
No, these DBs can be located in the same place.
Yes, you can run a SQL server instance alongside other database instances.
The performance should be equal between SQL and Mongo in the majority of cases. The only significantly slower performance occurs for workflows that take 5 seconds or less to run. See the table below for comparison times. For workflows that take longer than 5 seconds to run, the performance differences are negligible.
In the most extreme example, if a user ran 60 workflows a minute that took 1 second each, the difference in completion time would go from 1 minute in Mongo to 1 minute 15 seconds in SQL.
Ultimately the performance difference depends on what workflows are running, but since the slowdown only occurs with fast-running workflows, the difference of 0.25 seconds should not lead to a significant difference in performance.
Mongo Workflow Run Time | SQL Workflow Run Time |
|---|---|
5 seconds | 5.25 seconds |
1 second | 1.25 seconds |
To check the full MongoDB to SQL Migration Instructions, go to MongoDB to SQL Migration Guide. Please review the full migration instructions as this FAQ only answers the primary questions.
Server 2022.1+. If you’re on a version older than 2022.1, we recommend you upgrade to a version 2022.1 - 2023.2 before upgrading to 2024.1.
No, the SQL migration is done via a workflow you can run after you upgrade to 2024.1. This allows you to upgrade to 2024.1 and do your initial testing before migrating to SQL.
No. This is an optional migration and if you don’t want to migrate to SQL in 2024.1, you can migrate in a future release.
We recommend your SQL DB size to be twice the size of your existing MongoDB. This is because Mongo compresses the database size and the same compression does not occur in MSSQL.
For testing, you can run the migrator when the service is running. After testing, delete all records (not tables) from the SQL DB before you run the final migration. Fully shut down the service for the final migration and all records will be moved over now.
Yes. If the migrator is interrupted or stopped before completion, it will start off where it ended and continue to transfer records. This only applies if the service remains off and there are no changes to the database during the stopped period.
If you stop the migration and the service is started and changes are made to the MongoDB, then you will need to clear the SQL table contents, not the tables themselves before re-running the migrator.
You will see 0 errors in the results window and the logs will show all records transferred from MongoDB to MSSQL.
bcp_batch
When does the error occur? During Migration, the error will show in the Results window.
Solution: Ensure the correct SQL Driver is installed and configured (ODBC SQL Driver 17).
Example error message:
MongoToSQL_Migration_Macro (829): Migrator (22): Record #17: BatchTransferProcess (574): Record #1: Tool #9: Unable to find address for bcp_batch”
Service Failed to Start after Migration
When does the error occur? After migration, the error will show in \Alteryx\Service\alteryx-migration.csv.
Solution: The AlteryxService needs to start up at least once with MongoDB as the backend BEFORE migrating data to MSSQL. This ensures the MongoDB schema is updated correctly.
Example error message (in service logs):
ERROR,1,AlteryxServerMigrator,migrationLogger,ExecuteServerSqlDbMigrations,Server SQL database migrations have failed: Exception has been thrown by the target of an invocation.
String to Number Conversion Failure
When does the error occur?: During migration, the error will show in the Results window.
Solution: The AlteryxService needs to start up at least once with MongoDB as the backend BEFORE migrating data to MSSQL. This ensures the MongoDB schema is updated correctly.
Example error message:
Error: MongoToSQL_Migration_Macro (829): Migrator (22): Record #54: BatchTransferProcess (574): Record #1: Tool #2: Error SQLFetch: [Simba][Support] (50090) Conversion from string to number failed with value ''[Simba][Support] (50090) Conversion from string to number failed with value ''[Simba][Support] (50090) Conversion from string to number failed with value ''
AlteryxGallery.alteryx_server.Table_Name
When does the error occur? During migration, the error will show in the Results window.
Solution: Make sure MongoDB schema is PUBLISHED to MongoDB while setting up the Simba driver.
Example error message:
Error: MongoToSQL_Migration_Macro (829): Tool #46: Error opening "SELECT COUNT(DISTINCT Primary_Key) AS Count_distinct FROM AlteryxGallery.alteryx_server.Table_Name": No Columns Returned.
Unauthorized Command during MongoDB Schema Setup
When does the error occur? While attempting to publish the MongoDB Schema during Simba Driver setup.
Solution: Make sure the authentication database is set to the target database. So, when creating the DSN entry for AlteryxGallery, set the Authentication database to AlteryxGallery, not admin.
Example error message:
[Simba][MongoDBODBC] (110) Error from MongoDB Client: not authorized on test to execute command { insert: "DatabaseMetadata_SchemaMap", ordered: true, $db: "test", lsid: { id: UUID("9819f76d-486b-4722-a4f1-f8398cd9a4ae") } } (Error Code: 13)
The workflow produces errors during a failure. If this occurs, report the error from the Results window and send any screenshots and log files created during the run to your support team.
If the migration fails, you can start AlteryxService again and continue to use your MongoDB. At this point, Mongo is still fully connected and functional, so you don’t need to do anything additional to continue using Mongo if the migration fails.
If you made a backup of your RuntimeSettings.xml file before the migration, you can switch it out with your current RuntimeSettings.xml file (making backups of these two files can help). However, any changes made to Server while connected to SQL will not be represented after switching back to MongoDB.
No. The data is left unchanged. However, 1 collection is created to store the schema for the Simba MongoDB ODBC.
Migration from MongoDB to SQL is currently not supported for the FIPS environments. However, NEW FIPS environments can be set up using the user-managed MSSQL.