Examples/xSQLServerAvailabilityGroupListener.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
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
$ConfigData = @{
    AllNodes = @(
        @{
            NodeName= "*"
            CertificateFile = "C:\Certificates\dsc-public.cer" 
            Thumbprint = "D6F57B6BE46A7162138687FB74DBAA1D4EB1A59B" 
            SqlInstanceName = "MSSQLSERVER"
            PSDscAllowDomainUser = $true
        },

        @{ 
            NodeName = 'SQLNODE01.company.local'
            Role = "PrimaryReplica"
        },

        @{
            NodeName = 'SQLNODE02.company.local' 
            Role = "SecondaryReplica" 
        }
    )
}
 
Configuration SQLAlwaysOnNodeConfig 
{
    param
    (
        [Parameter(Mandatory=$false)] 
        [ValidateNotNullorEmpty()]
        [PsCredential] $SqlAdministratorCredential
    )

    Import-DscResource -ModuleName PSDesiredStateConfiguration
    Import-DscResource -ModuleName xSqlServer

    Node $AllNodes.Where{$_.Role -eq "PrimaryReplica" }.NodeName
    {
        #region Example to add listeners
        xSQLServerAvailabilityGroupListener AvailabilityGroupListenerWithSameNameAsVCO
        {
            Ensure = "Present"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            AvailabilityGroup = "AG-01"
            Name = "AG-01"
            IpAddress = "192.168.0.73/255.255.255.0"
            Port = 5301

            PsDscRunAsCredential = $SqlAdministratorCredential
            
            DependsOn = "[xSQLServerAvailabilityGroup]AvailabilityGroupForSynchronousCommitAndAutomaticFailover"
        }

        xSQLServerAvailabilityGroupListener AvailabilityGroupListenerWithDifferentNameAsVCO
        {
            Ensure = "Present"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            AvailabilityGroup = "AvailabilityGroup-02"
            Name = "AG-02"
            IpAddress = "192.168.0.74/255.255.255.0"
            Port = 5302

            PsDscRunAsCredential = $SqlAdministratorCredential
            
            DependsOn = "[xSQLServerAvailabilityGroup]AvailabilityGroupForAsynchronousCommitAndManualFailover"
        }
        #endregion
        
        #region Example to remove listeners
        xSQLServerAvailabilityGroupListener RemoveAvailabilityGroupListenerWithSameNameAsVCO
        {
            Ensure = "Absent"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            AvailabilityGroup = "AG-01"
            Name = "AG-01"

            PsDscRunAsCredential = $SqlAdministratorCredential
            
            DependsOn = "[xSQLServerAvailabilityGroupListener]AvailabilityGroupListenerWithSameNameAsVCO"
        }

        xSQLServerAvailabilityGroupListener RemoveAvailabilityGroupListenerWithDifferentNameAsVCO
        {
            Ensure = "Absent"
            NodeName = $Node.NodeName
            InstanceName = $Node.SqlInstanceName
            AvailabilityGroup = "AvailabilityGroup-02"
            Name = "AG-02"

            PsDscRunAsCredential = $SqlAdministratorCredential
            
            DependsOn = "[xSQLServerAvailabilityGroupListener]AvailabilityGroupListenerWithDifferentNameAsVCO"
        }
        #endregion
    }

    Node $AllNodes.Where{ $_.Role -eq "SecondaryReplica" }.NodeName
    {         
    }
}

$SqlAdministratorCredential = Get-Credential -Message "Enter credentials for SQL Server administrator account"

SQLAlwaysOnNodeConfig `
    -SqlAdministratorCredential $SqlAdministratorCredential `
    -ConfigurationData $ConfigData `
    -OutputPath 'C:\Configuration'