Is that DB online?

Ever had the need to figure out if the DB you are connecting to is actually online? Like for example if you have a SQL Agent Job that runs on top of a mirrored database? That is a fairly easy task after all when you only consider Mirroring or LogShipping, in that case you could just query sys.databases and look at the state. But once you have AlwaysOn involved the thing gets a little tricky, as AlwaysOn Availability Groups don’t set the state right…

So here is the simple way of doing it right for all versions starting with Yukon up to Denali:

CREATE FUNCTION [dbo].[ufn_IsDatabaseOnline](
    @dbname sysname) RETURNS bit
AS
BEGIN
    DECLARE @RetVal bit
    SET @RetVal=0
    DECLARE @Role int

    select @Role=mirroring_role from sys.database_mirroring m
    inner join sys.databases d ON m.database_id=d.database_id
    where mirroring_guid is not null
    AND d.name=@dbname

    -- Check if Mirror and Principal
    if (@Role IS NOT NULL)
        if (@Role=1)
            SET @RetVal=1
    IF (SELECT @@MICROSOFTVERSION / 0x01000000)>=11
    BEGIN
        -- Only check HADRON if Version is 11 or higher
        select @Role=rs.role from sys.dm_hadr_database_replica_states dr
        inner join sys.dm_hadr_availability_replica_states rs on dr.group_id=rs.group_id
        inner join sys.databases d ON dr.database_id=d.database_id
        WHERE dr.is_local=1 AND rs.is_local=1
        AND d.name=@dbname

        -- Check if HADRON and Principal
        if (@Role IS NOT NULL)
            if (@Role=1)
                SET @RetVal=1
    END

    -- Handle Non-Mirrored/HADRed DBs
    IF (@Role IS NULL)
    BEGIN
        IF(SELECT state FROM Sys.databases WHERE name=@dbname)=0
            SET @RetVal=1
    END

    RETURN @RetVal
END