SQL Server DB change causing workflows issues on server

[SGolnik]

I am one of the designer SMEs, but do not know much about server. We have a new server admin who knows nothing about designer and is still learning about server himself. I'm hoping to be able to help him troubleshoot what I suspect is a server issue.

Problem: One of the databases we report from is a MS SQL Server DB with AD credentials. They upgraded the DB through regular maintenance a few months ago changing the name. We updated our workflows with the new DB name in the connection and they run as expected. Our admin created a new connection on the server as well. The DSNs all match accordingly. Since this change was made reports that use to run in minutes on the server now take hours to run causing a backlog and queuing other workflows for hours. Some workflows are failing altogether with no real error message related to the workflow. I pull some of the long running offenders down and run them on my computer and again they run in minutes.

Questions: Are there multiple ways for setting up MS SQL Server connections on the Alteryx server? Is one better than the other? If the incorrect set up is chosen, can it impact how the workflow runs?

I will be meeting with the server admin this morning to review the connection and talk about other possible issues. There is only so much optimization of workflows that can be done!

TIA!

[joshuaburkhow · Accepted solution]

Hi @SGolnik 

Welcome to my world 😉 Many workflows to optimize!

A couple things for you:

* Don't be afraid of getting Alteryx support (support@alteryx.com) involved as they can ensure you get stuff fixed right away. 

* Highly recommend using InDB tools whenever possible. The performance gains are extraordinary

* Ensure your DB drivers on the server are correct/up to date

* You can use "DSN-less" connections which might be easier https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-format-common-ODBC-DSN-less-...

* For testing, get rid of any thing in the workflow except the input tool so that you know any performance issues are not caused by other tools. 

Hope this helps! 

[SGolnik]

Thanks for your reply. I'm well embedded in that world of many workflows to optimize!! (I had to start holding office hours for it because it was impacting my actual daily work LOL)😂

We have workflows touching dozens of databases, but the only workflows having issues are the ones hitting this particular SQL Server DB and this only started with the DB update a couple of months ago. These particular workflows have been pulled down and are optimized and run amazingly fast on our machines. When running on the server, even when nothing else is running, their run time is insane. I've never seen anything like this in all of the years we have had Alteryx. I'm going to work through the help documentation in my meeting with our admin this morning and if we don't get anywhere we'll reach out to Alteryx support.

[SGolnik]

There server admin was using ODBC Driver 17 for SQL Server and everyone using Designer is using SQL Server drivers in the connection.