Examples/SQLServerNetwork.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
[Diagnostics.CodeAnalysis.SuppressMessageAttribute("PSAvoidUsingConvertToSecureStringWithPlainText", "")]
param ()

Configuration SQLNetwork
{
    Import-DscResource -Module xSQLServer

    # A Configuration expects at least one Node
    Node $AllNodes.NodeName
    {
        # Set DCM Settings for each Node
        LocalConfigurationManager 
        { 
            RebootNodeIfNeeded = $true 
            ConfigurationMode = "ApplyOnly" 
        } 

        WindowsFeature "NET-Framework-Core"
        {
            Ensure = "Present"
            Name = "NET-Framework-Core"
        }

        xSqlServerSetup "RDBMS"
        {
            DependsOn = @("[WindowsFeature]NET-Framework-Core")
            SourcePath = $Node.SourcePath
            SourceFolder = $Node.SQL2012FolderPath
            InstanceName = $Node.Instance
            Features = $Node.Features
            SetupCredential = $Node.InstallerServiceAccount
            SQLCollation = "Latin1_General_CI_AS"
            SQLSysAdminAccounts = $Node.AdminAccount
            SQLSvcAccount = $Node.LocalSystemAccount
            AgtSvcAccount = $Node.LocalSystemAccount
        }

        xSqlServerFirewall "RDBMS"
        {
            DependsOn = @("[xSqlServerSetup]RDBMS")
            SourcePath = $Node.SourcePath
            SourceFolder = $Node.SQL2012FolderPath
            InstanceName = $Node.Instance
            Features = $Node.Features
        }

        # This will enable TCP/IP protocol and set custom static port, this will also restart sql service
        xSQLServerNetwork "RDBMS"
        {
            DependsOn = @("[xSqlServerSetup]RDBMS")
            InstanceName = $Node.Instance
            ProtocolName = "tcp"
            IsEnabled = $true
            TCPPort = 4509
            RestartService = $true 
        }        
    } 
}

#Following example of how to use credentials is intended only for demo and test purposes
#For production environments please use SSencryption, more info can be found here:
#http://blogs.msdn.com/b/powershell/archive/2014/01/31/want-to-secure-credentials-in-windows-powershell-desired-state-configuration.aspx
$InstallerServiceAccount = Get-Credential "CONTOSO\!Installer"
$LocalSystemAccount = Get-Credential "SYSTEM"

$ConfigurationData = @{
    AllNodes = @(
        #AllNodes
        @{
            NodeName = "*"
            PSDscAllowPlainTextPassword = $true
            SourcePath = "\\FileServer\Installs"
            SQL2012FolderPath = "SqlServer2012_Developer_SP3_x64"
            InstallerServiceAccount = $InstallerServiceAccount
            LocalSystemAccount = $LocalSystemAccount
            AdminAccount = "CONTOSO\Administrator"
        }
        @{
            NodeName = "nodename"
            Instance = "MSSQLSERVER"
            Features = "SQLENGINE"
        } 
    )
}