Infrastructure at your Service

Stéphane Haby

SQL Server Tips: Deactivate the Customer Experience Improvement Program (CEIP)

Before SQL Server 2016, you had the possibility to check the case “Send Windows and SQL Server Error Reports….” during the installation if you want to be a part of the Customer Experience Improvement Program (CEIP).
In SQL Server 2016, after the installation, all of the CEIP are automatically turned on.

Why?

SQL Server and SQL Azure share the same code now. On Azure, this service existed since a long time. It collects a large amount of data to automate various tasks and keeps the system functional including support for the following:

  • Incident Management (CRIs, LSIs)
  • Alert management (proactive approach)
  • Automated management via bots (based on alerts)
  • Machine learning / data science
  • Investigating potential new features that can benefit a maximum of clients

As you can see, the idea of integrating the CEIP service with SQL 2016 is to be able to extend this ability to collect “useful” data to Microsoft in order to maximize the impact on future developments.

My Thinking

In this article, I do not want to start a discussion whether to leave this service active or not.
With the guarantees given by Microsoft on the information collected, it is also not a question of security.
The SQL Server Team has published an explicit policy that spells out what and when data is collected: https://www.microsoft.com/EN-US/privacystatement/SQLServer/Default.aspx
As a lot of servers have no internet access, this service is often useless (as data cannot be sent).
In previous versions, I did not install the CEIP on Production environment. So in the same logic, I deactivated this service.

How to Deactivate the CEIP

To disable this service, we need 2 steps. I use PowerShell commands for both.
The first step is to deactivate all CEIP services.

Deactivate all CEIP services

CEIP is present for 3 SQL server services:

  • For SQL Server Engine, you have a SQL Server CEIP service
  • For SQL Server Analysis Service (SSAS), you have a SQL Analysis Services CEIP
  • For SQL Server Integration Service (SSIS), you have a SQL Server Integration Services CEIP service 13.0

CEIP01
As you can see on this picture, we have one CEIP service per instance per service. For the Engine & SSAS and one just for SSIS(shared component).
If you have a look on each service, the patterns for the name are the same:

  • For SQL Server CEIP service, you have a SQLTELEMETRY$<InstanceName>
  • For SQL Analysis Services CEIP, you have a SSASTELEMETRY$<InstanceName>
  • For SQL Server Integration Services CEIP service 13.0 CEIP, you have just SSISTELEMETRY130

CEIP02 I run PowerShell as Administrator and run these following command to have a status of these services:

Get-Service |? name -Like "SQLTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSASTELEMETRY*" | select -property name,starttype,status
Get-Service |? name -Like "SSISTELEMETRY*" | select -property name,starttype,status

CEIP03
We can also be more generic and use this command:

Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status

CEIP04
To disable these services, I do it in 2 steps. The first step is to stop the service and the second step is to disable the service:

  • Stop services
    Get-Service |? name -Like "*TELEMETRY*" | ? status -eq "running" | Stop-Service
  • Disable services
    Get-Service |? name -Like "*TELEMETRY*" | Set-Service -StartMode Disabled

Here you find the “step by step” script:

##################################################
# Disable CEIP services  #
##################################################
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
# Stop all CEIP services
Get-Service |? name -Like "*TELEMETRY*" | ? status -eq "running" | Stop-Service
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
# Disable all CEIP services
Get-Service |? name -Like "*TELEMETRY*" | Set-Service -StartMode Disabled
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
##################################################

CEIP05

My colleague David, send me a “optimize” script to do all-in-one:

##################################################
# Disable CEIP services  #
##################################################
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
Get-Service -name "*TELEMETRY*" | Stop-Service -passthru | Set-Service -startmode disabled
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status
##################################################

All CEIP services are now stopped and disabled. Good job, Stéphane 8-) , but it’s not finished, we have a second step to do…
The second step is to set all CEIP registry keys to 0.

Set all CEIP registry keys to 0

This step is more complex because we have a lot of registry keys. Two parameters have to be set to 0:

  • CustomerFeedback
  • EnableErrorReporting

The first registry key is HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\
CEIP06
The second registry key is HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\
CEIP07
The other registry keys are per instance and per services(Engine, SSAS and SSRS):
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\
CEIP08
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\
CEIP09
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\
CEIP10
To set all these keys to 0, I use “simply” PowerShell Commands:

##################################################
#  Deactivate CEIP registry keys #
##################################################
# Set all CustomerFeedback & EnableErrorReporting in the key directory HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server to 0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0
# *** --> Version of SQL Server (100,110,120,130,140,...)
# For the Engine
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.<instance>\CPE\EnableErrorReporting=0
# For SQL Server Analysis Server (SSAS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.<instance>\CPE\EnableErrorReporting=0
# For Server Reporting Server (SSRS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.<instance>\CPE\EnableErrorReporting=0
# ** --> Version of SQL Server (10,11,12,13,14,...)
##################################################
$Key = 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server'
$FoundKeys = Get-ChildItem $Key -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'
foreach ($Sqlfoundkey in $FoundKeys)
{
$SqlFoundkey | Set-ItemProperty -Name EnableErrorReporting -Value 0
$SqlFoundkey | Set-ItemProperty -Name CustomerFeedback -Value 0
}
##################################################
# Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\***\EnableErrorReporting=0
# *** --> Version of SQL Server(100,110,120,130,140,...)
##################################################
$WowKey = "HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server"
$FoundWowKeys = Get-ChildItem $WowKey | Where-Object -Property Property -eq 'EnableErrorReporting'
foreach ($SqlFoundWowKey in $FoundWowKeys)
{
$SqlFoundWowKey | Set-ItemProperty -Name EnableErrorReporting -Value 0
$SqlFoundWowKey | Set-ItemProperty -Name CustomerFeedback -Value 0
}

As you can see, I use only the EnableErrorReporting key in the Where-Object clause to find the impacted keys. After running this script you have all CEIP registry key set to 0…
Et voila, CEIP is totally deactivated!

To finish, I will thanks all my SQL Server colleagues for their help to have a good vision of this tricky subject. It was also a good discussion internally in our SQL Server Expert Team to define what to do by customer!   :-)

 

Leave a Reply


1 + two =

Stéphane Haby
Stéphane Haby

Delivery Manager