Czy z DMV możesz stwierdzić, czy połączenie używało ApplicationIntent = ReadOnly?

Mam skonfigurowaną grupę Always On Availability Group i chcę się upewnić, że moi użytkownicy używają ApplicationIntent = ReadOnly w swoich ciągach połączeń.

Czy z poziomu serwera SQL Server za pośrednictwem DMV (lub zdarzeń rozszerzonych itp.) Mogę stwierdzić, czy użytkownik połączony z ApplicationIntent = ReadOnly w swoim ciągu połączenia?

Proszę nie odpowiadać, w jaki sposób ZAPOBIEGAĆ połączeniom - nie o to chodzi w tym pytaniu. Nie mogę po prostu przerywać połączeń, ponieważ mamy istniejące aplikacje, które łączą się bez odpowiedniego ciągu, i muszę wiedzieć, które to są, aby móc współpracować z programistami i użytkownikami, aby stopniowo naprawić to z czasem.

Załóżmy, że użytkownicy mają wiele aplikacji. Na przykład Bob łączy się z SQL Server Management Studio i Excel. Łączy się z SSMS, gdy musi dokonać aktualizacji, i Excel, gdy musi dokonać odczytu. Muszę się upewnić, że używa ApplicationIntent = tylko do odczytu, gdy łączy się z programem Excel. (To nie jest dokładny scenariusz, ale jest wystarczająco blisko, aby to zilustrować.)

Biorąc pod uwagę sqlserver.read_only_route_completeWydarzenie rozszerzone wspomniane przez Kin i Remusa, jest to miłe wydarzenie debugowania , ale nie zawiera ze sobą dużej ilości informacji - tylko route_port(np. 1433) i route_server_name(np. Sqlserver-0.contoso.com) domyślnie . Pomogłoby to również ustalić, czy połączenie intencyjne tylko do odczytu zakończyło się powodzeniem. Wystąpiło read_only_route_failzdarzenie, ale nie mogłem go uruchomić, może jeśli wystąpił problem z routingowym adresem URL, wydawało się, że nie został uruchomiony, gdy druga instancja była niedostępna / została zamknięta, o ile mogłem powiedzieć.

Miałem jednak pewne sukcesy, łącząc to z sqlserver.loginwłączonym śledzeniem zdarzeń i przyczyn, a także pewnymi działaniami (takimi jak sqlserver.username), aby były przydatne.

Kroki ku reprodukcji

Utwórz sesję zdarzeń rozszerzonych, aby śledzić odpowiednie zdarzenia, a także przydatne działania i śledzić przyczynowość:

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.client_connection_id,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.context_info,
        sqlserver.database_id,
        sqlserver.database_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
)

Uruchom sesję XE (rozważ próbkowanie, ponieważ jest to zdarzenie debugowania) i zbierz kilka danych logowania:

Uwaga tutaj sqlserver-0 jest moim czytelnym wtórnym, a sqlserver-1 podstawowym. Tutaj używam -Kprzełącznika sqlcmddo symulacji logowania intencji aplikacji tylko do odczytu i niektórych loginów SQL. Zdarzenie tylko do odczytu jest uruchamiane po pomyślnym logowaniu tylko do odczytu.

Po wstrzymaniu lub zatrzymaniu sesji mogę wykonać zapytanie i połączyć dwa zdarzenia, np .:

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, 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/@timestamp)[1]', 'DATETIME2' ) AS [timestamp],
    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="username"]/value/text())[1]', 'VARCHAR(100)' ) AS username,
    event_data.value('(event/action[@name="client_app_name"]/value/text())[1]', 'VARCHAR(100)' ) AS client_app_name,
    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 #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

Kwerenda powinna pokazywać loginy z intencją aplikacji i tylko do odczytu:

• read_only_route_completejest zdarzeniem debugowania, więc używaj oszczędnie. Rozważ na przykład pobieranie próbek.
• dwa zdarzenia wraz z przyczynowością torów oferują możliwość spełnienia wymagań - konieczne są dalsze testy na tym prostym urządzeniu
• Zauważyłem, że jeśli nazwa połączenia nie została podana w połączeniu, wydaje się, że coś nie działa

• Próbowałem zmusić pair_matchingcel do pracy, ale zabrakło mi czasu. Istnieje tutaj pewien potencjał rozwojowy, na przykład:

  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'
      )

Nie, nie wydaje się, aby istniała jakaś właściwość połączenia narażona na DMV (w sys.dm_exec_connections lub sys.dm_exec_sessions ) lub nawet CONNECTIONPROPERTY, która odnosi się do ApplicationIntentsłowa kluczowego ConnectionString.

Warto jednak poprosić, za pośrednictwem Microsoft Connect, o dodanie tej właściwości do sys.dm_exec_connectionsDMV, ponieważ wydaje się, że jest to właściwość połączenia przechowywanego gdzieś w pamięci SQL Server, w oparciu o następujące informacje znalezione na stronie MSDN dla Obsługa SqlClient dla wysokiej dostępności, odzyskiwania po awarii (moje wyróżnienie kursywą):

Określanie zamiaru aplikacji

Gdy ApplicationIntent = ReadOnly , klient żąda obciążenia odczytu podczas łączenia się z bazą danych z włączoną funkcją AlwaysOn. Serwer wymusi zamiar w czasie połączenia i podczas instrukcji bazy danych USE, ale tylko do bazy danych z włączoną opcją Always On.

