Skip to content

Instantly share code, notes, and snippets.

@nmanzi
Last active July 22, 2021 19:41
Show Gist options
  • Save nmanzi/02fe7c281dc0b5b53705364cbba2f6dc to your computer and use it in GitHub Desktop.
Save nmanzi/02fe7c281dc0b5b53705364cbba2f6dc to your computer and use it in GitHub Desktop.
Checks if the database is a writable replica - primary in Always-On AG
USE [master]
GO
CREATE FUNCTION [dbo].[fn_is_writeable_replica] (@dbname sysname)
RETURNS BIT
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @is_writeable BIT;
IF EXISTS(SELECT 1 FROM master.sys.databases WHERE [name] = @dbname)
BEGIN
IF (DATABASEPROPERTYEX(@dbname, 'Updateability') <> 'READ_WRITE')
SELECT @is_writeable = 0
ELSE
SELECT @is_writeable = 1
END
ELSE
BEGIN
SELECT @is_writeable = 0
END
RETURN(@is_writeable);
END
GO
DECLARE @is_prime bit;
SELECT @is_prime = dbo.fn_is_writeable_replica('<DBNAME>');
IF @is_prime = 0
BEGIN
PRINT 'EXITING GRACEFULLY';
THROW 51000, 'This is not a writeable replica', 1;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment