Welcome to the Alteryx Knowledge Base
Created/Edited -
- Stand up a new Alteryx Server instance that uses MSSQL for the persistence layer with pre-existing / custom database names
- Alteryx Server 2024.1
- MSSQL Server 2019 / 2022
Important: This covers only for a NEW Alteryx Server install, NOT a migration from Mongo to MSSQL of an pre-existing Alteryx Server instance where SA access is restricted
- This is a new install for a fresh server to be stood up.
- The environment is restricted and only a dbadmin user is available with pre-made unused databases.
- Eg. SA user credentials can not be accessed
Important: While it is best practices for new Server environments to allow Alteryx Server to automatically create the necessary databases and tables within your SQL Server instance required by Alteryx Server., this requires the user in the connection string to have SA access. In some more restricted / hardened environments this may not be possible. This article covers the manual steps required for this particular use case only.
1) Create two SQL databases. 'mssql_db1' (eg "AlteryxService") and 'mssql_db2' (eg "AlteryxGallery")
2) Create an admin db user that can access both databases created
3) run ALTER commands :
ALTER DATABASE [mssql_db1]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [mssql_db1]
COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8
GO
ALTER DATABASE [mssql_db1]
SET MULTI_USER
GO
ALTER DATABASE [mssql_db2]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE [mssql_db2]
COLLATE LATIN1_GENERAL_100_CI_AS_SC_UTF8
GO
ALTER DATABASE [mssql_db2]
SET MULTI_USER
GO
4) Build your connection strings to enter into Alteryx System Settings:
Example:
Controller / Service
Driver={ODBC Driver 17 for SQL Server};Server=pod-xyz.a.ayxcloud.com,1433;UID=dbadmin;PWD=p@ssw0rd;Integrated Security=False;Database=mssql_db1;
Gallery / Server
Server=pod-xyz.a.ayxcloud.com,1433;Database=mssql_db2;User Id=dbadmin;Password=p@ssw0rd;5) Allow Alteryx services to startup after applying the connection strings in system settings. Then check / access Gallery UI to complete the fresh install setup as needed.