/******************************************************************************
* MODULE : HADR_META                                                          *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : metadata functions for AHDR/AlwaysOn DB and AG visibility         *
*******************************************************************************
* VALID : FROM SQL Server 2012                                                *
******************************************************************************/

USE master;
GO

/*
DROP FUNCTION dbo.F_HADR_IS_PRIMARY_AG_BY_NAME;
DROP FUNCTION dbo.F_HADR_IS_PRIMARY_DB_BY_NAME;
DROP FUNCTION dbo.F_HADR_IS_PRIMARY_DB_BY_ID;
DROP FUNCTION dbo.F_HADR_IS_DB_INVOLVED_BY_ID;
DROP FUNCTION dbo.F_HADR_IS_DB_INVOLVED_BY_NAME;
DROP FUNCTION dbo.F_HADR_IS_DB_ACTIVE_BY_NAME;
DROP FUNCTION dbo.F_HADR_IS_DB_ACTIVE_BY_ID;
*/

CREATE FUNCTION dbo.F_HADR_IS_PRIMARY_AG_BY_NAME (@AG_NAME sysname) 
RETURNS BIT 
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_PRIMARY_AG_BY_NAME                            *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if an availability group is the active one or not        *
*******************************************************************************
* INPUTS :                                                                    *
*    @AG_NAME sysname : name of the availability group                        *
*******************************************************************************
* OUPUT : BIT (1 AG is primary, 0 AG is not primary, NULL AG does'nt exists)  *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT master.dbo.F_HADR_IS_PRIMARY_AG_BY_NAME('MyAG')                  *
******************************************************************************/
BEGIN 
RETURN
   CASE 
      WHEN NOT EXISTS(SELECT * 
                      FROM   sys.availability_groups 
                      WHERE  name = @AG_NAME COLLATE Latin1_General_CI_AI)
         THEN NULL
      WHEN EXISTS(SELECT *
                  FROM   sys.dm_hadr_availability_group_states AS ags
                         JOIN sys.availability_groups AS ag 
                            ON ag.group_id = ags.group_id
                  WHERE  ag.name 
                         = @AG_NAME COLLATE Latin1_General_CI_AI
                    AND  ags.primary_replica =  @@SERVERNAME)
         THEN 1
      ELSE 0
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_PRIMARY_DB_BY_NAME (@DB_NAME sysname) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_PRIMARY_DB_BY_NAME                            *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is a PRIMARY one in an availability group  *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_NAME sysname : name of the database                                  *
*******************************************************************************
* OUPUT : BIT (1 DB is primary, 0 DB is not primary,                          *
*              NULL DB is not in an AG or does'nt exists)                     *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_PRIMARY_DB_BY_NAME(name)              *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE 
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                      WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI)
         THEN NULL
      WHEN EXISTS(SELECT *
                  FROM   sys.databases as d
                         JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                  WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI 
                    AND  ars.role_desc = N'PRIMARY')
         THEN 1 
      ELSE 0 
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_PRIMARY_DB_BY_ID (@DB_ID smallint) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_PRIMARY_DB_BY_ID                              *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is a PRIMARY one in an availability group  *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_ID smallint : numerical identifier of the database (database_id)     *
*******************************************************************************
* OUPUT : BIT (1 DB is primary, 0 DB is not primary,                          *
*              NULL DB is not in an AG or does'nt exists)                     *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_PRIMARY_DB_BY_ID(database_id)         *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE 
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                      WHERE  d.database_id = @DB_ID)
         THEN NULL 
      WHEN EXISTS(SELECT *
                  FROM   sys.databases as d
                         JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                  WHERE  d.database_id = @DB_ID 
                    AND  ars.role_desc = N'PRIMARY')
         THEN 1 
      ELSE 0 
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_DB_INVOLVED_BY_ID (@DB_ID smallint) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_DB_INVOLVED_BY_ID                             *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is involved in an availability group       *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_ID smallint : numerical identifier of the database (database_id)     *
*******************************************************************************
* OUPUT : BIT (1 DB is involved, 0 DB not involved, NULL DB does'nt exists)   *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_DB_INVOLVED_BY_ID(database_id)        *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE 
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      WHERE  d.database_id = @DB_ID)
         THEN NULL 
      WHEN EXISTS(SELECT *
                  FROM   sys.databases as d
                         JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                  WHERE  d.database_id = @DB_ID)
         THEN 1 
      ELSE 0 
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_DB_INVOLVED_BY_NAME (@DB_NAME sysname) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_DB_INVOLVED_BY_NAME                             *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is involved in an availability group       *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_NAME sysname : name of the database                                  *
*******************************************************************************
* OUPUT : BIT (1 DB is involved, 0 DB not involved, NULL DB does'nt exists)   *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_DB_INVOLVED_BY_NAME(name)             *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI)
         THEN NULL 
      WHEN EXISTS(SELECT *
                  FROM   sys.databases as d
                         JOIN sys.dm_hadr_availability_replica_states as ars
                            ON d.replica_id = ars.replica_id
                  WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI)
         THEN 1 
      ELSE 0 
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_DB_ACTIVE_BY_NAME (@DB_NAME sysname) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_DB_ACTIVE_BY_NAME                             *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is involved in an availability group       *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_NAME sysname : name of the database                                  *
*******************************************************************************
* OUPUT : BIT (1 DB is active, 0 DB not active, NULL DB does'nt exists)       *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_DB_ACTIVE_BY_NAME(name)               *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE 
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI)
         THEN NULL
      WHEN 
         EXISTS(SELECT *
                FROM   sys.databases as d
                       LEFT JOIN sys.dm_hadr_availability_replica_states as ars
                          ON d.replica_id = ars.replica_id
                WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI
                  AND  ars.replica_id IS NULL
                UNION ALL
                SELECT *
                FROM   sys.databases as d
                       JOIN sys.dm_hadr_availability_replica_states as ars
                          ON d.replica_id = ars.replica_id
                WHERE  d.name = @DB_NAME COLLATE Latin1_General_CI_AI 
                  AND  ars.role_desc = N'PRIMARY')
         THEN 1 
      ELSE 0 
   END;
END;
GO

CREATE FUNCTION dbo.F_HADR_IS_DB_ACTIVE_BY_ID (@DB_ID smallint) 
RETURNS BIT
AS
/******************************************************************************
* MODULE : HADR_META                                                          *
* NATURE : SCALAR_FUNCTION                                                    *
* OBJECT : master.dbo.F_HADR_IS_DB_ACTIVE_BY_ID                             *
* CREATE : 2018-09-12                                                         * 
* VERSION : 1                                                                 *
*******************************************************************************
* Frédéric BROUARD  -  alias SQLpro  -   SARL SQL SPOT  -  SQLpro@sqlspot.com *
* Architecte de données :  expertise, audit, conseil, formation, modélisation *
* tuning, sur les SGBD Relationnels, le langage SQL, MS SQL Server/PostGreSQL *
* blog: http://blog.developpez.com/sqlpro  site: http://sqlpro.developpez.com *
* publication, révision du code Microsoft SQL Server : http://mssqlserver.fr/ *
*******************************************************************************
* PURPOSE : indicate if a database is involved in an availability group       *
*******************************************************************************
* INPUTS :                                                                    *
*    @DB_ID smallint : numerical identifier of the database (database_id)     *
*******************************************************************************
* OUPUT : BIT (1 DB is active, 0 DB not active, NULL DB does'nt exists)       *
*******************************************************************************
* SAMPLE :                                                                    *
*     SELECT name, master.dbo.F_HADR_IS_DB_ACTIVE_BY_ID(database_id)          *
*     FROM   sys.databases                                                    *
******************************************************************************/
BEGIN
RETURN 
   CASE
      WHEN NOT EXISTS(SELECT *
                      FROM   sys.databases as d
                      WHERE  d.database_id = @DB_ID)
         THEN NULL 
      WHEN 
         EXISTS(SELECT *
                FROM   sys.databases as d
                       LEFT JOIN sys.dm_hadr_availability_replica_states as ars
                          ON d.replica_id = ars.replica_id
                WHERE  d.database_id = @DB_ID
                  AND  ars.replica_id IS NULL
                UNION ALL
                SELECT *
                FROM   sys.databases as d
                       JOIN sys.dm_hadr_availability_replica_states as ars
                          ON d.replica_id = ars.replica_id
                WHERE  d.database_id = @DB_ID  
                  AND  ars.role_desc = N'PRIMARY')
         THEN 1 
      ELSE 0 
   END;
END;
GO