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...
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.
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.
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
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...
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
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.