Wednesday, October 17, 2012

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

1 comment:

  1. Hi Christi!

    I am not certain if I am the right person to ask about training, but anyway... The answer to the question is: It depends... It depends on what your expectations are. Do you want to learn about being a DBA? a DB-Developer? Is your system a DWH? Or OLTP? And how deep do you want to go?
    If you want to go through the details please ping me via email (rick@dangerous.it) and let's have a chat about it.
    Cheers
    Rick

    ReplyDelete