Controllo locale per l'utilizzo di SQL Server e la raccolta dei dati diagnostici (CEIP)

Si applica a:SQL Server su Windows Istanza gestita di SQL di Azure

Introduzione

Microsoft SQL Server include funzionalità che supportano Internet e sono in grado di raccogliere e inviare a Microsoft dati relativi al computer o al dispositivo. Queste informazioni sono denominate informazioni standard del computer. Il componente per il controllo locale della raccolta di dati di diagnostica e utilizzo di SQL Server scrive i dati raccolti dal servizio in una specifica cartella, che rappresenta i dati (log) da inviare a Microsoft. Lo scopo del controllo locale è quello di consentire ai clienti di visualizzare tutti i dati che Microsoft raccoglie con questa funzionalità, per motivi di conformità alle normative o rispetto della privacy.

Per SQL Server 2016 CU2 e CU3 il controllo locale è configurabile a livello di istanza per il motore di database di SQL Server e SQL Server Analysis Services (SSAS). Per SQL Server 2016 CU4, SQL Server 2016 SP1 e versioni successive il controllo locale è abilitato anche per SQL Server Integration Services (SSIS). Per altri componenti di SQL Server installati durante la fase di installazione del programma e per strumenti di SQL Server scaricati o installati successivamente, la funzionalità di controllo locale per la raccolta dei dati di diagnostica e utilizzo non è disponibile.

Osservazioni:

• La rimozione o la disattivazione del servizio SQL CEIP non è supportata.
• La rimozione delle risorse SQL CEIP dal gruppo di cluster non è supportata.

Il rifiuto esplicito della raccolta dei dati di utilizzo e diagnostica di SQL Server è supportato, ma il servizio non può essere rimosso o disabilitato. Per rifiutare esplicitamente la raccolta di dati, vedere Attivazione o disattivazione del controllo locale

Prerequisiti

Per abilitare il controllo locale in ogni istanza di SQL Server sono previsti i prerequisiti seguenti:

1. L'istanza deve essere stata aggiornata a SQL Server 2016 RTM CU2 o versione successiva. Per i Servizi di Integrazione, l'istanza è aggiornata su SQL Server 2016 RTM CU4, SQL Server 2016 SP1 o versioni successive.

2. L'utente deve essere un amministratore di sistema o un ruolo con diritti di accesso per aggiungere e modificare la chiave del Registro di sistema, creare cartelle, gestire la sicurezza delle cartelle e arrestare o avviare un servizio di Windows.

Passaggi di configurazione preliminari all'attivazione del controllo locale

Prima di attivare il controllo locale, un amministratore di sistema deve:

1. Conoscere il nome dell'istanza di SQL Server e l'account di accesso del servizio SQL Server CEIP.

2. Configurare una nuova cartella per i file del controllo locale.

3. Concedi autorizzazioni all'account di accesso del servizio CEIP di SQL Server.

4. Crea un'impostazione di una chiave del Registro di sistema per configurare la directory di destinazione dell'audit locale.

Ottenere l'account di accesso del servizio CEIP di SQL Server

Per ottenere l'account di accesso del servizio CEIP di SQL Server, eseguire i passaggi seguenti

1. Aprire la console Servizi. A tale scopo, premere Tasto WINDOWS+R sulla tastiera per aprire la finestra di dialogo Esegui. Digitare services.msc nel campo di testo e scegliere OK per avviare la console Servizi.

2. Passa al servizio corretto. Ad esempio, per il motore di database individuare SQL Server CEIP service(Nome-istanza). Per Analysis Services, individuare SQL Server Analysis Services CEIP(Nome-istanza). Per Integration Services, individuare servizio CEIP di SQL Server Integration Services.

3. Fare clic con il pulsante destro del mouse sul servizio e scegliere Proprietà.

4. Fare clic sulla scheda Accesso. L'account di accesso è indicato in Questo account.

Configurare una nuova cartella per i file del controllo locale.

Crea una nuova cartella (directory di audit locale) in cui l'audit locale scriverà i log. Ad esempio, il percorso completo della directory del controllo locale per un'istanza predefinita del motore di database sarà: C:\SQLCEIPAudit\MSSQLSERVER\DB\.

Concedere le autorizzazioni all'account di accesso del servizio SQL Server CEIP

1. In Esplora fileindividuare la posizione della nuova cartella.

2. Fare clic con il pulsante destro del mouse sulla nuova cartella e scegliere Proprietà.

3. Nella scheda Sicurezza selezionare l'autorizzazione di gestione Modifica.

4. Selezionare Aggiungi e immettere le credenziali del servizio SQL Server CEIP. Ad esempio, NT Service\SQLTELEMETRY.

5. Selezionare Controlla nomi per convalidare il nome specificato e quindi selezionare OK.

6. Nella finestra di dialogo Autorizzazioni, scegliere l'account usato per l'accesso al servizio SQL Server CEIP e selezionare Visualizzazione contenuto cartella, Lettura e Scrittura.

7. Selezionare OK per applicare immediatamente le modifiche alle autorizzazioni.

Creare un'impostazione di una chiave del Registro di sistema per configurare la directory di destinazione del controllo locale

1. Avviare regedit.

2. Passare al percorso CPE appropriato:

   Versione	Motore di database - Chiave del Registro di sistema
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.Nome-della-tua-istanza\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.Nome-istanza-personalizzato\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.Nome-istanza-personalizzato\CPE

   Versione	Analysis Services - Chiave del Registro di sistema
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.Nome-dell'istanza\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.Nome-dell'istanza\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.Nome-dell'istanza\CPE

   Versione	Integration Services - Chiave del Registro di sistema
   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. Fare clic con il pulsante destro del mouse sul percorso di CPE e scegliere Nuovo. Selezionare Valore di tipo stringa.

4. Denominare la nuova chiave del Registro di sistema UserRequestedLocalAuditDirectory.

Attivazione o disattivazione del controllo locale

Dopo aver completato i passaggi di configurazione preliminari, è possibile attivare il controllo locale. A tale scopo, usare un account di amministratore di sistema, o un ruolo simile con accesso alla modifica delle chiavi del Registro di sistema, per attivare o disattivare il controllo locale seguendo questa procedura.

1. Avviare regedit.

2. Passare al percorso CPE corretto.

3. Fare clic con il pulsante destro del mouse su UserRequestedLocalAuditDirectory e selezionare Modifica.

4. Per attivare il controllo locale, digitare il relativo percorso, ad esempio C:\SQLCEIPAudit\MSSQLSERVER\DB\.

   Per disattivare il controllo locale, svuotare il valore in UserRequestedLocalAuditDirectory.

5. Chiudere regedit.

SQL Server CEIP dovrebbe riconoscere immediatamente l'impostazione di audit locale se il servizio è già in esecuzione. Per avviare il Servizio CEIP di SQL Server, un amministratore di sistema o un utente che dispone delle autorizzazioni necessarie per avviare o arrestare i servizi di Windows può seguire i passaggi seguenti:

1. Aprire la console Servizi. A tale scopo, premere Tasto WINDOWS+R sulla tastiera per aprire la finestra di dialogo Esegui. Digitare services.msc nel campo di testo e scegliere OK per avviare la console Servizi.

2. Passa al servizio corretto.

   • Per motore di database, usare il servizio SQL Server CEIP (Your-Instance-Name).
   • Per Analysis Services, utilizzare Programma Analisi utilizzo software di SQL Server Analysis Services (Nome-dell'istanza).
   • Per Integration Services,
     • Per SQL Server 2016, usa SQL Server Integration Services CEIP service 13.0.
     • Per SQL Server 2017, utilizza SQL Server Integration Services CEIP service 14.0.
   • Per SQL Server 2019, usa SQL Server Integration Services CEIP service 15.0.

3. Fare clic con il pulsante destro del mouse sul servizio e scegliere Riavvia.

4. Verificare che lo stato del servizio sia In esecuzione.

La funzionalità di controllo locale genererà un file di log al giorno. Ai file di log viene assegnato un nome nel formato <YYYY-MM-DD>.json. Ad esempio, 2016-07-12.json. Se nella directory designata esiste già un file per il giorno corrente, i dati verranno aggiunti a tale file. In caso contrario, verrà creato un nuovo file per il giorno corrente.

Manutenzione

1. Per limitare l'utilizzo di spazio su disco per i file scritti dalla funzionalità di controllo locale, configurare criteri o un processo regolare per rimuovere dalla directory del controllo locale i file meno recenti e non necessari.

2. Mettere in sicurezza il percorso della directory di audit locale in modo che sia accessibile solo agli utenti autorizzati. Si noti che i file di log contengono informazioni come descritto in Come configurare SQL Server 2016 per inviare commenti e suggerimenti a Microsoft. È quindi opportuno impostare diritti di accesso a questi file in modo da impedirne la lettura alla maggior parte dei membri dell'organizzazione.

Dizionario dei dati della struttura di dati di output del controllo locale

• I file di log del controllo locale sono in formato JSON, contenente un set di oggetti (righe) che rappresentano i punti dati inviati a Microsoft nella data e all'ora definite da emitTime.
• Ogni riga segue uno schema specifico identificato da schemaVersion.
• Ogni riga corrisponde a un output di una sessione del servizio SQLCEIP identificata come sessionID.
• Le righe vengono inviate in base a una sequenza identificata da sequence.
• Ogni riga di punto dati contiene l'output di un elemento queryIdentifier che può essere una query T-SQL, una sessione XE o un messaggio associato a un tipo di traccia, identificata come traceName.
• Gli elementiqueryIdentifier vengono riuniti in gruppi a cui viene assegnata una versione con querySetVersion.
• L'elemento data contiene l'output dell'esecuzione della query corrispondente, che ha richiesto queryTimeInTicks.
• Gli elementiqueryIdentifier per le query T-SQL contengono la definizione di query T-SQL archiviata nella query.

Definizione di coppie nome/valore ed esempi

Le colonne elencate di seguito rappresentano l'ordine dell'output del file di audit locale. Per rendere anonimi i valori per alcune delle colonne seguenti viene usato un hash unidirezionale con SHA 256.

Categorie di traccia

Attualmente i dati vengono raccolti in base alle categorie di traccia seguenti:

• SQLServerXeQueries: contiene i punti dati raccolti tramite la sessione di eventi estesi.
• SQLServerPeriodicQueries: contiene i punti dati raccolti tramite query periodiche eseguite in un'istanza di SQL Server.
• SQLServerPerDBPeriodicQueries: contiene i punti dati raccolti tramite query periodiche eseguite su un massimo di 30 database in un'istanza di SQL Server.
• SQLServerOneSettingsException: contiene i messaggi di eccezione correlati all'aggiornamento dello schema e/o al set di query.
• DigitalProductID: contiene dati per aggregare l'ID prodotto digitale sottoposto ad hash anonimizzato (SHA-256) delle istanze di SQL Server.

Esempi di file di audit locale

Di seguito è riportato un estratto dell'output di un file JSON di una verifica locale.

[
  {
    "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
  }
]

Domande frequenti

Come fanno gli amministratori di database a leggere i file di log del controllo locale? Questi file di log vengono scritti in formato JSON. Ogni riga corrisponde a un oggetto JSON che rappresenta un elemento di dei dati di diagnostica e utilizzo inviati a Microsoft. I nomi dei campi devono essere facilmente comprensibili.

Cosa accade se l'amministratore di database disabilita la raccolta dei dati di diagnostica e utilizzo? Non verrà scritto alcun file del controllo locale.

Cosa accade se la connettività Internet non è disponibile o il computer si trova all'interno del firewall? I dati di diagnostica e utilizzo di SQL Server non verranno inviati a Microsoft. Se configurato correttamente, il computer proverà comunque a scrivere i log del controllo locale.

Come fanno gli amministratori di database a disabilitare il controllo locale? Rimuovere la voce della chiave di registro UserRequestedLocalAuditDirectory.

Chi può leggere i file di log del controllo locale? Chiunque nella tua organizzazione abbia accesso alla directory di controllo locale.

Come fanno gli amministratori di database a gestire i file di log scritti nella directory designata? Gli amministratori di database devono gestire autonomamente la pulizia dei file nella directory per evitare un utilizzo eccessivo dello spazio su disco.

Quale client o strumento è possibile usare per leggere l'output JSON? L'output può essere letto con il Blocco note, in Visual Studio o nel lettore JSON desiderato. In alternativa, è possibile leggere il file JSON e analizzare i dati in un'istanza di SQL Server, come illustrato di seguito. Per altre informazioni su come leggere il file JSON in SQL Server, visitare Importing JSON files into SQL Server using OPENROWSET (BULK) and OPENJSON (Transact-SQL)(Importazione di file JSON in SQL Server mediante OPENROWSET (BULK) e 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'

Vedi anche

Controllo locale per la raccolta di dati di diagnostica e utilizzo di SSMS