Skip to main content

Powershell to collect SQLServer compliant info Verson 2

We established some best practice rules for SQL Server, we need method to collect these data and analyze the compliance.

Powershell script is our solution。

The Best Practice rules are:

##• Do not install sqlserver on C drive,
##• do not put database files on C drive
##• Set maximum memory: should not left as default, leave 1GB memory for OS
##• Agent start automatically
##• Database auto growth on, with 10% percentage
##• Database backup regularly, either by SQLServer maintenance, Netbackup agent or Doc Ave
##• Transaction log backup regularly to prevent db size growth out of control
##• Disable CLR, with exception on kdcwdbsqlprd03 where respontek requires CLR.
##• Disable xp_cmdshell
##• Database recovery mode: full recovery mode for production, simple recovery mode for PPE
##• Turn off DB autoshrink


Step 1: create the tables in oracle where the data will be saved, we choose oracle, it can be MSSQL,

create table mssqlhostcpuinfo (hostname varchar2(20), cores int,logicalcors int);
create table mssqlhostmemoryinfo (hostname varchar2(20), membytes number(13));
create table mssqlhostdiskinfo (hostname varchar2(20), diskletter varchar2(2),capacity number(13), freedisk number(13));
create table mssqlhostosinfo (hostname varchar2(20), os varchar2(60),version varchar2(15));
create table mssqlhostdbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60));
create table mssqldbinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(200),recoverymode varchar2(10),status varchar2(10),version varchar2(15),sp varchar2(10),edition varchar2(60),dbsizeMB varchar2(20),filename varchar2(200),ispercentgrowth varchar2(1),growth varchar2(20),autoshrink varchar2(1));
create table mssqlserviceinfo (hostname varchar2(20), instancename varchar2(30), servicename varchar2(30),state varchar2(10),startmode varchar2(10),pathname varchar2(200));
create table mssqlconfiginfo (hostname varchar2(20), instancename varchar2(30), name varchar2(50),value varchar2(20),description varchar2(100));
create table mssqldbbackupinfo (hostname varchar2(20), instancename varchar2(30), dbname varchar2(100),startdate varchar2(20),finishdate varchar2(20),backuptype varchar2(15),backupsize varchar2(20),physicalname varchar2(800));
--create table mssqlmaintplaninfo (hostname varchar2(20), instancename varchar2(30), planname varchar2(100),description varchar2(200),finishdate varchar2(20),backuptype varchar2(15),backupsize varchar2(20),physicalname varchar2(200));
create table mssqlserverlist (hostname varchar2(20), instancename varchar2(30))

Step 2: Powershell script: it will truncate the info tables, remove the output files, collect info and save info to output files, load data from output files to oracle tables using sqlldr

#* FileName: ServerInventory.ps1
#*=============================================================================
#* Script Name: [CollectMSSQLServerinfo.ps1]
#* Created: [Nov 6th, 2018]
#* Author: jiulu Sun
#* Company: BCFerries
# usage : BCFerries data service team managed sqlservers
# How to use : run powershell, type $PathOf\filename.ps1, it will loop all servers defined in $arrComputers
# you need to change executionpolicy first if its at restricted mode. or you will get an error
# "File C:\vb\sqlserver_info1.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details."
# this is an example
# PS C:\vb> Get-ExecutionPolicy
# Restricted
# PS C:\vb> set-executionpolicy remotesigned
# PS C:\vb> Get-ExecutionPolicy
# RemoteSigned

#get the sqlserver list
$OutputfileServerList = "C:\projects\mssql\CMDB\MSSQLServerList.txt"
$server="emrept"
remove-item -Path $OutputfileServerList -ErrorAction SilentlyContinue
$sql= @"
set echo off
set pagesize 0
set termout off
set feedback off
set pause off
set verify off
set pagesize 0
--select distinct '"'||hostname||'","'||instancename||'"' from mssqlserverlist;
select distinct instancename from mssqlserverlist;
"@
(echo $sql)|sqlplus -S jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))" > $OutputfileServerList

# loop the instancenames, define outputfiles names
#$arrComputers = get-Content -Path "C:\projects\mssql\CMDB\SQLServerList.txt"
$arrComputers = get-Content -Path $OutputfileServerList
$OutputfileCPU = "C:\projects\mssql\CMDB\MSSQLCPU.txt"
$OutputfileMem = "C:\projects\mssql\CMDB\MSSQLMem.txt"
$OutputfileDisk = "C:\projects\mssql\CMDB\MSSQLDisk.txt"
$OutputfileDB = "C:\projects\mssql\CMDB\MSSQLDB.txt"
$OutputfileDBBackup = "C:\projects\mssql\CMDB\MSSQLDBBackup.txt"
$OutputfileOS = "C:\projects\mssql\CMDB\MSSQLOS.txt"
$OutputfileService = "C:\projects\mssql\CMDB\MSSQLService.txt"
$OutputfileConfig = "C:\projects\mssql\CMDB\MSSQLConfig.txt"

#clear the outputfiles
remove-item -Path $OutputfileCPU -ErrorAction SilentlyContinue
remove-item -Path $OutputfileMem -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDisk -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDB -ErrorAction SilentlyContinue
remove-item -Path $OutputfileDBBackup -ErrorAction SilentlyContinue
remove-item -Path $OutputfileOS -ErrorAction SilentlyContinue
remove-item -Path $OutputfileService -ErrorAction SilentlyContinue
remove-item -Path $OutputfileConfig -ErrorAction SilentlyContinue

$ONETAB+=" "
$TWOTABS=" "
$BLANKLINE=""

Function SQLserviceinfo {
Get-WmiObject -computername $strComputer win32_service | ?{$_.Name -like '*sql*'} | select systemname,@{ Name = 'instancename'; Expression = {  $strInstance }},Name, State, startmode, PathName|Export-Csv -Path $OutputfileService -append -NoTypeInformation -force
}

Function MemInfo {
Get-WmiObject Win32_ComputerSystem -Namespace "root\CIMV2" -ComputerName $strComputer|Select-Object -Property "Name","TotalPhysicalMemory" |Export-Csv -Path $OutputfileMEM -append -NoTypeInformation -force
}

Function OSInfo {
 Get-WmiObject Win32_OperatingSystem -ComputerName $strComputer|select-object csname,caption,version|Export-Csv -Path $OutputfileOS -append -NoTypeInformation -force
}

Function CPUInfo {
$property = "systemname","numberOfCores", "NumberOfLogicalProcessors"
Get-WmiObject win32_processor -computername $strComputer |Select-Object -Property $property |sort -unique|Export-Csv -Path $OutputfileCPU -append -NoTypeInformation -force
}

Function DiskInfo {
$property = "__SERVER","DriveLetter","Capacity","FreeSpace"
Get-WmiObject Win32_Volume -filter "DriveLetter IS NOT NULL AND FileSystem IS NOT NULL" -computername $strComputer|Select-Object -Property $property|Export-Csv -Path $OutputfileDISK -append -NoTypeInformation -force
}

Function sysdbbackupInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' +CONVERT(varchar(100), SERVERPROPERTY('Servername'))
+ ',' +msdb.dbo.backupset.database_name
+ ',' +CONVERT(varchar,msdb.dbo.backupset.backup_start_date,120)
+ ',' +CONVERT(varchar,msdb.dbo.backupset.backup_finish_date,120)
+ ',' +CASE msdb..backupset.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END
+ ',' +CONVERT(varchar(100),msdb.dbo.backupset.backup_size)
+ ',' +msdb.dbo.backupmediafamily.physical_device_name
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDBBackup
}

Function sysdbInfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel'))
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
FROM master.dbo.sysdatabases sd
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}

