Server: Allow server to use large-scale SQL backend (with enterprise options)

[SeanAdams]

So - one of the biggest challenges that we have with the MongoDB used by Alteryx Server is that we continually have issues with locking (where our admins have to go in and undo locks)

Additionally - the current implementation of MongoDB connectivity does not support full Kerberos authentication which means that we're on a non-compliant install (which in a large enterprise is an uncomfortable place).

Given that a very large amount of what the server does is transactional - it would make sense to have an option to use a large-scale SQL server instead of using Mongo.   For large enterprise customers, there must be flexibility to allow the databases that they have large supported instances of (my strong preference would be MS SQL 2016).

MS SQL natively supports XML so all the canvasses can be stored in native format.   Additionally, MS SQL allows very fast query across XML, and given the clustering and reporting capabilities in MS SQL, this would dramatically increase our ability to self-manage our infra.

Given that Alteryx is looking more and more at large Enterprise customers - a move to a large-scale clustered SQL env as the back-end would be a very positive move.

NOTE: as we consider DB options for a SQL backend - please consider your large-scale enterprise customers.   For example - MS SQL or Oracle or DB2 are all much more prevalent in enterprises than databases like Postgres - so it's important to focus on the enterprise support for the DB that you choose.

+ @Deeksha @avinashbonu @revathi @BenBu

[brandonculver]

MS or MySQL would be my top 2 preferred

[patrick_digan]

+1 We have 0 internal MongoDB knowledge and plenty of folks who know SQL.

[MattB]

Hi @SeanAdams,

Thanks for posting the idea. We are re-evaluating MongoDB as the persistence layer in our Server product and will be sure to consider your feedback as we determine what other options to support.

For other customers, please reply to this thread and list out your top two choices for persistence layer. Microsoft SQL Server, Oracle, PostgreSQL...?

[patrick_digan]

@MattB MS SQL would be our pick as we have in house people who could support it.

[Claje]

MS SQL would also be optimal for our organization, for the same reasons listed above.

I would also say that a version/variant of a MySQL option might be acceptable - MySQL is close enough in syntax that I think for most of our needs we could leverage our existing MS SQL background.

Overall, using the current MongoDB backend is difficult and slow even using Alteryx, and I don't often say things are hard in Alteryx.

[andrewdatakim]

Currently my answer would be Oracle as my 1st and MS SQL as a second. I been at a few different companies since I started working with Alteryx and the most common back-ends I have seen are these two. Some of the factors I have heard concern expressed about are two-factor authentication, Active Directory linkage, Kerberos connections, automated storage backups and storage transparency.  Some of these factors stem from the lack of the company's knowledge of MongoDB and others another system to manage if the back-end does not conform with the company's standard. Most of these have been made possible/addressed over time by Alteryx, but I do feel they could be easier to implement.

I think this would definitely make a difference for companies struggling with IT to add on an Alteryx server, if the back-end of Alteryx matches something already been supported by the company. Currently my company is waiting to grant everyone access to Gallery because of the lack of two-factor authentication and MongoDB back-end our Security team will not approve a full enterprise deployment. 

[SeanAdams]

Hey @MattB,

We are in the process of rebuilding our Beta Test env - let me know if there's anything that you'd want our team to test on this new Alteryx Server backend?

We're significantly ramping up our user numbers, so this will be important to help avoid some of the locking issues that have plagued us due to the MongoDB implementation

CC: @ydmuley @revathi @Deeksha @MPistone @Ari_Fuller @Arianna_Fuller @JoshKushner @samnelson  @samN @avinashbonu @Sunder_Sriram @Rahul_Thakur @Rahul_Singh

[MattB]

Hi @SeanAdams,

Thanks for asking. An upgrade from MongoDB v3.0 to MongoDB v3.4 for the Server backend will be coming out very soon. There will be more details when that is released. For the next generation of Server, we are currently considering PostgreSQL as the backend. Thank you Sean for sharing your feedback already. I'll continue to monitor this thread as a data point while we continue to refine and develop on our next generation platform.

@andrewdatakim, two factor authentication is not currently on the roadmap. However, we'll be sure to keep this in mind.

[SeanAdams]

Hey @MattB,

Is there any way that we can intercept this decision on Postgress as a backend?    Postgress is not a well supported database in large enterprise environments like ours, so I fear that this may not actually give us the relief we need.

In point of fact, by putting this on Postgress, we will actually diverge substantially FARTHER away from enterprise level support since we don't have an enterprise platform support for this database so this will mean that we'll have to self-support a Postgress implementation and all the attendant BCP.

MS SQL would be a materially better solution - it's super-well supported in the enterprise; it's already directly tied to Kerberos login; and the ability to set up a fail-over / clustered solution is superb, so BCP is well taken care of.

Please could you add any folk to this discussion internally so that we can intercept this discussion / decision before it goes too far down the path?

Thanks Matt

Sean

CC:  @rijuthav; @jithinmony; @HengHe; @RajK; @ydmuley; @revathi; @Deeksha; @MPistone; @Ari_Fuller; @Arianna_Fuller; @JoshKushner; @samnelson; @samN; @avinashbonu; @Sunder_Sriram; @Rahul_Thakur; @Rahul_Singh

[Arianna_Fuller]

@SeanAdams - Couldn't agree more.

@MattB - Let me know if you need another voice at the table. MS SQL is my preferred as well.