AKN540048

Monitor_AKN540048 (UnitMonitor)

SQL Server Database might reside in a Advanced format type (512E)

Knowledge Base article:

Element properties:

Source Code:

<UnitMonitor ID="Monitor_AKN540048" Comment="SupportTopic=TBD;VersionNumber=1.0.0.0;" Accessibility="Public" Enabled="true" Target="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine" ParentMonitorID="Health!System.Health.ConfigurationState" Remotable="true" Priority="Normal" TypeID="KnowledgeServicesLibrary!Microsoft.KnowledgeServices.Library.PowerShellMonitorEx" ConfirmDelivery="true">

  <Category>Alert</Category>

  <AlertSettings AlertMessage="MonitorMessagea0abcbdb2f1844bb870c33e99a3909dc">

    <AlertOnState>Error</AlertOnState>

    <AutoResolve>true</AutoResolve>

    <AlertPriority>Normal</AlertPriority>

    <AlertSeverity>Warning</AlertSeverity>

    <AlertParameters>

      <AlertParameter1>$Data/Context/Property[@Name='StrAffectedDatabasesXMLOutput']$</AlertParameter1>

    </AlertParameters>

  </AlertSettings>

  <OperationalStates>

    <OperationalState ID="Success" MonitorTypeStateID="Success" HealthState="Success"/>

    <OperationalState ID="Error" MonitorTypeStateID="Error" HealthState="Error"/>

  </OperationalStates>

  <Configuration>

    <ScriptName>AKN540048.ps1</ScriptName>

    <Parameters>

      <Parameter>

        <Name>ConnectionString</Name>

        <Value>$Target/Property[Type="MicrosoftSQLServerLibrary!Microsoft.SQLServer.DBEngine"]/ConnectionString$</Value>

      </Parameter>

      <Parameter>

        <Name>ProductVersion</Name>

        <Value>$Target/Property[Type="KnowledgeServicesSQLServerLibrary!Microsoft.KnowledgeServices.SQLServer.DBEngine"]/ProductVersion$</Value>

      </Parameter>

    </Parameters>

    <ScriptBody><Script>





param($ConnectionString,$ProductVersion)



$ErrorActionPreference = "Stop"



# Set up the arguments

$scriptargs = new-object psobject

$scriptargs | add-member NoteProperty "ConnectionString" $ConnectionString

$scriptargs | add-member NoteProperty "ProductVersion" $ProductVersion



# Set up the output

$global:scriptoutput = new-object psobject

$scriptoutput | add-member NoteProperty "HasIssue" $false

$scriptoutput | add-member NoteProperty "StrAffectedDatabasesXMLOutput" ""

#----------------------------------------------------- 

# MAIN CODE SECTION

#----------------------------------------------------- 



#region Environment



$scriptenv = New-Object System.Management.Automation.PSObject

$scriptenv | Add-Member NoteProperty "RuntimeError" $false

$scriptenv | Add-Member NoteProperty "Is512EOSEnv" $false

$scriptenv | Add-Member NoteProperty "IsLowerThanSQLR2SP1Env" $false

$scriptenv | Add-Member NoteProperty "VolumeListForFilterEnv" @()

$scriptenv | Add-Member NoteProperty "DeviceInfoListEnv" $null

$scriptenv | Add-Member NoteProperty "VolumeDiskDeviceIDHashEnv" @()

$scriptenv | Add-Member NoteProperty "IsDBAffectedEnv" $false

$scriptenv | Add-Member NoteProperty "MountPointWMIEnv" $null

$scriptenv | Add-Member NoteProperty "VolumeWMIEnv" $null

$scriptenv | Add-Member NoteProperty "AffectedDatabasesXMLEnv" ""



#endregion



function New-Type 

