0

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

Greg Gum
  • 33,478
  • 39
  • 162
  • 233
  • you can create a function like FN_AM_I_IN_PRODUCTION that returns something on one server but something else on test server. I would say in general, if you need to check, you already doing things wrongly :D – siggemannen Jun 13 '23 at 16:02
  • 1
    `select isnull((select cluster_name from sys.dm_hadr_cluster), @@SERVERNAME)` https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-hadr-cluster-transact-sql?view=azuresqldb-current – Charlieface Jun 13 '23 at 16:06
  • @Charlieface, If you want to post as answer, I will accept. – Greg Gum Jun 14 '23 at 11:53

2 Answers2

1

You can use the sys.dm_hadr_cluster management view.

select isnull(
  (select cluster_name from sys.dm_hadr_cluster),
  @@SERVERNAME
)
Charlieface
  • 52,284
  • 6
  • 19
  • 43
0

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.

Sean
  • 91
  • 4