1 2 Previous Next 19 Replies Latest reply on Sep 9, 2016 6:32 AM by Toby Erkson

    Edit SQL Server connection to change database name?

    Dan Colbert

      I am working on a project that requires I change both the server name and the database name for all of my data source connection strings.  I would like to avoid having to redeploy all of my data source extracts, as I have over 300 of them.

       

      As a little background, I have created state-specific copies of my database (in a new SQL instance) that are exact DDL copies of the original DB.  I am guaranteed to have the exact same metadata as the current database, so all I need to do is change the connection to point to the new instance & database name.

       

      When I went to change the SQL Server connection through the server web interface, I found I can only change the server name.  There is no place provided to change the database name.

       

      Anyone have any suggestions?

       

      Dan

        • 1. Re: Edit SQL Server connection to change database name?
          Glen Robinson

          Hi Dan

           

          There are a few options here, but none of them are great

           

          1. If you have the original TDS files from  when you published the Data source, you can use Tableau Desktop to edit them, point at new DB, and republish.

           

          2. If you have the TDS files, you can edit them (they are just XML), and make the changes, and publish using TABCMD.

           

          3. If you don't have the TDS Files, you can download them from Tableau Server (as TDSX files), make the changes inTableau Desktop and republish.

           

          You can't directly edit the TDSX file downloaded from Tableau Server, so to get a TDS file out of Tableau Server (that you can edit) you can do the following.

           

          4.

          i. Ensure that you have readonly access to the Repository by using tabadmin dbpass -u readonly <password>

          ii. using a command prompt, change to the C:\Program Files\Tableau\Tableau Server\9.3\pgsql\bin>

          iii. Run the following command to get a list of all Object IDs for Data sources.

          psql -h localhost -p 8060 -U readonly -d workgroup -c "select content,name from repository_data";

          (You might find a better way of filtering just for Data sources, as this will give you all the views aswell.

          iv. For Each Object ID (content field) you run the following

          psql -h localhost -p 8060 -U readonly -d workgroup -c "\lo_export <OID> 'D://tableau//<filename>.tds'";

           

          And Voila you have a TDS files

          v. Edit the TDS, make DB name changes, and publish using TABCMD

          tabcmd Commands

           

          Rinse and repeat for each of the Data Sources

           

          Hope this helps

          Glen

          • 2. Re: Edit SQL Server connection to change database name?
            Dan Colbert

            Thanks, Glen, but that doesn't give me what I'm looking for.  I'm looking for a way to do it without republishing.

             

            With over 300 data sources (and their attached visuals), I'm trying to avoid the massive amount of manual time that will take.  All I need is to point the connection to a new location.  Everything else is the same.

             

            It surprises me that I can't update both the server name and database name through the web interface.

            • 3. Re: Edit SQL Server connection to change database name?
              Glen Robinson

              Hi Dan

              I dont think that it is possible to update without re-publishing. (I agree that it would be great to be able to do so)

              The best that you will get (I think) is to script it.

              ie create a query in postgres to list out all the Datasources, run an export to download the TDS files, update the Database name in the TDS file, and publish using TABCMD.

               

              Regards

              Glen

              • 4. Re: Edit SQL Server connection to change database name?
                Toby Erkson

                Maybe one way to narrow down the list is looking at what items have a tds.  Since public.repository_data.content = public.datasources.repository_data_id you could just look for non-NULL entries in public.datasources.repository_data_id and use those in Glen's example query as part of the automated process*.

                 

                 

                * Which you should share if you end up successfully doing it

                • 5. Re: Edit SQL Server connection to change database name?
                  Dan Colbert

                  Hey Toby,

                   

                  I will be happy to share my final solution.

                   

                  Since about 95% of our data sources have an attached TDS, finding which ones need updating isn't really the challenge. 

                   

                  With that said, your suggestion is helpful.

                   

                  D

                  • 6. Re: Edit SQL Server connection to change database name?
                    Toby Erkson

                    Update:  I've been playing with Glen's suggestion.  I finally got a file to download (.  The file will be saved as a .tds but it's really a .zip file so change the file extension of the downloaded file from .tds to .zip and then you can open the unzipped file in a text editor or via scripting   Or alter Glen's script to:

                    psql -h localhost -p 8060 -U readonly -d workgroup -c "\lo_export <obj_id> 'D://temp//testing.zip'";
                    

                     

                    Next, this fails:

                    Select * from public.repository_data, public.datasources
                    WHERE public.repository_data.content = public.datasources.repository_data_id
                    

                     

                    Looks like the data types are different!

                    vs.

                    • 7. Re: Edit SQL Server connection to change database name?
                      Glen Robinson

                      Hi Toby

                      Thats curious. When I run the export command, the file definitely comes out as a tds file (which can be opened straight away in Tableau Desktop)

                      Am running this on 9.3.5

                      Glen

                      • 8. Re: Edit SQL Server connection to change database name?
                        Toby Erkson

                        Hmm, this probably isn't correct or else needs further definition.  On two table checks it looked like the IDs matched but others I'm finding to not match.  Weird.  So I think I spoke too soon about public.repository_data.content = public.datasources.repository_data_id 

                        • 9. Re: Edit SQL Server connection to change database name?
                          Toby Erkson

                          I ran it against my QA Server which is version 10.0.   ? ?

                           

                          Here's what it looks like if I open it in Notepad++

                           

                          The first two letters, PK, clued me in to that this was likely a zipped file, thus the file extension name change I suggested.

                          • 10. Re: Edit SQL Server connection to change database name?
                            Dan Colbert

                            If you connect to the database with the readonly account and run this:

                             

                            SELECT dc.*

                            FROM datasources ds

                              INNER JOIN data_connections dc ON ds.id = dc.owner_id

                              INNER JOIN projects p ON ds.project_id = p.id

                            WHERE p.name = '<insert project name here>'

                            AND   dc.dbclass = 'sqlserver'

                            ORDER BY ds.repository_url,

                                     ds.name

                             

                            You'll see that each datasource (ds) has a db_class and a db_name.

                            Each data_connection (dc) has a server, dbname and keychain.  Each of those fields contains information about the actual location of the data for the datasource.

                             

                            Those are the places that I need to change and that need to point to the new database locations.

                             

                            Again, the goal is to avoid doing it RBAR (or in this case DataSourceByAgonizingDataSsource)...especially because nothing is changing except the location of the data.

                            1 of 1 people found this helpful
                            • 11. Re: Edit SQL Server connection to change database name?
                              Dan Colbert

                              I'm going to try something naughty and I'll let you know if it is successful.

                               

                              I'm thankful for a restored backup on an isolated machine...

                               

                              D

                              • 12. Re: Edit SQL Server connection to change database name?
                                Toby Erkson

                                Glen, when I open it in Desktop v.10:

                                • 13. Re: Edit SQL Server connection to change database name?
                                  Toby Erkson

                                  Okay, I got it working:

                                  public.datasources.repository_data_id = public.repository_data.id

                                  public.repository_data.content is then the <OID> to use in Glen's command.

                                   

                                  So here's the modified SQL to get the repository_data.content needed for getting the OID (returned as the first column):

                                  SELECT rd.content AS "OID", dc.*
                                  FROM datasources ds
                                    INNER JOIN data_connections dc ON ds.id = dc.owner_id
                                    INNER JOIN projects p ON ds.project_id = p.id
                                    INNER JOIN repository_data rd ON ds.repository_data_id = rd.id
                                  WHERE p.name = 'Default'
                                  AND   dc.dbclass = 'sqlserver'
                                  ORDER BY ds.repository_url, ds.name
                                  

                                   

                                  < edit > FYI for anyone coming into the conversation, change the project name in line 6 above to match which ever project you wish to pull from or just remove that piece of the WHERE clause if you want all .tds files.

                                  • 14. Re: Edit SQL Server connection to change database name?
                                    Dan Colbert

                                    I have successfully performed what will definitely be an unsupported hack, but which has resulted in exactly what I need.

                                     

                                    A refresher of the background:

                                    1. We have around 300 data sources, all connected to SQL Server, each connected to state-specific views in a single database.  They are all also connected to extracted data.
                                    2. We are splitting our single database into a new SQL instance, one database per state.  We're doing this to increase performance and decrease development bottlenecks. (Don't get me started on table partitions or snapshots, we can't afford SQL Enterprise.)
                                    3. Every state database is the exact same structure as the original, single database (which means we have no metadata changes to worry about).  Each state database contains only that state's data.
                                    4. We need every data source in Tableau server to point to the new instance and database name

                                     

                                     

                                    Things we don't want:

                                    1. We don't want the data_repository to change, because that data is the same in both the old and the new databases.  I only want to update the connection for when the next extract update happens.
                                    2. We don't want to re-extract and republish every datasource, because that would literally take us days, because of the volume of data we're handling.

                                     

                                    I directly updated the references to the connection in the datasources and data_connections tables in the workgroup database. I then ran an extract refresh from the server web UI, and it successfully pulled the data from the new location.

                                    I'm hesitant to put my actual solution here, but am happy to share with those who are interested.Toby Erkson

                                     

                                    D

                                    2 of 2 people found this helpful
                                    1 2 Previous Next