Examples/Resources/xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership/2-RemoveDatabasesFromAvailabilityGroup.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
110
111
112
113
114
115
<#
.EXAMPLE
    This example shows how to ensure that the databases 'DB*' and 'AdventureWorks' are not members of the Availability Group 'TestAG'.
#>


$ConfigurationData = @{
    AllNodes = @(
        @{
            NodeName              = '*'
            SQLInstanceName       = 'MSSQLSERVER'
            AvailabilityGroupName = 'TestAG'
        },

        @{
            NodeName = 'SQL1'
            Role     = 'PrimaryReplica'
        },

        @{
            NodeName = 'SQL2'
            Role     = 'SecondaryReplica'
        }
    )
}

Configuration Example
{
    param(
        [Parameter(Mandatory = $true)]
        [PSCredential]
        $SysAdminAccount
    )

    Import-DscResource -ModuleName xSqlServer

    Node $AllNodes.NodeName {
        # Adding the required service account to allow the cluster to log into SQL
        xSQLServerLogin AddNTServiceClusSvc
        {
            Ensure               = 'Present'
            Name                 = 'NT SERVICE\ClusSvc'
            LoginType            = 'WindowsUser'
            SQLServer            = $Node.NodeName
            SQLInstanceName      = $Node.SQLInstanceName
            PsDscRunAsCredential = $SysAdminAccount
        }

        # Add the required permissions to the cluster service login
        xSQLServerPermission AddNTServiceClusSvcPermissions
        {
            DependsOn            = '[xSQLServerLogin]AddNTServiceClusSvc'
            Ensure               = 'Present'
            NodeName             = $Node.NodeName
            InstanceName         = $Node.SqlInstanceName
            Principal            = 'NT SERVICE\ClusSvc'
            Permission           = 'AlterAnyAvailabilityGroup', 'ViewServerState'
            PsDscRunAsCredential = $SysAdminAccount
        }

        # Create a DatabaseMirroring endpoint
        xSQLServerEndpoint HADREndpoint
        {
            EndPointName         = 'HADR'
            Ensure               = 'Present'
            Port                 = 5022
            SQLServer            = $Node.NodeName
            SQLInstanceName      = $Node.SQLInstanceName
            PsDscRunAsCredential = $SysAdminAccount
        }

        if ( $Node.Role -eq 'PrimaryReplica' )
        {
            # Create the availability group on the instance tagged as the primary replica
            xSQLServerAlwaysOnAvailabilityGroup AddTestAG
            {
                Ensure               = 'Present'
                Name                 = $Node.AvailabilityGroupName
                SQLInstanceName      = $Node.SQLInstanceName
                SQLServer            = $Node.NodeName
                DependsOn            = '[xSQLServerEndpoint]HADREndpoint', '[xSQLServerPermission]AddNTServiceClusSvcPermissions'
                PsDscRunAsCredential = $SysAdminAccount
            }
        }

        if ( $Node.Role -eq 'SecondaryReplica' )
        {
            # Add the availability group replica to the availability group
            xSQLServerAlwaysOnAvailabilityGroupReplica AddReplica
            {
                Ensure                        = 'Present'
                Name                          = $Node.NodeName
                AvailabilityGroupName         = $Node.AvailabilityGroupName
                SQLServer                     = $Node.NodeName
                SQLInstanceName               = $Node.SQLInstanceName
                PrimaryReplicaSQLServer       = ( $AllNodes | Where-Object { $_.Role -eq 'PrimaryReplica' } ).NodeName
                PrimaryReplicaSQLInstanceName = ( $AllNodes | Where-Object { $_.Role -eq 'PrimaryReplica' } ).SQLInstanceName
            }
        }

        if ( $Node.Role -eq 'PrimaryReplica' )
        {
            xSQLServerAlwaysOnAvailabilityGroupDatabaseMembership 'TestAGDatabaseMemberships'
            {
                AvailabilityGroupName = $Node.AvailabilityGroupName
                BackupPath            = '\\SQL1\AgInitialize'
                DatabaseName          = 'DB*', 'AdventureWorks'
                SQLInstanceName       = $Node.SQLInstanceName
                SQLServer             = $Node.NodeName
                Ensure                = 'Absent'
                PsDscRunAsCredential  = $SysAdminAccount
            }
        }
    }
}