Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.