Algorithm to Automate SQL Server Collation change

Define Collation: Collation is a way of recognizing different languages, their ordering the characters and accents. It determines how the data is sorted and compared. There are following collation options provided by SQL Server to support it:

1. Case Sensitivity:
2. Accent Sensitivity:
3. Kana Sensitivity:
4. Width Sensitivity:

Collation can be specified at following levels in any SQL Server:

1. Server Level
2. Database Level
3. Column Level of a database table

Changing Server Level Collation:

There are two approaches to change Server Level Collation:

1. Documented approach (supported by Microsoft):
2. Undocumented approach (not supported by Microsoft):

In the undocumented approach of server collation change, following are the steps:

1. Detach all the databases of SQL Server
2. Take backup of SQL Jobs, logins and users if required.
3. Stop the service of SQL Server
4. Execute following command to change the collation:  sqlservr -m -T4022 -T3659 -s"SQL2017" –q "SQL_Latin1_General_CP1_CI_AI"
5. After execution of above command, after successfully collation change you would see following message:
6. “The default collation was successfully changed.”
7. After getting above message, user can stop further processing by pressing Ctrl+C button.
8. Start the Service of SQL Server, which was stopped in step 3.
9. Check the server collation now, it should be changed to SQL_Latin1_General_CP1_CI_AI
10. Attach the databases detached in step 1.
11. Check SQL Jobs, logins, users and other artifacts if they are there and behaving the same way as it was before the collation change operation.
    

Automating undocumented approach of collation change using powershell

Algorithm with following steps can be implemented:

1. Create a SQL Script to detach and attach all the databases of SQL Server. Execute the detach script using powershell.
2. Stop the service of SQL Server using below command:
3. STOP-SERVICE -NAME $SQLServiceName –Force
4. Get the location of SqlServr.exe file and execute command to change the collation:
5. $CollationChangeProcess=Start-Process -FilePath $SQLRootDirectory -ArgumentList "-c -m -T 4022 -T 3659 -s $JustServerInstanceName -q $NewCollationName" -NoNewWindow –passthru
6. Using loop check the event viewer application logs for SqlServr.exe continuously, to check when it generates following log message: The default collation was successfully changed.
7. $log=Get-WinEvent -FilterHashtable @{logname='application'; providername=$SQLServiceName; starttime = $StartDateTime} | Where-Object -Property Message -Match 'The default collation was successfully changed.'
8. Once the log message is generated, kill the process using below command:
9. Stop-Process -ID $CollationChangeProcess.ID
10. Start the service of SQL Server using below command:
11. START-SERVICE -NAME $SQLServiceName
12. Execute the attach script using powershell.

I have posted additional information here:
https://stackoverflow.com/questions/58337969/change-instance-level-collation-of-sql-server-using-powershell?noredirect=1#comment103033939_58337969