0

We currently have a number of databases on a single SQL Server 2008R2 instance (server name: prdServer01; Win VM). We want to migrate to a SQL Server 2016 instance (server name: prdServer02; Win VM).

We have many applications that connect to the 2008R2 DB server. So ideally we would like to avoid having to update each connection string in each application.

From what I can tell, our options are

  1. Add an alias to the SQL 2016 server(prdServer02). That alias will be the same name as the SQL 2008R2 server(i.e. prdServer01)
  2. Add a DNS entry to the Windows server (I'm not entirely sure how this works)

The problem I see, is that we would have to take the 2008R2 server offline before the alias will work. And it is also going to be difficult to test.

Is there a better solution for this? Are we best to give the new 2016 server a completely different alias and then update our apps one by one to use this new alias in the connection strings?

2 Answers 2

1

you may eventually rename the old VM and the old instance (es: prdServer01_old) and assign the prodServer01 at the new one (both VM and instance)

prdServer01 --> prdServer01_old prdServer02 --> prdServer01

here how to rename an instance: https://learn.microsoft.com/en-us/sql/database-engine/install-windows/rename-a-computer-that-hosts-a-stand-alone-instance-of-sql-server?view=sql-server-ver15

As i'm supposing you are not using ip addresses in connection string, that's all.

0

The DNS entry is most likely the most straight forward for you.

If you go the alias route, you need to create the alias on each client machine. The old SQL Server machine doesn't have to be taken offline in any way. The client having the alias just means that the client has some server name in the connection string, and the alias on that machine points it to a different (your new) SQL Server.

DNS do not have SQL Server instance awareness, though. But assuming the same instance name before and after (possibly both being default instances), then that won't be an issue.

3
  • The alias would have the same name as the old SQL machine. So this is why I would have thought the old machine would need to be taken offline?
    – K09
    May 24, 2021 at 13:58
  • 1
    You mean you don't want two machines on your network with the same name, yes, I see now what you mean. :-) Yes, take the old machine down or rename it. May 24, 2021 at 15:08
  • 2
    If you use a DNS alias there’s some extra configuration required for Windows Auth. See: learn.microsoft.com/en-us/archive/blogs/dbrowne/… May 26, 2021 at 4:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.