{

	#http://huddledmasses.org/custom-icomparers-in-powershell-and-add-type-for-v1/

	param([string]$TypeDefinition,[string[]]$ReferencedAssemblies)



	## Obtains an ICodeCompiler from a CodeDomProvider class.

	$provider = New-Object Microsoft.CSharp.CSharpCodeProvider

	## Get the location for System.Management.Automation DLL

	$dllName = [PsObject].Assembly.Location

	## Configure the compiler parameters

	$compilerParameters = New-Object System.CodeDom.Compiler.CompilerParameters



	$assemblies = @("System.dll", $dllName)

	$compilerParameters.ReferencedAssemblies.AddRange($assemblies)

	if($ReferencedAssemblies) 

	{ 

	  $compilerParameters.ReferencedAssemblies.AddRange($ReferencedAssemblies) 

	}



	$compilerParameters.IncludeDebugInformation = $true

	$compilerParameters.GenerateInMemory = $true



	$compilerResults = $provider.CompileAssemblyFromSource($compilerParameters, $TypeDefinition)

	if($compilerResults.Errors.Count -gt 0) 

	{

	 $compilerResults.Errors | % { Write-Error ("{0}:`t{1}" -f $_.Line,$_.ErrorText) }

	}

}



#Private method to create affected databases entity object

function NewAffectedDBColumn($strDatabaseName,$strPath)

{

	$scriptCollectedInfo = New-Object System.Management.Automation.PSObject

	$scriptCollectedInfo | Add-Member -MemberType NoteProperty -Name "DatabaseNameDC" -Value $strDatabaseName

	$scriptCollectedInfo | Add-Member -MemberType NoteProperty -Name "PathDC" -Value $strPath



	$scriptCollectedInfo

}



#Private method to format XML data for UI display

function FormatXMLData([array]$arrayList)

{

	$xmlTemplate = "&lt;row&gt;&lt;DatabaseName&gt;{0}&lt;/DatabaseName&gt;&lt;Path&gt;{1}&lt;/Path&gt;&lt;/row&gt;"



	if($arrayList)

	{

		$arrayList | ForEach-Object{

			$scriptenv.AffectedDatabasesXMLEnv += ($xmlTemplate -f $_.DatabaseNameDC,$_.PathDC)

		}

	}

}



function Check512E()

