Database connections in lower and higher environment

Question

Hi All,

We have a different environment, the plan is to deploy workflow in DEV gallery then move to QA--> UAT and Then prod.

DEV-->QA--UAT-->Prod

Now we have created Data connections in each server.

If we are migrating workflows from lower to the higher environment and if Database connection names are different then need to modify workflows.

Every time we can't keep the same names of database connections in all environments.

Please let us know how others are managing and any best option to manage multiple database connections for different teams.

Thanks and Regards

Bhausaheb

PaulFields2 · Answer

Hi Patrick - I was interested in your post below, although would ask for a little more information on implementation.

If it helps, in my case we are using DSN-less connection strings and have three database environments (DEV - TEST - PROD) across which our workflows need to operate. These connections are being made by standard Alteryx Input Data, Dynamic Input, and Output Data nodes.  Here is an example of our DB connection strings - with differentiations bolded.

oci:sd_rules/{my_dev_password}@(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=rbalvorse16d.bas.{my_company}.com)(PORT=15214))(CONNECT_DATA = (SERVER=DEDICATED)(SERVICE_NAME=PBSD.BAS.{my_company}.COM)))

oci:sd_rules/{my_test_password}@(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=rbalvorse16d.bas.{my_company}.com)(PORT=15215))(CONNECT_DATA = (SERVER=DEDICATED)(SERVICE_NAME=PBST.BAS.{my_company}.COM)))

oci:sd_rules/{my_prod_password}@(DESCRIPTION = (ADDRESS=(PROTOCOL=tcp)(HOST=rbalvorse11p.bas.{my_company}.com)(PORT=15212))(CONNECT_DATA = (SERVER=DEDICATED)(SERVICE_NAME=PBSP.BAS.{my_company}.COM)))

Questions

1) With the approach you outline in this thread, how would I go about enabling a Database Connection in the Gallery (assuming you mean Alteryx server) to handle these differences.

2) What approach can I use when running a workflow from my local machine when doing development work, e.g. so that the DB is always pointed at my DEV database instance?

Back when I was doing Java in a Unix environment, we would use a script to set environment variables for database connections that were specific to DEV, TEST and PROD.  It would be great if Alteryx were to support such an approach, but so far I've not seen a way to use environment variables in database connection strings. This would really simplify things.

Thanks very much in advance for your help.

revathi · Answer

Considering the number of environments you have, as the number of canvases in your environment grows  - managing data connections in Gallery and user entitlements to those connections can be cumbersome.

Alternatively - encourage your users to use connection strings (DSN less connection) so that you don't have to worry about managing the connections for your users.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-format-common-ODBC-DSN-less-connection-strings/ta-p/467506

Other concerns with data connections:

user entitlements..  You will have to monitor if a user moves out of a team and if yes  - check if the person should continue to have access or not.

update credentials.. if password to a database connection changes  - then you will have to manage those connections password as well.

This approach also requires users to provide the credentials to you. Which can be avoided through connection string.

Anyway  - Connection string is one way of connecting to databases that works for our org. Please deploy the approach that makes more sense to your org.

patrick_mcauliffe · Answer

It sounds like an opportunity to re-design the environment.

The Database Connections names are what gets stored in the workflow as a reference point to the machine running it.

In order to smoothly transition, the names have to remain the same across each environment (desktop, dev, qu, uat, prod).

Is there a reason you can't use the same connection name across each environment?

Keeping in mind that the connection name needs to be the same, but the actual database the connection name goes to can be different in each environment.

For example, I have separate DB2 databases in dev, qa, and prod.

Hostname in dev is: db2-dev

Hostname in qa is: db2-qa

Hostname in prod is: db2-prod

I can make a Database Connection in Dev names DB2 that connects to db2-dev, the Database Connection in QA connects to db2-qa, and the Database Connection in prod connects to db2-prod.

I would set each of these up in the Database Connections within the Gallery, and then grant users access to those through the Gallery so that you can manage access and update as needed without needing to rely on users to make changes.