locked
Remote connection to a SQL Server instance using an alias. RRS feed

  • Question

  • Hello, we have a question about alias in SQL.

    We have a server with this configuration:

    Server Name: HostName1 (IP = 10.10.10.10)
    Instance #1: InstanceName1\InstanceName1 (IP = 10.10.10.11. SQLPort = 1433)
    Instance #2: InstanceName2\InstanceName2 (IP = 10.10.10.12. SQLPort = 1435)

    I want to use another instance name as TEST\TEST to connect to InstanceName2\InstanceName2 from a remote client.

    How can I create an Alias to make this configuration?

    I'm checking with DNS and SQL alias but I can see some problems:

    - Created a DNS alias (C Name) named "TEST" that points to InstanceName2.
    - Created a SQL alias with next configuration:
       1) Alias: TEST\TEST
       2) Port: 1435
       3) HostName: InstanceName2
    - After that, I can connect with an ODBC to TEST\TEST locally (creating the ODBC in HostName1).
    - But I can not connect with an ODBC to TEST\TEST remotelly (creating the ODBC on other server or client).

    Can you help me with this test?

    thank you in advance.

    Best regards.

    Wednesday, January 20, 2016 10:45 AM

Answers

All replies

  • Hello,

    a "SQL Alias" is a client Setting, not a central server setting like a DNS entry; you have to create the alias on every client.

    SQL Server Alias Configuration on Remote Machines


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by XavierCA Wednesday, January 20, 2016 2:52 PM
    Wednesday, January 20, 2016 10:55 AM
    Answerer
  • XavierCA,

    Try to look at the cliconfg tool.

    You can create alias with it.

    Read the links below and see if it helps you:

    http://sqlandme.com/2011/05/05/create-sql-server-alias-cliconfg-exe/

    http://sqlblog.com/blogs/tibor_karaszi/archive/2009/09/08/sql-client-config-32-and-64-bit.aspx


    Felipe Lauffer MCSA: SQL Server | MCP

    Wednesday, January 20, 2016 10:56 AM
  • Ok, I can understand. Then, we need to configure the alias on all of our clients too. Using  cliconfg.exe or registry keys.

    I checked this solution and works fine, but... we need to change 35000 clients. Is not there another method that does not need to change client configuration?  ^_^

    Thank you in advance.

    Best regards.

    Wednesday, January 20, 2016 12:12 PM
  • XavierCA,

    With the named instance there is no option besides the cliconfg tool.

    Maybe you can try install a default instance of SQL Server if that is an option and then configure the DNS with the CNAME registry.

    Or maybe you can create a GPO to resolve the manual modification of 35000 clients.


    Felipe Lauffer MCSA: SQL Server | MCP

    Wednesday, January 20, 2016 12:26 PM
  • I checked this solution and works fine, but... we need to change 35000 clients. Is not there another method that does not need to change client configuration?  ^_^

    The only way to connect using an instance name that differs from the actual one is with the suggested client configuration change. If the clients can omit the instance name, you could change InstanceName2 to listen on 1433 and create a DNS alias TEST pointing to 10.10.10.12.  Clients could then connect to InstanceName2 using "TEST" alone.  This assumes InstanceName1 does not listen on that interface too.

    If this isn't acceptable, please share the reason "TEST\TEST" must be used.  Perhaps there is another way to meet the business objective without this technical requirement.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    • Proposed as answer by Ice Fan Sunday, January 24, 2016 9:33 AM
    Wednesday, January 20, 2016 1:14 PM
    Answerer
  • Yes, we are planning a migration from SQL2005 to SQL2012 and we have a SQL2005 server with two instances related. We have a new SQL2012 server with another server and instance names:

    We are installed a new SQL-Server:

    Server Name: HostName2
    Instance #1: InstanceName3\InstanceName3 (port 1433)
    Instance #2: InstanceName4\InstanceName4 (port 1435)


    After migrate all DBs from InstanceName2\InstanceName2 to InstanceName4\InstanceName4 we want to make something for a correct connection from all these 35000 clients, but we don't want to change these connection strings.

    My question is about how can we do a "transparent" migration, without changing string connections, using alias. My first idea:

    - Migrate DBs from InstanceName2\InstanceName2 (Hostname1) to InstanceName4\InstanceName4 (Hostname2).
    - Delete InstanceName2 DNS (A) record.
    - Create a DNS alias (C Name) named InstanceName2 that points to InstanceName4.
    - Create a InstanceName2\InstanceName2 SQL Alias on InstanceName4.
    - Cross fingers.

    But after your answer, I can see that we need to "touch" the client side.

    Wednesday, January 20, 2016 3:06 PM