March 17, 2016 at 1:33 pm
See also: http://dba.stackexchange.com/questions/132313/sql-server-2008r2-alias-not-working
I have a SQL 2008R2 cluster (on Win 2008R2 OS), called "SQL-OLD". Nodes are like "SQL-OLD1" & "SQL-OLD2". We want to "migrate" it to new hardware, where there are LOT of existing applications & services that know it by that name, "SQL-OLD". SQL Service is running under a domain account named "svc_sqlold". Its IP address is, say, 11.11.11.20.
The new server is called "SQL-NEW" (nodes "SQL-NEW1" and "SQL-NEW2"). Also running SQL 2008R2 (but this time on Win 2012R2 OS). SQL Service is running under a domain account that IS trusted for Kerberos delegation, named "svc_sqlnew". It's IP address is, say, 11.11.11.30.
To help "test & QA" the apps for the migration, we have a tester workstation, say "TWS1". It connects to the servers just fine via SSMS or any application that specifically uses the registered names "SQL-OLD" or "SQL-NEW". Connections also work when using IP addresses.
Now, here's where it gets interesting. Because so many of the apps have the name "SQL-OLD" hard-coded into them, we're trying to make it easier by using a SQL Server "Alias", which you can set up via SQL Configuration Manager under the "Native Client Configuration" section. So first we edit the Hosts file on TWS1 to say "SQL-OLD -> 11.11.11.30" (the IP of SQL-NEW). Then we configure the alias on the server SQL-NEW, using the Config-Mgr: alias name "SQL-OLD", TCP/IP protocol, port 1433, destination name "SQL-NEW".
With me so far? So yes, the alias of the NEW instance is the name of the OLD instance. We used the Hosts file trick to make sure that ONLY the QA machine, TWS1, actually "interprets" SQL-OLD to mean, really, SQL-NEW (IP address). At one point we almost thought "well why is the alias even necessary?" but then I tried it without that (i.e. with just the hosts file trick) and it still didn't work. Specifically, the error from SSMS in the connection attempt is the infamous "Cannot generate SSPI context" error. HOWEVER, I can ping it! Ping "SQL-OLD" resolves to 11.11.11.30 (SQL-NEW).
But wait there's more! If we use a "unique" alias, i.e. something that's not the real name of a real server somewhere else on the network, IT WORKS FINE. So if I use the alias "SQL-OLDGUY" for SQL-NEW, and then try to connect from TWS1 using SSMS, to "SQL-OLDGUY", the connection is successful. That even works without having the SQL-side alias set up at all -- just pure Hosts file redirection of "SQL-OLDGUY" to IP 11.11.11.30 -- no problem.
Furthermore, we have the SAME TYPE of setup with some SQL 2014 clusters using Aliases *identical* to the names of their old counterparts, and they work FINE TOO. So like, old box is running SQL 2005, name is "SQL-OLDBOY05". Its replacement is "SQL-NEWBOY14", but we give it an alias "SQL-OLDBOY05", and set up the Hosts file to point at "SQL-OLDBOY05" with the *IP* of "SQL-NEWBOY14", and connections work beautifully.
I'm pulling my hair out here. What's going wrong, why can't I do this? The managers will have my head if we tell them that the migration strategy has suddenly changed to "Hey you've got to change *all* connection-strings/configuration-settings, on all *hundred-plus* apps/services/websites that use those old server-names, because we can't get this smooth-switchover to work".
-Nate the DBA natethedba.com
March 21, 2016 at 3:49 pm
Have you considered using a DNS alias, or reusing the SQL Server Network Name?
April 15, 2016 at 11:48 am
Sometimes SPN content can go missing after doing these type of migrations. Check that your SPNs are set properly on the NEWSERVER for the OLDSERVER
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply