Ha a mirroringgal gond van, arr\u00f3l fontos azonnal tudni. K\u00e9t esetet szoktunk figyelni. Az egyik a state change, azaz ha valami\u00e9rt \u00e1tv\u00e1ndorol a kiszolg\u00e1l\u00e1s az egyik l\u00e1br\u00f3l a m\u00e1sikra, illetve, ha feltorl\u00f3dnak a tranzakci\u00f3k valamelyik oldalon.<\/p>\n
A monitoroz\u00e1sra vannak be\u00e9p\u00edtett dolgok, ezekr\u0151l itt lehet b\u0151vebben olvasni<\/a>, itt szinte minden inf\u00f3 megvan az alertez\u00e9s fel\u00e9p\u00edt\u00e9s\u00e9hez. Lehetne eleg\u00e1nsabban is lookup t\u00e1bl\u00e1val, de a c\u00e9lnak ez megfelelt.<\/p>\n A m\u00e1sik k\u00e9rd\u00e9s macar\u00e1sabb. Egy job percenk\u00e9nt mintav\u00e9telezi a mirror queue-jainak a m\u00e9rt\u00e9t, \u00e9s ezeket be\u00edrja egy rendszert\u00e1bl\u00e1ba. Ha valamelyik t\u00falmegy egy megadott hat\u00e1r\u00e9rt\u00e9ken, akkor ez be\u00edrja az event logba, onnan egy alert ki tudja olvasni, \u00e9s emailt k\u00fcldeni r\u00f3la. Csak \u00e9ppen a l\u00e9nyeg nincs ebben benne, melyik adatb\u00e1zissal van a baj. Ez bug, ez van<\/a>. Ez az ut\u00f3bbi 3 nap torl\u00f3d\u00e1sait k\u00fcldi el emailben, ebb\u0151l m\u00e1r l\u00e1tszik, melyik db akadt el.<\/p>\n","protected":false},"excerpt":{"rendered":" Ha a mirroringgal gond van, arr\u00f3l fontos azonnal tudni. K\u00e9t esetet szoktunk figyelni. Az egyik a state change, azaz ha valami\u00e9rt \u00e1tv\u00e1ndorol…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,4,30,21,58,77,78],"tags":[],"class_list":["post-1322","post","type-post","status-publish","format-standard","hentry","category-adatbazisok","category-szakmai-elet","category-sql-server","category-sql-server-2005","category-sql-server-2008","category-sql-server-2008-r2","category-sql-server-2012"],"_links":{"self":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1322","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/comments?post=1322"}],"version-history":[{"count":3,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1322\/revisions"}],"predecessor-version":[{"id":1325,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/posts\/1322\/revisions\/1325"}],"wp:attachment":[{"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/media?parent=1322"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/categories?post=1322"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/soci.hu\/blog\/index.php\/wp-json\/wp\/v2\/tags?post=1322"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\nK\u00e9t probl\u00e9m\u00e1s pontba akadtunk bele. Az el\u0151bbi cikkben a WMI esem\u00e9nyek hat\u00e1s\u00e1ra csak sz\u00e1mok j\u00f6ttek vissza st\u00e1tuszk\u00e9nt, ezek nem t\u00fal besz\u00e9desek egy emailben. Ezeket el\u00e9g k\u00f6nny\u0171 visszafejteni stringekk\u00e9:<\/p>\n\r\ndeclare @msg nvarchar(4000) = 'State of $(ESCAPE_SQUOTE(WMI(DatabaseName))) database changed to ''';\r\ndeclare @state nvarchar(50) = '$(ESCAPE_SQUOTE(WMI(State)))';\r\ndeclare @newStateString nvarchar(100) =\r\ncase @state\r\nwhen '2' then 'Synchronized Principal without Witness'\r\nwhen '7' then 'Manual Failover'\r\n...\r\nend;\r\n\r\nset @msg += @newStateString + ''' on $(ESCAPE_SQUOTE(WMI(StartTime)))';\r\n\r\nEXEC msdb..sp_send_dbmail @profile_name='SQL Server 2008 Mirroring Notifications',\r\n@recipients='foo@bar.com',\r\n@subject= 'DB Mirroring Alert',\r\n@body=@msg;\r\n<\/pre>\n
\nHogy valami k\u00e9p\u00fcnk m\u00e9gis legyen m\u00e1r email alapj\u00e1n mi a gond, az al\u00e1bbi jobot futtatjuk le a threshold alert alapj\u00e1n:<\/p>\n\r\nEXEC msdb..sp_send_dbmail @profile_name='SQL Server 2008 Mirroring Notifications',\r\n@recipients='foo@bar.com',\r\n@subject= 'DB Mirroring Alert',\r\n@execute_query_database = 'msdb',\r\n@query = '\r\nselect * from (select distinct(database_id) id from dbo.dbm_monitor_data) d\r\ncross apply\r\n(select top 5 cast(DB_NAME(database_id) as nvarchar(20)) db, local_time, redo_queue_size, send_queue_size\r\nfrom dbo.dbm_monitor_data\r\nwhere ((send_queue_size > 0 or redo_queue_size > 0) and local_time > GETDATE() - 3 and database_id = d.id)) t\r\norder by db, local_time desc'\r\n<\/pre>\n