Invoke-Command - SQL collation change function

Hi,

I am creating a script to automatically configure SQL servers in Azure from one of the base Azure SQL VM templates. All of my code works well besides changing the default collation of the instance. The PowerShell code works but the problem is that the command only runs the setup.exe command correctly if I have an open RDP session to the server. I'm trying to figure out why it won't run without the RDP session and either work around it or change the code to something that will work consistently. For now, I have it open an RDP session but this method won't work in future when I fully automate the scripts.

Here is my code for the ChangeCollation function:

FUNCTION ChangeCollation
{
    param
    (
    [Parameter(Mandatory=$true)]$DeploymentName,
    [Parameter(Mandatory=$true)]$AdminName,
    [Parameter(Mandatory=$true)]$AdminPassword,
    [Parameter(Mandatory=$true)]$Collation
    )
    Write-Host ("Rebuilding instance collation, setting to $Collation ... ") -ForegroundColor Green

    $setup = (Start-Process -FilePath "C:\SQLServer_12.0_Full\Setup.exe" -ArgumentList "/QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=$DeploymentName\$AdminName /SAPWD=$AdminPassword /SQLCOLLATION=$Collation" -NoNewWindow -Wait -PassThru).ExitCode
            If ($setup -eq 0) { Write-Host ("Successfully set collation to $Collation") -ForegroundColor Green}	 
            If ($setup -ne 0) { Write-Host ("Failed to set collation to $Collation") -ForegroundColor Red}
}

I execute it as follows, its in a basic retry loop which tries restarting the services or server if the command fails:

try { Invoke-Command -ConnectionUri $uri.ToString() -Credential $credential -ScriptBlock ${function:ChangeCollation} -ArgumentList $DeploymentName, $AdminName, $AdminPassword, $Collation $NewCollation = Invoke-Command -ConnectionUri $uri.ToString() -Credential $credential -ScriptBlock {Invoke-SQLCmd "SELECT CONVERT (varchar, SERVERPROPERTY('collation'));"} $NewCollation = $NewCollation.Column1 If ($Collation -ne $NewCollation) { Throw "Collation not changed exception!" } $Stoploop = $true }

Catch

{

.......

}

If I have an RDP session open as the local admin which is the same account as I am running the invoke-command with, the setup.exe runs through just fine. Without an RDP session, Invoke waits a minute or two then exits. I can see from the logs on the server it hasn't tried to change the collation of the instance.

I've tried additional switches on the start-process, like -LoadUserProfile or without -nonewwindow but they made no difference. I have also tried changing the collation with the command sqlservr, seems to work sometimes but its inconsistent and I don't get the returned result saying that it completed successfully so the code just hangs even after the change.

Does anyone have any idea why an exe wouldn't work via an invoke without the RDP session? Or alternatively a better way to change the collation of the database which is consistent?




  • Edited by ssjaronx4 Wednesday, March 25, 2015 12:01 PM
  • Moved by Bill_Stewart Monday, April 27, 2015 8:29 PM Move to more appropriate forum
March 25th, 2015 11:59am

Hi,

Thanks for posting here.

The default collation in SQL Azure is SQL_Latin1_General_CP1_CI_AS.

It is not possible to change the collation in SQL Azure. You can however query the collation collection with

SELECT SERVERPROPERTY('Collation')
SELECT DATABASEPROPERTYEX('TestDB', 'Collation')

These queries will both return only 1 result, as SQL_Latin1_General_CP1_CI_AS

You can refer the limitation on SQL Azure which does not support Collation change.

Ref: https://msdn.microsoft.com/en-us/library/ee336281.aspx

The default database collation used by Microsoft Azure SQL Database is SQL_LATIN1_GENERAL_CP1_CI_AS, where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AS is accent-sensitive.

When using an on-premise SQL Server, you can set collations at server, database, column, and expression levels. Microsoft Azure SQL Database does not allow setting the collation at the server level. To use the non-default collation with Microsoft Azure SQL Database, set the collation with the Create Database Collate option, or at the column level or the expression level. SQL Database does not support the Collate option with the Alter Database command.  By default, in SQL Database, temporary data will have the same collation as the database. For more information about how to set the collation, see COLLATE (Transact-SQL) in SQL Server Books Online.

Hope this helps.

Girish Prajwal

Free Windows Admin Tool Kit Click here and download it now
May 5th, 2015 1:40pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics