Saturday, February 24, 2018

Customer Experience Improvement Program (CEIP) service - How to Disable

If we Install SQL Server 2016 automatically turns on all "CEIP" service.
CEIP: Customer Experience Improvement Program
SQL CEIP is only used for usage data collection. It is not used for any other purpose.
These CEIP elements report back to Microsoft about various aspects of your installation experience as well as your feature usage.

WHY CEIP?
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

Steps :
How to Deactivate the CEIP
How to set all CEIP registry keys to 0

How to configure SQL Server 2016 or later to send feedback to Microsoft :  
Click MS Link for more details

(1) How to Deactivate the CEIP

----------------------------------------
CEIP is present for 3 SQL server services:
DEngine: SQL Server CEIP service
SSAS:    SQL Analysis Services CEIP
SSIS:    SQL Server Integration Services CEIP service 13.0

1.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

OR
--This will display all telemetry services(CEIP)
Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status

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

Step by step  scripts:
# 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

OR
--All in once script (All CEIP services will stop and disable)
# 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

(2)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

Example: registry keys
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\130\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\CPE\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\CPE\EnableErrorReporting=0
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\CustomerFeedback=0
HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\Microsoft SQL Server\130\EnableErrorReporting=0
Note: Search-and-replace MSSQLSERVER with your instance name.
      If any of the keys don't exist, then that's OK, because you apparently didn't install that feature.


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**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL**.\CPE\EnableErrorReporting=0
# For SQL Server Analysis Server (SSAS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSAS**.\CPE\EnableErrorReporting=0
# For Server Reporting Server (SSRS)
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.\CPE\CustomerFeedback=0
# Set HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSRS**.\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
}

Note: As you can see,  use only the EnableErrorReporting key in the Where-Object clause to find the impacted keys. After running this script we have all CEIP registry key set to 0