{

	$OSVersion = [System.Environment]::OSVersion.Version

	

	#Note: SCA only support server OS: Windows Server 2008 and Windows Server 2008 R2

	#Add Windows 7 and Vista for expansion

	

	#For Windows 7 and Windows Server 2008R2

	if($OSVersion -and `

	$OSVersion.Major -eq 6 -and `

	$OSVersion.Minor -eq 1)

	{

		if(cmd /c wmic qfe | find `"982018`")

		{

			$scriptenv.Is512EOSEnv = $true

			return $null

		}

	}

	

	#For Vista and Windows Server 2008

	if($OSVersion -and `

	$OSVersion.Major -eq 6 -and `

	$OSVersion.Minor -eq 0)

	{

		if(cmd /c wmic qfe | find `"2553708`")

		{

			$scriptenv.Is512EOSEnv = $true

		}

	}

}



function CheckSQLServer()

{

	if($scriptargs.ProductVersion)

	{

		if([version]$scriptargs.ProductVersion -lt [version]"10.50.2500" -and `

		[version]$scriptargs.ProductVersion -ge [version]"10.50.0000")

		{

			$scriptenv.IsLowerThanSQLR2SP1Env = $true

		}

	}

}



function FormatVolumeResults($volumeName,$diskDeviceID)

{

	$scriptInfo = New-Object System.Management.Automation.PSObject

	

	$scriptInfo | Add-Member -MemberType NoteProperty -Name "VolumeName" -Value $volumeName

	$scriptInfo | Add-Member -MemberType NoteProperty -Name "DiskDeviceID" -Value $diskDeviceID

	

	$scriptInfo

}



function GetVolumeAndDeviceID()

{

	$deviceID = ""

	$diskDrives = Get-WmiObject -Class Win32_DiskDrive

	

	foreach($diskDrive in $diskDrives)

	{

		#Convert disk drive to disk partition

		$deviceID = $diskDrive.DeviceID -replace "\\","\\"

		$diskPartitions = Get-WmiObject -Query "ASSOCIATORS OF {Win32_DiskDrive.DeviceID=`"$deviceID`"} `

		WHERE AssocClass=Win32_DiskDriveToDiskPartition"

		

		foreach($diskPartition in $diskPartitions)

		{

			#Convert disk partition to logical disk

			$query = "ASSOCIATORS OF {Win32_DiskPartition.DeviceID=`"$($diskPartition.DeviceID)`"} `

			WHERE AssocClass=Win32_LogicalDiskToPartition" 

			$logicalDisks = Get-WmiObject -Query $query

			

			foreach($logicalDisk in $logicalDisks)

			{

				if($logicalDisk)

				{

					if(-not ($scriptenv.VolumeDiskDeviceIDHashEnv | Where-Object{

						$_.VolumeName -eq $logicalDisk.DeviceID

					}))

					{

						$scriptenv.VolumeDiskDeviceIDHashEnv += FormatVolumeResults $logicalDisk.DeviceID $diskDrive.DeviceID

					}

				}

			}

		}

	}

}



function GetSectorSize($diskDeviceID)

{

	if($diskDeviceID)

	{

		$code = @'

using System;

using System.Collections.Generic;

using System.Runtime.InteropServices;

using System.Security;



namespace NSPInvoke

{

    public class PInvoke

    {

        public static Dictionary&lt;string, uint&gt; DetectSectorSize(string devName)

        {

            if (String.IsNullOrEmpty(devName))

                return null;



            Dictionary&lt;string, uint&gt; devInfo = new Dictionary&lt;string, uint&gt;();



            IntPtr diskHandle = NativeMethods.CreateFile(

            devName,

            NativeMethods.STANDARD_RIGHTS_READ,

            NativeMethods.FILE_SHARE_READ | NativeMethods.FILE_SHARE_WRITE,

            IntPtr.Zero,

            NativeMethods.OPEN_EXISTING,

            NativeMethods.FILE_ATTRIBUTE_NORMAL,

            0);



            int querySize = Marshal.SizeOf(typeof(STORAGE_PROPERTY_QUERY));

            IntPtr queryIntPtr = Marshal.AllocHGlobal(querySize);



            STORAGE_PROPERTY_QUERY query = new STORAGE_PROPERTY_QUERY();

            query.PropertyId = 6;

            query.QueryType = 0;

            Marshal.StructureToPtr(query, queryIntPtr, true);



            int alignmentDescriptorSize = Marshal.SizeOf(typeof(STORAGE_ACCESS_ALIGNMENT_DESCRIPTOR));

            IntPtr alignmentDescriptorSizeIntPtr = Marshal.AllocHGlobal(alignmentDescriptorSize);

            uint bytes = 0;



            try

            {

                if (0 == NativeMethods.DeviceIoControl(

                    diskHandle,

                    NativeMethods.IOCTL_STORAGE_QUERY_PROPERTY,

                    queryIntPtr,

                    (uint)querySize,

                    alignmentDescriptorSizeIntPtr,

                    (uint)alignmentDescriptorSize,

                    ref bytes,

                    0))

                {

                    return null;

                }

            }

            catch { return null; }



            STORAGE_ACCESS_ALIGNMENT_DESCRIPTOR sald = (STORAGE_ACCESS_ALIGNMENT_DESCRIPTOR)Marshal.PtrToStructure(

                alignmentDescriptorSizeIntPtr,

                typeof(STORAGE_ACCESS_ALIGNMENT_DESCRIPTOR));



            devInfo.Add("Version", sald.Version);

            devInfo.Add("Size", sald.Size);

            devInfo.Add("BytesPerCacheLine", sald.BytesPerCacheLine);

            devInfo.Add("BytesOffsetForCacheAlignment", sald.BytesOffsetForCacheAlignment);

            devInfo.Add("BytesPerLogicalSector", sald.BytesPerLogicalSector);

            devInfo.Add("BytesPerPhysicalSector", sald.BytesPerPhysicalSector);

            devInfo.Add("BytesOffsetForSectorAlignment", sald.BytesOffsetForSectorAlignment);



            Marshal.FreeHGlobal(diskHandle);

            Marshal.FreeHGlobal(queryIntPtr);

            Marshal.FreeHGlobal(alignmentDescriptorSizeIntPtr);



            return devInfo;

        }



        [StructLayout(LayoutKind.Sequential)]

        private struct STORAGE_PROPERTY_QUERY

        {

            public uint PropertyId;

            public uint QueryType;

            [MarshalAs(UnmanagedType.ByValArray, SizeConst = 1)]

            public byte[] AdditionalParameters;

        }



        [StructLayout(LayoutKind.Sequential)]

        private struct STORAGE_ACCESS_ALIGNMENT_DESCRIPTOR

        {

            public uint Version;

            public uint Size;

            public uint BytesPerCacheLine;

            public uint BytesOffsetForCacheAlignment;

            public uint BytesPerLogicalSector;

            public uint BytesPerPhysicalSector;

            public uint BytesOffsetForSectorAlignment;

        }



        [SuppressUnmanagedCodeSecurity()]

        private static class NativeMethods

        {

            public const uint FILE_DEVICE_MASS_STORAGE = 0x0000002d;

            public const uint METHOD_BUFFERED = 0;

            public const uint FILE_ANY_ACCESS = 0;



            public const uint STANDARD_RIGHTS_READ = 0x00020000;

            public const uint FILE_SHARE_READ = 0x00000001;

            public const uint FILE_SHARE_WRITE = 0x00000002;

            public const uint OPEN_EXISTING = 3;

            public const uint FILE_ATTRIBUTE_NORMAL = 0x00000080;



            public static uint IOCTL_STORAGE_QUERY_PROPERTY

            {

                get

                {

                    return CTL_CODE(FILE_DEVICE_MASS_STORAGE, 0x0500, METHOD_BUFFERED, FILE_ANY_ACCESS);

                }

            }



            private static uint CTL_CODE(uint DeviceType, uint Function, uint Method, uint Access)

            {

                return ((DeviceType &lt;&lt; 16) | (Access &lt;&lt; 14) | (Function &lt;&lt; 2) | Method);

            }



            [DllImport("Kernel32.dll", SetLastError = true, CharSet = CharSet.Auto)]

            public static extern IntPtr CreateFile(

                string fileName,

                uint fileAccess,

                uint fileShare,

                IntPtr securityAttributes,

                uint creationDisposition,

                uint flags,

                uint template);



            [DllImport("Kernel32.dll", SetLastError = false, CharSet = CharSet.Auto)]

            public static extern int DeviceIoControl(

                IntPtr device,

                uint controlCode,

                IntPtr query,

                uint inBufferSize,

                IntPtr outBuffer,

                uint outBufferSize,

                ref uint bytesReturned,

                uint overlapped);

        }

    }

}

'@

		New-Type -TypeDefinition $code



		[NSPInvoke.PInvoke]::DetectSectorSize($diskDeviceID)

	}

}



function GetMountPointWMI()

{

	$scriptenv.MountPointWMIEnv = Get-WmiObject "Win32_MountPoint"

}



function GetVolumeWMI()

{

	$scriptenv.VolumeWMIEnv = Get-WmiObject "Win32_Volume"

}



function GetMPDriveCaptionByPath($path)

{

	$mpFolder = $path.Replace("\","\\")

	$mps = $scriptenv.MountPointWMIEnv | Where-Object{

		$_.Directory -eq ("Win32_Directory.Name=`""+$mpFolder+"`"")

	}

	

	$mpVol = $mps | ForEach-Object{

		$volume = $_.Volume

		$scriptenv.VolumeWMIEnv | Where-Object{

			$_.__RELPATH -eq $volume

		}

	}

	

	if($mpVol)

	{

		$mpVol.Caption

	}

}



function GetSubFolderListByPath($path)

{

	if(Test-Path $path)

	{

		$folders = @()

		$folders += $path.Split('\')

		

		#Recursive method to get each sub folders

		for($i = 0;$i -le $folders.Length -2;$i++)

		{

			$folders[$i+1] += $folders[$i] +"\" +$folders[$i+1]

			$folders[$i+1] = $folders[$i+1].Substring($folders[$i+1].IndexOf($folders[$i]))

		}

		

		$folders

	}

}



function CheckSectorSizeForSingleFolderWithoutMP($path)

{

	if($path)

	{

		$volumeName = $path.Substring(0,$path.IndexOf("\"))

		$diskDeviceID = ($scriptenv.VolumeDiskDeviceIDHashEnv | Where-Object{

			$_.VolumeName -eq $volumeName

		}).DiskDeviceID

		

		if( -not ($scriptenv.VolumeListForFilterEnv | Where-Object{

			$_ -eq $volumeName

		}))

		{

			$scriptenv.VolumeListForFilterEnv += $volumeName

			$scriptenv.DeviceInfoListEnv = GetSectorSize $diskDeviceID

		}

		

		if($scriptenv.DeviceInfoListEnv)

		{

			if($scriptenv.DeviceInfoListEnv["BytesPerLogicalSector"] -eq 512 -and `

			$scriptenv.DeviceInfoListEnv["BytesPerPhysicalSector"] -eq 4096)

			{

				return $true

			}

		}

	}

}



function CheckSectorSizeForAllSubFoldersWithMP($mpFolder)

{

	[array]$folders = GetSubFolderListByPath $mpFolder

	

	if($folders)

	{

		for($i = $folders.Length-1; $i -ne 0; $i--)

		{

			$driveCaption = GetMPDriveCaptionByPath $folders[$i]

			if(CheckSectorSizeForSingleFolderWithoutMP $driveCaption)

			{

				return $true

			}

		}

	}

}



function IsExistMPForSpecifiedPath($path)

{

	[array]$folders = (GetSubFolderListByPath $path)

	

	if($folders)

	{

		#Recursive method to search mount point folder

		for($i = 1; $i -ne $folders.Length; $i++)

		{

			if(GetMPDriveCaptionByPath $folders[$i])

			{

				return $true

			}

		}

	}

}



function GetDBConnectionString()

{

	if($scriptargs.ConnectionString)

	{

		$connStrBulider = New-Object System.Data.SqlClient.SqlConnectionStringBuilder

        $ConnStrBulider["Data Source"] = $scriptargs.ConnectionString

        $ConnStrBulider["Database"] = "master"

        $ConnStrBulider["Trusted_Connection"] = "SSPI"

    

		$connStrBulider.ConnectionString

	}

}



function CheckDBFileSystem()

{	

	$DBConnectionString = GetDBConnectionString

	if($DBConnectionString)

	{

		# Create SqlConnection object and define connection string 

		$oConnection = New-Object System.Data.SqlClient.SqlConnection 

		$oConnection.ConnectionString = $DBConnectionString

		$oConnection.Open()



		# Create SqlCommand object, define command text, and set the connection

		$oSQLCommand = New-Object System.Data.SqlClient.SqlCommand

		$oSQLCommand.CommandText = "SELECT DB_NAME(mf.database_id) AS databaseName,name as File_LogicalName,case when type_desc = 'LOG' then 'Log File' when type_desc = 'ROWS' then 'Data File' Else type_desc end as File_type_desc,mf.physical_name as Physical_Path FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id AND mf.file_id = divfs.file_id" 

		$oSQLCommand.Connection = $oConnection

		

		# Execute Command 

		$reader = $oSQLCommand.ExecuteReader()

		$rowID = 0

		

		$affectedDBArray = @()

		$physicalPath = ""

		$pathForMP = ""

		

		while($reader.Read())

		{

			$physicalPath = $reader["Physical_Path"]

			$pathForMP = $physicalPath.Substring(0,$physicalPath.LastIndexOf("\"))

				

			#Check if the specified path contains MP

			if(IsExistMPForSpecifiedPath $pathForMP)

			{

				#Check if current MPDrive is 4kb aligned

				if(CheckSectorSizeForAllSubFoldersWithMP $pathForMP)

				{

					$affectedDBArray += (NewAffectedDBColumn $reader["databaseName"] $physicalPath)

					$rowID++

				}

			}

			#Check if the drive for specified path is 4kb aligned

			elseif(CheckSectorSizeForSingleFolderWithoutMP $physicalPath)

			{

				$affectedDBArray += (NewAffectedDBColumn $reader["databaseName"] $physicalPath)

				$rowID++

			}

		}

		

		if($rowID -ne 0)

		{

			FormatXMLData $affectedDBArray

			$scriptenv.IsDBAffectedEnv = $true

		}



		# Close reader object

		$reader.Close()



		# Close SqlConnection object

		$oConnection.Close()

	}

}





# Main function



function AdvisorRule($scriptargs, $scriptoutput)

{ 

	# All parameters should be populated outside of the main function. 

	# The main function should only include the detection logic so that it can be easily reused by the Atlanta authoring tool.



	trap [Exception] {

		$scriptenv.RuntimeError = $true

      	continue

   	}

	

	# Initialize parameters

	$scriptoutput.HasIssue = $false

	$scriptoutput.StrAffectedDatabasesXMLOutput = ""

	

	CheckSQLServer

	if($scriptenv.IsLowerThanSQLR2SP1Env -eq $true)

	{

		Check512E

		if($scriptenv.Is512EOSEnv -eq $true)

		{

			GetMountPointWMI

			GetVolumeWMI

			GetVolumeAndDeviceID

			CheckDBFileSystem

			if($scriptenv.IsDBAffectedEnv -eq $true)

			{

				if($scriptenv.RuntimeError -eq $false)

				{

					$scriptoutput.HasIssue = $true

					$scriptoutput.StrAffectedDatabasesXMLOutput = $scriptenv.AffectedDatabasesXMLEnv

				}

			}

		}

	}

}

AdvisorRule $scriptargs $scriptoutput



# set the output

$mom = new-object -comobject "MOM.ScriptAPI"

$bag = $mom.CreatePropertyBag()



if ($scriptoutput.HasIssue -ne $null)

{

	$bag.AddValue("HasIssue", $scriptoutput.HasIssue)

}



if ($scriptoutput.StrAffectedDatabasesXMLOutput -ne $null)

{

	$bag.AddValue("StrAffectedDatabasesXMLOutput", $scriptoutput.StrAffectedDatabasesXMLOutput)

}



$bag



</Script></ScriptBody>

    <SnapIns/>

    <TimeoutSeconds>300</TimeoutSeconds>

    <Schedule>86397</Schedule>

    <ErrorExpression>

      <SimpleExpression>

        <ValueExpression>

          <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

        </ValueExpression>

        <Operator>Equal</Operator>

        <ValueExpression>

          <Value Type="Boolean">true</Value>

        </ValueExpression>

      </SimpleExpression>

    </ErrorExpression>

    <SuccessExpression>

      <Not>

        <Expression>

          <SimpleExpression>

            <ValueExpression>

              <XPathQuery Type="Boolean">Property[@Name='HasIssue']</XPathQuery>

            </ValueExpression>

            <Operator>Equal</Operator>

            <ValueExpression>

              <Value Type="Boolean">true</Value>

            </ValueExpression>

          </SimpleExpression>

        </Expression>

      </Not>

    </SuccessExpression>

  </Configuration>

</UnitMonitor>