DSCResources/MSFT_xSqlHAGroup/MSFT_xSqlHAGroup.psm1
|
# # xSqlHAGroup: DSC resource to configure a Sql High Availability (HA) Group. If the HA Group does not exist, it will # create one with given name on given sql instance, it also adds the database(s) to the group. If the HA group # already exists, it will join sql instance to the group, replicate the database(s) in the group to local instance. # # # The Get-TargetResource cmdlet. # function Get-TargetResource { param ( [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $Name, [parameter(Mandatory)] [ValidateNotNull()] [string[]] $Database, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $ClusterName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $DatabaseBackupPath, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $InstanceName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $EndpointName, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $DomainCredential, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $SqlAdministratorCredential ) if ($Database.Count -lt 1) { throw "Parameter Database does not have any database" } $bConfigured = Test-TargetResource -Name $Name -Database $Database -ClusterName $ClusterName -DatabaseBackupPath $DatabaseBackupPath -InstanceName $InstanceName -EndpointName $EndpointName -DomainCredential $DomainCredential -SqlAdministratorCredential $SqlAdministratorCredential $returnValue = @{ Database = $Database Name = $Name ClusterName = $ClusterName DatabaseBackupPath = $DatabaseBackupPath InstanceName = $InstanceName EndpointName = $EndpointName DomainCredential = $DomainCredential.UserName SqlAdministratorCredential = $SqlAdministratorCredential.UserName Configured = $bConfigured } $returnValue } # # The Set-TargetResource cmdlet. # function Set-TargetResource { param ( [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $Name, [parameter(Mandatory)] [ValidateNotNull()] [string[]] $Database, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $ClusterName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $DatabaseBackupPath, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $InstanceName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $EndpointName, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $DomainCredential, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $SqlAdministratorCredential ) if ($Database.Count -lt 1) { throw "Parameter Database does not have any database" } Write-Verbose -Message "Checking if SQL HAG $Name is present ..." Write-Verbose -Message "Cluster: $ClusterName, Database: $Database" $bHAGExist = $false $primaryReplica = $InstanceName $sa = $SqlAdministratorCredential.UserName $saPassword = $SqlAdministratorCredential.GetNetworkCredential().Password try { ($oldToken, $context, $newToken) = ImpersonateAs -cred $DomainCredential $nodes = Get-ClusterNode -Cluster $ClusterName } finally { if ($context) { $context.Undo() $context.Dispose() CloseUserToken($newToken) } } foreach ($node in $nodes.Name) { $instance = Get-SQLInstanceName -node $node -InstanceName $InstanceName $bCheck = Check-SQLHAGroup -InstanceName $instance -Name $Name -sa $sa -saPassword $saPassword if ($bCheck) { Write-Verbose -Message "Found SQL HAG $Name on instance $instance" $bHAGExist = $true # check if it is the primary replica $bPrimaryCheck = Check-SQLHAGroupPrimaryReplica -InstanceName $instance -Name $Name -sa $sa -saPassword $saPassword if ($bPrimaryCheck) { $primaryReplica = $instance } } } if ($bHAGExist) { Write-Verbose -Message "Add instance $InstanceName to SQL HAG $Name" $bCheckPreviousInstance = Check-SQLHAGroupReplicaExist -InstanceName $InstanceName -Name $Name -PrimaryInstanceName $primaryReplica -sa $sa -saPassword $saPassword if ($bCheckPreviousInstance) { Write-Verbose -Message "SQLHAGroup $Name already has the instance $InstanceName, clean up first" $query = "alter availability group $Name ` remove replica on '$InstanceName'" Write-Verbose -Message "Query: $query" osql -S $primaryReplica -U $sa -P $saPassword -Q $query } # Add this instance to HAG group on instance $primaryInstance $query = "alter availability group $Name ` add replica on '$InstanceName' with ` ( ` EndPoint_URL = 'TCP://$EndpointName', ` Availability_Mode = Synchronous_Commit, ` Failover_Mode = Automatic, ` Secondary_Role(Allow_connections = ALL) ` ) " Write-Verbose -Message "Query: $query" osql -S $primaryReplica -U $sa -P $saPassword -Q $query # Add this node to HAG osql -S $InstanceName -U $sa -P $saPassword -Q "ALTER AVAILABILITY GROUP $Name JOIN" # restore database foreach($db in $Database) { $query = "restore database $db from disk = '$DatabaseBackupPath\$db.bak' with norecovery" Write-Verbose -Message "Instance $InstanceName Query: $query" osql -S $InstanceName -U $sa -P $saPassword -Q $query $query = "restore log $db from disk = '$DatabaseBackupPath\$db.log' with norecovery " Write-Verbose -Message "Query: $query" osql -S $InstanceName -U $sa -P $saPassword -Q $query # Add database to HAG osql -S $InstanceName -U $sa -P $saPassword -Q "ALTER DATABASE $db SET HADR AVAILABILITY GROUP = $Name" } } else # create { Write-Verbose -Message "Create SQL HAG $Name and primary instance $InstanceName" foreach($db in $Database) { Write-Verbose -Message "Create database $db ..." osql -S $InstanceName -U $sa -P $saPassword -Q "if not exists (select * from master.sys.databases where name = '$db') begin Create database $db end;" Write-Verbose -Message "Backup to $DatabaseBackupPath .." osql -S $InstanceName -U $sa -P $saPassword -Q "backup database $db to disk = '$DatabaseBackupPath\$db.bak' with format" } $dblist = "$Database" -replace " ", ", " Write-Verbose -Message "AG: $Name " $query = "Create Availability Group $Name ` For Database $Database ` Replica ON ` '$InstanceName' with ` ( ` ENDPOINT_URL = 'TCP://$EndpointName', ` Availability_Mode = Synchronous_Commit, ` Failover_Mode = Automatic ` )" Write-Verbose -Message "Create HAG : $query.." osql -S $InstanceName -U $sa -P $saPassword -Q $query foreach($db in $Database) { Write-Verbose -Message "Backup Log to $DatabaseBackupPath .." osql -S $InstanceName -U $sa -P $saPassword -Q "backup log $db to disk = '$DatabaseBackupPath\$db.log' with NOFormat" } } } # # The Test-TargetResource cmdlet. # function Test-TargetResource { param ( [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $Name, [parameter(Mandatory)] [ValidateNotNull()] [string[]] $Database, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $ClusterName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $DatabaseBackupPath, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $InstanceName, [parameter(Mandatory)] [ValidateNotNullOrEmpty()] [string] $EndpointName, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $DomainCredential, [Parameter(Mandatory)] [ValidateNotNullOrEmpty()] [PSCredential] $SqlAdministratorCredential ) if ($Database.Count -lt 1) { throw "Parameter Database does not have any database" } Write-Verbose -Message "Checking if SQL HA Group $Name on instance $InstanceName present ..." $sa = $SqlAdministratorCredential.UserName $saPassword = $SqlAdministratorCredential.GetNetworkCredential().Password $bFound = Check-SQLHAGroup -InstanceName $InstanceName -Name $Name -sa $sa -saPassword $saPassword if ($bFound) { Write-Verbose -Message "SQL HA Group $Name is present" $true } else { Write-Verbose -Message "SQL HA Group $Name not found" $false } } function Check-SQLHAGroup($InstanceName, $Name, $sa, $saPassword) { Write-Verbose -Message "Check HAG $Name including instance $InstanceName ..." $query = OSQL -S $InstanceName -U $sa -P $saPassword -Q "select count(name) from master.sys.availability_groups where name = '$Name'" -h-1 Write-Verbose -Message "SQL: $query" [bool] [int] ([String] $query[0]).Trim() } function Check-SQLHAGroupPrimaryReplica($InstanceName, $Name, $sa, $saPassword) { $query = OSQL -S $InstanceName -U $sa -P $saPassword -Q "select count(replica_id) from sys.dm_hadr_availability_replica_states s ` inner join sys.availability_groups g on g.group_id = s.group_id ` where g.name = '$Name' and s.role_desc = 'PRIMARY' and s.is_local = 1" -h-1 [bool] [int] ([string] $query[0]).Trim() } function Check-SQLHAGroupReplicaExist($InstanceName, $Name, $PrimaryInstanceName, $sa, $saPassword) { $query = OSQL -S $PrimaryInstanceName -U $sa -P $saPassword -Q "select count(replica_id) from sys.availability_replicas r ` inner join sys.availability_groups g on g.group_id = r.group_id ` where g.name = '$Name' and r.replica_server_name = '$InstanceName' " -h-1 [bool] [int] ([string] $query[0]).Trim() } function Get-PureInstanceName ($InstanceName) { $list = $InstanceName.Split("\") if ($list.Count -gt 1) { $list[1] } else { "MSSQLSERVER" } } function Get-SQLInstanceName ($node, $InstanceName) { $pureInstanceName = Get-PureInstanceName -InstanceName $InstanceName if ("MSSQLSERVER" -eq $pureInstanceName) { $node } else { $node + "\" + $pureInstanceName } } function Get-ImpersonatetLib { if ($script:ImpersonateLib) { return $script:ImpersonateLib } $sig = @' [DllImport("advapi32.dll", SetLastError = true)] public static extern bool LogonUser(string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken); [DllImport("kernel32.dll")] public static extern Boolean CloseHandle(IntPtr hObject); '@ $script:ImpersonateLib = Add-Type -PassThru -Namespace 'Lib.Impersonation' -Name ImpersonationLib -MemberDefinition $sig return $script:ImpersonateLib } function ImpersonateAs([PSCredential] $cred) { [IntPtr] $userToken = [Security.Principal.WindowsIdentity]::GetCurrent().Token $userToken $ImpersonateLib = Get-ImpersonatetLib $bLogin = $ImpersonateLib::LogonUser($cred.GetNetworkCredential().UserName, $cred.GetNetworkCredential().Domain, $cred.GetNetworkCredential().Password, 9, 0, [ref]$userToken) if ($bLogin) { $Identity = New-Object Security.Principal.WindowsIdentity $userToken $context = $Identity.Impersonate() } else { throw "Can't Logon as User $cred.GetNetworkCredential().UserName." } $context, $userToken } function CloseUserToken([IntPtr] $token) { $ImpersonateLib = Get-ImpersonatetLib $bLogin = $ImpersonateLib::CloseHandle($token) if (!$bLogin) { throw "Can't close token" } } Export-ModuleMember -Function *-TargetResource |