November 24, 2017 at 4:17 am
Hi,
I've set up a test AlwaysOn AG environment.
I have 2 SQL Servers, SQL-PRI and SQL-SEC, and I have a listener name of SQL-LSNR
everything seems to work fine. I can manually failover the databases from one node to the other and back again. I can force an unplanned failover by killing one of the SQL Servers.
Even my applications work fine and failover.
However, what I don't understand is what should be in sys.servers on each of the SQL Servers.
If i run a query using 4 point naming convention with the listener as the server name, i get an error to say that the server (listener name) is not in sys.servers
(select * from SQL-LSNR.DB1.dbo.Table1)
As I see it, I have 2 options.
1) I add the listener name as a linked server as per the error message and loop back to itself
This doesn't seem like a very good idea
2) I add the listener name to sys.servers by using sp_addserver
I assume this will mean dropping the actual server name that is already in sys.servers
This seems like it might be the right solution, however, i've read through a couple of guides online for creating this environment and none of them mention the need to do this.
on top of that, I can't find any posts online where people are saying that they're doing this.
What is the correct configuration in this environment that will allow me to run a query using 4 point naming convention.
Thank you very much for your help
Steve
November 24, 2017 at 4:28 am
The correct server name should be the NETBIOS\INSTANCE of the host machine, not the listener. As AOAG use separate instances and the Listener is just a DNS alias which controls where to route the connections its not actually a server.
Is there a reason your trying to loop back using 4 part naming, and not using 3 part naming instead?
November 24, 2017 at 5:40 am
Hi Anthony,
Thanks for the quick reply.
So it seems that my environment is set correctly at the moment. sys.servers has SQL-PRI on my primary node and SQL-SEC on the secondary node.
One of the vendor applications that we use has a multi-database model which can be scaled across different SQL Servers (although i don't think anyone ever does that)
The idea is that you can have the meta database at a central location, and then have regional databases scattered at different locations.
Some of the queries the application runs are in the context of the meta database, but are querying the regional databases. As these databases could in theory be on different SQL Servers, the application uses 4 part naming.
When I put this particular application on our AlwaysOn AG environment, some of the functions fail because the listener name isn't in sys.servers.
As this is just a testing environment, I've tried adding a loopback linked server for the listener name.
The application seems happy with that, but I'm not overly happy with that as a solution
I've also tried dropping the actual server names using sp_dropserver, and then adding the linked server name using sp_addserver.
Again, the application is happy with that.... but based on your reply, that now seems wrong.
November 24, 2017 at 5:53 am
In that case I would go with the linked server method as that's technically the safest way to do 4 part naming, but again a linked server for a loopback is a bit strange, but that way you keep everything as default from a SQL instance perspective and thus any issues you know the setup and Microsoft will be happy to help.
Just need to remember to create the linked server on both nodes of the AG to ensure upon failover that the application still works.
November 24, 2017 at 6:25 am
Thanks Anthony,
I'd gone with the Linked Server initially, but didn't really like it.
Someone had told me that it may be better to use sp_dropserver and sp_addserver to ensure just the listener name was in sys.servers, but while it worked, it didn't feel right.
I'm still waiting for the actual vendors to come back and advise what they recommend as the proper configuration
Thanks for your help... have a good day
Steve
November 24, 2017 at 6:40 am
Yeah I don't like the drop & add server approach, seems very dirty to me, as that's used for renaming a SQL server, so having two machines named the same from SQL's point might cause a few instabilities.
November 24, 2017 at 6:42 am
Steve Smith-163358 - Friday, November 24, 2017 4:17 AMHi,I've set up a test AlwaysOn AG environment.
I have 2 SQL Servers, SQL-PRI and SQL-SEC, and I have a listener name of SQL-LSNReverything seems to work fine. I can manually failover the databases from one node to the other and back again. I can force an unplanned failover by killing one of the SQL Servers.
Even my applications work fine and failover.However, what I don't understand is what should be in sys.servers on each of the SQL Servers.
If i run a query using 4 point naming convention with the listener as the server name, i get an error to say that the server (listener name) is not in sys.servers
(select * from SQL-LSNR.DB1.dbo.Table1)As I see it, I have 2 options.
1) I add the listener name as a linked server as per the error message and loop back to itself
This doesn't seem like a very good idea2) I add the listener name to sys.servers by using sp_addserver
I assume this will mean dropping the actual server name that is already in sys.servers
This seems like it might be the right solution, however, i've read through a couple of guides online for creating this environment and none of them mention the need to do this.
on top of that, I can't find any posts online where people are saying that they're doing this.What is the correct configuration in this environment that will allow me to run a query using 4 point naming convention.
Thank you very much for your help
Steve
There are specific HA catalogs and views that you should use rather than the route you're going down imho.
The catalog details are fully detailed on MSDN and there are even some useful example queries available on the net
anthony.green - Friday, November 24, 2017 4:28 AMthe Listener is just a DNS alias
It's not an alias, its a DNS host (A) record with its own VIP created as a cluster resource inside the Availability Group cluster role. The Availability Group cluster resource directs the traffic coming into the listener to the appropriate AG replica
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply