How to create an alias for Excel users to connect to a specific server in our farm? That can be changed ondemand

• boxta

  Ten Centuries

  Points: 1374

  More actions

  February 26, 2013 at 3:35 pm

  #274670

  Hi all,

  I have a few SQL Server OLAP (Analysis services) cube servers here, and users typically connect directly to them for ad hoc analysis. Let's say I have 2 servers, cube1 and cube2.

  I used to create a DNS cname called "cube" and have it re route to cube1 or cube2 depending on the status of the server (cube2 being more up to date than cube1, e.g.). That strategy somehow stopped working completely after we bought new routers / switches.

  Meaning I can have CNAME CUBE point to cube1.... people connect to CUBE in Excel. Then I switched CUBE to point to cube2. And I noticed existing users are STILL connecting to cube1, even though new connection / PING would actually point to cube2.

  I asked around the office network people, and they actually were surprided that it ever worked before... (the network people who helped me before is no longer with the company). They said Excel typically resolve the DNS to a specific IP, then stay connected to that IP. So new DNS CNAME changes after they are connected should NOT change their connection.

  But I need to be able to route our users to a specific server, even if they have already connected to a cube before. For example, everyone is connected to CUBE, and is pointed to CUBE1. I need to be able to change it to point to CUBE2, and they would automatically be routed to CUBE2.

  I am open to all solutions from network to sql server specific.

  Thanks in advance!!!

  Also, we have added a few new Named instance in the server farm. Is there any way I can set up an alias (not sql alias as that's local computer only), which have user point to that, and it would automatically be routed to a specific named instance?

  thanks