July 15, 2015 at 7:39 am
Hi all,
I would like to ask for some help form all you SQL experts about how to set up a specific aliased instance name, maybe someone can point me at an article of give me some guidance.
I have installed two identical named instances on a server called LONPOCSQL. The instance names are FINPROD and FINREP, so when connection to them remotely I connect to LONPOCSQL\FINPROD or LONPOCSQL\FINREP. My question is how can I configure it so that I can connect remotely to the instances in by referencing them as FINPROD\FINPROD and FINREP\FINREP?
The reason for this is that I need to replace a very old system where the applications connect to the sql servers by referencing them as FINPROD\FINPROD and FINREP\FINREP respectively. I'm trying to set up a 2-node SQL alwayson AG cluster for HA+DR with one server in the prod DC and the other in DR. I need both instances to live one the same box.
1. As far as I'm aware SQL Aliases only work when the connection is local, remote connections fail
2. I can't create a DNS alias called "FINPROD\FINPROD" and point it at a listener name as slashes "\" are not allowed in DNS CNAMEs
3. My old boss once built a SQL 2008 R2 failover cluster with two instaces on it called "lonsql40\lonsql40" but there is no documentation on how he did it and I can't find anything on the web
So any help or advice on how to achieve it at this point will help. Thanks!
July 16, 2015 at 5:36 am
I'm not sure why remote connections wouldn't work using an alias, I've used them many times. If you setup your alias like this it should work. (Sorry for the lack of picture)
Alias Name: FINPROD\FINDPROD
Port No: <put the port number the instance is listening on here>
Protocol: TCP/IP
Server: LONPOCSQL\FINPROD
I tried several variations with a remote server and I could make the alias (including the "pseudo" instance name after the slash) anything I wanted.
Senior DBA - Gateway Ticketing Systems
Co-Founder - Do It Simply Software
July 16, 2015 at 6:57 am
Alias will work, in conjunction with DNS entry.
DNS: old servername
Alias: old servername\instance
server: new server
port: whatever port your instance is listening on.
July 17, 2015 at 8:40 am
Thanks Justin that did the tick, using a DNS alias along with the SQL Alias did the trick.
I also managed to get it working using this method - http://kevine323.blogspot.co.uk/2013/08/setting-up-aliases-on-sql-server-with.html
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply