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

SQL Server 2017 Installation on Linux steps

SQL Server 2017  Installation on Linux and follow below steps

SQL download and install
*************************
$ curl https://packages.microsoft.com/config/rhel/7/mssql-server.repo > /etc/yum.repos.d/mssql-server.repo

sudo yum install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

$ sudo systemctl enable mssql-server
$ sudo systemctl start mssql-server


$ sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
$ sudo firewall-cmd --reload

$ rpm -qa | grep mssql


--Use the following command to view the status of the SQL Server service.
$ sudo systemctl status mssql-server -l


*******************************
SQLCMD/BCP install

$ sudo su
# curl https://packages.microsoft.com/config/rhel/7/prod.repo >
/etc/yum.repos.d/prod.repo
# exit

$ sudo yum -y install mssql-tools

*************************
sqlcmd Connection

$export PATH=$PATH:/opt/microsoft/bin
$export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/opt/microsoft/lib64
$/opt/mssql-tools/bin/sqlcmd -H -U -P  

Powershell script to re-start SQL Server services for multiple servers

Step1: Create folder D:\Test\
Step2:Open notepad and add all servers one after one as per below in "D:\Test\ServersList.txt" file
Server1
Server2
Server3
...etc

Step2: Create Test.ps1 powershell script file with below code in "D:\Test" folder
D:\Test\Test.ps1 file code:

Get-Content D:\Test\ServersList.txt | % {

 $Server=$_

 Write-Host  "Server Name " : $Server

If(Test-Connection $Server -Quiet)
{

 $Srv=Get-Service -Name MSSQLSERVER -ComputerName $Server

  If ($Srv.Status -eq "Running")
   {
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Stop-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name MSSQLSERVER | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name MSSQLSERVER | Select name, status

Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Restart-Service -Verbose -Force
Get-Service -ComputerName $Server -Name SQLSERVERAGENT | Select name, status

    } Else {
      Write-Host  "SQL Server Service not running"
    }
 } else {
      Write-Host  "Failed to connec to servers:" $Server
  }| Export-Csv D:\Test\FailedConnLog.txt -NoTypeInformation
} | Export-Csv D:\Test\OutputLog.txt -NoTypeInformation


OR
$SList= Get-Content "D:\Test\ServersList.txt"
ForEach ($Server in  $SList)
{
  Write-Host $Server
#### Write code here ######
}

Command to execute or schedule in job:
D:\\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\Test.ps1

SQL Server 2016 -- New features

New Features in SQL Server 2016
------------------------------------------------
Always Encrypted
Dynamic Data Masking
JSON Support --Java Script Object Notation
Multiple TempDB Database Files
PolyBase
Query Store
Row Level Security
In-database R language support: Microsoft acquired Revolution Analytics
       --R Comes to SQL Server-- advance analytics Big data & SQL Server
Stretch Database
Temporal Table
In-Memory Enhancements --supporting foreign keys, check and unique constraints and parallelism.                                               -- Tables up to 2TB are now supported.
Parallel Data Warehouse Appliance, for more easily managing relational and non-relational data
Enhanced server management for Master Data Services
Enhanced hybrid backup to Azure and faster restores to SQL Server in Azure virtual machines
Improvements on AlwaysOn Availability Groups:
  --3 sync replicas, up from 2 in SQL 2014.
  --Listener will be able to do round-robin load balancing of read-only requests on multiple secondaries
Improvements on Columnstore Indexes



Additional details: sql server 2016 Features

CSV file data load into SQL Server table Using BCP and schedule job

Using BCP, CSV file data load into SQL Server table

--1.Create table "BCPDataLoad" with required columns
example:
CREATE TABLE BCPDataLoad
(
id int,
Name varchar(20)
);

2.LoadData.txt
--Loadable data keep in "E:\Data\LoadData.txt" file
--data should be id,name columns wiht comma separately
example
id,name
11,aaaa
22,bbb
33,ccc

