AWS RDS Basic TSQL DBA Tasks SQL Server

SELECT @@SERVERNAME on AWS RDS

By David Speight / April 18, 2023

I have a lot of legacy code that uses SELECT @@SERVERNAME and then performs an operation based on the response. When my company migrated some servers to Amazon Web Services (AWS) Relational Database Server (RDS), I quickly discovered that @@SERVERNAME does not return the value I want, but instead the underlying EC2 instance. To complicate things more, in a multiple availability zone environment (MULTI-AZ), that EC2 instance name will change every time there is a failover.

So how do I keep track of this and still use my legacy code?

The solution I came up that works for me, is to create a table that keeps track of the active EC2 node. I wanted to use this route because then my raw auditing and performance logs will tell me exactly what was occurring on which node, yet I can still output my cluster (server) name in reports.

First, I will create the TABLE dbo.Server.

The ISACTIVE column flags any EC2 instance that might be used and/or needed in reporting.

The ISCURRENT column flags the current row returned by SELECT @@SERVERNAME of which value is stored in the SERVERNM column.

SERVERRESOLVEDNAME is the column that stores my cluster (or RDS Server) name.

Some of the other columns I use in other projects which are not mentioned here.

CREATE TABLE [dbo].[Server](
[ServerID] [int] IDENTITY(1,1) NOT NULL,
[ServerNm] [varchar](50) NOT NULL,
[IsActive] [bit] NOT NULL,
[IsCurrent] [bit] NULL,
[RAMmb] [int] NULL, -- optional
[connString] [varbinary](2000) NULL,  -- encrypted string used in other ssis projects.
[ServerResolvedName] [varchar](50) NULL,
[ServerGroupID] [int] NULL,
[ServerGroupName] [varchar](150) NULL,
 CONSTRAINT [PK_Server] PRIMARY KEY CLUSTERED 
([ServerID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Next, I will create a STORED PROCEDURE and set up a job to run this every five or ten minutes. This will update my table when something changes.

I will store my server’s CLUSTER NAME to a variable I’ve called @ServerResolvedName, getting this from the current value in the Server table.

	DECLARE @ServerResolvedName varchar(50);
	SELECT TOP(1) @ServerResolvedName = ServerResolvedName
	FROM dbo.Server 
	WHERE IsCurrent = 1
	ORDER BY ServerID DESC;

As a safety I will mark all rows as ISCURRENT = 0 that do not match the current EC2 name returned by SELECT @@SERVERNAME; Then, I will mark the correct row as IsCurrent.

	-- This unmarks all servers if node has changed.
	UPDATE [dbo].[Server]
	SET IsCurrent = 0
	WHERE  IsCurrent !=0 and ServerNm != @@SERVERNAME;  

	-- This marks current server if node has changed.
	UPDATE [dbo].[Server]
	SET IsCurrent = 1
	WHERE  (IsCurrent !=1 or IsCurrent is null) and ServerNm = @@SERVERNAME;

Occasionally during an outage or maintenance window, something goes awry (or I plumb forgot about a node) and I find out there is a new node with a new EC2 @@SERVERNAME not in my table. So I want to check for this and add it to the table when it happens.

-- This creates a new record should current node not have one.
IF (SELECT COUNT(*) FROM [dbo].[Server] WHERE ServerNm = @@ServerName) = 0
BEGIN 
	INSERT INTO dbo.Server ([ServerNm], [ServerResolvedName], [IsActive], RAMmb, isCurrent)
	SELECT TOP(1) @@ServerName, ServerResolvedName, 1,RAMmb,1
	FROM dbo.Server 
	WHERE ServerResolvedName = @ServerResolvedName;  
END;

Finally, I want to do one last check in case anything is out of place, such as multiple rows for an EC2 instance or some other oddity. I want to make sure there is only one row marked current.

-- This is a safegaurd measure in case of duplicate records or nothing set.
IF ((SELECT COUNT(*) FROM [dbo].[Server] WHERE IsCurrent = 1) != 1)
BEGIN
	--- reset everything to zero.
	UPDATE [dbo].[Server]
	SET IsCurrent = 0;

	-- set the most recent record for the current node.
	UPDATE [dbo].[Server]
	SET IsCurrent = 1
	WHERE ServerID IN (	SELECT TOP(1) ServerID
				FROM [dbo].[Server]
				WHERE  ServerNm = @@SERVERNAME
				ORDER BY ServerID DESC);
END;

Here is the entire stored procedure put together.

CREATE OR ALTER PROCEDURE [dbo].[ServerCurrentUpdate]
AS
BEGIN

	DECLARE @ServerResolvedName varchar(50);
	SELECT TOP(1) @ServerResolvedName = ServerResolvedName
	FROM dbo.Server 
	WHERE IsCurrent = 1
	ORDER BY ServerID DESC;

	-- This unmarks all servers if node has changed.
	UPDATE [dbo].[Server]
	SET IsCurrent = 0
	WHERE  IsCurrent !=0 and ServerNm != @@SERVERNAME;  

	-- This marks current server if node has changed.
	UPDATE [dbo].[Server]
	SET IsCurrent = 1
	WHERE  (IsCurrent !=1 or IsCurrent is null) and ServerNm = @@SERVERNAME;

	-- This creates a new record should current node not have one.
	IF (SELECT COUNT(*) FROM [dbo].[Server] WHERE ServerNm = @@ServerName) = 0
	BEGIN 
		INSERT INTO dbo.Server ([ServerNm], [ServerResolvedName], [IsActive], RAMmb, isCurrent)
		SELECT TOP(1) @@ServerName, ServerResolvedName, 1,RAMmb,1
		FROM dbo.Server 
		WHERE ServerResolvedName = @ServerResolvedName;  
	END;

	-- This is a safegaurd measure in case of duplicate records or nothing set.
	IF ((SELECT COUNT(*) FROM [dbo].[Server] WHERE IsCurrent = 1) != 1)
	BEGIN
		--- reset everything to zero.
		UPDATE [dbo].[Server]
		SET IsCurrent = 0;

		-- set the most recent record for the current node.
		UPDATE [dbo].[Server]
		SET IsCurrent = 1
		WHERE ServerID IN (	SELECT TOP 1 ServerID
						FROM [dbo].[Server]
						WHERE  ServerNm = @@SERVERNAME
						ORDER BY ServerID DESC);
	END;

END

So how do I use this? Yes, I do have to write a little bit more code than before to use it. 

-- OLD CODE
SELECT @@SERVERNAME AS ServerName
-- NEW CODE
SELECT ServerResolvedName AS ServerName FROM dbo.Server WHERE ServerNm = @@SERVERNAME;

I use this table to join to other tables in my audit, maintenance, analysis, and performance projects talked about within this blog. This article is a reminder of how and why I use this.

You may also like