SQLServer DSC

sl E:\SQLSaturday\

Configuration SQLInstall2017
{
    param (
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String]
        $PackagePath,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String]
        $WinSources,
        [Parameter(Mandatory=$true)]
        [ValidateNotNullOrEmpty()]
        [String]
        $AuditFilePath #,
       # [Parameter(Mandatory = $true)]
       # [System.Management.Automation.PSCredential]
       # [System.Management.Automation.Credential()]
     
    )
    Import-DscResource –ModuleName PSDesiredStateConfiguration
    Import-DSCResource -ModuleName xComputerManagement
    Import-DSCResource -ModuleName xSQLServer
   
    Node $AllNodes.where{ $_.Role.Contains("SQLENGINE") }.NodeName
    {
       
                       
        Log ParamLog
        {
            Message = "Running SQLInstall. PackagePath = $PackagePath"
        }

       xPowerPlan SetPlanHighPerformance
        {
          IsSingleInstance = 'Yes'
          Name = 'High performance'
        }

     
    #    WindowsFeature NetFramework35Core
    #    {
    #        Name = "NET-Framework-Core"
    #        Ensure = "Present"
    #        Source = $WinSources
    #    }
    #
    # 
    #    WindowsFeature NetFramework45Core
    #    {
    #        Name = "NET-Framework-45-Core"
    #        Ensure = "Present"
    #        Source = $WinSources
    #    }


        # copy the sqlserver iso
        File SQLServerIso
        {
            SourcePath = "$PackagePath\SQL2017\SQLServer2017-x64-ENU-Dev.iso"
            DestinationPath = "c:\temp\SQLServer.iso"
            Type = "File"
            Ensure = "Present"
        }

        #copy cumulative update 3
         File SQLServerCU3
        {
            SourcePath = "$PackagePath\SQL2017\SQLServer2017-KB4052987-x64.exe"
            DestinationPath = "c:\temp\updates\SQLServer2017-KB4052987-x64.exe"
            Type = "File"
            Ensure = "Present"
        }

        # copy the ini file to the temp folder
        File SQLServerIniFile
        {
            SourcePath = "$PackagePath\$($Node.Configuration)"
            DestinationPath = "c:\temp"
            Type = "File"
            Ensure = "Present"
            DependsOn = "[File]SQLServerIso"
        }

        # copy the .sql scripts to the temp folder
        File ManagementDBScripts
        {
            SourcePath = "$PackagePath\ManagementDBs\"
            DestinationPath = "c:\temp\"
            Type = "Directory"
            Recurse = $true
            MatchSource = $true
            Ensure = "Present"
            DependsOn = "[File]SQLServerIso"
        }

        #Setup the SQL Audit file path
        File SQLAuditpath {
            Ensure = "Present"
            Type = "Directory"
            DestinationPath = $AuditFilePath
        }

           
        #
        # Install SqlServer using ini file
        # Based on the script located at http://www.colinsalmcorner.com/post/install-and-configure-sql-server-using-powershell-dsc
        Script InstallSQLServer
        {
            GetScript =
            {
                $sqlInstances = gwmi win32_service -computerName localhost | ? { $_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption }
                $res = $sqlInstances -ne $null -and $sqlInstances -gt 0
                $vals = @{
                    Installed = $res;
                    InstanceCount = $sqlInstances.count
                }
                $vals
            }
            SetScript =
            {
                # mount the iso
                $setupDriveLetter = (Mount-DiskImage -ImagePath c:\temp\SQLServer.iso -PassThru | Get-Volume).DriveLetter + ":"
                $ConfigFile = $using:Node.Configuration
                $SQLServerServiceAccount = $using:Node.SQLServerServiceAccount
               # $SQLServerServiceAccountPWD = $using:Node.SQLServerServiceAccountPWD
                $SQLServerAgentServiceAccount = $using:Node.SQLServerAgentServiceAccount
               # $SQLServerAgentServiceAccountPWD = $using:Node.SQLServerAgentServiceAccountPWD
                $SAPwd = $using:Node.SAPwd
                write-verbose $ConfigFile
                if ($setupDriveLetter -eq $null) {
                    throw "Could not mount SQL install iso"
                }
                Write-Verbose "Drive letter for iso is: $setupDriveLetter"
               
                # run the installer using the ini file
                $cmd = "$setupDriveLetter\Setup.exe /ConfigurationFile=c:\temp\$ConfigFile /AGTSVCACCOUNT=$SQLServerAgentServiceAccount /SQLSVCACCOUNT=$SQLServerServiceAccount /SAPWD=$SAPwd"
                Write-Verbose "Running SQL Install - check %programfiles%\Microsoft SQL Server\140\Setup Bootstrap\Log\ for logs..."
                Invoke-Expression $cmd | Write-Verbose
   
                #dismount the iso
                Dismount-DiskImage -ImagePath c:\temp\SQLServer.iso
            }
            TestScript =
            {
                $sqlInstances = gwmi win32_service -computerName localhost | ? { $_.Name -match "mssql*" -and $_.PathName -match "sqlservr.exe" } | % { $_.Caption }
                $res = $sqlInstances -ne $null -and $sqlInstances -gt 0
                if ($res) {
                    Write-Verbose "SQL Server is already installed"
                } else {
                    Write-Verbose "SQL Server is not installed"
                }
                $res
            }
            DependsOn = "[File]SQLServerCU3","[File]SQLServerISO"
        }

     
        #set max memory
        xSQLServermemory Set_SQLServerMaxMemory_ToAuto {
            Ensure = "Present"
            DynamicAlloc = $true
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            DependsOn = "[Script]InstallSQLServer"
        }

        #set maxdop
        xSQLServerMaxDop Set_SQLServerMaxDop_To4 {
            Ensure = "Present"
            DynamicAlloc = $false
            MaxDop = 4
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            DependsOn = "[Script]InstallSQLServer"
        }

        #enable CLR
        xSQLServerConfiguration SQLConfigCLR {
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            OptionName = "clr enabled"
            OptionValue = 1
            DependsOn = "[Script]InstallSQLServer"
        }
       
        #set Cost Threshold For Parallelism to 20
        xSQLServerConfiguration SQLConfigCostThresholdForParallelism {
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            OptionName = "cost threshold for parallelism"
            OptionValue = 20
            DependsOn = "[Script]InstallSQLServer"
        }
       

        #create standard databases
        xSQLServerDatabase DBA {
            Ensure = "Present"
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            Name = "DBA"
            DependsOn = "[Script]InstallSQLServer"
           
        }

     
       #deploy the sp_whoisactive stored procedure
       xSQLServerScript WhoIsActive
       {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-who_is_active_v11_17.sql"
            GetFilePath = "C:\temp\Get-WhoIsActive.sql"
            TestFilePath = "C:\temp\Test-WhoIsActive.sql"
            QueryTimeout = 600
           DependsOn = "[Script]InstallSQLServer"
        }
       
       #deploy objects to the DBA database
       xSQLServerScript DBAObjectDeployment
       {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-DBA.sql"
            GetFilePath = "C:\temp\Get-DBA.sql"
            TestFilePath = "C:\temp\Test-DBA.sql"
            QueryTimeout = 600
           DependsOn = "[xSQLServerDatabase]DBA"
        }

       #deploy standard operators
        xSQLServerScript Operators
       {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-Operator.sql"
            GetFilePath = "C:\temp\Get-Operator.sql"
            TestFilePath = "C:\temp\Test-Operator.sql"
            QueryTimeout = 600
            DependsOn = "[Script]InstallSQLServer"
       }
       
        #deploy standard maintenance jobs
         xSQLServerScript MaintenanceJobs
       {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-StandardMaintenanceJobs.sql"
            GetFilePath = "C:\temp\Get-StandardMaintenanceJobs.sql"
            TestFilePath = "C:\temp\Test-StandardMaintenanceJobs.sql"
            QueryTimeout = 600
            DependsOn = "[xSQLServerDatabase]DBA"
        }

       #setup the standard SQL Audit
        xSQLServerScript SQLAuditSpec
        {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-SQLAudit.sql"
            GetFilePath = "C:\temp\Get-SQLAudit.sql"
            TestFilePath = "C:\temp\Test-SQLAudit.sql"
            QueryTimeout = 600
            DependsOn = "[script]InstallSQLServer","[file]SQLAuditPath"
        }

        #set number of error logs to 30
        xSQLServerScript NumErrorLogs
        {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-NumErrorLogs.sql"
            GetFilePath = "C:\temp\Get-NumErrorLogs.sql"
            TestFilePath = "C:\temp\Test-NumErrorLogs.sql"
            QueryTimeout = 600
            DependsOn = "[script]InstallSQLServer"
        }


        #disable sql authentication
        xSQLServerScript DisableSQLAuth
        {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-LoginMode.sql"
            GetFilePath = "C:\temp\Get-LoginMode.sql"
            TestFilePath = "C:\temp\Test-LoginMode.sql"
            QueryTimeout = 600
            DependsOn = "[script]InstallSQLServer"
        }

       #install Integration services catalog     
       Script InstallIntegrationServicesCatalog
        {
            GetScript =
            {
              # import-module sqlps -DisableNameChecking
                $srv = New-Object Microsoft.SQLServer.Management.SMO.Server "localhost"
                $srv.Databases | where Name -eq "SSISDB"

                $res = ($srv.Databases | where Name -eq "SSISDB").count

                $vals = @{
                            Installed = $res
                        }
                $vals
                       
            }
            SetScript =
            {

                $CatalogPWD = $using:Node.SSISCatalogPWD
              #  import-module sqlps -DisableNameChecking
                # Load the IntegrationServices Assembly 
                [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") 

                # Store the IntegrationServices Assembly namespace to avoid typing it every time 
                $ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices" 

                write-verbose "Connecting to server and starting Integration Services catalog setup ..." 

                # Create a connection to the server 
                $sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;" 
                $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString 

                # Create the Integration Services object 
                $integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection 

                # Provision a new SSIS Catalog 
                $catalog = New-Object $ISNamespace".Catalog" ($integrationServices, "SSISDB", "$CatalogPWD") 
                $catalog.Create()
            }

            TestScript =
            {
               #  import-module sqlps -DisableNameChecking
                 $srv = New-Object Microsoft.SQLServer.Management.SMO.Server "localhost"
                 $res =  ($srv.Databases | where Name -eq "SSISDB").Count
                if ($res) {
                    Write-Verbose "Integration services catalog already installed"
                } else {
                    Write-Verbose "Integration services catalog not installed"
                }
                $res
            }
            DependsOn="[script]InstallSQLServer"
        }

        xSQLServerNetwork ChangeTcpIpPort
        {
            InstanceName = "MSSQLServer"
            ProtocolName = "Tcp"
            IsEnabled = $true
            TCPDynamicPorts = ""
            TCPPort = $Node.TCPPort
            RestartService = $true
            DependsOn = "[xSQLServerScript]DisableSQLAuth","[Script]InstallIntegrationServicesCatalog"
        }


        #rename the SA account and disable
        xSQLServerScript RenameSA
        {
            ServerInstance = "localhost"
            SetFilePath = "C:\temp\Set-SAaccount.sql"
            GetFilePath = "C:\temp\Get-SAaccount.sql"
            TestFilePath = "C:\temp\Test-SAaccount.sql"
            QueryTimeout = 600
            DependsOn = "[xSQLServerScript]MaintenanceJobs"
        }

        #set the database owners of the new databases
        xSQLServerDatabaseOwner DBA {
            Name = "DSSACT" #This can be paramaterized
            Database = "DBA"
            SQLServer = "localhost"
            SQLInstanceName = "MSSQLSERVER"
            DependsOn = "[xSQLServerDatabase]DBA","[xSQLServerScript]RenameSA"
       }
                     
    }
}

SQLInstall2017 -ConfigurationData E:\SQLSaturday\MyServerData.psd1 `
 -PackagePath "\\LABDC\InstallMedia" `
 -WinSources "\\LABDC\InstallMedia\Win2k12R2_Sources\sources\sxs" `
 -AuditFilePath "F:\SQLAudit" `
 -Verbose