function Main() {
#if service is not in running state when exit without any error
$state = GetServiceState $computerName $serviceName $instanceName
if(($state -ne "Running") -and ($state -ne "Unknown"))
{
return
}
#
# Prepare MOM API and property bag object
#
$api = New-Object -comObject "MOM.ScriptAPI"
#Get all databases for Instance
$queryDb = "SELECT
d.database_id as 'dbId',
d.name as 'name',
d.resource_pool_id as 'pool'
FROM sys.databases d
WHERE d.[state] = 0
AND d.source_database_id is null AND d.collation_name is not null AND d.is_read_only = 0 AND d.is_in_standby = 0 AND d.user_access != 1"
if($hkVerify -eq $null) {
if($DEBUG_MODE -eq 1) {
$api.LogScriptEvent($DEBUG_MODULE, $SCRIPT_EVENT_ID, $INFORMATION_EVENT_TYPE, "There are no Hekaton tables in $databaseName")
}
}
else {
#
# Database with memory-optimized tables should be bound to a dedicated resource pool.
#
if(([System.DBNull]::Value).Equals($poolId)) {
$result += "Database with memory-optimized tables should be bound to a dedicated resource pool." + [Environment]::NewLine
}
else {
#
# In addition, the dedicated pool should have both max_memory and min_memory configured (so check if params are not defautlts)
#
$queryPool = "select
min_memory_percent as 'min_memory',
max_memory_percent as 'max_memory'
from sys.dm_resource_governor_resource_pools
where pool_id = @p1"
$poolRow = SqlQueryRows $sqlConnection $queryPool $poolId
if($poolMinMemoryPercent -eq 0 -and $poolMaxMemoryPercent -eq 100) {
$result += "The dedicated pool should have both max_memory and min_memory configured." + [Environment]::NewLine
}
#
# Only one database should be bound to a single dedicated pool (i.e., you should not bind multiple DBs to a pool)
#
$queryDbsPool = "select Count(d.database_id) as 'dbCount'
from sys.databases d
where resource_pool_id = @p1"
$dbsPoolCountRow = SqlQueryRows $sqlConnection $queryDbsPool $poolId
[int]$dbsPoolCount = $dbsPoolCountRow.dbCount
if($dbsPoolCount -ne 1) {
$result += "Only one database should be bound to a single dedicated pool (i.e., you should not bind multiple DBs to a pool)." + [Environment]::NewLine
}
}
#
# Containers in a MEMORY_OPTIMIZED_DATA filegroup should be on a different physical disk than the transaction log
#
$queryDbInfo = "use ""{0}"";
select
sdf.type as 'type',
sdf.physical_name as 'path'
from sys.database_files sdf
left JOIN sys.filegroups fg ON sdf.data_space_id = fg.data_space_id
where sdf.type = 1 or fg.type = 'FX'"
$dbInfoList = SqlConnQueryRows $sqlConnection $queryDbInfo $databaseName
if($dbContSameDrives -ne $null) {
$result += "Containers in a MEMORY_OPTIMIZED_DATA filegroup should be on a different physical disk than the transaction log." + [Environment]::NewLine
}
}
}
}
#Verify status
if($result -eq $null) {
$result = "Health"
}
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
$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
#-------------------------------------------------------------------------------
#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
}