1

How do you manage changing out a SQL Server and updating all the clients that connect to it to point to the new server? If I have SQLSERVER1, then buy new hardware and call the new server SQLSERVER2 and migrate all the databases over I have to change the ODBC connections on all the application servers, report servers, some workstations, etc to point to the new SQL Server instance.

Is there a way to point clients at an alias or proxy that points to the real SQL Server instance so it's not necessarily to update all the clients on a change?

Benjamin Bryan
  • 197
  • 1
  • 9

1 Answers1

5

The easiest method should be to point all your clients to a certain DNS name when setting up connections. When you have to switch servers, you simply assign this DNS name to the new server.

In preparation for that, you could lower the TTL value for the DNS entry to a very small value so the change has a chance to propagate quickly.

Sven
  • 97,248
  • 13
  • 177
  • 225
  • I use this method all the time. – joeqwerty Feb 28 '12 at 00:42
  • Thanks. Do you use CNAME records? Does it work if you have multiple instances on a server? – Benjamin Bryan Feb 28 '12 at 01:00
  • `CNAME` or `A` doesn't matter. What do you mean with multiple instances? – Sven Feb 28 '12 at 01:42
  • It does work with multiple instances, you just have to keep the instance name and/or port the same as on the old server. – Jason Cumberland Feb 28 '12 at 14:33
  • Jason, If you are using named instances (not the default instance) the CNAME or A record only replaces the server name ("SQLSERVER1") in your example... not the instance name. The instance is not related to DNS. So, if you created a new CNAME called "SQLCNAME" for your "SQLSERVER2", the syntax would still be SQLCNAME\INSTANCENAME. DNS can only replace the machine name, not the instance. – Chris Anton Mar 01 '12 at 03:09
  • Thanks, for the help. I don't think the instance name will be an issue, since we can specify that in connection with the CNAME. Just be careful about naming instances since you're stuck with them... – Benjamin Bryan Mar 02 '12 at 16:41