我有一个Always On Availability Group,我想确保我的用户在他们的连接字符串中使用ApplicationIntent = ReadOnly.
从sql Server通过DMV(或扩展事件或其他),我可以判断用户是否在其连接字符串中与ApplicationIntent = ReadOnly连接？
请不要回答如何预防连接 – 这不是这个问题的内容.我不能简单地停止连接,因为我们现有的应用程序没有正确的字符串连接,我需要知道它们是哪些,所以我可以与开发人员和用户一起逐步修复它.
假设用户有多个应用程序.例如,Bob连接sql Server Management Studio和Excel.当他需要进行更新时,他与SSMS连接,当需要进行读取时,他与Excel连接.我需要确保他在使用Excel连接时使用ApplicationIntent = ReadOnly. (这不是确切的情况,但它足以说明.)
解决方法
拿起Kin和Remus提到的sqlserver.read_only_route_complete扩展事件,这是一个很好的Debug事件,但它不带有大量的信息 – 只是route_port(例如1433)和route_server_name(例如sqlserver-0.contoso) .com)默认情况下.这也仅有助于确定只读意图连接何时成功.有一个read_only_route_fail事件,但是我无法触发它,也许如果路由URL出现问题,就我所知,当辅助实例不可用/关闭时它似乎没有触发.
然而,我已经成功加入sqlserver.login事件和启用了因果关系跟踪,以及一些操作(如sqlserver.username)以使其有用.
重现步骤
创建扩展事件会话以跟踪相关事件,以及有用的操作和跟踪因果关系：
CREATE EVENT SESSION [xe_watchLoginIntent] ON SERVER ADD EVENT sqlserver.login ( ACTION ( sqlserver.username ) ),ADD EVENT sqlserver.read_only_route_complete ( ACTION ( sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.context_info,sqlserver.database_id,sqlserver.database_name,sqlserver.username ) ),ADD EVENT sqlserver.read_only_route_fail ( ACTION ( sqlserver.client_app_name,sqlserver.username ) ) ADD TARGET package0.event_file( SET filename = N'xe_watchLoginIntent' ) WITH ( MAX_MEMORY = 4096 KB,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY = 30 SECONDS,MAX_EVENT_SIZE = 0 KB,MEMORY_PARTITION_MODE = NONE,TRACK_CAUSALITY = ON,--<-- relate events STARTUP_STATE = ON --<-- ensure sessions starts after failover )
运行XE会话(考虑采样,因为这是一个Debug事件),并收集一些登录：
注意这里sqlserver-0是我的可读辅助和sqlserver-1的主要.这里我使用sqlcmd的-K开关来模拟只读应用程序意图登录和一些sql登录. readonly事件在成功的只读意向登录时触发.
在暂停或停止会话时,我可以查询它并尝试链接这两个事件,例如：
DROP TABLE IF EXISTS #tmp
SELECT IDENTITY( INT,1,1 ) rowId,file_offset,CAST( event_data AS XML ) AS event_data
INTO #tmp
FROM sys.fn_xe_file_target_read_file( 'xe_watchLoginIntent*.xel',NULL,NULL )
ALTER TABLE #tmp ADD PRIMARY KEY ( rowId );
CREATE PRIMARY XML INDEX _pxmlidx_tmp ON #tmp ( event_data );
-- Pair up the login and read_only_route_complete events via xxx
DROP TABLE IF EXISTS #users
SELECT
rowId,event_data.value('(event/@timestamp)[1]','DATETIME2' ) AS [timestamp],event_data.value('(event/action[@name="username"]/value/text())[1]','VARCHAR(100)' ) AS username,event_data.value('(event/action[@name="attach_activity_id_xfer"]/value/text())[1]','VARCHAR(100)' ) AS attach_activity_id_xfer,event_data.value('(event/action[@name="attach_activity_id"]/value/text())[1]','VARCHAR(100)' ) AS attach_activity_id
INTO #users
FROM #tmp l
WHERE l.event_data.exist('event[@name="login"]') = 1
AND l.event_data.exist('(event/action[@name="username"]/value/text())[. = "sqlUserShouldBeReadOnly"]') = 1
DROP TABLE IF EXISTS #readonly
SELECT *,event_data.value('(event/data[@name="route_port"]/value/text())[1]','INT' ) AS route_port,event_data.value('(event/data[@name="route_server_name"]/value/text())[1]','VARCHAR(100)' ) AS route_server_name,event_data.value('(event/action[@name="client_app_name"]/value/text())[1]','VARCHAR(100)' ) AS client_app_name,'VARCHAR(100)' ) AS attach_activity_id
INTO #readonly
FROM #tmp
WHERE event_data.exist('event[@name="read_only_route_complete"]') = 1
SELECT *
FROM #users u
LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
SELECT u.username,COUNT(*) AS logins,COUNT( DISTINCT r.rowId ) AS records
FROM #users u
LEFT JOIN #readonly r ON u.attach_activity_id_xfer = r.attach_activity_id_xfer
GROUP BY u.username
> read_only_route_complete是一个Debug事件,所以请谨慎使用.例如,考虑采样.
>这两个事件以及跟踪因果关系提供了满足您需求的潜力 – 在这个简单的装备上进行必要的进一步测试
>我确实注意到如果没有在连接中指定数据库名称,事情似乎不起作用
>我试图让pair_matching目标工作,但没时间了.这里有一些发展潜力,例如：
ALTER EVENT SESSION [xe_watchLoginIntent] ON SERVER
ADD TARGET package0.pair_matching (
SET begin_event = N'sqlserver.login',begin_matching_actions = N'sqlserver.username',end_event = N'sqlserver.read_only_route_complete',end_matching_actions = N'sqlserver.username'
)
