Lokale Überwachung für SQL Server-Nutzungs- und -Diagnosedatensammlung (CEIP)

Gilt für:SQL Server – nur Windows Azure SQL Managed Instance

Einführung

Microsoft SQL Server enthält internetfähige Features, die Daten über Ihren Computer oder Ihr Gerät erfassen und senden können. Die zugehörigen Informationen werden als Standardcomputerinformationen bezeichnet. Die Komponente „Lokale Überwachung“ der SQL Server-Nutzungs- und -Diagnosedatensammlung schreibt die vom Dienst erfassten Daten, die die an Microsoft zu sendenden Daten (Protokolle) darstellen, in einen bestimmten Ordner. Der Zweck der lokalen Überwachung besteht darin, dass Kunden die von Microsoft mit dem Feature erfassten Daten einsehen können, um die Compliance sowie die Einhaltung von behördlichen Bestimmungen oder Datenschutzbestimmungen zu überprüfen. 

Seit SQL Server 2016 CU2 und CU3 kann die lokale Überwachung auf der Instanzebene für SQL Server-Datenbank-Engine und Analysis Services (SSAS) konfiguriert werden. In SQL Server 2016 CU4, SQL Server 2016 SP1 und späteren Releases ist die lokale Überwachung auch für SQL Server Integration Services (SSIS) aktiviert. Andere SQL Server-Komponenten, die beim Setup installiert werden, und SQL Server-Tools, die nach dem Setup heruntergeladen oder installiert werden, bieten keine Funktion zur lokalen Überwachung der Nutzungs- und Diagnosedatensammlung.

Bemerkungen

• Das Entfernen oder Deaktivieren des CEIP-Diensts für SQL wird nicht unterstützt.
• Das Entfernen von CEIP-Ressourcen für SQL aus der Clustergruppe wird nicht unterstützt.

Das Abmelden von SQL Server Verwendung und Diagnosedatensammlung wird unterstützt, der Dienst kann jedoch nicht entfernt oder deaktiviert werden. Informationen zur Deaktivierung der Datensammlung finden Sie unter Aktivieren oder Deaktivieren der lokalen Überwachung.

Voraussetzungen

Nachfolgend sind die erforderlichen Komponenten zum Aktivieren der lokalen Überwachung auf jeder SQL Server-Instanz aufgeführt:

1. Die Instanz wird auf SQL Server 2016 RTM CU2 oder höher gepatcht. Für Integration Services wird die Instanz auf SQL 2016 RTM CU4, SQL 2016 SP1 oder höher gepatcht.

2. Benutzer muss ein Systemadministrator oder eine Rolle mit Zugriff zum Hinzufügen und Ändern von Registrierungsschlüsseln, Erstellen von Ordnern, Verwalten der Ordnersicherheit und Beenden/Starten eines Windows-Diensts sein. 

Vorkonfigurationsschritte vor der Aktivierung der lokalen Überwachung

Vor der Aktivierung der lokalen Überwachung muss ein Systemadministrator folgende Voraussetzungen erfüllen:

1. Er muss den SQL Server-Instanznamen und das Anmeldekonto für den SQL Server-CEIP-Dienst kennen.

2. Konfigurieren Sie einen neuen Ordner für die Dateien der lokalen Überwachung.

3. Er muss Berechtigungen für das Anmeldekonto des SQL Server-CEIP-Diensts gewähren.

4. Er muss eine Registrierungsschlüsseleinstellung erstellen, um das Zielverzeichnis für die lokale Überwachung zu konfigurieren.

Abrufen des Anmeldekontos für den SQL Server-CEIP-Dienst

Führen Sie die folgenden Schritte aus, um das Anmeldekonto für den SQL Server-CEIP-Dienst abzurufen:

1. Starten Sie die Konsole Dienste. Wählen Sie dazu die Windows-Taste + R auf der Tastatur aus, um das Dialogfeld Ausführen zu öffnen. Geben Sie dann services.msc in das Textfeld ein, und wählen OK aus, um die Konsole Dienste zu starten.

2. Navigieren Sie zu dem entsprechenden Dienst. Suchen Sie z. B. für die Datenbank-Engine nach dem SQL Server-CEIP-Dienst(Name Ihrer Instanz). Suchen Sie für Analysis Services nach SQL Server Analysis Services-CEIP(Name Ihrer Instanz). Suchen Sie für Integration Services nach SQL Server Integration Services-CEIP-Dienst.

3. Klicken Sie mit der rechten Maustaste auf den Dienst, und wählen Sie Eigenschaftenaus.

4. Wählen Sie die Registerkarte Anmelden aus. Das Anmeldekonto wird unter Dieses Konto aufgelistet.

Konfigurieren Sie einen neuen Ordner für die Dateien der lokalen Überwachung.

Erstellen Sie einen neuen Ordner (Verzeichnis für die lokale Überwachung), in den die Protokolle von der lokalen Überwachung geschrieben werden. Der vollständige Pfad zum Verzeichnis der lokalen Überwachung für eine Standardinstanz der Datenbank-Engine wäre beispielsweise: C:\SQLCEIPAudit\MSSQLSERVER\DB\. 

Gewähren von Berechtigungen für das Anmeldekonto des SQL Server-CEIP-Diensts

1. Navigieren Sie in Datei-Explorerzum Speicherort, in dem sich der neue Ordner befindet.

2. Klicken Sie mit der rechten Maustaste auf den neuen Ordner, und wählen Sie Eigenschaftenaus.

3. Wählen Sie auf der Registerkarte Sicherheit die Option Bearbeiten und dann „Berechtigung verwalten“ aus.

4. Wählen Sie Hinzufügen aus, und geben Sie die Anmeldeinformationen des SQL Server-CEIP-Diensts ein. Beispiel: NT Service\SQLTELEMETRY.

5. Wählen Sie Namen überprüfen aus, um den bereitgestellten Namen zu überprüfen, und wählen Sie dann OK aus.

6. Wählen Sie im Dialogfeld Berechtigung das Anmeldekonto für den SQL Server-CEIP-Dienst aus, und wählen Sie Ordnerinhalt auflisten, Lesen und Schreiben aus.

7. Wählen Sie OK aus, um die Berechtigungsänderungen sofort anzuwenden.

Erstellen einer Registrierungsschlüsseleinstellung, um das Zielverzeichnis für die lokale Überwachung zu konfigurieren

1. Starten Sie „regedit“.

2. Navigieren Sie zu dem entsprechenden CPE-Pfad:

   Version	Datenbank-Engine – Registrierungsschlüssel
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.Your-Instance-Name\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.Your-Instance-Name\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.Your-Instance-Name\CPE

   Version	Analysis Services - Registrierungsschlüssel
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.Your-Instance-Name\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.Your-Instance-Name\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.Your-Instance-Name\CPE

   Version	Integration Services – Registrierungsschlüssel
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\150

3. Klicken Sie im mit der rechten Maustaste auf den CPE-Pfad, und wählen Sie dann Neu aus. Wählen Sie Zeichenfolgenwert aus.

4. Benennen Sie den neuen Registrierungsschlüssel UserRequestedLocalAuditDirectory.

Aktivieren oder Deaktivieren der lokalen Überwachung

Nachdem Sie die Schritte zur Vorkonfiguration abgeschlossen haben, können Sie die lokale Überwachung aktivieren. Verwenden Sie dazu ein Systemadministratorkonto oder eine ähnliche Rolle mit Zugriff auf die Änderungsfunktion für Registrierungsschlüssel, um die lokale Überwachung mithilfe der folgenden Schritte zu aktivieren oder zu deaktivieren.

1. Starten Sie regedit. 

2. Navigieren Sie zu dem entsprechenden CPE-Pfad.

3. Klicken Sie mit der rechten Maustaste auf UserRequestedLocalAuditDirectory, und wählen Sie Ändern aus.

4. Geben Sie den Pfad für die lokale Überwachung ein, um diese zu aktivieren. Beispiel: C:\SQLCEIPAudit\MSSQLSERVER\DB\.

   Um die lokale Überwachung zu deaktivieren, löschen Sie den Wert in UserRequestedLocalAuditDirectory.

5. Schließen Sie regedit.

Das SQL Server-CEIP sollte die Einstellung für die lokale Überwachung sofort erkennen, wenn der Dienst bereits ausgeführt wird. Um den SQL Server CEIP-Dienst zu starten, kann ein Systemadministrator oder eine Person mit der Berechtigung zum Starten oder Beenden von Windows-Diensten die folgenden Schritte ausführen:

1. Starten Sie die Konsole Dienste. Wählen Sie dazu die Windows-Taste + R auf der Tastatur aus, um das Dialogfeld Ausführen zu öffnen. Geben Sie dann services.msc in das Textfeld ein, und wählen OK aus, um die Konsole Dienste zu starten.

2. Navigieren Sie zu dem entsprechenden Dienst.

   • Für die Datenbank-Engine verwenden Sie SQL Server CEIP-Dienst (Name-Ihrer-Instanz).
   • Verwenden Sie für Analysis Services SQL Server Analysis Services CEIP (Name Ihrer Instanz).
   • Für Integration Services:
     • Für SQL 2016 verwenden Sie SQL Server Integration Services CEIP-Dienst 13.0.
     • Für SQL 2017 verwenden Sie SQL Server Integration Services CEIP-Dienst 14.0.
   • Für SQL 2019 verwenden Sie den SQL Server Integration Services-CEIP-Dienst 15.0.

3. Klicken Sie mit der rechten Maustaste auf den Dienst, und wählen Sie „Neu starten“ aus.

4. Vergewissern Sie sich, dass der Dienst den Status Wird ausgeführt hat.

Die lokale Überwachung generiert eine Protokolldatei pro Tag. Die Protokolldateien erhalten Dateinamen im Format <YYYY-MM-DD>.json. Beispiel: 2016-07-12.json. Wenn für den Tag im ausgewählten Verzeichnis eine Datei vorhanden ist, werden die Einträge von der lokalen Überwachung an die Datei angefügt. Andernfalls wird für den Tag eine neue Datei erstellt.

Wartung

1. Um die Nutzung von Speicherplatz beim Schreiben von Dateien durch die lokale Überwachung einzuschränken, richten Sie eine Richtlinie oder einen regulären Auftrag ein. So kann das Verzeichnis für die lokale Überwachung bereinigt werden, um ältere, überflüssige Dateien zu entfernen. 

2. Schützen Sie das Verzeichnis der lokalen Überwachung, sodass nur geeignete Personen darauf zugreifen können. Beachten Sie, dass die Protokolldateien Informationen enthalten, die in Konfigurieren von SQL Server 2016 zum Senden von Feedback an Microsoft erläutert werden. Der entsprechende Zugriff auf diese Datei sollte die meisten Personen in Ihrem Unternehmen daran hindern, die Datei zu lesen. 

Datenwörterbuch für die Ausgabedatenstruktur der lokalen Überwachung

• Die Protokolldateien der lokalen Überwachung besitzen das JSON-Format und enthalten einen Satz von Objekten (Zeilen), die Datenpunkte darstellen, die zu emitTimean Microsoft gesendet werden.
• Jede Zeile entspricht einem bestimmten Schema, das durch schemaVersionidentifiziert wird.
• Jede Zeile stellt eine Ausgabe einer SQLCEIP-Dienstsitzung dar, die als sessionIDidentifiziert wird.
• Zeilen werden in Folge ausgegeben und durch sequenceidentifiziert.
• Jede Datenpunktzeile enthält die Ausgabe für ein queryIdentifier, wobei es sich um eine T-SQL-Abfrage, eine XE-Sitzung oder eine handeln kann, die mit einer Art von Ablaufverfolgung verknüpft ist, die als traceName identifiziert wird.
• queryIdentifiers werden zusammen mit querySetVersiongruppiert und erhalten eine Versionsangabe.
• data enthält die Ausgabe der entsprechenden Abfrageausführung, die queryTimeInTicks benötigt hat.
• FürqueryIdentifiers für T-SQL-Abfragen ist die T-SQL-Abfragedefinition in der Abfrage gespeichert.

Definition und Beispiele für Name-Wert-Paare

Die nachfolgend aufgeführten Spalten stellen die Reihenfolge für die Dateiausgabe der lokalen Überwachung dar. Unidirektionaler Hash mit SHA-256 dient zum Anonymisieren von Werten für eine Reihe der nachfolgenden Spalten. 

Ablaufverfolgungskategorien

Derzeit werden die folgenden Ablaufverfolgungskategorien erfasst:

• SQLServerXeQueries: Enthält Datenpunkte, die über die Sitzung für erweiterte Ereignisse erfasst wurden.
• SQLServerPeriodicQueries: Enthält Datenpunkte, die über regelmäßige Abfragen erfasst werden, die in einer SQL Server-Instanz ausgeführt werden.
• SQLServerPerDBPeriodicQueries: Enthält Datenpunkte, die über regelmäßige Abfragen erfasst werden, die in einer SQL Server-Instanz für bis zu 30 Datenbanken ausgeführt werden.
• SQLServerOneSettingsException: Enthält Ausnahmenachrichten hinsichtlich der Aktualisierung von Schema- und/oder Abfragesätzen.
• DigitalProductID: Enthält Datenpunkte zum Aggregieren anonymisierter (SHA-256) digitaler Produkt-IDs mit Hash von SQL Server-Instanzen.

Dateibeispiele für die lokale Überwachung

Es folgt ein Auszug aus einer JSON-Dateiausgabe der lokalen Überwachung.

[
  {
    "instanceUniqueId": "888770C4D5A8C6729F76F33D472B28883AE518C92E1999888B171A085059FD",
    "isSSEIInstance": "0",
    "schemaVersion": "5",
    "emitTime": "2018-05-04T15:27:59.7031518Z",
    "sessionId": "c3cd1b56-ab61-462f-8363-8881779aa223",
    "correlationId": 0,
    "sequence": 18,
    "clientVersion": "14.0.3025.34 ((SQLServer2017-CU6).180410-0033)",
    "isInternalMachine": "1",
    "operatingSystem": "Microsoft Windows 10 Enterprise",
    "querySetVersion": "14.0.3025.34",
    "traceName": "SQLServerPeriodicQueries",
    "queryIdentifier": "SQLServerProperties.002",
    "data": [
      {
        "Collation": "SQL_Latin1_General_CP1_CI_AS",
        "SqlFTinstalled": "0",
        "SqlIntSec": "1",
        "IsSingleUser": "0",
        "SqlFilestreamMode": "2",
        "SqlPbInstalled": "1",
        "SqlPbNodeRole": "Head",
        "SqlVersionMajor": "14",
        "SqlVersionMinor": "0",
        "SqlVersionBuild": "3025",
        "ProductBuildType": "",
        "ProductLevel": "RTM",
        "ProductUpdateLevel": "CU6",
        "ProductUpdateReference": "KB4101464",
        "ProductRevision": "34",
        "SQLEditionId": "1872460670",
        "IsClustered": "0",
        "IsHadrEnabled": "0",
        "SqlAdvAInstalled": "1",
        "PacketReceived": "422",
        "Version": "Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) \n\tApr  9 2018 18:00:41 \n\tCopyright (C) 2017 Microsoft Corporation\n\tEnterprise Edition: Core-based Licensing (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: )\n"
      }
    ],
    "query": "SELECT\n      SERVERPROPERTY('Collation') AS [Collation],\n      SERVERPROPERTY('IsFullTextInstalled') AS [SqlFTinstalled],\n      SERVERPROPERTY('IsIntegratedSecurityOnly') AS [SqlIntSec],\n      SERVERPROPERTY('IsSingleUser') AS [IsSingleUser],\n      SERVERPROPERTY ('FileStreamEffectiveLevel') AS [SqlFilestreamMode],\n      SERVERPROPERTY('IsPolyBaseInstalled') AS [SqlPbInstalled],\n      SERVERPROPERTY('PolyBaseRole') AS [SqlPbNodeRole],\n      SERVERPROPERTY('ProductMajorVersion') AS [SqlVersionMajor],\n      SERVERPROPERTY('ProductMinorVersion') AS [SqlVersionMinor],\n      SERVERPROPERTY('ProductBuild') AS [SqlVersionBuild],\n      SERVERPROPERTY('ProductBuildType') AS ProductBuildType,\n      SERVERPROPERTY('ProductLevel') AS ProductLevel,\n      SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,\n      SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,\n      RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)),CHARINDEX('.', REVERSE(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)))) - 1) AS ProductRevision,\n      SERVERPROPERTY('EditionID') AS SQLEditionId,\n      SERVERPROPERTY('IsClustered') AS IsClustered,\n      SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,\n      SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [SqlAdvAInstalled],\n      @@PACK_RECEIVED AS PacketReceived,\n      @@VERSION AS Version",
    "queryTimeInTicks": 0
  },
  {
    "instanceUniqueId": "8884F770C4D5A8C6729F76F33D472B28883AE518C92E1999888B171A085059FD",
    "isSSEIInstance": "0",
    "schemaVersion": "5",
    "emitTime": "2018-05-04T15:28:00.9025999Z",
    "sessionId": "c3cd1b56-ab61-462f-8363-8881779aa223",
    "correlationId": 0,
    "sequence": 23,
    "clientVersion": "14.0.3025.34 ((SQLServer2017-CU6).180410-0033)",
    "isInternalMachine": "1",
    "operatingSystem": "Microsoft Windows 10 Enterprise",
    "querySetVersion": "14.0.3025.34",
    "traceName": "SQLServerPeriodicQueries",
    "queryIdentifier": "OsSysInfo.003",
    "data": [
      {
        "LogicalCPUCount": "8",
        "HyperthreadRatio": "8",
        "PhysicalMemoryMB": "32710.902343",
        "SQLServerStartTime": "05/04/2018 08:22:30",
        "AffinityTypeDesc": "AUTO",
        "VirtualMachineType": "0",
        "SocketCount": "1",
        "CoresPerSocket": "4",
        "NumaNodeCount": "1",
        "ContainerType": "0",
        "ContainerDescription": "NONE"
      }
    ],
    "query": "SELECT\n      cpu_count AS LogicalCPUCount,\n      hyperthread_ratio AS HyperthreadRatio,\n      physical_memory_kb/1024.0 AS PhysicalMemoryMB,\n      sqlserver_start_time AS SQLServerStartTime,\n      affinity_type_desc AS AffinityTypeDesc,\n      virtual_machine_type AS VirtualMachineType,\n      socket_count as SocketCount,\n      cores_per_socket as CoresPerSocket,\n      numa_node_count as NumaNodeCount,\n      container_type as ContainerType,\n      container_type_desc as ContainerDescription\n      FROM sys.dm_os_sys_info WITH(nolock)",
    "queryTimeInTicks": 0
  }
]

