SqlSsmsSetup: Resource proposal - download and Install new SQL Server Management Studio

[StefanSchoof]

Since the SQL Server Management Studio (SSMS) is in the 2016 release a standalone software with a own download, would it be nice to have a resource that can download and install the studio.

[johlju]

Nice suggestion, thought about this when issue #91 was raised. We need a way to get the new SQLServer module on to the server as long as it is not a standalone module (like in PowerShell Gallery).

Is this better as a standalone resource as you suggest, or could it be implemented into the xSQLServerSetup?
Thinking if one chooses SSMS as feature and together with a new parameter, provide a path to binaries for the new standalone SSMS. Then instead of installing SSMS from SQL Server media, the new SSMS is installed instead. But maybe that is to much of logic in the xSQLServerSetup resource? Or maybe there are instances where one needs both installed on the server? (can both be installed? I haven't tried that)

When this is done, we have a good starting point for fixing issue #91 and create tests for all resources to use the new SQLServer module for test as well. :)

[StefanSchoof]

Mixing they both in one resource is nice with upgrade from previous SQL Version. Also is is simple to add the ssms Feature string.

But as I understand the release cycles should separated. If this is one resource, I don't know if this may lead into problems in the future (which version should installed). If the ssms is installed standalone, there is the required instance field, which a standalone installation does not need.

[bodunjo]

I managed to install the SSMS for SQL 2016 with the script resource pointing to the ps1 file that references the SSMS installer:

Script InstallSSMS
{

         GetScript =
        {
            # Do Nothing 
            
        }

        SetScript = 
        {
        $InstallSSMS = C:\DBA\SQLDSC\SSMSInstaller.ps1  

        }

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

    }

The below script is saved as SSMSInstaller.ps1. This also generates the log to check the state of the install

C:\DBA\SQLDSC\SSMS-Setup-ENU.exe /Install /passive /norestart /log C:\DBA\SQLDSC\SSMSInstallerlog.txt

[johlju]

In this issue comment @mdaniou suggested that this registry key can be used to verify if SQL Server Management Studio is installed or not.

$sqlMajorVersion = '14'
$registryPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\$($sqlMajorVersion)0\Tools\Setup\SQL_SSMS_Adv"
$IsSqlServerManagementStudioInstalled = Test-Path $registryPath

And it seems (so far) that we can get the major version from the version of the file, just as we do with setup.exe.

[johlju]

@StefanSchoof You come with good points. They are separate products, and one should not need to add InstanceName to install SSMS. Also, we have said that SqlSetup resource should only contain functionality that setup.exe handles. So I agree that it's best if we make this a separate resource.

[johlju]

I suggest we call this new resource SqlSsmsSetup as per naming convention.

<Module Identifier>[<Component>][<Action>]<Scope>{<Feature>|<Property>}

Module identifier: Sql
Component: Ssms
Action:: Setup
Scope: -
Feature: -
Property: -

Result: SqlSsmsSetup

To anyone: Please comment on this name, both if it looks okay, or if it doesn't. Both name and naming convention is, as everything, open for discussion.

[johlju]

Schema

I suggest this schema.mof. I can only see that the key for this resource need to be Path.

[ClassVersion("1.0.0.0"), FriendlyName("SqlSsmsSetup")]
class MSFT_SqlSsmsSetup : OMI_BaseResource
{
    [Key, Description("The path to source file for installation. I.e and UNC path to a shared resource, or an URI.")] String Path;
    [Write, Description("An enumerated value that describes if the SQL Server Management Studio should be installed on the target node ('Present'), or uninstalled from the target node ('Absent'). Default value is 'Present'."), ValueMap{"Present","Absent"}, Values{"Present","Absent"}] String Ensure;
    [Write, EmbeddedInstance("MSFT_Credential"), Description("Credentials used to access the path assigned to the parameter Path.")] String PathCredential;
    [Write, Description("Suppresses any attempts to restart the target node.")] Boolean SuppressRestart;
    [Write, Description("Forces restart of the target node on successful installation.")] Boolean ForceRestart;
    [Write, Description("Writes to installation log to this path.")] String LogPath;
    [Write, Description("The timeout, in seconds, to wait for the setup process to finish. Default value is 7200 seconds (2 hours). If the setup process does not finish before this time, and error will be thrown.")] Uint32 SetupProcessTimeout;
};

Parameters

Path

The Path parameter should be able to be assigned the following

http://source.download.site.com/SSMS-Setup-ENU.exe
https://source.download.site.com/SSMS-Setup-ENU.exe
file://C:\Temp\SSMS-Setup-ENU.exe
file://\\fileserver.company.local\SourceFiles\SSMS-Setup-ENU.exe
C:\Temp\SSMS-Setup-ENU.exe
\\fileserver.company.local\SourceFiles\SSMS-Setup-ENU.exe

The property IsFile returned from [uri] could possible be used to determine if the file is locally accessible (IsFile -eq $true) or if it need to be downloaded (IsFile -eq $false).

PathCredential

Thought this should be used if the account running the resource does not have permission to the path assigned to Path.

SuppressRestart

Should add the argument /norestart to the argument list.

ForceRestart

Always force the target node to restart.

LogPath

When set adds the argument /log $LogPath to the argument list.

Argument list

The full argument list when all properties are set

SSMS-Setup-ENU.exe </install | /uninstall> [/norestart] [/log $LogPath] /quiet

Uncertain what happens if not using parameter SuppressRestart and the setup restarts the node. If it always requires a restarts the target node to finish setup, then maybe we should always add argument /norestart and after installation is finished we control the restart with $global:DSCMachineStatus = 1.

[johlju]

The location for downloading the SSMS to use with an integration test can be found here

https://github.com/aws-quickstart/quickstart-microsoft-sql/blob/78947e5afd39c18f7a465b0b15f5af7f9338a8a0/scripts/DownloadSQLEE.ps1

[NemoDima]

Very important feauture.
Waiting for it.

[NemoDima]

I hope it would be help: https://serverfault.com/questions/907771/how-to-install-ssms-on-a-client-workstation-using-azure-automation-dsc

[NemoDima]

Interesting things:
Try to install sql_ssms.msi wich got following the instruction above, but get the error, that
"Package from C:\Install\Software\sql_ssms.msi was installed, but the specified
ProductId does not match package details "

Configuration:

Configuration Basic_settings
{
    Import-DscResource -ModuleName PSDscResources
    Import-DSCResource -ModuleName StorageDsc #For ISO-mounting
    Import-DSCResource -ModuleName SqlServerDsc #For SQL

    Node "localhost"
    {
        $password = "12345" | ConvertTo-SecureString -asPlainText -Force
        $username = "test"
        [PSCredential] $credential = New-Object System.Management.Automation.PSCredential($username,$password)

        #---------------- SQL install ----------
         MountImage MountMSSQL2016
        {
            ImagePath   = 'C:\Install\Software\Microsoft SQL Server 2016 RTM with SP2 (MSDN)\en_sql_server_2016_enterprise_core_with_service_pack_2_x64_dvd_12124052.iso'
            DriveLetter = 'S'
            Ensure = 'Present'
        }

        WaitForVolume WaitForISO
        {
            DriveLetter      = 'S'
            RetryIntervalSec = 5
            RetryCount       = 10
            DependsOn = '[MountImage]MountMSSQL2016'
        }

        SqlSetup InstallMSSQL2016 #ResourceName
        {
            ProductKey = 'XXXXX-XXXXX-XXXXX-XXXXX-XXXXX'
            InstanceName = 'TEST_MSSQLSERVER'
            Action = 'Install'
            UpdateEnabled = $false
            # components codes http://sqldatapartners.com/2015/03/26/abbreviations-for-sql-server-components-when-installing-with-powershell/
            Features = 'SQLENGINE,CONN,BC,SDK'
            #InstanceDir = 'C:\Program Files\Microsoft SQL Server'
            InstanceDir = 'C:\TestMSSQL\InstanceDir'
            #InstallSharedDir = 'C:\Program Files\Microsoft SQL Server'
            InstallSharedDir = 'C:\TestMSSQL\InstallSharedDir'
            #InstallSharedWOWDir = 'C:\Program Files (x86)\Microsoft SQL Server'
            InstallSharedWOWDir = 'C:\TestMSSQL\InstallSharedWOWDir'
            AgtSvcStartupType = 'Automatic'
            SqlSvcStartupType = 'Automatic'
            BrowserSvcStartupType = 'Disabled'
            # set grant perform volume maintenance task privilege to sql server database engine ?
            SQLCollation = 'Cyrillic_General_CI_AS'
            SecurityMode = 'SQL' #Mixed mode
            PsDscRunAsCredential = $credential
            SQLSvcAccount = $credential
            AgtSvcAccount = $credential
            SAPwd = $credential
            # SQLSysAdminAccounts = [string[]] список админов
            #[InstallSQLDataDir = [string]]
            #SQLUserDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SSSS\MSSQL\Data'
            SQLUserDBDir = 'C:\TestMSSQL\SQLUserDBDir'
            #SQLUserDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SSSS\MSSQL\Data'
            SQLUserDBLogDir = 'C:\TestMSSQL\SQLUserDBLogDir'
            #SQLBackupDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.SSSS\MSSQL\Backup'
            SQLBackupDir = 'C:\TestMSSQL\SQLBackupDir'
            #SQLTempDBDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
            SQLTempDBDir = 'C:\TestMSSQL\SQLTempDBDir'
            #SQLTempDBLogDir = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Data'
            SQLTempDBLogDir = 'C:\TestMSSQL\SQLTempDBLogDir'

            SourcePath = 'S:\'
            
            ForceReboot = $false
            #[SuppressReboot = [bool]]
        }

        MsiPackage SSMS # get SSMS.msi folowing instruction: https://serverfault.com/questions/907771/how-to-install-ssms-on-a-client-workstation-using-azure-automation-ds 
        {
            Ensure = 'Present'
            #Name = 'SQL Server Management Studio'
            Path = 'C:\Install\Software\sql_ssms.msi'
            ProductId = '3E532AF4-B9B1-4DE0-9511-7ACEB14C8D6D'
            Arguments = '/qn /norestart'
        }                
    }
}

$configData = @{
    AllNodes = @(
        @{
            NodeName = '*'
            PSDscAllowPlainTextPassword = $True
            PsDscAllowDomainUser = $true   
        }
        @{
            NodeName = 'localhost'
            PSDscAllowPlainTextPassword = $True
            PsDscAllowDomainUser = $true   
        }
    )
}

Basic_settings -OutputPath "C:\DSC\Configurations" -ConfigurationData $configData

Error:

PowerShell DSC resource MSFT_MsiPackage  failed to execute Set-TargetResource functionality with error message: 
System.InvalidOperationException: Package from C:\Install\Software\sql_ssms.msi was installed, but the specified 
ProductId does not match package details 
    + CategoryInfo          : InvalidOperation: (:) [], CimException
    + FullyQualifiedErrorId : ProviderOperationExecutionFailure
    + PSComputerName        : localhost
 
VERBOSE: [TESTSERVER]: LCM:  [ End    Set      ]
The SendConfigurationApply function did not succeed.
    + CategoryInfo          : NotSpecified: (root/Microsoft/...gurationManager:String) [], CimException
    + FullyQualifiedErrorId : MI RESULT 1
    + PSComputerName        : localhost

[mjcarrabine]

This may not be solving this issue, but it has been getting me through in the meantime.

I am downloading the SSMS installer from the website and saving it to an Azure storage account. I am then able to use the File and Package resources as shown below to install SSMS. It does update if there is a previous version installed. Note, the installation does seem to result in the machine restarting.

# To get the ProductId, just install SSMS on any machine, and then run this
Get-WmiObject Win32_Product | Sort Name | Format-Table IdentifyingNumber, Version, PackageName, Name

# It will return something like this
# {A401EAB9-4FC7-4F0C-8D79-9575E4910FDE} 15.0.18390.0    sql_ssms.msi                                                   SQL Server Management Studio

		# This will update if there is an existing version of SSMS
		# This may require a restart to be compliant
		File SQLServerManagementStudio18Source {
			Ensure = "Present"
			Type = "Directory"
			Recurse = $true
			SourcePath = "\\$($Node.FileShareStorageAccountName).file.core.windows.net\applications\MS SQL Server Management Studio 18\SSMS 18.10\"
			DestinationPath = "C:\IRM\MS SQL Server Management Studio 18\SSMS 18.10\"
			Credential = [pscredential]::new("Azure\$($Node.FileShareStorageAccountName)", (ConvertTo-SecureString -force -AsPlainText $Node.FileSharePassword))
		}

		# even with /norestart, this still seems to be causing a restart
		Package InstallSSMS{
			Ensure = "Present"
			Name = "SSMS-Setup-ENU"
			Path = "C:\IRM\MS SQL Server Management Studio 18\SSMS 18.10\SSMS-Setup-ENU.exe"
			Arguments = "/install /passive /norestart"
			ProductId = "A401EAB9-4FC7-4F0C-8D79-9575E4910FDE"
			DependsOn = "[File]SQLServerManagementStudio18Source"
		}