SQL Server How to determine which server you are on when running in a Cluster

jjhzyzn0 于 2023-06-21 发布在其他

关注(0)|答案(2)|浏览(152)

Our company now has a Sql Cluster consisting of two different Sql Servers. This works fine.

The difficulty, however, is knowing which server you are on. For the purpose of making sure that an SP is on Prod (and not on Test) we often do a check on which server the sql code is currently running on. However, with a Cluster, the server's name can change, depending on which server is the is.

This is how we are currently doing it: if @@SERVERNAME in ('SQLServer1','SQLServer2')

This is what we would like to do so we are not hardcoding Server names:

if @@SERVERNAME = 'SqlCluster'

Any suggestions? This is on Sql Server 2022

sql-server

来源：https://stackoverflow.com/questions/76466669/how-to-determine-which-server-you-are-on-when-running-in-a-cluster

2条答案

按热度按时间

zkure5ic1#

You can use the sys.dm_hadr_cluster management view.

select isnull(
  (select cluster_name from sys.dm_hadr_cluster),
  @@SERVERNAME
)

赞(0）回复(0）举报 2023-06-21

9rnv2umw2#

Depending on your needs and considerations, you could set up extended properties in your environments; adding with sp_addextendedproperty and querying with sys.extended_properties or fn_listextendedproperty . You will still need some "hard coding", but this binds to a user defined data context, rather than a hardware architectural context.

There are other methods you could use that similarly involve putting data in a schema, including the FN_AM_I_IN_PRODUCTION suggestion from comments.

赞(0）回复(0）举报 2023-06-21

我来回答

SQL Server How to determine which server you are on when running in a Cluster

2条答案

相关问题

热门标签

最新问答