cSQLConfig

1.3

Examples/SQL-Standalone_TAMZ.ps1

#requires -Version 5

Configuration SQLSA
{
    Import-DscResource �Module PSDesiredStateConfiguration
    Import-DscResource -Module xSQLServer
    Import-DscResource -Module xSystemVirtualMemory
    Import-DscResource -Module cSQLConfig

    Node $AllNodes.NodeName
    {
        # Set LCM to reboot if needed
        LocalConfigurationManager
        {
            AllowModuleOverwrite = $true
            RebootNodeIfNeeded = $true
        }

        WindowsFeature "NET-Framework-Core"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
            Source = $Node.SourcePath + "\WindowsServer2012R2\sources\sxs"
        }

        $SQLInstanceName = $Node.InstanceName
        $Features = $Node.Features
            
        if($Features -ne "")
        {
            xSqlServerSetup ($Node.NodeName)
            {
                DependsOn = "[WindowsFeature]NET-Framework-Core"
                SourcePath = $Node.SourcePath
                SetupCredential = $Node.InstallerServiceAccount
                InstanceName = $SQLInstanceName
                Features = $Features
                SQLSysAdminAccounts = $Node.AdminAccount
                InstallSharedDir = "E:\Program Files\Microsoft SQL Server"
                InstallSharedWOWDir = "E:\Program Files (x86)\Microsoft SQL Server"
                InstanceDir = "E:\Program Files\Microsoft SQL Server"
                InstallSQLDataDir = "E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
                SQLUserDBDir = "E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
                SQLUserDBLogDir = "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
                SQLTempDBDir = "T:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
                SQLTempDBLogDir = "L:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
                SQLBackupDir = "E:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data"
            }

            xSqlServerFirewall ($Node.NodeName)
            {
                DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
                SourcePath = $Node.SourcePath
                InstanceName = $SQLInstanceName
                Features = $Features
            }
        }

        TAMZ_cPowerPlan ($Node.Nodename)
        {
            PlanName = "High Performance"
        }
        TAMZ_cSQLMemory ($Node.Nodename)
        {
            DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
            MinMemory = "256"
            MaxMemory ="1024"
            DynamicAlloc = $False
            Ensure = "Present"
        }
        xSystemVirtualMemory ($Node.Nodename)
        {
            ConfigureOption = "CustomSize"
            InitialSize = 1000
            MaximumSize = 4000
            DriveLetter = "C:"
        }
        TAMZ_cSQLMaxDop($Node.Nodename)
        {
            DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
            DynamicAlloc = $False
            MaxDop = 1
            Ensure = "Present"
        }

        TAMZ_cSQLCheckSA($Node.Nodename)
        {
            DependsOn = ("[xSqlServerSetup]" + $Node.NodeName)
            IncludeDisabled =$False
            AccountstoPass ="'TAMZ\troyault','TAMZ\SQLAutoSvc'"
            Ensure = "Present"
        }
    }
}


$SecurePassword = ConvertTo-SecureString -String "MyPass" -AsPlainText -Force
$InstallerServiceAccount = New-Object System.Management.Automation.PSCredential ("TAMZ\SQLAutoSvc", $SecurePassword)
$LocalSystemAccount = New-Object System.Management.Automation.PSCredential ("SYSTEM", $SecurePassword)

$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true

            SourcePath = "\\ohhv003\SQLBuilds\SQLAutoInstall\SQL2014"
            InstallerServiceAccount = $InstallerServiceAccount
            LocalSystemAccount = $LocalSystemAccount

            AdminAccount = "TAMZ\troyault"

        }
        #@{
        # NodeName = "MISQL6002.tamz.local"
        # InstanceName = "MSSQLSERVER"
        # Features ="SQLENGINE,IS,SSMS,ADV_SSMS"
        # }
        #@{
        # NodeName = "MISQL6003.tamz.local"
        # InstanceName = "MSSQLSERVER"
        # Features ="SQLENGINE,IS,SSMS,ADV_SSMS"
        # }
        @{
            NodeName = "MISQL6005.tamz.local"
            InstanceName = "MSSQLSERVER"
            Features ="SQLENGINE,IS,SSMS,ADV_SSMS"            
         }
    )
}

foreach($Node in $ConfigurationData.AllNodes) 
{
    if($Node.NodeName -ne "*")
    {
        $Destination = "\\"+$Node.NodeName+"\\c$\Program Files\WindowsPowerShell\Modules"
        Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSQLServer' -Destination $Destination -Recurse -Force
        Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\cSQLConfig' -Destination $Destination -Recurse -Force
        Copy-Item 'C:\Program Files\WindowsPowerShell\Modules\xSystemVirtualMemory' -Destination $Destination -Recurse -Force
    }
}

SQLSA -ConfigurationData $ConfigurationData -OutputPath E:\DSCTesting
Set-DSCLocalConfigurationManager -ComputerName MISQL6005.tamz.local -Path E:\DSCTesting �Verbose
Start-DscConfiguration -ComputerName MISQL6005.tamz.local -Path E:\DSCTesting -Verbose -Wait -Force
Test-DscConfiguration -CimSession MISQL6005
Get-DscConfiguration -CimSession MISQL6005