SqlSetup: Cumulative Update

[bodunjo]

I'm trying to install the SQL server cummulative update. It doesn't seem to do anything and it didn't fail either.
Although, if i run just the powershell script in the set script, it runs ok but just doesn't work well with the set script
Is there anything i'm doing wrong here? Can someone point me towards any resource or a better way of doing theis.
NB. I also used the Package resource, but that won't work if the product id is not known.
Does anyone know how to find the product id of the CU updates o/patches in general.
Configuration UpdateWindowsWithPath
{
Import-DSCResource -ModuleName xWindowsUpdate

Node 'NodeName'
{  

<#
xHotfix HotfixInstall
{
Ensure = "Present"
Path = "\Sharepath\SQL Team\SQLInstalls\SQLServer2014SP2-KB3171021-x64-ENU.exe"
Id = "KB3171021"
}
#>
}

Script SQLPatch
    {

         GetScript =
        {
            # Do Nothing 
            
        }

        SetScript = 
        {
       
        C:\DBA\SQLDSC\Patch.ps1 #references 

"C:\DBA\SQLDSC\SQLServer2016-KB3205052-x64.exe /qs /IAcceptSQLServerLicenseTerms /Action=Patch /AllInstances"

in another file

        }

        TestScript = 
        {
        
            $false
        }
        #Dependson = "[xSQLServersetup]SQL2016"

    }

}

cd C:\Temp
UpdateWindowsWithPath -outputpath C:\Temp\UpdateWindowsWithPath

Start-DscConfiguration -Path C:\Temp\UpdateWindowsWithPath -Wait -Force -Verbose

[Zuldan]

@bodunjo here is another way to do it.

# Patch SQL Server
switch ($Node.SQLVersion)
{
    '11' { # SQL 2012 CU 4
        $SQLPatchFile = 'SQLServer2012-KB3165264-x64.exe'
        $SQLPatchName = 'Hotfix 6540 for SQL Server 2012 (KB3165264) (64-bit)'
    }
    '12' { # SQL 2014 CU 1
        $SQLPatchFile = 'SQLServer2014-KB3178925-x64.exe'
        $SQLPatchName = 'Hotfix 5511 for SQL Server 2014 (KB3178925) (64-bit)'
    }
    '13' { # SQL 2016 SP1
        $SQLPatchFile = 'SQLServer2016SP1-KB3182545-x64-ENU.exe'
        $SQLPatchName = 'Service Pack 1 for SQL Server 2016 (KB3182545) (64-bit)'
    }
}

xRemoteFile SQLServerPatchFile
{
    DependsOn            = '[xSQLServerSetup]SQLServerSetup'
    PsDscRunAsCredential = $Credential
    Uri                  = "$($Node.SQLSourcePath)\$SQLPatchFile"
    DestinationPath      = "C:\Patches\$SQLPatchFile"
}

xPackage SQLServerPatchInstall
{
    DependsOn  = '[xRemoteFile]SQLServerPatchFile'
    Ensure     = 'Present'
    Name       = $SQLPatchName
    ProductId  = ''
    Path       = "C:\Patches\$SQLPatchFile"
    Arguments  = "/action=patch /instancename=$($Node.SQLInstanceName) /quiet /IAcceptSQLServerLicenseTerms"
    ReturnCode = 0,3010
}

[bodunjo]

@Zuldan I tried this but still facing the same issues as shown below.
I believe, this is because i don't know what the the guid for the product id is.
The productid is a mandatory property. Is there any way I can find the product id of the patches to satisfy the property before proceeding?
Or is there any other way i can bypass this product id?

WARNING: [Server01]: [] The TEST operation will be carried against a pending configuration since the latest configuration has not converged yet.
Could not find mandatory property Name. Add this property and try again.
+ CategoryInfo : ObjectNotFound: (root/Microsoft/...gurationManager:String) [], CimException
+ FullyQualifiedErrorId : MI RESULT 6
+ PSComputerName : localhost

[Zuldan]

For SQL patches the ProductID should set to an empty string and make sure you use the exact name of the patch. EG 'Service Pack 1 for SQL Server 2016 (KB3182545) (64-bit)'.

[bodunjo]

@Zuldan, how about the installation of the cumulative updates? Also, can I run the the updates/ patches on all the instances in one go, rather than specifying an individual instances?

…

Sent from my iPhone On 17 Feb 2017, at 17:02, Zuldan <[email protected]<mailto:[email protected]>> wrote: For SQL patches the ProductID should set to an empty string and make sure you use the exact name of the patch. EG 'Service Pack 1 for SQL Server 2016 (KB3182545) (64-bit)'. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#375 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ASRVCvlNBYUguSyD4M0dNkxCI1m6YVTFks5rddJWgaJpZM4L7N-g>.

[johlju]

You should also be able to install CU during the installation with xSQLServerSetup. I install CU1 for SQL Server 2016 this way. I create a folder 'Updates' in the root of the media folder and put the .exe of the CU in the 'Updates' folder (in my case SQLServer2016-KB3164674-x64.exe). The Updates folder could be anywhere as long as it is accessible by the setup account, I guess, but have not tried that.
Then add the following to lines to xSQLServerSetup configuration.

UpdateEnabled = 'True'
UpdateSource = '.\Updates'

[bodunjo]

@johju, I never thought about that. Will give it a try. May I ask, how you Install your SSMS for SQL 2016 installation and what features are supported? I'm using version xsqlserver 5.0 but not sure it it supports 2016. I tend to install the features and then install the SSMS separately.
Has this been improved?

[johlju]

SSMS is not included in SQL 2016. So you need to install it separately from https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms.
There is currently no support to install the stand alone SSMS by using a resource in xSQLServer. If you find a solution to do a silent install, please post that solution in issue #125 since that could be helpful to create support for it.

[bodunjo]

@johlju i tried that using the below and it failed with the following error message:

SQLUSERDBDIR="H:\SQLData\InfraDBA" /QUIET="True" /SQLTEMPDBLOGDIR="L:\SQLTempLog\InfraDBA" /AGTSVCACCOUNT="Domain\Server01spAG" /SQLBACKUPDIR="H:\SQLBackup\InfraDBA" /FTSVCPASSWORD="********" /IACCEPTSQLLICEN
SETERMS="True" /SECURITYMODE="SQL" /ACTION="Install" /AGTSVCSTARTUPTYPE="Automatic" /SQLSVCPASSWORD="********" /UPDATESOURCE="\\ctpshd01vmfps01\SQL Team\SQLInstalls\SQLInstaller\Software\CU_Updates" /INSTALLSQLDATADIR="H:
\SQLData\InfraDBA" /SQLTEMPDBDIR="H:\SQLTemp\InfraDBA" /SQLCOLLATION="Latin1_General_CI_AS_KS_WS" /SAPWD="********" /UPDATEENABLED="True" /SQLUSERDBLOGDIR="L:\SQLLogs\InfraDBA" /INSTANCENAME="INFRADBA" /AGTSVCPASSWORD="**
******" /INSTANCEDIR="C:\Program Files\Microsoft SQL " /SQLSYSADMINACCOUNTS="Domain\dl_sqladmins" /FTSVCACCOUNT="Domain\Server01spFT" /SQLSVCACCOUNT="Domain\Server01spDE"
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-31: Process matching path 'K:\setup.exe' started in process ID 28856
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-34: Using path: K:\setup.exe
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] Perform operation 'Query CimInstances' with following parameters, ''queryExpression' = SELECT *
 FROM Win32_Service WHERE Name = 'MsDts130','queryDialect' = WQL,'namespaceName' = root\cimv2'.
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] Operation 'Query CimInstances' complete.
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-34: Features found: 'IS'
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-34: Unable to find feature 'SQLENGINE' among the installed features: 'IS'
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-34: Unable to find feature 'FULLTEXT' among the installed features: 'IS'
VERBOSE: [Server01]:                            [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA] 2017-02-20_11-49-34: Unable to find feature 'REPLICATION' among the installed features: 'IS'
VERBOSE: [Server01]: LCM:  [ End    Set      ]  [[xSQLSetup]SQL2016::[TestScript]InfraDBA::[TestScript]InfraDBA]  in 4.4990 seconds.
PowerShell DSC resource MSFT_xSQLSetup  failed to execute Set-TargetResource functionality with error message: Test-TargetResource returned false after calling set. 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : Server01
 
VERBOSE: [Server01]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : Server01

xSQLServersetup SQL2016

     {
        InstanceName = $InstanceName 
        Setupcredential = $Setupcredential
        SApwd = $SAPwd
        SourcePath = $SourcePath
        #SourceFolder = ""
        FEATURES= $FEATURES #"SQLENGINE" #REPLICATION,FULLTEXT,DQ,POLYBASE,ADVANCEDANALYTICS,TOOLS,IS,AS,RS,DQC,MDS,SQL_SHARED_MR"
        InstallSharedDir = "C:\Program Files"
        InstallSharedWOWDir = "C:\Program Files (x86)"
        InstanceDir = "C:\Program Files\Microsoft SQL Server"
        InstallSQLDataDir = "H:\SQLData\$InstanceName"
        SQLUserDBDir = "H:\SQLData\$InstanceName"
        SQLUserDBLogDir = "L:\SQLLogs\$InstanceName"
        SQLTempDBDir = "H:\SQLTemp\$InstanceName"
        SQLTempDBLogDir = "L:\SQLTempLog\$InstanceName"
        SQLBackupDir = "H:\SQLBackup\$InstanceName"
        ASDATADIR="H:\OLAP\Data\$InstanceName"
        ASLOGDIR="L:\OLAP\Log\$InstanceName"
        ASBACKUPDIR="H:\OLAP\Backup\$InstanceName"
        ASTEMPDIR="H:\OLAP\Temp\$InstanceName"
        ASConfigDir = "C:\Program Files"
        UpdateSource = "\\sharepath\SQL Team\SQLInstalls\SQLInstaller\Software\CU_Updates" # "MU" # set this to windows update, otherwise it will use the default and cause the setup to crash
        UpdateEnabled = 'True'
        SuppressReboot = $False
        ForceReboot = $False
        PsDscRunAsCredential = $PsDscRunAsCredential
        SQLSvcAccount = $SQLSvcAccount
        FTSvcAccount = $FTSvcAccount
        RSSvcAccount = $RSSvcAccount
        ASSvcAccount = $ASSvcAccount
        AGTSVCACCOUNT= $AGTSVCACCOUNT
        ISSvcAccount = $ISSvcAccount
        ASSysAdminAccounts = "CTCPLC\dl_sqladmins"
        ASCollation = $ASCollation
        SQLCollation = $SQLCollation
        SQLSYSADMINACCOUNTS= "CTCPLC\dl_sqladmins"
        SecurityMode = "SQL" #"Windows"
        #PID =
        Dependson = "[WindowsFeature]NetFramework45Core"
          }

I used to set the UpdateSource = "MU" and this used to work.
What's the difference between setting the path for the CU updates and inserting "MU" in its place?

[johlju]

"MU" means Microsoft Update, which is the default (by setup.exe) when UpdateEnable = $true.

Only one backslash before the server name in this output you submitted. Just want to verify that you missed one when you removed sensitive information?

/UPDATESOURCE="\ctpshd01vmfps01\SQL Team\SQLInstalls\SQLInstaller\Software\CU_Updates"

Could you please look in the SQL Server ERROR LOG to see what went wrong?

Also the CU_Updates only contain the CU you want to apply, no other CU's? If so, could you try with only the SQL 2016 CU in the folder (if it is SQL2016 you are trying to install).

I have never tried with a UNC path in the UpdateSource. But there is no mention that is does not support UNC path in this article https://msdn.microsoft.com/en-us/library/dd638066.aspx.

And in this article it does say it supports an UNC share

Specify the location where SQL Server setup will obtain product updates. The valid values are "MU" to search Microsoft Update, a valid folder path, a relative path such as .\MyUpdates or a UNC share. By default, SQL Server setup will search Microsoft Update or a Windows Update Service through the Windows Server Update Services.
https://msdn.microsoft.com/en-us/library/ms144259.aspx#Anchor_2

[shreyaschavda]

can u please provide me final code which you have to install updates

[johlju]

@shreyaschavda Depends on what source you want updates from. And what kind of updates you ar looking to install.

This should get updates from the folder Updates from the root of the installation media.

UpdateEnabled = 'True'
UpdateSource = '.\Updates'

This should get updates from Microsoft Update. This probably won't install CU's.

UpdateEnabled = 'True'

More information here.

[ctabe]

Here is a silent PS script I use for SSMS:

Type: Enter-PSSession –ComputerName

Type: Cd \ to directory with “SSMS.exe” (D:\SQL2016\17)

Type: .\SSMS-Setup-ENU.exe /q /IAcceptSQLServerLicenseTerms /Action=Install

[johlju]

Closing this one as it seems this have been answered and resolved. If that is not the case then please reopen or ask this to be reopened.

[cnilak]

DSC resource MSFT_PackageResource failed to execute Set-TargetResource functionality with error
message: Package from************************* was installed, but the specified
ProductId and/or Name does not match package details

Is there a way to make ProductID not required as even after the install the DSC config status remains as failed, when we leave the product ID blank.

[johlju]

@cnilak That is a resource that is not part of this resource module. Please submit an issue to xPSDesiredStateConfiguration repository.