5 Replies Latest reply on Jul 9, 2018 12:23 PM by Joseph LaForest

    TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly

    Joseph LaForest

      I've upgraded to Tableau Server 2018.  In doing that, I moved my TDC file to the new bin directory - restarted the server, and I'm testing my connections to data sources.  My TDC file is very simple - Connecting the Microsoft SQL Server 2012 with MultiSubnetFailover and ApplicationIntent ReadOnly:

       

      <?xml version='1.0' encoding='utf-8' ?>

      <connection-customization class='sqlserver' enabled='true' version='8.10'>

      <vendor name='sqlserver'/>

      <driver name='sqlserver'/>

      <customizations>

      <customization name='MultisubnetFailover' value='ApplicationIntent=ReadOnly;MultiSubnetFailover=True;Integrated Security=true;' />

      </customizations>

      </connection-customization>

       

      I'm pretty sure that Tableau Server is not paying it any attention.

       

      com.tableausoftware.nativeapi.dll.DataSourceException: [Microsoft][SQL Server Native Client 11.0]Unable to complete login process due to delay in opening server connection Unable to connect to the server "MYSERVERHERE". Check that the server is running and that you have access privileges to the requested database.

       

      I usually see that when something is ignoring the multisubnetfailover=true. 

       

      I'm looking for

      • Where I can verify that it is not looking at the TDC?
      • How do I make it look at the TDC?
      • Is there some other bleedingly obvious way that I should be doing this?

       

      Thanks!

        • 1. Re: TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly
          Patrick A Van Der Hyde

          Hello Joseph,

           

          Did you upgrade from a 32 bit version to a 62 bit version? 

           

          Check out the KB article here: - Using a .tdc File with Tableau Server | Tableau Software

           

          And make sure you are using the 64 bit path of C:\Program Files\Tableau\Tableau Server\[version]\bin

           

          Patrick

          • 2. Re: TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly
            Joseph LaForest

            Nope.  I've been on 64 bit since first setting up the server and that is the path that I have the file in.

            • 3. Re: TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly
              Kelly Figueroa

              Couple thoughts:

               

              1. Is the TDC on each machine running vizqlserver or backgrounder?

               

              2. You have verified that Tableau Desktop 2018.1.X is using the TDC file and it is working correctly?

               

              3. THe version in the TDC file is that version and higher will be used 

               

              4. The vizql session log will show if a tdc is detected. The viqlserver logs folder have the tabprotosrv files in them.  In Desktop, it will be in the tabprotosrv log. 

              • 4. Re: TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly
                Joseph LaForest
                1. It is one server for all - so vizqlserver and backgrounder are the same.
                2. Yes.  On desktop it works.  I tested by looking at logs and it finds the TDC.  If I remove the file, I get connection issues
                3. The version is currently 8.10.  SQL server version is 12.0.5207.0
                4. I uploaded logs and am waiting to hear.  On desktop i was able to find it and confirm.  I took another stab at hunting through logs

                 

                Took a bit to find what I think is hteh right one

                C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs\vizqlserver_0-1_2018_06_22_00_00_00.txt

                 

                I found 2 lines indicating that it found the TDC (both of them since I tried using all the possible places that one could shove the TDC)

                 

                {"ts":"2018-06-22T18:28:35.437","pid":4940,"tid":"2ab4","sev":"info","req":"-","sess":"-","site":"{EC88E816-54FA-44DD-B158-84A8D3C98C44}","user":"-","k":"msg","v":"Found TDC 'C:\\Program Files\\Tableau\\Tableau Server\\2018.1\\bin\\ServerTDC.tdc'."}

                {"ts":"2018-06-22T18:28:35.438","pid":4940,"tid":"2ab4","sev":"info","req":"-","sess":"-","site":"{EC88E816-54FA-44DD-B158-84A8D3C98C44}","user":"-","k":"msg","v":"Found TDC 'C:\\ProgramData\\Tableau\\Tableau Server\\data\\tabsvc\\vizqlserver\\Datasources\\ServerTDC.tdc'."}

                 

                Given that - I'm guessing that either the default driver for Microsoft SQL Server does not support MultiSubnetFailover and ApplicationIntent=ReadOnly or some other mischief is afoot.

                 

                Thoughts?

                • 5. Re: TDC usage on Tableau Server 2018 - MultiSubnetFailover and ApplicationIntent ReadOnly
                  Joseph LaForest

                  Thanks to Robert Calvert with Tableau Support for getting this resolved.  Here is the final TDC file:

                   

                  <?xml version='1.0' encoding='utf-8' ?>

                   

                  <connection-customization class='sqlserver' enabled='true' version='2018.1'>

                  <vendor name='sqlserver'/>

                  <driver name='sqlserver'/>

                  <customizations>

                  <customization name='odbc-connect-string-extras' value='ApplicationIntent=READONLY;MultiSubnetFailover=Yes' />

                  </customizations>

                  </connection-customization>

                   

                  Things we learned:

                  • customization name='odbc-connect-string-extras' is correct.  customization name='MultisubnetFailover' will only bring you heartache and frustration.
                  • Microsoft SQL Server can be particular about MultiSubnetFailover=Yes vs MultiSubnetFailover=True.  Both worked for Robert.  Only MultiSubnetFailover=Yes worked for me.
                  • Version updated to 2018.1.  I'll admit - I originally did not know if this was SQL version or Tableau version - it is talking about a data source and I never considered sharing TDCs between Tableau instances.  Version does indeed refer to the Tableau version.
                  • After you make changes to the TDC file, close and reopen Tableau Desktop or Restart the server.  Everybody knows that, but good reminder for new folks.

                   

                  I'm using Native SQL Driver 11.0.

                   

                  Extra tidbit - Testing ApplicationIntent=READONLY

                  • Using SQL Server Management Studio, connect to each node and open a query window for each.
                  • Open at Always On High Availability -> Availability Groups.  Note which of the machines is the current primary
                  • Run sp_who @loginame = 'TheServiceAccountTableauIsUsing' in each query window while Tableau is actively pulling data.
                  • If you see the connection to the Availability Replica and that is not currently the primary, congrats... it's working.  If not, give Configure Read-Only Routing for an Availability Group (SQL Server) | Microsoft Docs a read.