I am Berke

Aki használja a SQL Server Master Data Services (MDS) szolgáltatás, az nagyon hamar szembesül pár “érdekes” dologgal. Infrastruktúra oldalon a magas rendelkezésre állást az MDS esetén két oldalról kell megközelíteni:

1. Web alkalmazás
2. Adatbázis

Az első esetben egyszerűen egy újabb IIS kell és egy Windows NLB is tökéletesen megteszi, azonban az adatbázis felől már nem ennyire triviális a dolog (sajnos). Az MDS adatbázis használ egy csomó SQL szolgáltatást:

• Service Broker
• CLR
• Database Mail

Ezeket a szolgáltatásokat engedélyezni kell minden olyan szerveren, ahol az MDS adatbázis valamilyen példánya jelen lesz.

Esetemben több helyen is van MDS és AAG (AlwaysOn Availability Group) van beállítva az adatbázisra. Ez tök jó, azonban egy failover esetén az MDS az alábbi üzenetet adja:

Illetve Excel kliens esetén:

Ennek a megoldása relatív egyszerű:

• El kell indítani a Master Data Services Configuration Manager-t
• A Database Configuration oldalon a Select Database gomb megnyomásával válasszuk ki a problémás adatbázist

• Ezek után a Repair Database gomb megnyomásával a probléma meg is van oldva. (látható, hogy pirossal meg is van jelölve, hogy “This database should be repaired”)

Na jó, ez manuális failover, illetve backup/restore vagy detach/attach esetén működik, de mi van akkor, ha az SLA azt mondja, hogy egy failover után azonnal rendelkezésre kell álljon az MDS? Akkor ez nem igazán hatékony: valakinek folyamatosan figyelni kell, hogy volt e failover és a szolgáltatások elérhetőek vagy sem. Erre én az alábbi megkerülő megoldást találtam ki: megnéztem, hogy mit is csinál a “Repair Database” a háttérben és ezt a failover során felmerülő egyik eseményre ráültettem az alábbiak szerint:

• kell egy job, ami lefuttatja ugyan azt az SQL kódot, amit a “repair database” is.
• beállítottam egy Alertet, ami az MDS adatbázis 35266-os hibakódra meghívja a fenti job-ot

Ezt az alábbi script segítségével be is lehet állítani, értelemszerűen a változókat mindenki állítsa be a saját környezetének megfelelően.

/*============================================================================
  File:     MDS-01. Failover.sql

  Summary:  
  Date:     
  Modified:	

  SQL Server Version: 11.0.3393 (SQL Server 2012 w/ SP1 or later)
------------------------------------------------------------------------------
  Written by Janos Berke, IamBerke.com

  For more scripts and sample code, check out http://www.IamBerke.com
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/
--Use ALT+Q+M switching to SQLCMD mode!!!
:setvar jobowner "sa"
:setvar jobname "MDS_FAILOVER_POSTSCRIPT"
:setvar alertname "MDS_FAILOVER"
:setvar mds_dbname "mds_test1"

USE [msdb]
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'$(jobname)', 
		@enabled=1, 
		@notify_level_eventlog=0, 
		@notify_level_email=0, 
		@notify_level_netsend=0, 
		@notify_level_page=0, 
		@delete_level=0, 
		@description=N'No description available.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'STEP1', 
		@step_id=1, 
		@cmdexec_success_code=0, 
		@on_success_action=1, 
		@on_success_step_id=0, 
		@on_fail_action=2, 
		@on_fail_step_id=0, 
		@retry_attempts=0, 
		@retry_interval=0, 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'  
GO
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF (DB_ID(@dbName) IS NOT NULL  
    AND DATABASEPROPERTYEX(@dbName,''Status'') <> N''ONLINE'')  
BEGIN  
    RAISERROR(N''The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.'', 16, 127,@dbName) WITH NOWAIT  
    RETURN  
END  
  
GO

 
  
PRINT N''Creating $(DatabaseName)...''  
GO

DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
EXECUTE sp_dbcmptlevel @dbName, 100;  
  
  
GO
DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF EXISTS (SELECT 1  
           FROM   [master].[dbo].[sysdatabases]  
           WHERE  [name] = @dbName)  
    BEGIN  
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
            SET ANSI_NULLS ON,  
                ANSI_PADDING ON,  
                ANSI_WARNINGS ON,  
                ARITHABORT ON,  
                CONCAT_NULL_YIELDS_NULL ON,  
                NUMERIC_ROUNDABORT OFF,  
                QUOTED_IDENTIFIER ON,  
                ANSI_NULL_DEFAULT ON,  
                CURSOR_DEFAULT LOCAL,  
                RECOVERY FULL,  
                CURSOR_CLOSE_ON_COMMIT OFF,  
                AUTO_CREATE_STATISTICS ON,  
                AUTO_SHRINK OFF,  
                AUTO_UPDATE_STATISTICS ON,  
                RECURSIVE_TRIGGERS OFF   
            WITH ROLLBACK IMMEDIATE;  
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
            SET AUTO_CLOSE OFF   
            WITH ROLLBACK IMMEDIATE;  
    END  
  
  

'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF EXISTS (SELECT 1  
           FROM   [master].[dbo].[sysdatabases]  
           WHERE  [name] = @dbName)  
    BEGIN  
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
            SET ALLOW_SNAPSHOT_ISOLATION OFF;  
    END  
  
  

'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF EXISTS (SELECT 1  
           FROM   [master].[dbo].[sysdatabases]  
           WHERE  [name] = @dbName)  
    BEGIN  
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
            SET READ_COMMITTED_SNAPSHOT ON;  
    END  
  
  

'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF EXISTS (SELECT 1  
           FROM   [master].[dbo].[sysdatabases]  
           WHERE  [name] = @dbName)  
    BEGIN  
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,  
                PAGE_VERIFY TORN_PAGE_DETECTION,  
                DATE_CORRELATION_OPTIMIZATION OFF,  
                PARAMETERIZATION FORCED,  
                SUPPLEMENTAL_LOGGING OFF   
            WITH ROLLBACK IMMEDIATE;  
    END  
  
  

'';
EXEC (@execSQL)

GO

DECLARE @execSQL NVARCHAR(MAX);
DECLARE @IsAlwaysOnSupported BIT = CASE WHEN OBJECT_ID(N''[master].[sys].[availability_databases_cluster]'') IS NULL THEN 0 ELSE 1 END;

set @execSQL = N''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
  
IF EXISTS (SELECT 1  
            FROM   [master].[dbo].[sysdatabases]  
            WHERE  [name] = @dbName)  
BEGIN '' + CASE @IsAlwaysOnSupported WHEN 1 THEN N''
    IF NOT EXISTS (SELECT 1  
            FROM   [master].[sys].[availability_databases_cluster]  
            WHERE  [database_name] = @dbName)  
    BEGIN '' ELSE N'''' END + N''
        ALTER DATABASE ''+ QUOTENAME(db_name()) +''  
        SET ENABLE_BROKER
        WITH ROLLBACK IMMEDIATE; '' +
        CASE @IsAlwaysOnSupported WHEN 1 THEN N''
    END 
    ELSE
    BEGIN
        IF NOT EXISTS (SELECT 1  
                FROM   [master].[sys].[databases]  
                WHERE  [name] = @dbName AND [is_broker_enabled] = 1)  
        BEGIN
            RAISERROR(N''''Cannot enable the service broker because the database is in an availability group. You must remove the DB from the availability group and run repair before joining the DB to the group.'''', 16, 127,@dbName);
            RETURN;
        END
    END '' ELSE N'''' END + N''
END  
'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF IS_SRVROLEMEMBER(N''''sysadmin'''') = 1  
    BEGIN  
        IF EXISTS (SELECT 1  
                   FROM   [master].[dbo].[sysdatabases]  
                   WHERE  [name] = @dbName)  
            BEGIN  
                EXECUTE sp_executesql N''''ALTER DATABASE ''+ QUOTENAME(REPLACE(db_name(), '''''''', '''''''''''')) +''  
    SET TRUSTWORTHY ON,  
        DB_CHAINING OFF   
    WITH ROLLBACK IMMEDIATE'''';  
            END  
    END  
ELSE  
    BEGIN  
        PRINT N''''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'''';  
    END  
  
  

'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
DECLARE @dbName NVARCHAR(128);
SELECT @dbName= db_name();
 
  
IF IS_SRVROLEMEMBER(N''''sysadmin'''') = 1  
    BEGIN  
        IF EXISTS (SELECT 1  
                   FROM   [master].[dbo].[sysdatabases]  
                   WHERE  [name] = @dbName)  
            BEGIN  
                EXECUTE sp_executesql N''''ALTER DATABASE ''+ QUOTENAME(REPLACE(db_name(), '''''''', '''''''''''')) +''  
    SET HONOR_BROKER_PRIORITY OFF   
    WITH ROLLBACK IMMEDIATE'''';  
            END  
    END  
ELSE  
    BEGIN  
        PRINT N''''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'''';  
    END  
  
  

'';
EXEC (@execSQL)

GO


DECLARE @execSQL NVARCHAR(MAX);
 set @execSQL = ''
 
  
USE ''+ QUOTENAME(db_name()) +''  
  

'';
EXEC (@execSQL)

GO


 
  
IF fulltextserviceproperty(N''IsFulltextInstalled'') = 1  
    EXECUTE sp_fulltext_database ''enable'';  
  
  
GO
 
  

/*------------------------------------------------------------------------------------------------------------
Make database TRUSTWORTHY in a safe manner
--------------------------------------------------------------------------------------------------------------*/

--Create a low-privileged login to own the database.
--This mitigates problems with (later) marking the database trustworthy
--since trustworthy is only really dangerous when the DB owner has high privilege.
--The term ''mds dlp login'' stands for ''MDS disabled low-privileged login''
IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE [sid] = 0x1618033988749894848204586834365) BEGIN
    CREATE LOGIN [mds_dlp_login]
        WITH PASSWORD = ''747F8F64-06D3-4aa0-9A83-9A4C87C48934'', 
        SID = 0x1618033988749894848204586834365;
END; --if

--Immediately disable & assign a random password to the login to mitigate it being used as an attack vector.
ALTER LOGIN [mds_dlp_login] DISABLE;
DECLARE @sql NVARCHAR(MAX) = N''
    ALTER LOGIN [mds_dlp_login] 
        WITH PASSWORD = N'' + QUOTENAME(NEWID(), N'''''''') + N'';'';
--PRINT @sql;
EXEC sp_executesql @sql;

--Grant ownership of the database to the disabled low-privileged login.
SET @sql = N''
    ALTER AUTHORIZATION ON DATABASE::'' + QUOTENAME(db_name()) + N'' 
        TO [mds_dlp_login];'';
--PRINT @sql;
EXEC sp_executesql @sql;
    

--Finally, flip the trusthworthy bit and we are done.
SET @sql = N''
    ALTER DATABASE '' + QUOTENAME(db_name()) + N''
        SET TRUSTWORTHY ON;'';
--PRINT @sql;
EXEC sp_executesql @sql;
GO


/*------------------------------------------------------------------------------------------------------------
Configure database to safely handle unsafe assemblies
--------------------------------------------------------------------------------------------------------------*/

--Any DB owned by mds_dlp_login marked as TRUSTWORTHY can host unsafe assemblies
DECLARE @sql NVARCHAR(MAX) = N''
    USE master;
    GRANT UNSAFE ASSEMBLY TO [mds_dlp_login];'';
EXEC sp_executesql @sql;
GO

--Tell msdb that it can trust any user impersonated as secure when coming from db owned by mds_lp_login
DECLARE @sql NVARCHAR(MAX) = N''
    USE msdb;
    IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE [name] = N''''mds_clr_user'''') BEGIN
        CREATE USER [mds_clr_user] 
            FOR LOGIN [mds_dlp_login];
    END; --if
    GRANT AUTHENTICATE 
        TO [mds_clr_user];
'';
--PRINT @sql;
EXEC sp_executesql @sql;
GO


/*------------------------------------------------------------------------------------------------------------
Configure security for database mail
--------------------------------------------------------------------------------------------------------------*/

--Create a LOGIN specifically for sending email
IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE [sid] = 0x3819660112501051517954131656343) BEGIN
    CREATE LOGIN [mds_email_login]
        WITH PASSWORD = ''809931C0-4D0A-4740-ADA6-BDF26FF655CD'', 
        SID = 0x3819660112501051517954131656343;
END; --if

--Immediately assign a random password to the login to mitigate it being used as an attack vector.
DECLARE @sql NVARCHAR(MAX) = N''
    ALTER LOGIN [mds_email_login] 
        WITH PASSWORD = N'' + QUOTENAME(NEWID(), N'''''''') + N'';'';
--PRINT @sql;
EXEC sp_executesql @sql;

--Make sure local USER is able to EXECUTE email stored procedure
GRANT EXECUTE 
    ON OBJECT::mdm.udpNotificationQueueActivate 
    TO [mds_email_user];
GRANT EXECUTE 
    ON TYPE::mdm.MemberGetCriteria 
    TO [mds_email_user];
GRANT SELECT 
    ON SCHEMA::[mdm] 
    TO [mds_email_user];

--Ensure activated email stored procedure runs under USERs context
ALTER QUEUE mdm.[microsoft/mdm/queue/notification]
WITH ACTIVATION 
(
    STATUS = ON, 
    PROCEDURE_NAME = [mdm].[udpNotificationQueueActivate], 
    MAX_QUEUE_READERS = 1,
    --In the original queue declaration we used the standard mds_ssb_user context
    --since the appropriate login/user we needed did not exist yet.
    --So here we execute using the context we actually require.
    EXECUTE AS N''mds_email_user''
);

--Finally, create & associate a user in MSDB for the login
--Then add the new user to the email role in msdb
SET @sql = N''
    USE msdb;
    
    IF NOT EXISTS (SELECT 1 FROM sys.sysusers WHERE [name]=N''''mds_email_user'''') BEGIN
        CREATE USER [mds_email_user] FOR LOGIN [mds_email_login];
    END; --if
    
    EXEC sp_addrolemember N''''DatabaseMailUserRole'''', N''''mds_email_user'''';
    '';
--PRINT @sql;
EXEC sp_executesql @sql;	
GO

/*------------------------------------------------------------------------------------------------------------
Enable advanced configurations
--------------------------------------------------------------------------------------------------------------*/

EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE WITH OVERRIDE;
GO

--Switch on Database Mail
EXEC sp_configure ''Database Mail XPs'', 1;
RECONFIGURE WITH OVERRIDE;
GO

--Switch on CLR
EXEC sp_configure ''clr_enable'', 1;
RECONFIGURE WITH OVERRIDE;
GO

sp_configure ''show advanced options'', 0;
RECONFIGURE WITH OVERRIDE;
GO


/*------------------------------------------------------------------------------------------------------------
Ensure the Service Broker timers are running. When changing this code be sure to likewise change the corresponding code in SystemData.sql
--------------------------------------------------------------------------------------------------------------*/
DECLARE @handle UNIQUEIDENTIFIER;

--Script to start the member security message queue conversation, if it not already running.
SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/system'', N''microsoft/mdm/service/securitymember'');
IF @handle IS NULL
BEGIN
    BEGIN DIALOG CONVERSATION @handle
        FROM SERVICE [microsoft/mdm/service/system] 
        TO SERVICE N''microsoft/mdm/service/securitymember''
        ON CONTRACT [microsoft/mdm/contract/securitymember] 
        WITH ENCRYPTION=OFF; --is by default
END;

--Script to Start the member security timer, if it not already running. 
SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/securitymembertimer'', N''microsoft/mdm/service/system'');
IF @handle IS NULL
BEGIN
    DECLARE @memberSecHandle UNIQUEIDENTIFIER;
    BEGIN DIALOG CONVERSATION @memberSecHandle
        FROM SERVICE [microsoft/mdm/service/securitymembertimer]
        TO SERVICE N''microsoft/mdm/service/system''
        WITH ENCRYPTION=OFF --is by default
    BEGIN CONVERSATION TIMER (@memberSecHandle) TIMEOUT = 30;
END;
GO
    
--Script to Start the StagingBatch timer, if it not already running. 
DECLARE @handle UNIQUEIDENTIFIER;
SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/stagingbatch'', N''microsoft/mdm/service/system'');
IF @handle IS NULL
BEGIN
    BEGIN DIALOG CONVERSATION @handle 
        FROM SERVICE [microsoft/mdm/service/stagingbatch]
        TO SERVICE N''microsoft/mdm/service/system''
        WITH ENCRYPTION = OFF;
    BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 30;
END;
GO

--Script to Start the Notification timer, if it not already running. 
DECLARE @handle UNIQUEIDENTIFIER;
SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/notification'', N''microsoft/mdm/service/system'');
IF @handle IS NULL
BEGIN
    BEGIN DIALOG CONVERSATION @handle
        FROM SERVICE [microsoft/mdm/service/notification]
        TO SERVICE N''microsoft/mdm/service/system''
        WITH ENCRYPTION = OFF;
    BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 30;
END;
GO

', 
		@database_name=N'$(mds_dbname)', 
		@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO


EXEC msdb.dbo.sp_add_alert @name=N'$(alertname)', 
		@message_id=35266, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@database_name=N'$(mds_dbname)', 
		@category_name=N'[Uncategorized]', 
		@job_name =N'$(jobname)'
GO

Így nem kell izgulni egy failover után, hogy nem lesz elérhető az MDS, ezzel a kis egyszerű megoldással automatikusan minden rendben lesz. A minta megoldás egy nagyon leegyszerűsített verzió, mielőtt bárki élesbe teszi, nézze át és értelmezze.