Starting with SQL Server 2016, when you install SQL Server it also installs the CEIP (Customer Experience Improvement Program) Services, also known at Telemetry. In previous versions of SQL Server this was optional, but as of SQL Server 2016 it is automatically installed and enabled by default.
Now whether this is good or bad is up for debate, and I encourage you read Brent Ozar’s post on What Queries Does Microsoft’s Telemetry Service Run On Your SQL Server to determine for yourself if you want this service running or not. I personally like to turn it off, mainly for a couple of reasons. First, many of my SQL Servers do not have access to anything outside of our internal network, so it’s pointless for them to gather this telemetry data and try to send it to Microsoft. Second, I deal with confidential data and do not want to run the risk of anything accidentally getting shipped offsite.
How Do I Disable Telemetry?
There are a couple of ways to turn off the CEIP Service. If you only need to do it on a few servers, you can follow this SQL Server Central article on using the Error and Usage Reporting Application that installs with SQL Server. Simply uncheck a few boxes, click “OK”, and you’re done.
However, if you have multiple SQL Servers you may want to consider using PowerShell to handle this for you. Basically you’ll need to stop/disable the Telemetry service, as well as edit some values in the registry to turn off CEIP. The beauty of doing this in PowerShell is that you can give it a list of servers and it will go do all of them for you. Here is the PowerShell script that I use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
$server = 'Server01' ,'Server02','Server03','Server04' # Stop all CEIP services invoke-command -computername $server {Get-Service |? name -Like "*TELEMETRY*" | ? status -eq "running" | Stop-Service} # Disable all CEIP services invoke-command -computername $server {Get-Service |? name -Like "*TELEMETRY*" | Set-Service -StartMode Disabled} # Check service status (should see "stopped" and "disabled" invoke-command -computername $server {Get-Service |? name -Like "*TELEMETRY*" | select -property name,starttype,status} # Disable "Error Reporting" and "Customer Feedback" Registry Values # invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'| Set-ItemProperty -Name EnableErrorReporting -Value 0} invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'| Set-ItemProperty -Name CustomerFeedback -Value 0} invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'| Set-ItemProperty -Name EnableErrorReporting -Value 0} invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'| Set-ItemProperty -Name CustomerFeedback -Value 0} # Check registry values (should see 0 for "EnableErrorReporting" and "CustomerFeedback") invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'} invoke-command -computername $server {Get-ChildItem 'HKLM:\SOFTWARE\Wow6432Node\Microsoft\Microsoft SQL Server' -Recurse | Where-Object -Property Property -eq 'EnableErrorReporting'} |
There are a couple of things to point out here:
- Edit the registry at your own risk! To quote Brent in the above linked article, “editing the registry, much like the Wu-Tang Clan, is nothing to, uh, mess around with.”
- This only works on “Paid For” versions of SQL Server. You cannot disable this functionality in Developer, Enterprise Evaluation, and Express editions of SQL Server.
As always, test this in a non-production environment first. It’s never a good idea to download some random script off of the internet and run it in production.