Häufig gestellte Fragen

Wie lesen DBAs die Protokolldateien der lokalen Überwachung? Diese Protokolldateien werden im JSON-Format geschrieben. Jede Zeile ist ein JSON-Objekt, das Nutzungs-/Diagnosedaten darstellt, die zu Microsoft hochgeladen werden. Die Feldnamen sollten selbsterklärend sein.

Was geschieht, wenn der DBA die Nutzungs- und Diagnosedatensammlung deaktiviert? Es wird keine Datei für die lokale Überwachung geschrieben.

Was geschieht, wenn keine Verbindung mit dem Internet besteht bzw. der Computer sich hinter der Firewall befindet? Nutzungs- und Diagnosedaten von SQL Server werden nicht an Microsoft gesendet. Es wird weiterhin versucht, die Protokolle der lokalen Überwachung zu schreiben, wenn dies ordnungsgemäß konfiguriert wurde.

Wie deaktivieren DBAs die lokale Überwachung? Entfernen Sie den Registrierungsschlüsseleintrag „UserRequestedLocalAuditDirectory“.

Wer kann die Protokolldateien der lokalen Überwachung lesen? Jeder in Ihrem Unternehmen mit Zugriff auf das Verzeichnis für die lokale Überwachung.

Wie verwalten DBAs die Protokolldateien, die in das vorgesehene Verzeichnis geschrieben werden? Das Bereinigen der Dateien im Verzeichnis muss von den Datenbankadministratoren selbst verwaltet werden, damit die Verwendung von zu viel Speicherplatz vermieden wird.

Gibt es einen Client oder ein Tool, mit dem diese JSON-Ausgabe gelesen werden kann? Die Ausgabe kann mit Editor, Visual Studio oder einem beliebigen JSON-Reader gelesen werden. Alternativ können Sie die JSON-Datei lesen und die Daten in einer SQL Server-Instanz analysieren, wie unten dargestellt. Weitere Informationen zum Lesen von JSON-Datei in SQL Server finden Sie unter Importieren von JSON-Dateien in SQL Server mithilfe von OPENROWSET (BULK) und OPENJSON (Transact-SQL).

DECLARE @JSONFile AS VARCHAR(MAX)

-- Read the JSON file into variable 
SELECT @JSONFile = BulkColumn 
FROM OPENROWSET (BULK 'C:\SQLCEIPAudit\MSSQLSERVER\2016-09-08.json', SINGLE_CLOB) MyFile 

-- Check if the JSON file has been read properly and if it's in a JSON format
SELECT 
	@JSONFile LocalAuditOutput, 
	ISJSON(@JSONFile) IsFileInJSONFormat

-- Get the query identifier, query and the data (output of the query)	
SELECT 
	sequence,
	queryIdentifier,
	query,
	data
FROM OPENJSON(@JSONFile) 
	WITH (sessionId VARCHAR(64)
		 ,sequence INT
		 ,queryIdentifier VARCHAR(128)
		 ,query VARCHAR(MAX)
		 ,data NVARCHAR(MAX) AS JSON)
-- Get specific details about the output of "DatabaseProperties.001" query	
SELECT 
	QueryIdentifier,
	DatabaseID,
	CompatibilityLevel,
	IsQueryStoreOn
FROM OPENJSON(@JSONFile) 
	WITH (sessionId VARCHAR(64)
		 ,sequence INT
		 ,queryIdentifier VARCHAR(128)
		 ,query VARCHAR(MAX)
		 ,data NVARCHAR(MAX) AS JSON) 
	CROSS APPLY OPENJSON(data) 
		WITH (	 DatabaseID varchar(128) '$.database_id'
				,CompatibilityLevel varchar(128) '$.compatibility_level'
				,IsQueryStoreOn varchar(128) '$.QS'
			 )
WHERE queryIdentifier = 'DatabaseProperties.001'

Weitere Informationen:

Lokale Überwachung für SSMS-Nutzungs- und -Diagnosedatensammlung