ユーザー ポリシー検出データ ソース
Microsoft.SQLServer.2014.AlwaysOn.UserPolicyDataSource (DataSourceModuleType)
Element properties: Member Modules:
Overrideable Parameters:
Source Code: <DataSourceModuleType ID="Microsoft.SQLServer.2014.AlwaysOn.UserPolicyDataSource" Accessibility="Internal">
<Configuration>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="InstanceName" type="xsd:string"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="IntervalSeconds" type="xsd:integer"/>
<xsd:element xmlns:xsd="http://www.w3.org/2001/XMLSchema" name="TimeoutSeconds" type="xsd:integer"/>
</Configuration>
<OverrideableParameters>
<OverrideableParameter ID="IntervalSeconds" Selector="$Config/IntervalSeconds$" ParameterType="int"/>
<OverrideableParameter ID="TimeoutSeconds" Selector="$Config/TimeoutSeconds$" ParameterType="int"/>
</OverrideableParameters>
<ModuleImplementation>
<Composite>
<MemberModules>
<DataSource ID="DS" TypeID="System!System.CommandExecuterDiscoveryDataSource">
<IntervalSeconds>$Config/IntervalSeconds$</IntervalSeconds>
<ApplicationName/>
<WorkingDirectory/>
<CommandLine>powershell.exe -version 2 -NoLogo -NoProfile -Noninteractive "$ep = get-executionpolicy; if ($ep -gt 'RemoteSigned') {set-executionpolicy -Scope Process remotesigned} & '$$file/GeneralAlwaysOnUserPolicyDiscovery.ps1$$' '$MPElement$' '$Target/Id$' '$Config/InstanceName$'</CommandLine>
<SecureInput/>
<TimeoutSeconds>$Config/TimeoutSeconds$</TimeoutSeconds>
<RequireOutput>true</RequireOutput>
<Files>
<File>
<Name>GeneralAlwaysOnUserPolicyDiscovery.ps1</Name>
<Contents><Script> # GeneralAlwaysOnUserPolicyDiscovery.ps1
# Group User Policy Discovery Script
#
param($MapElement, $TargetID, $InstancePath)
# $InstancePath supposed to be of three types:
# computername
# computername\MSSQLSERVER
# computername\MSSQLSERVER.db1
#TODO: Discuss event id
$SCRIPT_EVENT_ID = 4202
$DEBUG_MODULE = "GeneralAlwaysOnUserPolicyDiscovery.ps1"
$DEBUG_SA = $null
$DEBUG_PWD = $null
$DISCOVERY_ERROR_EVENT_ID = 7104
#Event Severity values
$INFORMATION_EVENT_TYPE = 0
$ERROR_EVENT_TYPE = 1
$DBNull = [System.DBNull]::Value
$arAssemblies = @(
"\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSProvider.dll",
"\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll")
#debug $api.LogScriptEvent("Policy Discovery Script ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, "started policy discovery script")
function FillUserPolicyInstance
{
param ($UserPolicyObject = $(throw "Name is required parameter."), $UserPolicyInstance = $(throw "Name is required parameter."))
$UserPolicyInstance.AddProperty("$MPElement[Name='System!System.Entity']/DisplayName$", $UserPolicyObject.DisplayName)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/PolicyID$", $UserPolicyObject.PolicyID)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/PolicyName$", $UserPolicyObject.PolicyName)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/PolicyCategory$", $UserPolicyObject.PolicyCategory)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Description$", $UserPolicyObject.Description)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Condition$", $UserPolicyObject.Condition)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/Facet$", $UserPolicyObject.Facet)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/HelpLink$", $UserPolicyObject.HelpLink)
$UserPolicyInstance.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.Policy']/IsEnabled$", $UserPolicyObject.IsEnabled)
return $UserPolicyInstance
}
function Main(){
$errorCode = ModuleAction $MapElement $TargetID $InstancePath
Exit $errorCode
}
function ModuleAction {
param(
$MapElement,
$TargetID,
$InstancePath
)
$ErrorActionPreference = "stop"
$exitCode = 0
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
$discoveryData = $api.CreateDiscoveryData(0, $MapElement, $TargetID)
$sqlConnection = $null
try
{
ImportModuleActionSqlModules $arAssemblies
$msg = [string]::Empty
$pathArray = $InstancePath -split "\\"
$ComputerName = ($pathArray[0] -split "\.")[0]
$InstanceName = "MSSQLSERVER"
if ($pathArray.Length -gt 1)
{
$InstanceName = $pathArray[1]
}
#if service is not in running state when exit without any error
$service = GetSQLServiceName $InstanceName
$state = GetServiceState $ComputerName $service $InstanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
$discoveryData.IsSnapshot = $false
$api.Return($discoveryData)
return
}
$UserPolicies = @{}
$msg = "User Policies: {0}" -f [Environment]::NewLine
$AvailabilityGroupFacet = "AvailabilityGroup"
$AvailabilityReplicaFacet = "AvailabilityReplica"
$DatabaseReplicaStateFacet = "DatabaseReplicaState"
$query = " SELECT p.policy_id,
p.name AS policy_name,
p.[description],
p.help_text,
p.help_link,
p.is_enabled,
c.name AS condition_name,
CAST(LEFT(c.facet, 4000) as nvarchar(4000)) as facet,
pc.name AS category_name,
cf.obj_name
FROM syspolicy_policies p
JOIN syspolicy_conditions c ON c.condition_id = p.condition_id
JOIN syspolicy_policy_categories pc ON pc.policy_category_id = p.policy_category_id
JOIN syspolicy_object_sets os ON os.object_set_id = p.object_set_id
JOIN syspolicy_target_sets ts ON ts.object_set_id = os.object_set_id
JOIN syspolicy_target_set_levels tsl ON tsl.target_set_id = ts.target_set_id
LEFT OUTER JOIN syspolicy_conditions cf ON cf.condition_id = tsl.condition_id
WHERE p.is_system = 0 AND tsl.level_name = c.facet AND c.facet in (@p1, @p2, @p3)"
$initialDataSource = BuildDataSourceFromParts $ComputerName $InstanceName
$sqlConnection = SmartConnect $initialDataSource "msdb" $ComputerName $InstanceName $DEBUG_SA $DEBUG_PWD
$rows = SqlQueryRows $sqlConnection $query $AvailabilityGroupFacet $AvailabilityReplicaFacet $DatabaseReplicaStateFacet
if($rows -ne $null) {
$rows | foreach {
$UserPolicy = New-Object PSObject
$UserPolicy | Add-Member -memberType NoteProperty -name "DisplayName" -value $_.policy_name
$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyID" -value $_.policy_id
$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyName" -value $_.policy_name
$UserPolicy | Add-Member -memberType NoteProperty -name "PolicyCategory" -value $_.category_name
$UserPolicy | Add-Member -memberType NoteProperty -name "Description" -value $_.description
$UserPolicy | Add-Member -memberType NoteProperty -name "Condition" -value $_.condition_name
$UserPolicy | Add-Member -memberType NoteProperty -name "Facet" -value $_.facet
$UserPolicy | Add-Member -memberType NoteProperty -name "HelpLink" -value $_.help_link
$UserPolicy | Add-Member -memberType NoteProperty -name "ObjName" -value $_.obj_name
$UserPolicy | Add-Member -memberType NoteProperty -name "IsEnabled" -value $_.is_enabled
$UserPolicies[$UserPolicy.PolicyID] = $UserPolicy
}
$serverConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$serverObj = New-Object Microsoft.SqlServer.Management.Smo.Server($serverConnection)
$serverObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.AvailabilityReplica], 'UniqueId', 'EndpointUrl')
$serverObj.SetDefaultInitFields([Microsoft.SqlServer.Management.Smo.DatabaseReplicaState], 'AvailabilityReplicaId', 'AvailabilityReplicaServerName', 'AvailabilityDatabaseName')
$serverObj.AvailabilityGroups | foreach {
$avaliabilityGroupName = $_.Name
$availabilityReplicas = $_.AvailabilityReplicas
$availabilityDatabases = $_.DatabaseReplicaStates
$availabilityReplicas | where { $ComputerName -eq [regex]::Replace($_.Name, "\\.+", [string]::Empty) } | foreach {
$replicaId = $_.UniqueId
$replicaName = $_.Name
#
# Get the name of computer that should host replica from EndpointUrl
#
$HostedComputer = Get-FQDN([regex]::Replace($_.Name, "\\.+", [string]::Empty))
$UserPolicies.GetEnumerator() | ForEach-Object {
$currentUserPolicy = $_.Value
$newPolicy = $null
switch ($currentUserPolicy.Facet)
{
$AvailabilityGroupFacet
{
$createPolicy = $true
if (($currentUserPolicy.ObjName -ne $null) -and (!$DBNull.Equals($currentUserPolicy.ObjName)))
{
$createPolicy = $currentUserPolicy.ObjName.ToLower() -eq $avaliabilityGroupName.ToLower()
}
if ($createPolicy)
{
switch -wildcard ($currentUserPolicy.PolicyCategory)
{
"*error*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/UniqueId$", $replicaId.ToString("B"))
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityGroupHealth']/UniqueId$", $replicaId.ToString("B"))
}
}
}
}
$AvailabilityReplicaFacet
{
switch -wildcard ($currentUserPolicy.PolicyCategory)
{
"*error*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplicaErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/UniqueId$", $replicaId.ToString("B"))
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplicaWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.AvailabilityReplica']/UniqueId$", $replicaId.ToString("B"))
}
}
}
}
if ($newPolicy -ne $null)
{
$newPolicy.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $HostedComputer)
$newPolicy = FillUserPolicyInstance $currentUserPolicy $newPolicy
$discoveryData.AddInstance($newPolicy)
$msg += "Name = {0}; Facet = {1}; Category = {2}{3}" -f $currentUserPolicy.PolicyName, $currentUserPolicy.Facet, $currentUserPolicy.PolicyCategory, [Environment]::NewLine
}
}
$availabilityDatabases | where { $_.AvailabilityReplicaId -eq $replicaId } | foreach {
$drname = "{0}.{1}" -f $_.AvailabilityReplicaServerName, $_.AvailabilityDatabaseName
$UserPolicies.GetEnumerator() | ForEach-Object {
$currentUserPolicy = $_.Value
$newPolicy = $null
switch ($currentUserPolicy.Facet)
{
$DatabaseReplicaStateFacet
{
switch -wildcard ($currentUserPolicy.PolicyCategory)
{
"*error*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplicaErrorUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica']/Name$", $drname)
}
"*warning*"
{
$newPolicy = $discoveryData.CreateClassInstance("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplicaWarningUserPolicy']$")
$newPolicy.AddProperty("$MPElement[Name='Microsoft.SQLServer.2014.AlwaysOn.DatabaseReplica']/Name$", $drname)
}
}
}
}
if ($newPolicy -ne $null)
{
$newPolicy.AddProperty("$MPElement[Name='Windows!Microsoft.Windows.Computer']/PrincipalName$", $HostedComputer)
$newPolicy = FillUserPolicyInstance $currentUserPolicy $newPolicy
$discoveryData.AddInstance($newPolicy)
$msg += "Name = {0}; Facet = {1}; Category = {2}{3}" -f $currentUserPolicy.PolicyName, $currentUserPolicy.Facet, $currentUserPolicy.PolicyCategory, [Environment]::NewLine
}
}
}
}
}
}
#debug $api.LogScriptEvent("Workflow succeeded! ", $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $msg)
}
catch {
$header = "Management Group: $Target/ManagementGroup/Name$. Script: {0} Module: {1} Version: {2}" -f ($MyInvocation.MyCommand).Name.ToString(), $DEBUG_MODULE, $MANAGEMENT_PACK_VERSION
$msg += "Error occurred during User Policy discovery.{0}Computer:{1} {0}Reason: {2} {0}Position:{3} {0}Offset:{4}" -f [Environment]::NewLine, $env:COMPUTERNAME, $_.Exception.Message, $_.InvocationInfo.ScriptLineNumber, $_.InvocationInfo.OffsetInLine
$msg += "{0}Detailed error output: {1}" -f [Environment]::NewLine, [String]::Join("{0}--------{0}" -f [Environment]::NewLine, $Error.ToArray())
$api.LogScriptEvent($header, $DISCOVERY_ERROR_EVENT_ID, $ERROR_EVENT_TYPE, $msg)
$exitCode = -1
}
finally {
if($sqlConnection -ne $null){
$sqlConnection.Dispose()
}
}
$api.Return($discoveryData)
return $exitCode
}
#SQL2014Constants.ps1
$MANAGEMENT_PACK_VERSION = "6.7.2.0"
#AlwaysOnCommon.ps1
#
# Get FQDN of machine in current domain as we assume that all replicas in the same domain
# If IP address is provided the host name is resolved via DNS
#
function Get-FQDN {
param ([string]$name = $(throw "Name is required parameter."))
# return FQDN
return [System.Net.Dns]::GetHostByName($name).HostName
}
function ImportModuleActionSqlModules($arAssemblies){
$paths = @(
(get-itemproperty -path "hklm:\Software\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath ,
(get-itemproperty -path "hklm:\Software\Wow6432Node\Microsoft\Microsoft SQL Server\120\Tools\ClientSetup\" | Select SQLPath).SQLPath )
if (!(ImportSQLModules $arAssemblies $paths "120\Tools")) {
Import-Module SQLPS -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
if(!(IsModuleLoaded "SQLPS"))
{
throw "Unable to load SQLPS modules"
}
}
}
function IsModuleLoaded([string]$include) {
$modules = Get-Module
foreach($module in $modules){
if($module.Name.Contains($include)) {
return $true
}
}
return $false
}
function ImportSQLModules {
param (
$arAssemblies,
$paths,
[string]$include)
$flRet = $false
foreach($path in $paths){
if($path.Contains($include)){
foreach($assemblyName in $arAssemblies){
$filename = $path + $assemblyName
if(Test-Path $filename){
Import-Module $filename -ErrorAction SilentlyContinue -WarningAction SilentlyContinue | Out-Null
$flRet = $true;
}
}
}
}
return $flRet
}
#SqlConnectionCommon.ps1
function SqlTcpPortIsEmpty($tcpPort) {
return [string]::IsNullOrEmpty($tcpPort) -or $tcpPort -eq '0'
}
function GetDataSource($dataSource, $tcpPort) {
$targetDataSource = $dataSource
if (!(SqlTcpPortIsEmpty $tcpPort)){
$nameParts = $dataSource.Split("{\}")
$targetDataSource = $nameParts[0] + "," + $tcpPort
}
return $targetDataSource
}
function BuildDataSourceFromParts($computerName, $instanceName, $tcpPort){
$dataSource = $computerName
if($instanceName -ne "MSSQLSERVER"){
$dataSource = "$computerName\$instanceName"
}
return GetDataSource $dataSource $tcpPort
}
function BuildConnectionString(
[String] $dataSource,
[string] $databaseName,
[String] $timeout = 100,
[String] $user = $null,
[String] $password = $null) {
$builder = New-Object System.Data.SqlClient.SqlConnectionStringBuilder
$builder["Data Source"] = $dataSource
$builder["Initial Catalog"] = $databaseName
$builder['Connection Timeout'] = $timeout
if (($user.Length -ne 0) -and ($password.Length -ne 0)) {
$builder["User ID"] = $user
$builder["Password"] = $password
} else {
$builder["Integrated Security"] = 'SSPI'
}
return $builder.ConnectionString
}
function GetEnabledSqlServerProtocols($computerName, $instanceName) {
$protocolsWmi = Get-WmiObject -Namespace "root\Microsoft\SqlServer\ComputerManagement12" -Class 'ServerNetworkProtocol' -Filter "InstanceName = '$instanceName'"
$protocolsArr = @()
if($protocolsWmi -ne $null)
{
foreach($protocol in $protocolsWmi){
if($protocol.Enabled){
$protocolsArr+=($protocol.ProtocolName.ToLower())
}
}
}
return (,$protocolsArr)
}
function GetSqlServerTcpIpSettings([string] $instanceName){
$ipSettings = @{}
$settingsWmi = Get-WmiObject -Namespace 'root\Microsoft\SqlServer\ComputerManagement12' -Class 'ServerNetworkProtocolProperty' -Filter "ProtocolName = 'Tcp' and InstanceName = '$instanceName'"
$listenAllObj = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.PropertyName -eq "ListenOnAllIPs")}
$listenAll = $false
if($listenAllObj.PropertyNumVal -eq 1){
$listenAll = $true
}
if($listenAll) {
$portArr = @()
$tcpIpAll = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and ($_.IPAddressName -eq "IPAll") -and (($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}
foreach($port in $tcpIpAll)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add("IPAll", $portArr);
}
else{
$ipAddresses = ($settingsWmi | Where-Object { ($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -ne "")-and($_.PropertyName -eq "Enabled") -and ($_.PropertyNumVal -eq 1)})
foreach($ipAddress in $ipAddresses){
$ipAddressName = $ipAddress.IPAddressName
$ip = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( $_.PropertyName -eq "IpAddress") -and ($_.PropertyStrVal -ne '')} | select -ExpandProperty PropertyStrVal
$ports = $settingsWmi | Where-Object {($_.ProtocolName -eq "Tcp") -and ($_.InstanceName -eq $instanceName) -and($_.IPAddressName -eq $ipAddressName)-and( ($_.PropertyName -eq "TcpPort") -or ($_.PropertyName -eq "TcpDynamicPorts")) -and ($_.PropertyStrVal -ne '')}
$portArr = @()
foreach($port in $ports)
{
$splittedPorts = $port.PropertyStrVal.Split("{,}", [System.StringSplitOptions]::RemoveEmptyEntries) | %{$_.Trim()} | ?{-not (SqlTcpPortIsEmpty $_)}
$portArr += $splittedPorts
}
$ipSettings.Add($ip, $portArr);
}
}
return New-Object -TypeName PSObject -Property @{'ListenAllIPs' = $listenAll; 'IpSettings' = $ipSettings }
}
function SqlTryToConnectAndValidate(
[String] $dataSource,
[string] $databaseName,
[String] $serverName,
[String] $instanceName,
[string] $timeout = 30,
[String] $user = $null,
[String] $password = $null){
$serverNameWithoutDomain = $serverName
$connectionString = BuildConnectionString $dataSource $databaseName $timeout $user $password
$query = "SELECT SERVERPROPERTY('MachineName') AS ServerName, @@servicename AS InstanceName"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection ($connectionString)
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter ($query,$sqlConnection)
$dataSet = New-Object System.Data.DataSet
try{
$sqlConnection.Open()
$sqlAdapter.Fill($dataSet) | Out-Null
$res = $dataSet.Tables | Select -First 1
if ($res -ne $null) {
$queryServerName = ($res | Select -ExpandProperty ServerName).ToUpperInvariant()
$queryInstanceName = ($res | Select -ExpandProperty InstanceName).ToUpperInvariant()
$dotPosition = $serverName.IndexOf(".")
if ($dotPosition -gt -1) {
$serverNameWithoutDomain = $serverName.Substring(0, $dotPosition)
}
if (($serverNameWithoutDomain.ToUpperInvariant() -eq $queryServerName) -and ($instanceName.ToUpperInvariant() -eq $queryInstanceName)) {
return $sqlConnection;
}
throw "Connection target check failed: connected to $serverNameWithoutDomain\$instanceName, but got $queryServerName\$queryInstanceName."
}
}
catch{
$sqlConnection.Dispose()
throw
}
}
function SmartConnectNTE($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){
try{
return SmartConnect $connectionDataSource $databaseName $machineName $instanceName $debug_user $debug_password
}
catch{}
return $null
}
function SmartConnect($connectionDataSource, $databaseName, $machineName, $instanceName, $debug_user = $null, $debug_password = $null){
$connectionString = [string]::Empty
$lastError = $null
$errorColl = @()
$targetDataSource = ""
try{
$targetDataSource = GetDataSource $connectionDataSource ""
return SqlTryToConnectAndValidate $targetDataSource $databaseName $machineName $instanceName 15 $debug_user $debug_password
}
catch{
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
if ((GetEnabledSqlServerProtocols $machineName $instanceName) -contains "tcp") {
$tcpIpSettings = GetSqlServerTcpIpSettings $instanceName
$pathArray = $connectionDataSource.Split("{'\'}")
$targetName = $pathArray[0]
if ($tcpIpSettings.ListenAllIps) {
foreach($port in $tcpIpSettings.IpSettings["IPAll"]){
try {
$targetDataSource = GetDataSource $targetName $port
return SqlTryToConnectAndValidate $targetDataSource $databaseName $machineName $instanceName 10 $debug_user $debug_password
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
else{
$upc = New-Object "System.Collections.Generic.HashSet[string]"
foreach($portArr in $tcpIpSettings.IpSettings.Values){
if(($portArr.Length -gt 0) -and $upc.Add($portArr[0])){
try {
$targetDataSource = GetDataSource $targetName $portArr[0]
return SqlTryToConnectAndValidate $targetDataSource $databaseName $machineName $instanceName 10 $debug_user $debug_password
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
foreach($pair in $tcpIpSettings.IpSettings.GetEnumerator()){
foreach($port in $pair.Value){
try {
$targetDataSource = GetDataSource $pair.Key $port
return SqlTryToConnectAndValidate $targetDataSource $databaseName $machineName $instanceName 10 $debug_user $debug_password
}
catch {
$lastError = $_.Exception
$errorColl += "Failed to connect to data source '$targetDataSource': $($_.Exception.Message)"
}
}
}
}
}
throw [Exception] ("Cannot connect to the target Sql Server instance.`nConnection log:`n" + [string]::Join([Environment]::NewLine, $errorColl)) #, $lastError
}
function SqlQueryTablesCommon($sqlConnection, $inputQuery, [bool]$useDbName, $dbName, $queryParams){
$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$dataSet = New-Object System.Data.DataSet
try {
$query = $inputQuery
if($useDbName) {
$query = $inputQuery -f ($dbName -replace '"', '""')
}
$sqlCmd.CommandText = $query
$sqlCmd.Connection = $sqlConnection
for ($i = 0; $i -lt $queryParams.Length; $i++) {
$sqlCmd.Parameters.AddWithValue(("@p" + ($i+1)), [object]($queryParams[$i])) | Out-Null
}
$sqlAdapter.SelectCommand = $sqlCmd
$sqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
}
finally{
$sqlAdapter.Dispose()
$sqlCmd.Dispose()
}
return $dataSet.Tables
}
function SqlQueryTables($sqlConnection, $query) {
return SqlQueryTablesCommon $sqlConnection $query $false $null $args
}
function SqlConnQueryTables($sqlConnection, $query, $dbName) {
return SqlQueryTablesCommon $sqlConnection $query $true $dbName $args
}
function SqlQueryRows($sqlConnection, $query){
return (SqlQueryTablesCommon $sqlConnection $query $false $null $args | Select -First 1).Rows
}
function SqlConnQueryRows($sqlConnection, $query, $dbName){
return (SqlQueryTablesCommon $sqlConnection $query $true $dbName $args | Select -First 1).Rows
}
function SqlQueryScalar($SqlConnection, $query) {
$sqlAdapter = New-Object 'System.Data.SqlClient.SqlDataAdapter' ($query, $SqlConnection)
$dataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($dataSet) | Out-Null
if(($dataSet.Tables.Count -eq 0) -or ($DataSet.Tables[0] -eq $null))
{
throw 'Can not query data from {0} database. Please check read permissions for this db.' -f $SqlConnection.Database
}
return ($dataSet.Tables[0]|Select -first 1)[0]
}
#''' Returns -1: If DB is not in AlwaysOn
#''' Returns 0: If DB is in AlwaysOn and replica allow connections is NO
#''' Returns 1: If DB is in AlwaysOn and replica allow connections is YES
function AlwaysOnReplicaAllowConnections([System.Data.SqlClient.SqlConnection]$SqlConnection, $DatabaseID)
{
$query = " SELECT columns.id, CASE WHEN OBJECT_ID('sys.availability_replicas') IS NOT NULL THEN 1 ELSE 0 END AS HasAlwaysOn " +
" FROM master.sys.syscolumns columns where name = 'replica_id' and id = OBJECT_ID('sys.databases')"
$dr = SqlConnQueryTables $SqlConnection $query | Select -First 1
if($dr -eq $null -or $dr.HasAlwaysOn -ne 1) {
return -1
}
$query = "SELECT d.name, d.database_id, drs.is_primary_replica AS db_is_primary_replica
, CASE WHEN d.replica_id IS NULL THEN 0 ELSE 1 END AS is_replica
, CASE WHEN drs.is_primary_replica = 1 THEN ar.primary_role_allow_connections ELSE ar.secondary_role_allow_connections END AS role_allow_connections
, CASE WHEN drs.is_suspended = 0 THEN -1 ELSE suspend_reason END AS db_suspended_state
FROM sys.databases as d
JOIN sys.dm_hadr_database_replica_states drs ON drs.database_id = d.database_id
JOIN sys.availability_replicas ar on d.replica_id = ar.replica_id
WHERE drs.is_local = 1 AND d.database_id = @p1"
$rdr = SqlQueryTables $SqlConnection $query $DatabaseID | Select -First 1
if($rdr -ne $null -and $rdr.is_replica -eq 1) {
if($rdr.role_allow_connections -le 1) {
return 0
}
else {
if($rdr.db_suspended_state -gt 0 -and (!$rdr.db_is_primary_replica -or ($rdr.db_is_primary_replica -and $rdr.db_suspended_state -ne 5))){
return 0
}
return 1
}
}
return -1
}
#Common.ps1
#-------------------------------------------------------------------------------
#The function returns service or "Unknown" state
#Input:
# server - compute name
# service - system service name
# InstanceName - sql server instance name
#Output:
# service state or "Unknown" state
function GetServiceState($server, $service, $InstanceName)
{
try {
if ($service -eq "MSSQL") {
$service = "MSSQL`${0}" -f $InstanceName
}
$namespace = "root/cimv2"
$obje = Get-WmiObject -Property 'Name,State' -Namespace $namespace -ComputerName $server -Class "win32_service" -Filter "Name = '$service'" -ErrorAction SilentlyContinue
if ($obje -ne $null) {
return $obje.State
}
}
catch {
}
return "Unknown"
}
function GetSQLServiceName($InstanceName)
{
if ($InstanceName -eq "MSSQLSERVER")
{
return "MSSQLSERVER"
}
return 'MSSQL$' + $InstanceName
}
function IsNullOrZero($count)
{
return ($count -eq 0) -or ($count -eq $null)
}
function LogEventDebug($message) {
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, $message)
}
}
function ConvertToInt($v) {
if ($v -eq $null) {
return $null;
}
if ([int].IsInstanceOfType($v)) {
return $v;
}
$res = $null;
if([int]::TryParse($v.ToString(),[ref] $res)) {
return $res;
}
return $null;
}
Main
</Script> </Contents>
<Unicode>true</Unicode>
</File>
</Files>
</DataSource>
</MemberModules>
<Composition>
<Node ID="DS"/>
</Composition>
</Composite>
</ModuleImplementation>
<OutputType>System!System.Discovery.Data</OutputType>
</DataSourceModuleType>