Deploy Alteryx Connect on MS SQL Database

[MattH]

The purpose of this article is to describe steps how to deploy Connect on the MS SQL database.
This article is valid for Alteryx Connect versions 2020.1 and higher.

Prerequisites

• Install package for Alteryx Connect version 2020.1 or newer.
• Prepared MS SQL database with the following parameters (see Installing and Setting Up Microsoft SQL Server Step by Step Guide) :

1. Database should be case insensitive with UTF8 support (LATIN1_GENERAL_100_CI_AS_SC_UTF8).
2. Empty database “std_xforms” exists.
3. Schema “std_xforms” exists.
4. User “std_xforms” with the default database “std_xforms” exists, user has assigned role “db_owner”. 
5. Test database connection with your SQL client to make sure your database is accepting TCP/IP connection and your username and password is working. 
6. TBD SQL CREATE DATABASE & SCHEMA & USER SCRIPT

Installation Steps

1. Run installer and make sure to uncheck "Start the Alteryx Connect service now".  If start-up happened, stop instance, remove .done files in ac_init folder and delete default h2 database files.

1. Copy the driver mssql-jdbc-7.4.1.jre11.jar (https://www.microsoft.com/en-us/download/details.aspx?id=58505 ) to C:\Program Files\AlteryxConnect\lib\ .
2. Update the connection string in alteryx_connect.properties as follows:

jdbc:sqlserver://localhost:1433;databaseName=std_xforms

NOTE: “localhost” will be correct only in case you are running MS SQL locally. Otherwise you need to enter hostname/IP address of your MS SQL server.

Here is the full properties file:
[global]
semanta.xf3.db.jdbc=jdbc:sqlserver://localhost:1433;databaseName=std_xforms
semanta.xf3.db.user=std_xforms
semanta.xf3.db.schema=std_xforms
semanta.xf3.db.password=Conn3ct!!
enable.configuration=true
display.use.button=true

1. Start the windows service “Alteryx Connect” and wait for the initialization of the database (this can take up to 10 minutes).
2. After the database is initialized, you should be able to connect to UI (such as http://localhost/). 

If you are not able to connect to UI, follow these steps:  

1. Check logs (../connect_catalina.log).
2. Check tables are created in the selected schema.

Check your connection string is OK.
Appendix: Installing and Setting Up Microsoft SQL Server Step by Step Guide

1. Mssql basic install (180 days evaluation version) (SQL2019-SSEI-Eval).  
2. Basic installation is sufficient.
3. After mssql server is installed, set port to fixed and enable TCP/IP:

   Open the SQL Server Configuration Manager.
   In SQL Server Network Configuration select Protocols for [yourServerInstance].
   In the right-hand pane, make sure that TCP/IP is Enabled.
   Open the 1433 port in your firewall.
   In the SQL Server Configuration Manager, right-click TCP/IP and select Properties.
   Select the IP Addresses tab and make sure the TCP Port for IP1 is 1433.
   Make sure that’s not configured to DYNAMIC (on old SQL server versions).

4. Download and install SQL Server Management Studio (SSMS):

   Login with windows authentication
   or when using advanced settings configure password for sa user.

5. Configure and change the authentication mode to use also sql server creds:

   Once we login to SSMS using Windows Authentication, we need to check the security settings to confirm whether MS SQL is set up to allow both Windows and SQL Authentication.

6. Recommended step: Unlock user sa in Security/Logins/select sa/properties/tab Status/login enabled and and set up password in the General tab.
7. Create database "std_xforms", select owner "default", collation needs to be UTF8. So, select some local collation ending with _UTF (tested with unicode characters with LATIN1_GENERAL_100_CI_AS_SC_UTF8).  The collation must be _CI (case insentitive) and _UTF8.
   See MSFT whitepaper https://docs.microsoft.com/en-us/sql/relational-databases/collations/collation-and-unicode-support?v... .  
8. Create login server/Security/Logins new login /set default database std_xforms Security/Logins/create new user "std_xforms". Select SQL Server authentication and Default database.
9. Create database user “std_xforms” server/Databases/std_xforms/Secutity/users.
10. Create schema std_xforms. server/Databases/std_xforms/Security/schemas/create new.
11. Create user mappings on login properties and assign role membership server/Security/Logins/std_xforms properties.

Known Errors Troubleshooting:

• The user should not be member of Sysadmin server role: Members of the sysadmin fixed server role ignore the DEFAULT_SCHEMA and use dbo by default. So tables are not created in std_xforms schema but in dbo schema https://dba.stackexchange.com/questions/42955/query-without-having-to-specify-tables-schema. 
• Wrong database collection: Instead of case insensitive, case sensitive was selected while creating a new database (it can be checked if you right-click on the database and select Properties). 

 Collation can be selected while creating a database in New Database > Options > Collation: 
Or on an existing database in Database Properties:
 

[Nikul-TheFirstMile]

Hi Mattt

This is great. And thanks for putting this KB out. Quick question. How easy/feasible it will be to upgrade Connect instance to 2020.1 and migrate from H2 to SQL Server back-end?

[DanH]

Hi @Nikul-TheFirstMile ,

An actual migration of preexisting content from H2 to MSSQL is not yet supported, though the development team is looking into this as a possibility for an upcoming release.

Thanks,

Dan