2 Replies Latest reply on Nov 11, 2015 6:11 AM by Simon Horne

    Published SQL Server Connection - Always open???

    Simon Horne

      Hi

       

      We have a number of SQL Server data sources Published to our Tableau Server.  All but one Data source is  set to refresh on scheduled basis.  The data connections use Windows Authentication and are published to authenticate via "Server run as account"

       

      In this scenario I would expect to see an open connection on my SQL Server only when the workbook refreshes, or at worst whenever any workbooks are accessed by users on the Tableau Server.  However...there is a constant open connection under the Tableau Server account on the SQL Server and the connection has been open since the tableau server was started.

       

      What would cause this connection to stay open all of the time? No users access the workbooks overnight.  Is there a way of forcing it to close ?

       

      we are currently running Tableau Server 8.1.9

       

      Thanks

        • 1. Re: Published SQL Server Connection - Always open???

          Hey Simon,

           

          I moved this to our Server Admin space where you are more likely to receive a helpful response.

           

          On the SQL end, is there any connection timeout setting you could verify to make sure connections are closing? I'm not sure of what setting would change things from the Tableau Server end as it is initiating the connection.

          • 2. Re: Published SQL Server Connection - Always open???
            Simon Horne

            Hi Diego,

             

            Thanks for moving the post (and apologies for the double posting).

             

            The background to our issues is that we have a poorly engineered bulk updated plugin/facility on one of our source CRM applications, upon which Tableau Server has a number of workbooks, that requires effective exclusive use of a database and will not run if there are other connections to the CRM database.  Obviously from a design perspective this application should be using proper row/page locking and enlisting proper atomic transactions rather than effectively trying to logically acquire exclusive use of the database as a catch all.  So the upshot is Tableau Workbooks on Tableau Server that we have against this app create pooled sleeping connections effectively stopping our CRM manager from running this app and we are trying to mitigate for a poorly designed CRM app (obviously if we had the option we would change the CRM app haha :-))

             

            Anyway after speaking to Tableau Support and confirming that killing the processes on SQL server doesn't have any negative impacts on the Tableau server,  I have written regular job on the database server that kills any sleeping connection not active for a certain amount of time made from Tableau Server.  I've had this in place for a few days now.

             

            I thought originally there maybe some setting on the Tableau Server or the Apache webserver to recycle or dispose of any pooled connections after a certain time limit

             

            Thanks

             

            Simon