SQLTeam.com | Weblogs | Forums

Mask a named server instance


#1

Hi everybody,
this is my first post on this forum, so I apologize if I choose the wrong category...

some year ago, as a total newbie, I installed a server with a named instance. At that time I used a physical server, and moreover I probably thought that SQL 2005 was the last version name for SQL server, so I named my instance with something like "hpserver\sql2005"...

Today I have a virtualized system, and I need to migrate SQL2005 to SQL 2014 (or 2016) and I would like to "rename" that instance.

The matter is that in last years more and more programs both with ODBC and ADO.NET connect to my server, and it's impossible to change all this programs in short time to connect to a new server.

My answer so is: it is possible to install a new server, named for example "ALPHA", and find some way to "redirect" all requests for older server to newest? I made some test and my matter is not (obviously) computer name, that I can mask with DNS, but instance name...

Hoping someone can help to correct my youth errors...

thanks

Mauro


#2

I'm afraid that, since you used a named instance, you will need to find and change all those connections. The other option might be to write a proxy server to translate the connection names, but I think that would be more work to develop and support than fixing the connections.


#3

A while ago, I had to do something like this, and I did some preliminary investigation. But then requirements changed and I did not follow up on it. In my case, it was not a named instance, and the conclusion I came to was that we could add a CNAME that would point to the new server which would accomplish what I wanted to accomplish.

You might want to read through these articles to see if you can make that work.

https://support.microsoft.com/en-us/kb/168322


#4

You can work around the instance name issue by adding a SQL Server alias, not a DNS alias, on each of the servers/desktops where the connection strings exist. You can deploy this easily too via a reg file.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo
String value
Name: hpserver\sql2005 (this is just an alias and can be anything)
Data: actualhostname,portnumber or actualhostname\newinstancename


#5

That's cool, Tara! Never knew that before!!


#6

I made some test, it seems working great for odbc connections, but not for ADO connections. In that case i think the only way is changing connection string...

thanks for Your help.
Mauro


#7

What error do you get for ADO connections? The solution should work for all SQL Server connections. I've never had a problem with ADO connections.


#8

Oh it just occurred to me that you might have a 32-bit vs 64-bit issue. Put the registry entries in this key too if it's a 64-bit machine:

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo


#9

Some information on my environment:
Windows 7x64
Borland Delphi 7 with SDAC components of DevArt
SQL Server 2005
I configured the alias using cliconfg.exe, obtaining this in reg:
Name: myalias
Data: DBMSSOCN,HPSERVER\GESTSQL05
I tryed deleting DBMSSOCN, without results
I tryed via TCP/IP or named pipes.

Using TCP/IP or named pipes I obtain:
"provider named pipe: impossible to open a connection to SQL Server [53]"

On server both TCP/IP and named pipes are enabled.

Using a generic ADO component I obtain: "(...) SQL Server does not exists (...)"

I tryed reboot after creating alias,
I tryed inserting an entry in hosts file

thanks for Your help.
Mauro


#10

cliconfg.exe is fine to use to create 64-bit entries on a 64-bit machine, but you need to use regedit.exe for 32-bit entries on a 64-bit machine. Please see my last reply for the registry key.

You can not specify DBMSSOCN in cliconfg.exe. That is for regedit.exe. Using cliconfg.exe, you would just select TCP/IP. TCP/IP == DBMSSOCN. If you go to regedit.exe and look at the entries in first registry location that I posted, you'll see your entries that you did in cliconfg.exe. cliconfg.exe is just a GUI for the SQL aliases, they'll actually reside in the registry.

If you have a 64-bit machine, please use regedit.exe and enter the 32-bit entry in the Wow6432Node location posted above.


#11

To make it simpler on x64 systems - when you setup/create the alias you can use the following:

C:\Windows\SysWOW64\cliconfg.exe

This will bring up the client configuration so you can create the x86 version and that will be saved in the appropriate registry key.


#12

Cool! I didn't know that.


#13

Ok, thank You all, now all works fine!

Your help will let me schedule the change of server with less stress.

Mauro


#14

Fabulous!!