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.
SQL FAQ
1. What versions of SQL are supported?
We officially support MSSQL Server 2019 and 2022.
2. Are cloud SQL versions supported?
The only officially supported versions are MSSQL Server 2019 and 2022. Some cloud SQL versions might work but are not supported by Alteryx.
3. What permission level is required for SQL setup?
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.
4. Is there an option for SQL for an embedded database option?
No, at this time we only offer MSSQL as a database option for user-managed databases.
5. Does the SQL DB support SSL/TLS connections?
Yes.
6. Do you support Kerberos SQL authentication?
Yes.
7. Do you support WinAuth SQL authentication?
Yes.
8. Did the schema change between Mongo and SQL?
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.
9. Do the Alteryx Service and Alteryx Gallery DBs need to be separate DBs?
No, these DBs can be located in the same place.
10. Can a new FIPS environment be set up using SQL in 2024.1?
Yes.
11. Can I add my Alteryx SQL DB to an existing SQL deployment?
Yes, you can run a SQL server instance alongside other database instances.
12. How does performance compare between Mongo and SQL?
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 |
MongoDB to MSSQL Migrator FAQ
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.
1. What version of Server can I upgrade to 2024.1 for full migration (Mongo to SQL) support?
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.
2. Does the MongoDB to MSSQL Migrator work for embedded Mongo databases and user-managed Mongo (Enterprise or Atlas) databases?
Yes.
3. Is the SQL migration part of the 2024.1 upgrade?
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.
4. Where can I download the SQL migration workflow?
5. Do I have to migrate to SQL in 2024.1?
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.
6. What size database do you recommend for SQL when migrating?
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.
7. Does the service need to be off for the migration to run?
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.
8. Can the migrator be stopped before it’s finished? What happens if the migration is interrupted?
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.
9. How will I know the migration is completed successfully?
You will see 0 errors in the results window and the logs will show all records transferred from MongoDB to MSSQL.
10. What are the common errors I might experience when running the migration?
bcp_batch
Example error message:
MongoToSQL_Migration_Macro (829): Migrator (22): Record #17: BatchTransferProcess (574): Record #1: Tool #9: Unable to find address for 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).
Service Failed to Start After Migration
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.When does the error occur?
After migration, the error will show in \Alteryx\Service\alteryx-migration.csv.
You can get this error if you incorrectly specify a Driver as part of the Server UI Connection string.
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.
Review your connection strings. In particular, ensure you do not specify a Driver in the Server UI persistence connection string. For more information, go to SQL DB Advanced Connection Strings.
String to Number Conversion Failure
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 ''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.
AlteryxGallery.alteryx_server.Table_Name
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.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.
Make sure to define the appropriate Authentication Source when creating the DCM Credential for each connection. To access the Authentication Source text box, expand the Advanced parameters section underneath the username and password entries when creating your credentials.
If you use the embedded MongoDB, this will require two separate Credentials: one using the AlteryxService database and the other using the AlteryxGallery database for Authentication Source. For more information, go to Mongo to SQL Migration Guide.
Unauthorized Command during MongoDB Schema Setup
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)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.
11. What should I do if the migration fails and how will I be notified?
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.
12. What happens if I want to return to using MongoDB after the migration?
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.
13. Will the SQL migration make any changes to the old MongoDB?
No. The data is left unchanged. However, 1 collection is created to store the schema for the Simba MongoDB ODBC.
14. Can I migrate a FIPS version of Server to SQL?
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.