Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2004
    Posts
    7

    Unanswered: moving ODBC connections between servers

    Hello all,

    I was wondering if there is a way to move ODBC connections between SQL servers? We are replacing our current server with newer hardware, and I have pretty much everything figured out except this.

    Thanks for any help and/or hints.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You can either use Client Network Utility on the client to create an alias, or modify client's registry to forcew an alias, or create an alias in AD for the new server to be known under the old name, or rename the new server to the old name. Take a pick
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Mar 2004
    Posts
    7
    I am afraid you lost me.

    I am building out an entirely new server to move the databases to, but I also need to move over the ODBC connections listed in the control panel applet.

  4. #4
    Join Date
    Mar 2004
    Posts
    7
    Small bump...

    Does anyone else have any ideas?

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is one of those kind of things that cartographers of olde used to mark "Here be dragons"... They didn't know exactly what was there, but they were suspicious that it wasn't good!

    The problem is that a machine's ODBC connections depend on drivers, which can depend on... You get the idea. The ODBC connections are pretty much the top layer in a house of cards.

    The short answer is that there isn't a really safe and reliable way to move the ODBC connections. It can be done, but unless you know EXACTLY how both of the machines were built, it is probably just a receipe for a disaster. Your best bet is to recreate the ODBC connections manually on the new machine.

    -PatP

  6. #6
    Join Date
    Mar 2004
    Posts
    7
    Quote Originally Posted by Pat Phelan
    This is one of those kind of things that cartographers of olde used to mark "Here be dragons"... They didn't know exactly what was there, but they were suspicious that it wasn't good!

    The problem is that a machine's ODBC connections depend on drivers, which can depend on... You get the idea. The ODBC connections are pretty much the top layer in a house of cards.

    The short answer is that there isn't a really safe and reliable way to move the ODBC connections. It can be done, but unless you know EXACTLY how both of the machines were built, it is probably just a receipe for a disaster. Your best bet is to recreate the ODBC connections manually on the new machine.

    -PatP
    Bah! Humbug! Humbug I say!

    Well, I was hoping to be lazy, but I guess it is not in the cards for me.

    Thanks for the input folks.

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    My initial understanding of your question was, that while moving the server to new HW, you wanted clients NOT to see the difference while retaining their ODBC connections. Now I see that you want to move ODBC Data Sources from your old server to the new one...Doh...You need to go to REGEDIT (be very carefull...oh well, you probably already know) and locate HKEY_LOCAL_MACHINE\SOFTWARE\ODBC. At this point you can either save the entire hive (highlight ODBC and use "Export Registry File..." from Registry menu) or each individual data source (they'll appear in the tree on the left pane as folders) using the same technique. Once the file(-s) is/are exported, you can copy it/them to the new machine and double-click on it/them.

    But, as Pat said, watch out for dragons. I'd backup the registry on the target before doing all this, and make sure I can restore successfully. Better yet, test it on a throw-away PC (server or workstation, doesn't matter) and see if you get what you wanted. I've done it several times, but it's your turn now, not mine
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  8. #8
    Join Date
    Mar 2004
    Posts
    7
    Quote Originally Posted by rdjabarov
    My initial understanding of your question was, that while moving the server to new HW, you wanted clients NOT to see the difference while retaining their ODBC connections. Now I see that you want to move ODBC Data Sources from your old server to the new one...Doh...You need to go to REGEDIT (be very carefull...oh well, you probably already know) and locate HKEY_LOCAL_MACHINE\SOFTWARE\ODBC. At this point you can either save the entire hive (highlight ODBC and use "Export Registry File..." from Registry menu) or each individual data source (they'll appear in the tree on the left pane as folders) using the same technique. Once the file(-s) is/are exported, you can copy it/them to the new machine and double-click on it/them.

    But, as Pat said, watch out for dragons. I'd backup the registry on the target before doing all this, and make sure I can restore successfully. Better yet, test it on a throw-away PC (server or workstation, doesn't matter) and see if you get what you wanted. I've done it several times, but it's your turn now, not mine
    Aye, I don't mind using my local workstation for the import test to avoid complications in the future.

    I did, in fact, see those listings in the registry, my main worry about that was if it did, or did not, transfer security with the connections.

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It transfers EVERYTHING that contains in the registry hive that corresponds to each data source.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The major problem with this is that some registry settings aren't recognized by earlier driver versions. This copys one part of the registry from one machine to another, without regard to whether the two machines are at the same release/patch levels.

    It certainly can be done, but I'd only do this with two servers that I'd built up from bare hardware (so that I knew exactly what was on the machines in the way of ODBC drivers and other files that they rely upon), or if I could run the new one in parallel with the old one for at least 90 days to be sure I hadn't missed some small but crucial detail. I don't think that I'd be willing to risk doing this on a production machine, considering how little time it takes to build the ODBC connections and then I could safely forget about them!

    -PatP

  11. #11
    Join Date
    Mar 2004
    Posts
    7
    Well, I will say that the OS, SQL and all the patch levels will be the same - corporate spec and all that.... The main difference will be the hardware. They are both Compaq(HP) boxes, just one happens to be a nice 8 way machine with 10 gigs of RAM.

    :drool:

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •