1

I need to migrate a SQL database to a new server. All applications access the old SQL Server with a named instance SQLA\InstOld. Now the new SQL instance is at SQLB\InstNew.

I don´t like to change all applications/clients, as in the past the connection string was hard coded. I tried several settings with SQL alias and DNS cname, with no success.

When using a DNS cname from SQLA to SQLB, I can access the new database with SQLA\InstNew or even just SQLA, as "InstNew" is the only and default instance. But what I need to achieve is to get access with SQLA\InstOld to the new instance, so I don´t need to change the running applications. I tried to set different SQL alias, like "InstOld" to "InstNew", with no success.

Any tips on how to achieve this?

Thank you Michael

KKRohr
  • 11
  • 1
  • Why not make it simple and name the new instance same as old? This way you only need to worry about DNS changes. – Alex Dec 14 '21 at 15:01
  • Because I thought it would be easy to change using SQL alias and the new instance is already in use. If it is technically impossible to redirect, this would be the easiest solution. – KKRohr Dec 14 '21 at 15:10
  • Is [this](https://stackoverflow.com/questions/3140380/how-do-we-alias-a-sql-server-instance-name-used-in-a-connection-string-config) or [this](https://mariusschulz.com/blog/how-to-configure-a-sql-server-alias-for-a-named-instance-on-a-development-machine) of any help? – Alex Dec 14 '21 at 15:58
  • 1
    So I followed your suggestion now and installed a new instance with the same old name like on the old server. Then moved all the databases to the new instance. Access over the DNS cname is working now and this is solved for me. – KKRohr Dec 15 '21 at 11:15
  • Thanks for posting an update. Good to know it worked! To help other people in the future I suggest you post an answer and accept it. Otherwise your question may get auto deleted after a period of time. – Alex Dec 15 '21 at 13:40
  • Hard-coded? Not the DBAs problem. The lazy developer needs to refactor his source code and parametrize web.config files (or similar) as external configuration files. How are you supposed to keep up with replication and alternate sites with HARD-CODED login??? – alejandrob Mar 28 '23 at 15:18

1 Answers1

0

I couldn´t find a solution to map all connections to the new instance on the new SQL server. Somehow the DNS alias / SQL alias doesn´t allow it.

My solution was to create a new instance with the same name on the new SQL server as it was on the old SQL server. Now with the DNS cname alias, all applications get routed to the new server without any problems.

KKRohr
  • 11
  • 1