Setting up a linked server to a named instance

  • Hello,

    I'm having trouble to redirect an existing databaselink to a new server.

    We're moving a default instance (D, SQLSERVER 2000,SP 4 ) to a named instance on a different server (X\Z, SQLSERVER 2008)

    There is one server B having a linked server set up with D, with the name D.

    The new situation should be

    B has a linked server set up with X\Z name D

    so that

    select 1 from D.mydb.mytable (pointing to D\default instance)

    still works in the "to be" situation

    select 1 from D.mydb.mytable (actually pointing to X\Z)

    The tryout is this

    Linked server name: C

    Productname : SQL Server

    Datasource : X

    Provider string: Provider=sqloledb;Data Source=X\Z

    Any suggestions?

  • I'm sorry, I don't understand what problem you are facing. Can you explain it in other words?

    -- Gianluca Sartori

  • I don't think if you change server name it will cause any issue, Only thing is you should script the linked server connections and change the server name (named instance server name) in the script after upgrade and make sure all permissions and logins exist for linked server connections in new instance.

    EnjoY!
  • In other words.

    There is an application MyAPP thats needs info from the database "operation" on server SRVOPS (2000), default instance SRVOPS

    It also needs information from database "EXTRA" located on server SRVEXTRA, default instance SRVEXTRA (2000)

    Currently the application connects to server SRVOPS and queries the "extra" information through the linked server "SRVEXTRA" on SRVOPS, which is actually the default instance on SRVEXTRA.

    SELECT mycolumn from SRVEXTRA.EXTRA.dbo.mytable originating from SRVOPS

    Now the server SRVEXTRA will be replaced by a new server BRANDNEW (2008) containing multiple instances where instance "MYNEWSRVEXTRA" is the replacement for SRVEXTRA.

    Goal:

    SELECT mycolumn from SRVEXTRA.EXTRA.dbo.mytable originating from SRVOPS

    MyAPP isn't aware of this and still connects to SRVOPS and queries its information through the linked server "SRVEXTRA", which is actually the instance BRANDNEW\MYNEWSRVEXTRA

    How do I configure the linked server connection "SRVEXTRA" on SRVOPS to do so?

    Option 1: Define an alias in client network configuration utility on server SRVOPS to redirect SRVEXTRA to BRANDNEW, instance MYNEWSRVEXTRA (possible?)

    Option 2: Alias information in the datasource field of the linked server "SRVEXTRA" (is SRVEXTRA\MYNEWSRVEXTRA possible?) using the oledb provider for sqlserver

    Option 3: by GTR script out linked server configuration "SRVEXTRA" (have to try out that).

    Other options?

    Logins were mostly sqllogins and are already transfered to BRANDNEW\MYNEWSRVEXTRA.

  • Got it working using

    sp_addlinkedserver @server = 'SRVEXTRA'

    , @srvproduct = 'SQLSERVER'

    , @datasrc = 'BRANDNEW\SRVEXTRA'

    ,@provider='SQLOLEDB'

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply