MDS és AAG automatikus failover
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:
- Web alkalmazás
- 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:
- 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.
1/*============================================================================
2 File: MDS-01. Failover.sql
3
4 Summary:
5 Date:
6 Modified:
7
8 SQL Server Version: 11.0.3393 (SQL Server 2012 w/ SP1 or later)
9------------------------------------------------------------------------------
10 Written by Janos Berke, IamBerke.com
11
12 For more scripts and sample code, check out http://www.IamBerke.com
13
14 THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
15 ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
16 TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
17 PARTICULAR PURPOSE.
18============================================================================*/
19--Use ALT+Q+M switching to SQLCMD mode!!!
20:setvar jobowner "sa"
21:setvar jobname "MDS_FAILOVER_POSTSCRIPT"
22:setvar alertname "MDS_FAILOVER"
23:setvar mds_dbname "mds_test1"
24
25USE [msdb]
26GO
27
28BEGIN TRANSACTION
29DECLARE @ReturnCode INT
30SELECT @ReturnCode = 0
31
32IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
33BEGIN
34EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
35IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
36
37END
38
39DECLARE @jobId BINARY(16)
40EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'$(jobname)',
41 @enabled=1,
42 @notify_level_eventlog=0,
43 @notify_level_email=0,
44 @notify_level_netsend=0,
45 @notify_level_page=0,
46 @delete_level=0,
47 @description=N'No description available.',
48 @category_name=N'[Uncategorized (Local)]',
49 @owner_login_name=N'sa', @job_id = @jobId OUTPUT
50IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
51
52EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'STEP1',
53 @step_id=1,
54 @cmdexec_success_code=0,
55 @on_success_action=1,
56 @on_success_step_id=0,
57 @on_fail_action=2,
58 @on_fail_step_id=0,
59 @retry_attempts=0,
60 @retry_interval=0,
61 @os_run_priority=0, @subsystem=N'TSQL',
62 @command=N'
63GO
64DECLARE @dbName NVARCHAR(128);
65SELECT @dbName= db_name();
66
67
68IF (DB_ID(@dbName) IS NOT NULL
69 AND DATABASEPROPERTYEX(@dbName,''Status'') <> N''ONLINE'')
70BEGIN
71 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
72 RETURN
73END
74
75GO
76
77
78
79PRINT N''Creating $(DatabaseName)...''
80GO
81
82DECLARE @dbName NVARCHAR(128);
83SELECT @dbName= db_name();
84
85
86EXECUTE sp_dbcmptlevel @dbName, 100;
87
88
89GO
90DECLARE @execSQL NVARCHAR(MAX);
91set @execSQL = ''
92DECLARE @dbName NVARCHAR(128);
93SELECT @dbName= db_name();
94
95
96IF EXISTS (SELECT 1
97 FROM [master].[dbo].[sysdatabases]
98 WHERE [name] = @dbName)
99 BEGIN
100 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
101 SET ANSI_NULLS ON,
102 ANSI_PADDING ON,
103 ANSI_WARNINGS ON,
104 ARITHABORT ON,
105 CONCAT_NULL_YIELDS_NULL ON,
106 NUMERIC_ROUNDABORT OFF,
107 QUOTED_IDENTIFIER ON,
108 ANSI_NULL_DEFAULT ON,
109 CURSOR_DEFAULT LOCAL,
110 RECOVERY FULL,
111 CURSOR_CLOSE_ON_COMMIT OFF,
112 AUTO_CREATE_STATISTICS ON,
113 AUTO_SHRINK OFF,
114 AUTO_UPDATE_STATISTICS ON,
115 RECURSIVE_TRIGGERS OFF
116 WITH ROLLBACK IMMEDIATE;
117 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
118 SET AUTO_CLOSE OFF
119 WITH ROLLBACK IMMEDIATE;
120 END
121
122
123
124'';
125EXEC (@execSQL)
126
127GO
128
129
130DECLARE @execSQL NVARCHAR(MAX);
131set @execSQL = ''
132DECLARE @dbName NVARCHAR(128);
133SELECT @dbName= db_name();
134
135
136IF EXISTS (SELECT 1
137 FROM [master].[dbo].[sysdatabases]
138 WHERE [name] = @dbName)
139 BEGIN
140 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
141 SET ALLOW_SNAPSHOT_ISOLATION OFF;
142 END
143
144
145
146'';
147EXEC (@execSQL)
148
149GO
150
151
152DECLARE @execSQL NVARCHAR(MAX);
153set @execSQL = ''
154DECLARE @dbName NVARCHAR(128);
155SELECT @dbName= db_name();
156
157
158IF EXISTS (SELECT 1
159 FROM [master].[dbo].[sysdatabases]
160 WHERE [name] = @dbName)
161 BEGIN
162 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
163 SET READ_COMMITTED_SNAPSHOT ON;
164 END
165
166
167
168'';
169EXEC (@execSQL)
170
171GO
172
173
174DECLARE @execSQL NVARCHAR(MAX);
175set @execSQL = ''
176DECLARE @dbName NVARCHAR(128);
177SELECT @dbName= db_name();
178
179
180IF EXISTS (SELECT 1
181 FROM [master].[dbo].[sysdatabases]
182 WHERE [name] = @dbName)
183 BEGIN
184 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
185 SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
186 PAGE_VERIFY TORN_PAGE_DETECTION,
187 DATE_CORRELATION_OPTIMIZATION OFF,
188 PARAMETERIZATION FORCED,
189 SUPPLEMENTAL_LOGGING OFF
190 WITH ROLLBACK IMMEDIATE;
191 END
192
193
194
195'';
196EXEC (@execSQL)
197
198GO
199
200DECLARE @execSQL NVARCHAR(MAX);
201DECLARE @IsAlwaysOnSupported BIT = CASE WHEN OBJECT_ID(N''[master].[sys].[availability_databases_cluster]'') IS NULL THEN 0 ELSE 1 END;
202
203set @execSQL = N''
204DECLARE @dbName NVARCHAR(128);
205SELECT @dbName= db_name();
206
207IF EXISTS (SELECT 1
208 FROM [master].[dbo].[sysdatabases]
209 WHERE [name] = @dbName)
210BEGIN '' + CASE @IsAlwaysOnSupported WHEN 1 THEN N''
211 IF NOT EXISTS (SELECT 1
212 FROM [master].[sys].[availability_databases_cluster]
213 WHERE [database_name] = @dbName)
214 BEGIN '' ELSE N'''' END + N''
215 ALTER DATABASE ''+ QUOTENAME(db_name()) +''
216 SET ENABLE_BROKER
217 WITH ROLLBACK IMMEDIATE; '' +
218 CASE @IsAlwaysOnSupported WHEN 1 THEN N''
219 END
220 ELSE
221 BEGIN
222 IF NOT EXISTS (SELECT 1
223 FROM [master].[sys].[databases]
224 WHERE [name] = @dbName AND [is_broker_enabled] = 1)
225 BEGIN
226 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);
227 RETURN;
228 END
229 END '' ELSE N'''' END + N''
230END
231'';
232EXEC (@execSQL)
233
234GO
235
236
237DECLARE @execSQL NVARCHAR(MAX);
238set @execSQL = ''
239DECLARE @dbName NVARCHAR(128);
240SELECT @dbName= db_name();
241
242
243IF IS_SRVROLEMEMBER(N''''sysadmin'''') = 1
244 BEGIN
245 IF EXISTS (SELECT 1
246 FROM [master].[dbo].[sysdatabases]
247 WHERE [name] = @dbName)
248 BEGIN
249 EXECUTE sp_executesql N''''ALTER DATABASE ''+ QUOTENAME(REPLACE(db_name(), '''''''', '''''''''''')) +''
250 SET TRUSTWORTHY ON,
251 DB_CHAINING OFF
252 WITH ROLLBACK IMMEDIATE'''';
253 END
254 END
255ELSE
256 BEGIN
257 PRINT N''''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'''';
258 END
259
260
261
262'';
263EXEC (@execSQL)
264
265GO
266
267
268DECLARE @execSQL NVARCHAR(MAX);
269set @execSQL = ''
270DECLARE @dbName NVARCHAR(128);
271SELECT @dbName= db_name();
272
273
274IF IS_SRVROLEMEMBER(N''''sysadmin'''') = 1
275 BEGIN
276 IF EXISTS (SELECT 1
277 FROM [master].[dbo].[sysdatabases]
278 WHERE [name] = @dbName)
279 BEGIN
280 EXECUTE sp_executesql N''''ALTER DATABASE ''+ QUOTENAME(REPLACE(db_name(), '''''''', '''''''''''')) +''
281 SET HONOR_BROKER_PRIORITY OFF
282 WITH ROLLBACK IMMEDIATE'''';
283 END
284 END
285ELSE
286 BEGIN
287 PRINT N''''The database settings cannot be modified. You must be a SysAdmin to apply these settings.'''';
288 END
289
290
291
292'';
293EXEC (@execSQL)
294
295GO
296
297
298DECLARE @execSQL NVARCHAR(MAX);
299set @execSQL = ''
300
301
302USE ''+ QUOTENAME(db_name()) +''
303
304
305'';
306EXEC (@execSQL)
307
308GO
309
310
311
312
313IF fulltextserviceproperty(N''IsFulltextInstalled'') = 1
314 EXECUTE sp_fulltext_database ''enable'';
315
316
317GO
318
319
320
321/*------------------------------------------------------------------------------------------------------------
322Make database TRUSTWORTHY in a safe manner
323--------------------------------------------------------------------------------------------------------------*/
324
325--Create a low-privileged login to own the database.
326--This mitigates problems with (later) marking the database trustworthy
327--since trustworthy is only really dangerous when the DB owner has high privilege.
328--The term ''mds dlp login'' stands for ''MDS disabled low-privileged login''
329IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE [sid] = 0x1618033988749894848204586834365) BEGIN
330 CREATE LOGIN [mds_dlp_login]
331 WITH PASSWORD = ''747F8F64-06D3-4aa0-9A83-9A4C87C48934'',
332 SID = 0x1618033988749894848204586834365;
333END; --if
334
335--Immediately disable & assign a random password to the login to mitigate it being used as an attack vector.
336ALTER LOGIN [mds_dlp_login] DISABLE;
337DECLARE @sql NVARCHAR(MAX) = N''
338 ALTER LOGIN [mds_dlp_login]
339 WITH PASSWORD = N'' + QUOTENAME(NEWID(), N'''''''') + N'';'';
340--PRINT @sql;
341EXEC sp_executesql @sql;
342
343--Grant ownership of the database to the disabled low-privileged login.
344SET @sql = N''
345 ALTER AUTHORIZATION ON DATABASE::'' + QUOTENAME(db_name()) + N''
346 TO [mds_dlp_login];'';
347--PRINT @sql;
348EXEC sp_executesql @sql;
349
350
351--Finally, flip the trusthworthy bit and we are done.
352SET @sql = N''
353 ALTER DATABASE '' + QUOTENAME(db_name()) + N''
354 SET TRUSTWORTHY ON;'';
355--PRINT @sql;
356EXEC sp_executesql @sql;
357GO
358
359
360/*------------------------------------------------------------------------------------------------------------
361Configure database to safely handle unsafe assemblies
362--------------------------------------------------------------------------------------------------------------*/
363
364--Any DB owned by mds_dlp_login marked as TRUSTWORTHY can host unsafe assemblies
365DECLARE @sql NVARCHAR(MAX) = N''
366 USE master;
367 GRANT UNSAFE ASSEMBLY TO [mds_dlp_login];'';
368EXEC sp_executesql @sql;
369GO
370
371--Tell msdb that it can trust any user impersonated as secure when coming from db owned by mds_lp_login
372DECLARE @sql NVARCHAR(MAX) = N''
373 USE msdb;
374 IF NOT EXISTS(SELECT 1 FROM sys.sysusers WHERE [name] = N''''mds_clr_user'''') BEGIN
375 CREATE USER [mds_clr_user]
376 FOR LOGIN [mds_dlp_login];
377 END; --if
378 GRANT AUTHENTICATE
379 TO [mds_clr_user];
380'';
381--PRINT @sql;
382EXEC sp_executesql @sql;
383GO
384
385
386/*------------------------------------------------------------------------------------------------------------
387Configure security for database mail
388--------------------------------------------------------------------------------------------------------------*/
389
390--Create a LOGIN specifically for sending email
391IF NOT EXISTS(SELECT 1 FROM sys.server_principals WHERE [sid] = 0x3819660112501051517954131656343) BEGIN
392 CREATE LOGIN [mds_email_login]
393 WITH PASSWORD = ''809931C0-4D0A-4740-ADA6-BDF26FF655CD'',
394 SID = 0x3819660112501051517954131656343;
395END; --if
396
397--Immediately assign a random password to the login to mitigate it being used as an attack vector.
398DECLARE @sql NVARCHAR(MAX) = N''
399 ALTER LOGIN [mds_email_login]
400 WITH PASSWORD = N'' + QUOTENAME(NEWID(), N'''''''') + N'';'';
401--PRINT @sql;
402EXEC sp_executesql @sql;
403
404--Make sure local USER is able to EXECUTE email stored procedure
405GRANT EXECUTE
406 ON OBJECT::mdm.udpNotificationQueueActivate
407 TO [mds_email_user];
408GRANT EXECUTE
409 ON TYPE::mdm.MemberGetCriteria
410 TO [mds_email_user];
411GRANT SELECT
412 ON SCHEMA::[mdm]
413 TO [mds_email_user];
414
415--Ensure activated email stored procedure runs under USERs context
416ALTER QUEUE mdm.[microsoft/mdm/queue/notification]
417WITH ACTIVATION
418(
419 STATUS = ON,
420 PROCEDURE_NAME = [mdm].[udpNotificationQueueActivate],
421 MAX_QUEUE_READERS = 1,
422 --In the original queue declaration we used the standard mds_ssb_user context
423 --since the appropriate login/user we needed did not exist yet.
424 --So here we execute using the context we actually require.
425 EXECUTE AS N''mds_email_user''
426);
427
428--Finally, create & associate a user in MSDB for the login
429--Then add the new user to the email role in msdb
430SET @sql = N''
431 USE msdb;
432
433 IF NOT EXISTS (SELECT 1 FROM sys.sysusers WHERE [name]=N''''mds_email_user'''') BEGIN
434 CREATE USER [mds_email_user] FOR LOGIN [mds_email_login];
435 END; --if
436
437 EXEC sp_addrolemember N''''DatabaseMailUserRole'''', N''''mds_email_user'''';
438 '';
439--PRINT @sql;
440EXEC sp_executesql @sql;
441GO
442
443/*------------------------------------------------------------------------------------------------------------
444Enable advanced configurations
445--------------------------------------------------------------------------------------------------------------*/
446
447EXEC sp_configure ''show advanced options'', 1;
448RECONFIGURE WITH OVERRIDE;
449GO
450
451--Switch on Database Mail
452EXEC sp_configure ''Database Mail XPs'', 1;
453RECONFIGURE WITH OVERRIDE;
454GO
455
456--Switch on CLR
457EXEC sp_configure ''clr_enable'', 1;
458RECONFIGURE WITH OVERRIDE;
459GO
460
461sp_configure ''show advanced options'', 0;
462RECONFIGURE WITH OVERRIDE;
463GO
464
465
466/*------------------------------------------------------------------------------------------------------------
467Ensure the Service Broker timers are running. When changing this code be sure to likewise change the corresponding code in SystemData.sql
468--------------------------------------------------------------------------------------------------------------*/
469DECLARE @handle UNIQUEIDENTIFIER;
470
471--Script to start the member security message queue conversation, if it not already running.
472SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/system'', N''microsoft/mdm/service/securitymember'');
473IF @handle IS NULL
474BEGIN
475 BEGIN DIALOG CONVERSATION @handle
476 FROM SERVICE [microsoft/mdm/service/system]
477 TO SERVICE N''microsoft/mdm/service/securitymember''
478 ON CONTRACT [microsoft/mdm/contract/securitymember]
479 WITH ENCRYPTION=OFF; --is by default
480END;
481
482--Script to Start the member security timer, if it not already running.
483SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/securitymembertimer'', N''microsoft/mdm/service/system'');
484IF @handle IS NULL
485BEGIN
486 DECLARE @memberSecHandle UNIQUEIDENTIFIER;
487 BEGIN DIALOG CONVERSATION @memberSecHandle
488 FROM SERVICE [microsoft/mdm/service/securitymembertimer]
489 TO SERVICE N''microsoft/mdm/service/system''
490 WITH ENCRYPTION=OFF --is by default
491 BEGIN CONVERSATION TIMER (@memberSecHandle) TIMEOUT = 30;
492END;
493GO
494
495--Script to Start the StagingBatch timer, if it not already running.
496DECLARE @handle UNIQUEIDENTIFIER;
497SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/stagingbatch'', N''microsoft/mdm/service/system'');
498IF @handle IS NULL
499BEGIN
500 BEGIN DIALOG CONVERSATION @handle
501 FROM SERVICE [microsoft/mdm/service/stagingbatch]
502 TO SERVICE N''microsoft/mdm/service/system''
503 WITH ENCRYPTION = OFF;
504 BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 30;
505END;
506GO
507
508--Script to Start the Notification timer, if it not already running.
509DECLARE @handle UNIQUEIDENTIFIER;
510SET @handle = mdm.udfServiceGetConversationHandle(N''microsoft/mdm/service/notification'', N''microsoft/mdm/service/system'');
511IF @handle IS NULL
512BEGIN
513 BEGIN DIALOG CONVERSATION @handle
514 FROM SERVICE [microsoft/mdm/service/notification]
515 TO SERVICE N''microsoft/mdm/service/system''
516 WITH ENCRYPTION = OFF;
517 BEGIN CONVERSATION TIMER (@handle) TIMEOUT = 30;
518END;
519GO
520
521',
522 @database_name=N'$(mds_dbname)',
523 @flags=0
524IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
525EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
526IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
527EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
528IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
529COMMIT TRANSACTION
530GOTO EndSave
531QuitWithRollback:
532 IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
533EndSave:
534
535GO
536
537
538EXEC msdb.dbo.sp_add_alert @name=N'$(alertname)',
539 @message_id=35266,
540 @severity=0,
541 @enabled=1,
542 @delay_between_responses=0,
543 @include_event_description_in=0,
544 @database_name=N'$(mds_dbname)',
545 @category_name=N'[Uncategorized]',
546 @job_name =N'$(jobname)'
547GO
Í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.