none
SQLAlwaysOn 2012-Query server name shows NODE name not Cluster name

    Question

  • Hi guys,

    I just finished setting up my SQLAlwaysOnLab for our Development team. I'm having an issue understanding the AlwaysOn HA feature.... When I run  the "SELECT @@SERVERNAME AS 'Server Name'" The result show the local server name and not the ClusterName or the  Availabilty Group Listener name. We need it to show the actual Cluster name or AG Listner game. Our 2008 r2 2005 clustering we have a shared storage cluster and we setup the config services and application, so when i run a query in that environment it shows "NTS-PROD"(not the clustername). Which is what we want.. Thanks for any input at all! My dev config below:

    CLustername=ADM034SQLC050

    3 nodes in the cluster bellow: ADM034SQL051, ADM034SQL052, ADM034SQL053


    • Edited by tradingadmin Thursday, March 27, 2014 7:26 PM added picture
    Thursday, March 27, 2014 6:50 PM

Answers

  • The Availability Group listener name is simply an alias to point to the correct SQL Server instance where the primary replica is running. This is not the same as having a SQL Server failover clustered instance since in an Availability Group, you have multiple SQL Server instances (multiple system databases, multiple configurations, etc.) working as replicas each independent of each other. In a SQL Server failover clustered instance, you have a single instance (shared system databases - including user databases - used by all nodes in the cluster.) This is the reason why @@SERVERNAME returns different results when you compare an Availability Group and failover clustered instance.

    Any reason why you want to return the listener name from your query? Because you can always query the Availability Group DMVs to display information like the listener name, active primary replica, etc.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    Thursday, March 27, 2014 7:40 PM
  • Hello,

    Edwin is correct, this is the expected and correct behavior as it's returning the current server that it is executing on.

    What you could do is create a function that could be executed to return the value you want inside of the databases involved in the AGs. That would give you what you want, but the built in functions are working correctly.

    Edit:

    I wanted to expand on what I wrote and qualify it a little more so that it adds some extra information and understanding.

    With a clustered instance, the servername will come back with the clustered instance name. This is because when using clustering, the resource for this is setup both at the SQL Server level (by choosing setup as a clustered instance) and at a windows level (resources in the cluster). In a cluster, each instance has a VCO (virtual computer object) created for it in AD and that is actually what is used, so servername comes back with the VCO as we would expect.

    This differs with AGs. AG instances are locally installed instances and they can be connected to without using a listener (in fact a listener isn't even needed for AGs). Listeners exist ONLY at the windows level as a resource, there is no VCO associated with a listener or any other AG resource. Each instance can be connected to just like you would any other stand alone instance. In this situation SERVERNAME returns the name of the server that it is currently on as there is nothing special about these servers. The clustering is only done at the windows layer and SQL Server installation are simply stand alone.


    Sean Gallardy | Blog | Twitter


    Thursday, March 27, 2014 8:31 PM

All replies

  • The Availability Group listener name is simply an alias to point to the correct SQL Server instance where the primary replica is running. This is not the same as having a SQL Server failover clustered instance since in an Availability Group, you have multiple SQL Server instances (multiple system databases, multiple configurations, etc.) working as replicas each independent of each other. In a SQL Server failover clustered instance, you have a single instance (shared system databases - including user databases - used by all nodes in the cluster.) This is the reason why @@SERVERNAME returns different results when you compare an Availability Group and failover clustered instance.

    Any reason why you want to return the listener name from your query? Because you can always query the Availability Group DMVs to display information like the listener name, active primary replica, etc.


    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    Thursday, March 27, 2014 7:40 PM
  • Hi Edwin,

    There are a bunch of processes that check and compare @@servername or serverproperty(servername), a bunch of other stuff monitors gather status, metrics, backup info from all the servers and return what server it came from.  I guess irregardless of how we use it, @@servername and serverproperty(servname). Thoughts? 



    Thursday, March 27, 2014 7:52 PM
  • Hello,

    Edwin is correct, this is the expected and correct behavior as it's returning the current server that it is executing on.

    What you could do is create a function that could be executed to return the value you want inside of the databases involved in the AGs. That would give you what you want, but the built in functions are working correctly.

    Edit:

    I wanted to expand on what I wrote and qualify it a little more so that it adds some extra information and understanding.

    With a clustered instance, the servername will come back with the clustered instance name. This is because when using clustering, the resource for this is setup both at the SQL Server level (by choosing setup as a clustered instance) and at a windows level (resources in the cluster). In a cluster, each instance has a VCO (virtual computer object) created for it in AD and that is actually what is used, so servername comes back with the VCO as we would expect.

    This differs with AGs. AG instances are locally installed instances and they can be connected to without using a listener (in fact a listener isn't even needed for AGs). Listeners exist ONLY at the windows level as a resource, there is no VCO associated with a listener or any other AG resource. Each instance can be connected to just like you would any other stand alone instance. In this situation SERVERNAME returns the name of the server that it is currently on as there is nothing special about these servers. The clustering is only done at the windows layer and SQL Server installation are simply stand alone.


    Sean Gallardy | Blog | Twitter


    Thursday, March 27, 2014 8:31 PM