Function sysdbInfo2 {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ',' + sd.name
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Recovery'))
+ ',' + CONVERT(varchar(20),DATABASEPROPERTYEX(sd.name, 'Status'))
+ ',' + RTRIM (CONVERT(char(20),serverproperty('ProductVersion')))
+ ',' + CONVERT(varchar(20), serverproperty('ProductLevel'))
+ ',' + CONVERT(varchar(80),SERVERPROPERTY('Edition'))
+ ',' + convert(varchar(20),(CAST(smf.size AS FLOAT) * 8096) / (1024 * 1024))
+ ',' + smf.physical_name
+ ',' + convert(varchar(1),smf.is_percent_growth)
+ ',' + convert(varchar(20),smf.growth)
+ ',' + CONVERT(varchar(1),DATABASEPROPERTYEX(sd.name, 'IsAutoShrink'))
  FROM master.sys.databases sd
  INNER JOIN sys.master_files smf ON sd.database_id = smf.database_id
  WHERE
  smf.type_desc IN (
      'ROWS'
      ,'LOG'
    )
"@
#invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|Export-Csv -append -path $OutputfileDB -NoTypeInformation -force
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileDB
}

Function sqlconfiginfo {
$sql= @"
SET NOCOUNT ON
SELECT CONVERT(varchar(80),serverproperty('MachineName'))
+ ',' + CONVERT(varchar(64),serverproperty ('servername'))
+ ','+ name+ ',' + CONVERT(varchar(20),value)+ ','  +[description]
FROM sys.configurations
WHERE lower(name) like '%max server memory%'
or lower(name) like '%degree of parallel%'
or lower(name) like '%xp_cmdshell%'
or lower(name) like '%clr%'
"@
invoke-sqlcmd -ServerInstance $strComputer  -Query $sql|ConvertTo-Csv -NoTypeInformation -Delimiter "," | Select-Object -Skip 1 | % {$_ -replace '"', ""} | Out-File -Force  -append -FilePath $OutputfileConfig
}

#*=============================================================================
#* SCRIPT BODY
#*=============================================================================

#truncate the cmdb tables before
$server="emrept"
(echo "truncate table mssqlhostcpuinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostmemoryinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostdiskinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostosinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlhostdbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlserviceinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqlconfiginfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqldbinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"
(echo "truncate table mssqldbbackupinfo;")|sqlplus jsun/$PASSWORD@"(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(Host = $server)(Port = 1521))) (CONNECT_DATA = (service_name =EMREPTGC12.bcferries.com)))"

foreach ($strComputer in $arrComputers)

{ #Function Calls go here

"#########################################################################"
"Computer Name:" +$strComputer
"#########################################################################"

#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbInfo2

#* Call database Function
$BLANKLINE
"@@@SQLServer database information"
sysdbbackupInfo

#* Call sqlconfiginfo Function
"SQLConfiginfo Information"
SQLconfiginfo

####now remove the name instance.
if ($strComputer.contains("\"))
{#'this is a named instance
$position=$strComputer.indexof("\")
$positionplus1=$strComputer.indexof("\")+1
$mylength=$strComputer.length-$positionplus1
$strInstance= $strComputer.substring($positionplus1,$mylength)
$strComputer = $strComputer.substring(0,$position)
}
else
{
$strInstance="default"
}
"computer name is :" + $strComputer
"instance name is :" + $strInstance

#* Call SysInfo Function
"Sytem Information"
MemInfo

#* Call OSInfo Function
"Operating System Information"
OSInfo

#* Call CPUInfo Function
"Processor Information"
CPUInfo

#* Call DiskInfo Function
"Disk Information"
DiskInfo

#* Call SQLserviceinfo Function
"SQLserviceinfo Information"
SQLserviceinfo

}


#now load these tables
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlcpu.ctl log=C:\projects\MSSQL\CMDB\mssqlcpu.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlOS.ctl log=C:\projects\MSSQL\CMDB\mssqlOS.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlMEM.ctl log=C:\projects\MSSQL\CMDB\mssqlMEM.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDisk.ctl log=C:\projects\MSSQL\CMDB\mssqlDISK.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlservice.ctl log=C:\projects\MSSQL\CMDB\mssqlservice.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlconfig.ctl log=C:\projects\MSSQL\CMDB\mssqlconfig.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDB2.ctl log=C:\projects\MSSQL\CMDB\mssqldbinfo.log
& "C:\ORACLE\product\12.1.0\client_1\BIN\sqlldr.exe" userid=jsun/$password@emrept control=C:\projects\MSSQL\CMDB\mssqlDBbackup.ctl log=C:\projects\MSSQL\CMDB\mssqlDBbackup.log

Step 3. Create controlfiles for sqlldr:
mssqlconfig.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLconfig.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlconfig.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlconfig.dis'
    APPEND
    INTO TABLE jsun.mssqlconfiginfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     NAME char,
     value char,
     Description char
     --     edition char "rtrim(:edition)"
     )
MSSQLCPU.ctl:
    LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLCPU.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlcpu.dis'
    APPEND
    INTO TABLE jsun.mssqlhostcpuinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     CORES ,
     LOGICALCORS
    )
MSSQLDB2.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDB.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDB.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDB.dis'
    APPEND
    INTO TABLE jsun.mssqldbinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char(200),
     RECOVERYMODE char,
     STATUS char,
     VERSION char,
     SP char,
   --  edition char "trim(null from trim(:edition))"
     edition char "rtrim(:edition)",
     dbsizemb char,
     filename char(200),
     ispercentgrowth char,
     growth char,
     autoshrink char
     )
  MSSQLDBBackup.ctl:
LOAD DATA
CHARACTERSET AL16UTF16
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\MSSQLDBBackup.dis'
    APPEND
    INTO TABLE jsun.mssqldbbackupinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     DBNAME char,
     --recoverymode char,
     startdate char,
     finishdate char,
     backuptype char,
     backupsize char,
     physicalname char(4000)
     )
MSSQLDisk.ctl:
 LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLDISK.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlDISK.dis'
    APPEND
    INTO TABLE jsun.mssqlhostdiskinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     diskletter char,
     capacity,
     freedisk
     )
MSSQLMemory.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLMEM.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlMEM.dis'
    APPEND
    INTO TABLE jsun.mssqlhostmemoryinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     membytes
     )
MSSQLOS.ctl:
LOAD DATA
CHARACTERSET UTF8
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLOS.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlOS.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlOS.dis'
    APPEND
    INTO TABLE jsun.mssqlhostosinfo
    when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     OS char ,
     version char
    )
MSSQLService.ctl:
LOAD DATA
    INFILE 'C:\projects\MSSQL\CMDB\MSSQLService.txt'
    BADFILE 'C:\projects\MSSQL\CMDB\mssqlService.bad'
    DISCARDFILE 'C:\projects\MSSQL\CMDB\mssqlService.dis'
    APPEND
    INTO TABLE jsun.mssqlserviceinfo
    --when hostname != BLANKS
     FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
     (
     HOSTNAME char,
     INSTANCENAME char,
     servicename char,
     state char,
      startmode char,
     pathname char
     )
 
  Step 4: run the powershell script
step 5: analyze the compliant data:

  Best practices:
