Amazon RDS SQL Server: how to detect if it is RDS?

user_566745 0
Viewed 1269

I am trying to port my database to RDS. Need to do some changes because of restrictions.

Is it possible to detect inside script (stored procedure etc) that current database is in RDS?

Upd. I use for the testing this way in my function: 

if CHARINDEX(N'EC2AMAZ',(cast(serverproperty('ServerName') as nvarchar(256))))>0 
   return 1 
else 
   return 0
amazon-rds sql-server
3 Answers

I liked @xiani's approach, and decided to enhance it slightly (in the event a "normal" instance of SQL Server has an [rdsadmin] database).

DECLARE @IsAmazonRDS BIT = 0;

--1st test: does the [rdsadmin] database exist?
IF DB_ID('rdsadmin') IS NOT NULL
BEGIN
    BEGIN TRY
        --2nd test: If this is an Amazon RDS instance, we should not able to access the database "model" under the current security context.
        DECLARE @FileCount INT;
        SELECT @FileCount = COUNT(*) FROM model.sys.database_files;
    END TRY
    BEGIN CATCH
        SET @IsAmazonRDS = 1;
        --Comment/uncomment to verify the flag is set.
        --SELECT 'Inside Catch';
    END CATCH
END

This could be supplemented with 3rd, 4th, etc. additional checks based on whatever criteria you determine. Decide for yourself how far you want to take it.

user_29611600
Related