3.Create BAT file as "BCPLoad.bat" with below
(OPen notepad and copy paste below script and save as
 file name: BCPLoad.bat
 save as type: allfiles

--BCPLoad.bat
set log=E:\Data\BCPLoad_Log.txt

echo %date%. %time% before BCP data load >>%log%

REM  change the Database_Name" below as your actual database name

bcp Database_Name.dbo.BCPDataLoad_Name in E:\Data\LoadData.txt -c -t, -S ServerName -T >>%log%

echo %date%. %time% After BCP data load >>%log%

4.Schedule job using SQL Server agent job or any 3rd party tools for "BCPLoad.bat" file

OR
Manually run BAT file: You can run manually "BCPLoad.bat" file,
   go start ,right click run "cmd" enter,cd "E:\Data" (go to e drive with data folder)
   in commandline window E:\Data\BCPLoad.bat hit enter, then the data load will finish.

5.Check the logs in "E:\Data\BCPLoad_Log.txt" location
6.Check the data load in "BCPDataLoad" table
   select * from BCPDataLoad

output : should be
id,name
11,aaaa
22,bbb
33,ccc

Batch script -- using FOR loop for multiple servers list to run SQL script

1.Save all servers in "Serverlist.txt" file in "E:\batch" folder.
example: Serverlist.txt
server1
server2
server3

2.Save "sqlquery.sql" file in "E:\batch" folder

3.Save below script as Test.bat in "E:\batch" folder

set log=E:\batch\Test_log.txt
echo start for loop >>%log%
for /F "tokens=*" %%S in (E:\batch\Serverlist.txt) do (
e:\ give here sqlcmd location\sqlcmd -E -S %%S -i "E:\batch\sqlquery.sql"  -s "," -h-1 -W >>"E:\batch\output.csv"
IF NOT ERRORLEVEL 0 GOTO :ERR
)
echo End for loop>>%log%
echo !Success. sqlcmd ok. >>%log%

GOTO :EOP

:ERR
echo !Error. sqlcmd failed >>%log%

:EOP

4.Output will store in "E:\batch\output.csv" file.

Note:Include SET NOCOUNT ON and SET NOCOUNT OFF in your "sqlquery.sql" to avoid info message in your output.

Poweshell/Batch script execute using sql agent job

Using Batch file 
----------------
1.Create D:\Test\TestDBFile.bat file

set log=D:\Test\TestDBFile_Log.txt

Echo %date%.%time%.... Before  rename>>%log%

Rename D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf >>%log%
Rename D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf  >>%log%

Echo %date%.%time%.... After  rename>>%log%

First test "TestDBFile.bat" execute , through command prompt,
if it working then schedule sql agent job( for sql agent job drop down select "Operating System(CMDExec)" option from step in sql agent.
command (give):D:\Test\TestDBFile.bat

2.Using Powershell 
-------------------------

1.Create D:\Test\test.ps1 file with below details

Rename-Item D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf ;
Rename-Item D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf;

2. You can execute with below:
D:\fullpath\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\test.ps1

3.You can schedule sql agent job.


OR

3.Powershell other way using batch script

1.Create D:\Test\test.ps1 file with below details

Rename-Item D:\SQLDATA\SQLDAT\Test.mdf TestOld.mdf ;
Rename-Item D:\SQLLOG\SQLLOG\Test_log.ldf TestOld_log.ldf;

2. Crete D:\Test\TestPowershellscript.bat file

set Pslog=D:\Test\TestPowershellDBFile_Log.txt

Echo %date%.%time%.... Before  rename file using PS>>%PSlog%
D:\fullpath\powershell.exe -Noninteractive -Inputformat none -Executionpolicy Bypass -Command D:\Test\test.ps1

Echo %date%.%time%.... After   rename file using PS>>%log%

3.First test "TestPowershellscript.bat " execute , through command promt,
if it working then schedule sql agent job( for sql agent job drop down selact "Operating System(CMDExec)" option from step in sql agent.
command (give):  D:\Test\TestPowershellscript.bat