Sql-server – SQL Server 2012 – Create Alias from default instance (i.e. ‘.’) to named instance of SQL 2012

aliassql serversql-server-2012

Scenario is: I have SQL 2012 running as a named instance 'SQL2012'. I can connect to this via SSMS using '.\SQL2012'

Working on a new project, I now want this named instance to be accessible as '.'

To complicate matters, I also have SQL 2014 installed as the default instance, don't need this simultaneously so we can assume this instance is stopped.

I have tried various combinations of alias, the most obvious being:

Sample Alias Combination

I've also tried setting the port number to the Dynamic Port found in the network configuration, but nothing I've tried so far seems to work.

Is this supported, and if so, what am I missing?

Best Answer

Turns out, it is possible, and after some more experimentation I figured this out. I think the missing elements were:

  • Using .\SQL2012 as the server name instead of MACHINENAME\SQL2012
  • Ensuring SQL Server Browser service is running.

Full Steps

  • Ensure SQL Server Browser service is started, as well as your named SQL2012 instance.
  • Open SQL Server Configuration Manager on your machine
  • Expand the SQL Server Network Configuration node, and double click Protocols for
  • Ensure TCP/IP is set to Enabled, if it's not, right-click and choose Enable
  • Double click TCP/IP
    • Ensure Listen All is set to Yes
    • Select the IP Addresses tab, and scroll to the bottom. Make a note of the value in TCP Dynamic Ports.
    • Press OK
  • Expand the SQL Native Client 11.0 Configuration node and double click Aliases
    • Right click in white space and select 'New Alias'
    • In Alias Name, enter '.'
    • In Port No, enter the value you noted earlier from TCP Dynamic Ports.
    • Leave Protocol as TCP/IP
    • In Server, enter .\<yourinstancename>
    • Press OK
  • Repeat the steps for SQL Native Client 11.0 Configuration for SQL Native Client 11.0 Configuration (32 bit)
  • Test the connection by using SSMS to attempt to connect to '.'
Related Question