Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Other SQL Server 2012 Topics
 SQL Server Alias for Named Instances
 Reply to Topic
 Printer Friendly
Author  Topic Next Topic  

fossm256
Starting Member

USA
3 Posts

Posted - 04/28/2015 :  23:04:35  Show Profile  Reply with Quote
I need to setup an Alias for a Name Instance on
SQL server 2012 sp2 CU2. I tried to set it up with SQL System configurator for both 32 bit and 64bit native client alias.
on the server where the name instance lives. For both Named Pipes and TCP/IP

I go try to connect to it from another SQL server 2012 management
studios. I get the error for named pipes

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

I know allow remote connection is set to 1. I am running each instance using a different port. SQL Server Browser is running.

I went through all the requirements from this web page
https://technet.microsoft.com/en-us/library/ms190445(v=sql.120).aspx

What am I missing. do I have to go to every machine that uses this named instance and setup the alias for the name instance. I am checking it by trying to connect to using SSMS on another server
using the alias.

I have setup link servers before with no problems, but I can not get it to work for named instances alias. I am trying to consolidate 3 servers to 1 server with a named instance for each of the servers.

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 04/29/2015 :  12:06:30  Show Profile  Visit tkizer's Homepage  Reply with Quote
The alias goes on the machine where you are getting the error, not on the server itself unless the alias is needed there for other reasons (there are good reasons).

Or fix why the browser service can't be reached.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

fossm256
Starting Member

USA
3 Posts

Posted - 04/29/2015 :  14:20:19  Show Profile  Reply with Quote
So you are saying every computer that accessed server a.instance
I need to add an alias for the new server.b
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 04/29/2015 :  14:25:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Every computer that has an issue, yes. You can easily mass-deploy it through a Windows login script or whatever they are called. Chat with your server admin.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

fossm256
Starting Member

USA
3 Posts

Posted - 05/06/2015 :  10:49:02  Show Profile  Reply with Quote
I am going to try another approach.
I am going to assign an IP address to each instance using SQL Server configuration manager.
Then in DNS, assign an alias for each IP address.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
38200 Posts

Posted - 05/06/2015 :  15:36:07  Show Profile  Visit tkizer's Homepage  Reply with Quote
But what matters is the port that the SQL instance is listening on. DNS can't help with that. You need an alias on each client machine that has this issue or figure out why the clients can't communicate with the SQL Browser service on the database server.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000