##•    Do not install sqlserver on C drive: violation 51%

  select sum(case instr(upper(pathname),'C:\') when 0 then 0 else 1 end)/count(*) Install_On_Cdrive,1-sum(case instr(upper(pathname),'C:\') when 0 then 0 else 1 end)/count(*) Install_Not_On_Cdrive,count(*) serviceCount
  from jsun.mssqlserviceinfo where (servicename like 'MSSQL$%' or servicename like 'MSSQLSERVER')
SQL> /

INSTALL_ON_CDRIVE INSTALL_NOT_ON_CDRIVE SERVICECOUNT
----------------- --------------------- ------------
       .514285714            .485714286           35

##•    do not put database files on C drive: Violation 9.8%

select sum(case instr(upper(filename),'C:\') when 0 then 0 else 1 end)/count(*) DBFile_On_Cdrive,1-sum(case instr(upper(filename),'C:\') when 0 then 0 else 1 end)/count(*) DBFile_Not_On_Cdrive,count(*) DBFileCount from jsun.mssqldbinfo
SQL> /

DBFILE_ON_CDRIVE DBFILE_NOT_ON_CDRIVE    DBFileCOUNT
---------------- -------------------- ----------
      .098191214           .901808786       1161

##•    Set maximum memory: (should not left as default.) violation: 78%

n  The value  in table mssqlconfiginfo for memory is MB, so we use 1024000 as threat hold, which is 1TB
select sum(case when (value >1024000) then 1 else 0 end)/count(*) Set_As_Max_memory, 1-sum(case when (value >1024000) then 1 else 0 end)/count(*) Set_As_Below_Max_memory,count(*) InstanceCount from MSSQLCONFIGINFO where lower(name) like '%memory%'
SQL> /

SET_AS_MAX_MEMORY SET_AS_BELOW_MAX_MEMORY INSTANCECOUNT
----------------- ----------------------- -------------
       .777777778              .222222222            27

##•    Agent start automatically: compliant 88%

SQL> select sum(case when startmode like 'Auto%' then 1 else 0 end)/count(*) Agent_Auto_Startup,count(*) ServiceCount from MSSQLSERVICEINFO where upper(SERVICENAME) like '%AGENT%';

AGENT_AUTO_STARTUP SERVICECOUNT
------------------ ------------
        .882352941           34

##•    Database auto growth on: compliant 42%

SQL> select sum(case when (ISPERCENTGROWTH='1' ) then 1 else 0 end)/count(*) db_grow_by_percentage,count(*) DBFileCount from MSSQLDBINFO;

DB_GROW_BY_PERCENTAGE    DBFileCOUNT
--------------------- ----------
            .42377261       1161

##•    Database backup regularly, (either by SQLServer maintenance, Netbackup agent or Doc Ave(new sharepoint)) violation: 38%
SELECT b.numb / a.numb No_backup_24hrs,a.numb DBCount
  FROM (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO) a,
       (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO dbinfo
         WHERE NOT EXISTS
                      (SELECT 1
                         FROM jsun.MSSQLDBBACKUPINFO bkinfo
                        WHERE     bkinfo.instancename = dbinfo.instancename
                              AND bkinfo.dbname = dbinfo.dbname)) b

NO_BACKUP_24HRS    DBCOUNT
--------------- ----------
     .380145278        413

##•    Transaction log backup regularly to prevent db size growth out of control: violation 34%

SELECT b.numb / a.numb No_Log_backup_24hrs,a.numb DBCount
  FROM (SELECT COUNT (distinct instancename||dbname) numb FROM jsun.MSSQLDBINFO where recoverymode<>'SIMPLE') a,
       (SELECT COUNT (distinct instancename||dbname) numb
          FROM jsun.MSSQLDBINFO dbinfo
         WHERE recoverymode<>'SIMPLE'
       and  NOT EXISTS
                      (SELECT 1
                         FROM jsun.MSSQLDBBACKUPINFO bkinfo
                        WHERE     bkinfo.instancename = dbinfo.instancename
                              AND bkinfo.dbname = dbinfo.dbname)) b
/
NO_LOG_BACKUP_24HRS    DBCOUNT
------------------- ----------
         .341584158        202

##•    Disable CLR. Violation : 0%
SQL> select sum(case value when '0' then 0 else 1 end) /count(*) CLR_Disabled,count(*) InstanceCount from MSSQLCONFIGINFO where name ='clr enabled';

CLR_DISABLED INSTANCECOUNT
------------ -------------
           0            27
##•    Disable xp_cmdshell : violation 0%

SQL> select sum(case value when '0' then 0 else 1 end) /count(*)  xpcmdshell_Disabled,count(*) InstanceCount from MSSQLCONFIGINFO where name ='xp_cmdshell';

XPCMDSHELL_DISABLED INSTANCECOUNT
------------------- -------------
0             27

##•    Rebuild/reorg index by maintenance plan or manually scheduled work
          Cannot get this yet, the value is in xml format cannot be easily get.

##•    Database recovery mode: (full recovery mode for production), violation 33%

Select sum(case recoverymode when ‘SIMPLE’ then 1 else 0 end)/count(*)  PRD_IN_SIMPLE_MODE, count(*) PRDCount
From
(
select distinct case instr(instancename,'PRD') when 0 then 'PPE' else 'PRD' end ENV , instancename, dbname,recoverymode from mssqldbinfo
) dbinfo
Where env=’PRD’;

PRD_IN_SIMPLE_MODE   PRDCOUNT
------------------ ----------
        .329113924        237

##•    Turn off DB autoshrink: violation 3%

select sum(case autoshrink when '1' then 1 else 0 end)/count(*) autoshrink,count(*) dbcount from (select distinct instancename,dbname,autoshrink from mssqldbinfo)
AUTOSHRINK    DBCOUNT
---------- ----------
.031476998        413
 
 
 
 
 
 
 
 
 

 
 

 
 
 



Comments

Post a Comment

Popular posts from this blog

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

I am applying the quarterly patch for 19c RDBMS, I tried using napply but failed, but somehow it corrupted the inventory though nothing applied. further apply and lsinventory command ran into error like this: $ ./OPatch/opatch lsinventory Oracle Interim Patch Installer version 12.2.0.1.21 Copyright (c) 2020, Oracle Corporation.  All rights reserved. Oracle Home       : /u02/app/oracle/19.0.0 Central Inventory : /u01/app/oraInventory    from           : /u02/app/oracle/19.0.0/oraInst.loc OPatch version    : 12.2.0.1.21 OUI version       : 12.2.0.7.0 Log file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/opatch2020-09-08_13-35-59PM_1.log Lsinventory Output file location : /u02/app/oracle/19.0.0/cfgtoollogs/opatch/lsinv/lsinventory2020-09-08_13-35-59PM.txt -------------------------------------------------------------------------------- Inventory load failed... OPatch cannot load inventory ...
Post a Comment
Read more

oracle dba_hist_sysmetric_summary

found this blog is helpful to get CPU and IO statistics on oracle database. http://shob-dbadmin.blogspot.ca/2012/12/how-to-find-total-io-of-database.html courtesy to  Shomil Bansal , below are hist writing, not mine. How to find total IO of the database instance Total IO of database instance is sum of the physical reads, physical writes and redo writes. There are several views to find these values. v$sysmetric  - Reports metric values for only the most current time sample 60 secs. v$sysmetric_summary  - Reports metric values for time sample of 1 hour. v$sysmetric_history  - Reports metric values every 60 sec from the time instance is up. Better way to analyse IO using this view to take deltas between two time periods. dba_hist_sysmetric_history  - All the above views are refreshed when the instance is restarted. This view, part of AWR, stores the historical stats. I have used this view for my report. Query: ====== set lines 350...
Post a Comment
Read more

ORA_RMAN_SGA_TARGET

assume that we lost all the files of oracle database but we do have rman backup, when trying to bring up a dummy database before restore start, I get this error. RMAN> startup nomount force; WARNING: cannot translate ORA_RMAN_SGA_TARGET value startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/PROD/spfilePROD.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/PROD/spfilePROD.ora ORA-15056: additional error message ORA-17503: ksfdopn:DGOpenFile05 Failed to open file +DATA/prod/spfileprod.ora ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 starting Oracle instance without parameter file for retrival of spfile RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =================================...
2 comments
Read more