Jeśli USEoświadczenie można zweryfikować, to ApplicationIntentmusi istnieć poza początkową próbą połączenia. Jednak osobiście nie zweryfikowałem tego zachowania.

PS Myślałem, że możemy wykorzystać fakty, które:

• replikę podstawową można ustawić tak, aby uniemożliwiała dostęp tylko do odczytu do jednej lub więcej baz danych, oraz
• „zamiar” będzie egzekwowany po wykonaniu USEinstrukcji.

Pomysł polegał na utworzeniu nowej bazy danych wyłącznie w celu przetestowania i śledzenia tego ustawienia. Nowa baza danych byłaby używana w nowej grupie dostępności, która byłaby ustawiona tak, aby zezwalała tylko na READ_WRITEpołączenia. Teoria polegała na tym, że wewnątrz Logon Trigger, EXEC(N'USE [ReadWriteOnly]; INSERT INTO LogTable...;');wewnątrz TRY...CATCHkonstrukcji, w której zasadniczo nic nie ma w CATCHbloku, albo nie spowoduje błędu w połączeniach ReadWrite (które zalogują się w nowej bazie danych), ani USEnie spowoduje błędu w połączeniach ReadOnly, ale wtedy nic by się nie stało, ponieważ błąd jest wychwytywany i ignorowany (a INSERToświadczenie nigdy nie zostanie osiągnięte). W obu przypadkach faktyczne zdarzenie logowania nie zostanie zablokowane / odrzucone. Kod Logon Trigger byłby efektywnie następujący:

BEGIN TRY
    EXEC(N'
        USE [ApplicationIntentTracking];
        INSERT INTO dbo.ReadWriteLog (column_list)
          SELECT sess.some_columns, conn.other_columns
          FROM   sys.dm_exec_connections conn
          INNER JOIN sys.dm_exec_sessions sess
                  ON sess.[session_id] = conn.[session_id]
          WHERE   conn.[session_id] = @@SPID;
        ');
END TRY
BEGIN CATCH
    DECLARE @DoNothing INT;
END CATCH;

Niestety, podczas testowania efektu wydania USEwyciągu w EXEC()ramach TRY...CATCHtransakcji, stwierdziłem, że naruszenie dostępu to przerwanie na poziomie partii, a nie przerwanie na poziomie wyciągu. A ustawienie XACT_ABORT OFFnic nie zmieniło. Stworzyłem nawet prostą Procedurę przechowywaną SQLCLR do użycia, Context Connection = true;a następnie wywołałem SqlConnection.ChangeDatabase()w ciągu a, try...catcha transakcja była nadal przerywana. I nie można używać Enlist=falsew połączeniu kontekstowym. A użycie zwykłego / zewnętrznego połączenia w SQLCLR, aby wyjść poza Transakcję, nie pomogłoby, ponieważ byłoby to zupełnie nowe Połączenie.

Istnieje bardzo, bardzo niewielka możliwość, że zamiast instrukcji można użyć HAS_DBACCESSUSE , ale tak naprawdę nie mam wielkich nadziei, że będzie w stanie uwzględnić bieżące informacje o połączeniu w swoich kontrolach. Ale nie mam też możliwości przetestowania tego.

Oczywiście, jeśli istnieje flaga śledzenia, która może spowodować, że naruszenie dostępu nie przerywa partii, to wspomniany powyżej plan powinien działać ;-).

Jak bardzo chcesz być chory? Strumień TDS nie jest trudny do proxy, zrobiliśmy to dla naszej aplikacji SaaS. To, czego szukasz (dosłownie trochę), znajduje się w komunikacie login7. Możesz poprosić użytkowników o połączenie za pośrednictwem serwera proxy i zalogowanie / wyegzekwowanie bitu. Do diabła, możesz nawet włączyć dla nich. :)

Czy Twoja aplikacja używa konta usługi, czy może wielu kont usług? Jeśli tak, użyj Extended Event, aby monitorować ruch związany z logowaniem, ale wyklucz konta usług na podstawowym, zawsze aktywnym serwerze. Powinieneś być teraz w stanie zobaczyć, kto loguje się do podstawowego, zawsze dostępnego serwera i nie używa dodatkowego ciągu połączenia tylko do odczytu. Przygotowuję się do instalacji Always-On i właśnie to zrobię, chyba że powiesz mi, że to nie zadziała.

Niestety nie mam środowiska do przetestowania następujących elementów i niewątpliwie istnieje kilka punktów, w których może się nie powieść, ale wyrzucę to tam, gdzie jest warte.

Procedura składowana CLR ma dostęp do bieżącego połączenia za pośrednictwem new SqlConnection("context connection=true")konstrukcji (pobranej stąd ). Typ SqlConnection ujawnia właściwość ConnectionString . Ponieważ ApplicationIntent znajduje się w początkowym ciągu połączenia, przypuszczam, że będzie on dostępny w tej właściwości i można go przeanalizować. W tym łańcuchu jest oczywiście wiele hand-offów, więc mnóstwo okazji, aby wszystko miało kształt gruszki.

Spowodowałoby to uruchomienie Logon Trigger, a wymagane wartości zachowywały się w razie potrzeby.