Art of BI: Moving ODBC DSNs from source machine to target – more fun with Windows
Christian Screen | | August 9, 2012
Every now and then comes the need to work on a Windows Server. With OBIEE this is some times a gift and a curse. Either way, if using multiple data source systems for connectivity you are most likely using the ODBC Administration tool. And what happens when you have a slue of DSN’s configured and you need to move them from your “Development” machine to the subsequent envionrment, let’s say “QA/TEST”?
The easiest manual way to do this is to copy the regiestry files from one machine to another. I won’t reinvent the wheel here but just point you in the right direction as well as to create a reference which I’ve used more than once at this point.
The basics are explained, here:
The main caveat here is that because a unique BI Server DSN ODBC entry (ex: coreapplication_OHSO30998023A, etc.) gets created with each BI Server installation, you cannot just copy the entire directory and expect things to work on the target server, without a bit of extra TLC.
To work around this you can do two things:
– Export the entire ODBC.INI folder and import it to the target system per the instructions from John Savill in the link above. Once that is accomplished, reference the backup ODBC.INI.BAK (or whatever you named it) and edit the registry string value for the coreapplication_OHS<unique_string> odbc entry as you have just copied the one from your source environment and they won’t be the same. You’ll also need to edit the key/value pairs inside of that folder by changing the source’s server name and port information to that of the target’s.
– The other way to go about this is to export each individual DSN folder to its own REG file. Only copy the entries that don’t yet exist in the target. So by cherry picking the entries you can avoid select the source environments coreapplication_OHS<unique_string> ODBC connection. You will also need to export the values inside of the “ODBC Data Sources” folder. Import each of these REG files into the target machine. Once you have imported the ODBC.INI files then import the “ODBC Data Source” file. You will then see two entries prefixed with “coreapplication”. Reference the backup ODBC.INI folder and confirm which one should actual remain; delete the other reference which comes from the source system.
You will still need to verify that each of the connections works properly. Some ODBC connections such as those for SQL Server, etc. that are not using the default ports may revert to the defaults which will require you to manually update its configuration once moved to the target machine. This is a good practice to verify after the migration anyway.
Good luck with the Windows OS joy ride.
EPM applications help measure the business performance. This post will help you choose the best EPM solutions for your organization’s needs and objectives.
It’s 2015 and you can now establish totally respectable MS SQL DBA credibility just by mentioning you have been in the game since SQL Server version 9. You may even get the same gasps of shock from some colleagues that used to be reserved for the version 6 veterans.