November 16, 2010 at 6:17 am
Hello all,
The databases on my old server are going to be moved to a new server.
In order to minimize the amount of work, I have set up an alias on this new server, using the old servername.
When I connect on the new local host, I can connect using the old name, but when I connect from a client machine, using the old name, I get a connection error. I think because the network does not know the old name anymore.
Example:
Previous server: Server1
New server: Server2
I have a user interface with a connection string that uses Server1. This user interface is on a client machine.
The new server is called Server2.
I do not want to have to change the connection string, so I set up the following alias, using SQL Server Configuration Manager (I am using SQL 2005):
Alias name: Server1
Port no: 1433
Protocol: TCP/IP
Server: Server2
When I connect on Server2, e.g. by using sqlcmd -SServer1 -E, it works.
When I connect on a client machine using sqlcmd -SServer2 -E, it also works.
When I connect on a client machine using the same sqlcmd statement, I get the following error:
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53]
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred w
shing a connection to the server. When connecting to SQL Server 2005
re may be caused by the fact that under the default settings SQL Ser
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
I also tried the same alias without specifying the portnumber, this also fails.
How can I resolve this situation so that I can connect to the new server, using the old servername.
tia
regards,
Hans
November 16, 2010 at 6:21 am
How can I resolve this situation so that I can connect to the new server, using the old servername.
Speak to you windows server admins, they can create a DNS forwarding alias.
this isn't the best way to do it, but might fix your issue.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
November 16, 2010 at 6:42 am
It lookc that client uses Named Pipes instead of TCP/IP. Make sure that TCP/IP is enabled in client tool (cliconfg) and TCP/IP is the first protocol.
November 16, 2010 at 6:50 am
Hello magasvs,
TCP/IP is enabled, Named Pipes is disabled, but no connection.
I have asked our windows admin for DNS forward alias (following Henrico's advice), see what he's coming up with.
thanks for fast reply!
regards,.
Hans
November 18, 2010 at 6:47 am
You can try to set up alias on client computer using cliconfg.exe utility. Use old server name and new IP address. This may work without DNS changes.
November 19, 2010 at 5:44 am
Yes, there are only two way's to do it.
The alias on a server won't be recognized by clients, so you have to set up a local alias on the clients, or make a DNS change.
November 19, 2010 at 5:56 am
Ok, thanks for the answers.
As there are quite a few clients involved I'll opt for the DNS change.
Thanks again to all for reading/replying this post.
regards,
Hans
November 19, 2010 at 6:34 am
About a year ago we started using DNS aliases for everything. No one will connect to the actual server name nor will they even know it. Each application will have a dns alias for it. The reason for this is for future server moves. Over the years we have found that as app folks come and go they can never seem to find all the connections for an application. When we go to upgrade and move to a new server if an alias is used all ya do is at cutover time is change the dns alias. No ODBCs or connection pools have to change.
November 19, 2010 at 6:46 am
Sorry about the side question.
How are you handling "named instances" in DNS? DNS doesn't accept a "\"?
thanks
November 19, 2010 at 6:52 am
Steve-3_5_7_9 (11/19/2010)
Sorry about the side question.How are you handling "named instances" in DNS? DNS doesn't accept a "\"?
thanks
That is the one issue... named instances. We just started using them recently. What I do is use the alias and the instance name.... DBSRVESSP\INST2 For an upgrade going forward I just figured that we would be forced to keep the same instance name if it gets upgraded. If it is on INST2 it will have to stay on INST2 on a new server.
November 19, 2010 at 7:06 am
Markus (11/19/2010)
Steve-3_5_7_9 (11/19/2010)
Sorry about the side question.How are you handling "named instances" in DNS? DNS doesn't accept a "\"?
thanks
That is the one issue... named instances. We just started using them recently. What I do is use the alias and the instance name.... DBSRVESSP\INST2 For an upgrade going forward I just figured that we would be forced to keep the same instance name if it gets upgraded. If it is on INST2 it will have to stay on INST2 on a new server.
Darn, that's what we're currently doing, too. I was hoping someone would have figured out a DNS solution for this.
November 19, 2010 at 7:12 am
Steve-3_5_7_9 (11/19/2010)
Markus (11/19/2010)
Steve-3_5_7_9 (11/19/2010)
Sorry about the side question.How are you handling "named instances" in DNS? DNS doesn't accept a "\"?
thanks
That is the one issue... named instances. We just started using them recently. What I do is use the alias and the instance name.... DBSRVESSP\INST2 For an upgrade going forward I just figured that we would be forced to keep the same instance name if it gets upgraded. If it is on INST2 it will have to stay on INST2 on a new server.
Darn, that's what we're currently doing, too. I was hoping someone would have figured out a DNS solution for this.
Nope. We came up with the naming standard for SQL DB aliases DBSRV then three letters for the app.. then last letter environment. It becomes a headache because we have to maintain a list and have aliases and create them ahead of time for a new app. But, the hours of pain and agony of trying to find where all connections are coming from for a specific app and a handful of very old apps where they just cannot figure out where the old servername is hardcoded and we are stuck really stinks.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply