SQL Server

Questions about health check script

I recieved a comment that contained a question about my previous post about writing a script to output a health check of SQL Server.

 http://itknowledgeexchange.techtarget.co…

The following is the question:

should we make anychanges for this do and what are the fields i need to provide at this columns: #Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv “\pni-vmdbasqld$monitoringinputssqltab.txt”
$servers = Import-Csv “\ent-pocpacapcx01d$monitoringsqltab.txt”

So You should make changes to the $servers variable and point it to the input file that you are going to be using. My csv file has the following columns in it and you can adjust to yours to make it fit your needs.

Monitor,Server,Instance,TorP,ErrorLog,Ping,OS2000

Those are the things that I have found that I use in multiple scripts to deal with SQL Server. The Monitor is jsut a Y or N value to tell my monitoring scripts if I want to monitor that server at this time. Then the physical host name is the Server value, instance is NULL if default and the name of the instance if named, TorP is T if the server is Test and P if Prod, ErrorLog is the location of the errorlog on the filesystem, Ping is also a Y or N value used by my Ping Servers Script to tell it if I want it to be pinged, and OS200 tells my scripts if the server is running Server 2000 or not. That is important since OS2000 can not have Powershell installed.

Hope that helps and if you have any other questions or comments please let me know.

Add a AD Group to MSSQL Server and add to a role using Powershell

In SQL Server 2005 the Builtin Administrators group is a login, by default, to your MSSQL Server Instance and is also a member of the sysadmin role. Well this has been changed in SQL Server 2008 with good reason. I decided that I would like to add a group for my DBA Group to every SQL Server in the company and also add this group to the sysadmin role. This way I do not need to have the builtin administrators group be a member of the sysadmin group or even a login to the server. Here is how I did it.

$servers = Import-Csv “csv listing of all servers”
#########################################################
foreach ($entry in $servers)
{
    $torp = $entry.TorP
    $mon = $entry.monitor
    $machine = $entry.server
    $errorlog = $entry.errorlog
    $os = $entry.os2000
    $iname = $entry.Instance
    if ($iname -eq “Null”)
    {
        $instance = “$machine”
    }
    else
    {
        $instance = “$machine\$iname”
    }
    if ($torp -eq “Prod”)
    {
        $ServerType = “Production”
    }
    else
    {
        $ServerType = “Test”
    }
    $instance = $instance.toupper()
#########################################################

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null   
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance

$logins = $s.Logins
$query = “CREATE LOGIN $sqladmin FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq $sqladmin}
#$dba
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq $sqladmin}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}
if ($dba -ne $null)
{
$sroles = $s.Roles
foreach ($srole in $sroles | where{$_.name -eq “sysadmin”})
    {
    $srole.AddMember($sqladmin)

}

This script walks through the list of all my instances and adds the login using sqlcmd and then also adds that new group to the sysadmin role. Let me know if you have any questions or need anything at all.

SQL Server 2008 Learning

I found this nice article that I thought I should pass on regarding Learning SQL Server 2008.

 http://searchsqlserver.techtarget.com/ge…

The article talks about key features and enhancments from SQL Server 2005, BI, Security, and more good stuff. Check it out and enjoy.

Powershell Add a Login to SQL Server

I have a need to write a script that will check to see if a Windows Group exists on a server instance and if not then add it. I was attempting to do this using Powershell and SMO, but I was not really having any luck with that. I changed modes and I have decided to use the invoke-sqlcmd commandlet to get the task done. I use SMO to determine if the group that I want is on the server. If not then I create it. Like so:

$instance = “server\instance”

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) | out-null
# Create an SMO connection to the instance
$s = New-Object (’Microsoft.SqlServer.Management.Smo.Server’) $instance
#$s | Get-Member -memberType Property
#$s.logins | Get-Member
$logins = $s.Logins
$query = “CREATE LOGIN [domain\group name] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]“
$dba = $logins | where{$_.Name -eq “domain\group name”}
if ($dba -eq $null)
{
Echo “DBA Group does not exist.`n Adding Group”
Invoke-Sqlcmd -ServerInstance $instance -Query $query
$logins = $s.Logins
$dba = $logins | where{$_.Name -eq “PNI\PNI SQL SYSTEMS DBAS”}
if ($dba -ne $null)
{
Echo “`n Group Created”
$dba.name
}
}
else
{
Echo “Group already exists.”
$dba.name
}

Not to hard and it is nice to be able to use SMO to check for the login and then Powershell to create it.

MR. Denny has a webcast

I just saw this and I am looking forward to attending. MR. Denny is a great SQL Resource and I am sure that I will learn new things during this webcast. Here is a link to his blog entry about the webcast.

 http://itknowledgeexchange.techtarget.co…

I suggest you check it out and learn something new from a knowledgable SQL Server DBA.

PowerShell SQL Server Health Check Script

OK so I think that I have everything I want in my script to check on the health of my SQL Servers. I will run this at least once a day and more if needed. I hope that this will help some people out and if you have any questions, comments, or ideas of what else might be good to include please let me know.

SQL Health Check Script:

#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
#$Excel.visible = $False

#$workbook = $excel.Workbooks.Open("D:\pac health checks.xlsx")

$Excel = $Excel.Workbooks.Add()
$Sheet = $Excel.Worksheets.Item(1)

#Counter variable for rows
$intRow = 2

#Read thru the contents of the SQL_Servers.txt file
#$servers = Import-Csv "\\pni-vmdbasql\d$\monitoring\inputs\sqltab.txt"
$servers = Import-Csv "\\ent-pocpacapcx01\d$\monitoring\sqltab.txt"

#########################################################
foreach ($entry in $servers)
{
$torp = $entry.TorP
$mon = $entry.monitor
$machine = $entry.server
$errorlog = $entry.errorlog
$os = $entry.os2000
$iname = $entry.Instance
if ($iname -eq "Null")
{
$instance = "$machine"
}
else
{
$instance = "$machine\$iname"
}
if ($torp -eq "Prod")
{
$ServerType = "Production"
}
else
{
$ServerType = "Test"
}
$instance = $instance.toupper()

#########################################################
#foreach ($instance in get-content "D:\SQL_Servers.txt")
#{

#Create column headers
$Sheet.Cells.Item($intRow,1) = "INSTANCE NAME:"
$Sheet.Cells.Item($intRow,2) = $instance
################################################

#This script gets SQL Server database information using PowerShell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Create an SMO connection to the instance
$s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance

$dbs = $s.Databases

#$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable

#Formatting using Excel

$version = $s.VersionString

if ($version -like "*9.00.1399*")
{        $sqlversion = "SQL Server 2005 No Service Pack"
}
elseif ($version -like "*9.00.2047*")
{        $sqlversion = "SQL Server 2005 SP1"
}
elseif ($version -like "*9.00.3042*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3068*")
{        $sqlversion = "SQL Server 2005 SP2"
}
elseif ($version -like "*9.00.3054*")
{        $sqlversion = "SQL Server 2005 SP2 with Q934458"
}
elseif ($version -like "*9.00.3073*")
{        $sqlversion = "SQL Server 2005 SP2 with Q954606"
}
elseif ($version -like "*9.00.3186*")
{        $sqlversion = "SQL Server 2005 SP2 with Q939562"
}
elseif ($version -like "*9.00.4035*")
{        $sqlversion = "SQL Server 2005 SP3"
}
## SQL Server 2000 Checks
elseif ($version -like "*8.00.194*")
{        $sqlversion = "SQL Server 2000 No SP"
}
elseif ($version -like "*8.00.384*")
{        $sqlversion = "SQL Server 2000 SP1"
}
elseif ($version -like "*8.00.532*")
{        $sqlversion = "SQL Server 2000 SP2"
}
elseif ($version -like "*8.00.760*")
{        $sqlversion = "SQL Server 2000 SP3"
}
elseif ($version -like "*8.00.818*")
{        $sqlversion = "SQL Server 2000 SP3 with MS03-031"
}
elseif ($version -like "*8.00.2039*")
{        $sqlversion = "SQL Server 2000 SP4"
}
elseif ($version -like "*8.00.2040*")
{        $sqlversion = "SQL Server 2000 NoSP with Q274329"
}
elseif ($version -like "*8.00.2050*")
{        $sqlversion = "SQL Server 2000 SP4 with MS08-040"
}
elseif ($version -like "*8.00.2187*")
{        $sqlversion = "SQL Server 2000 SP1 with Q923849"
}
elseif ($version -like "*8.00.2282*")
{        $sqlversion = "SQL Server 2000 QFE"
}
elseif ($version -like "*8.00.3073*")
{        $sqlversion = "SQL Server 2000 SP2 with Q954606"
}
#SQL Server 7
elseif ($version -like "*7.00.1094*")
{        $sqlversion = "SQL Server 7 SP4 with Q815495, Q821279"
}
#SQL Server 2008
elseif ($version -like "*10.0.1442.32*")
{        $sqlversion = "SQL Server 2008 X64 MSDN Beta"
}

else
{        $sqlversion = "Check me $version"
}

$Sheet.Cells.Item($intRow,3) = "Version:"
$Sheet.Cells.Item($intRow,4) = $sqlversion
$Sheet.Cells.Item($intRow,5) = $ServerType
if ($Servertype -eq "Production")
{
$fgColor = 35
}
else
{
$fgColor = 0
}
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

###########################################################

$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True
$Sheet.Cells.Item($intRow,4).Font.Bold = $True
$Sheet.Cells.Item($intRow,5).Font.Bold = $True

##############################################
## Are full and tranlog backups running?
## DB Reindexing running?
##############################################

$intRow ++

$jobsserver = $s.JobServer
$jobs = $jobsserver.Jobs
$ijob = 0
$backjob = 0
$tranjob = 0
foreach ($job in $jobs)
{

if (($job.name -like "*backup*") -and ($job.name -notlike "*Tranlog*"))
{
$backjob = 1
$fullbackup = $job.name
$fullbackupstatus = $job.LastRunOutcome
$fullbackupdate = $job.LastRunDate

}
if ($job.Name -like "*tranlog*")
{
$tranjob = 1
$tranlogbackup = $job.name
$tranlogbackupstatus = $job.LastRunOutcome
$tranlogbackupdate = $job.LastRunDate

}
if ($job.Name -like "*index*")
{
$ijob = 1
$rebuildindex = $job.Name
$rebuildindexstatus = $job.LastRunOutcome
$rebuildindexdate = $job.LastRunDate

}
}

## Check to verify full backups are running ##
$dayago = [datetime]::Now.AddDays(-1)
if (($fullbackupstatus -eq "Succeeded") -and ($fullbackupdate -gt $dayago))
{
$fullbackupjob = "Full Backups Running"
}
elseif(($fullbackupstatus -ne "succeeded") -or ($fullbackupdate -lt $dayago))
{
$fullbackupjob = "Full Backups Failing"
}
if ($backjob -eq 0)
{
$fullbackupjob = "No Full Backup Job"
}

## Check to verify that Tranlogs backups are running fine ##
$hourago = [datetime]::Now.AddHours(-1)
if (($tranlogbackupstatus -eq "Succeeded") -and ($tranlogbackupdate -gt $hourago))
{
$tranbackupjob = "Tran Backup Running"

}
if(($tranlogbackupstatus -ne "succeeded") -or ($tranlogbackupdate -lt $hourago))
{
$tranbackupjob = "Tran BackupFailing"
}
if ($tranjob -eq 0)
{
$tranbackupjob = "No Tran Backup Job"
}

$weekago = [datetime]::Now.AddDays(-7)
if (($rebuildindexstatus -eq "Succeeded") -and ($rebuildindexdate -gt $weekago))
{
$indexjob = "Indexing Running"

}
elseif(($rebuildindexstatus -ne "succeeded") -or ($rebuildindexdate -lt $weekago))
{
$indexjob = "Indexing Failing"

}
if ($ijob -eq 0)
{
$indexjob = "No Indexing Job"

}

$Sheet.Cells.Item($intRow,1) = $fullbackupjob
$Sheet.Cells.Item($intRow,2) = $tranbackupjob
$Sheet.Cells.Item($intRow,3) = $indexjob
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
$Sheet.Cells.Item($intRow,3).Font.Bold = $True

##############################################

$intRow++

$Sheet.Cells.Item($intRow,1) = "DATABASE NAME"
$Sheet.Cells.Item($intRow,2) = "RECOVERY MODEL"
$Sheet.Cells.Item($intRow,3) = "SIZE (MB)"
$Sheet.Cells.Item($intRow,4) = "SPACE AVAILABLE (MB)"
$Sheet.Cells.Item($intRow,5) = "DATA DRIVE"
$Sheet.Cells.Item($intRow,6) = "SPACE AVAILABLE ON DISK (GB)"
$Sheet.Cells.Item($intRow,7) = "MIRROR STATUS"
$Sheet.Cells.Item($intRow,8) = "LOG SIZE (MB)"

#Format the column headers
for ($col = 1; $col –le 8; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
}

$intRow++
#######################################################
#      #This script gets SQL Server database information using PowerShell
#
#      [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
#
#      # Create an SMO connection to the instance
#      $s = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
#
#      $dbs = $s.Databases
#
#      #$dbs | SELECT Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable
#
#      #Formatting using Excel

foreach ($db in $dbs)
{
$name = $db.name
$model = $db.recoverymodel
if ($model -eq 1)
{
$modelname = "Full"
}
elseif ($model -eq 2)
{
$modelname = "Bulk Logged"
}
elseif ($model -eq 3)
{
$modelname = "Simple"
}

$logfiles = $db.LogFiles
foreach ($log in $logfiles)
{
$logsize = $log.size/1KB
$logsize = [math]::Round($logsize, 2)
}

#if(($name -ne "master") -and ($name -ne "model")) # -and ($name -ne "msdb"))
#{
#Divide the value of SpaceAvailable by 1KB
$dbSpaceAvailable = $db.SpaceAvailable/1KB

#Format the results to a number with three decimal places
$dbSpaceAvailable = "{0:N3}" -f $dbSpaceAvailable

$Sheet.Cells.Item($intRow, 1) = $db.Name

$Sheet.Cells.Item($intRow, 2) = $modelname
$Sheet.Cells.Item($intRow, 3) = "{0:N3}" -f $db.Size

#Change the background color of the Cell depending on the SpaceAvailable property value
if ($dbSpaceAvailable -eq 0.00)
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow, 4) = $dbSpaceAvailable
$Sheet.Cells.item($intRow, 4).Interior.ColorIndex = $fgColor

$dblocation = $db.primaryfilepath

$dblocation = $dblocation.split(":")

$dbdrive = $dblocation[0]
$drives = Get-WmiObject -ComputerName $machine Win32_LogicalDisk | Where-Object {$_.DriveType -eq 3}
foreach($drive in $drives)
{
$size1 = $drive.size / 1GB
$size = "{0:N2}" -f $size1
$free1 = $drive.freespace / 1GB
$free = "{0:N2}" -f $free1
$ID = $drive.DeviceID
$a = $free1 / $size1 * 100
$b = "{0:N2}" -f $a

if ($dbdrive -eq "C")
{
$fgColor = 38
}
else
{
$fgColor = 0
}

$Sheet.Cells.Item($intRow,5) = $dbdrive
$Sheet.Cells.item($intRow, 5).Interior.ColorIndex = $fgColor

if ($id -like "$dbdrive*")
{

if ($free1 -lt 5)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
if (($ID -eq "C:") -and ($free1 -lt 1))
{
$fgColor = 38
}
$Sheet.Cells.Item($intRow,6) = $free1
$Sheet.Cells.item($intRow, 6).Interior.ColorIndex = $fgColor
}
}
if($version -like "*2000*")
{$mirrorstate = 0}
else
{
$mirrorstate = $db.MirroringStatus
}
if ($mirrorstate -eq 0)
{
$mirror = "No Mirror"
}
if ($mirrorstate -eq 1)
{$mirror = "Suspended"
}
if($mirrorstate -eq 5)
{
$mirror = "Synchronized"
}
if ($mirrorstate -eq 1)
{
$fgcolor = 38
}
else
{
$fgcolor = 0
}
$Sheet.Cells.Item($intRow,7) = $mirror
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

if ($logsize -gt 500)
{
$fgColor = 38
}
else
{
$fgColor = 0
}
$Sheet.Cells.Item($intRow,8) = $logsize
$Sheet.Cells.item($intRow, 8).Interior.ColorIndex = $fgColor

$intRow ++

#}
}

$intRow ++

}

$Sheet.UsedRange.EntireColumn.AutoFit()
cls

$workbook.saveas("D:\pac health checks.xlsx")
#$excel.Quit()
$WorkBook = $Null
$WorkSheet = $Null
$Excel = $Null
[GC]::Collect()

Here is a sample of what the report will look like when you have run it.

Report Output Sample

SQL Server Health Report

Yesterday I posted about a great article that I found on how to use Powershell to create an excel file and also use SMO to get information about SQL Server. I am working on a report that I will have run daily. This report is going to give me an overall health check of my SQL Servers. I am going to have it report to me if all of my important jobs are set up and running properly, These jobs being things like backups, tranlog backups (if the Database is using the Full Recovery Model), Rebuild indexes, and things like that. it will tell me how big each Database is, where the MDF and LDF files are on the filesystem, How much drive space we have available for those databases on the filesystem, how much space allocated to the DB is Free, and finally, for instances that have mirroring set up it will tell me the state of the mirror.

For the servers that I am working with on this report I feel that this really does give me a good health check of the server. I am sure that as time goes on I will add more and perhaps modify this to output as a web page and then put some nice eye candy on it for things like cache hit ratio and other metrics like that. I will be posting the code for this report when I am complete with it.

Powershell and SQL Server

Today I wanted to use Powershell to create a report about my SQL Servers. I found a great article about how to do this using Excel at this link. http://www.mssqltips.com/tip.asp?tip=175… the author does a great job here and has a great sample script that you can start with and then modify as you need. I am doing some modifications and adding some information from the filesystem to the report as well. If you want a good quick easy way to get some eyecandy reporting done in Powershell this is a great article. Thanks to Edwin Sarmiento for putting this out there.

Still working towards Cert.

Sorry that I have not posted in so long. I have had some family things come up and I have ben very busy. I think that I have all that worked out, except for being busy, and I should be back to posting at more regular intervals.

I am still working on getting my MCITP DBA. I, and the guys I work with, are still working on the first book. I must say that I really do still have a lot to learn but I love that. That is why I chose to get into IT, the education never ends. I just never have enough time to do all that I want and to read all that I want. I am still working with Powershell alot and trying to do things with SQL Server and Poweshell combined. I am working on some small scripts that will gather important information from SQL Server for us and I will share them as I complete them. Anyway, I still have about 5 chapters to go in the first study guide book and then I think that I will read it again and this time I will do all the practices and examples as I go. I am not this time around because I am just attempting to absorb as much as I can and get a basic understanding. Once I have that then I will be able to go though it again and dig deeper and do the stuff and really understand what it is all about and how it works.

Anyone have any thoughts on how I can better prepare myself for the MCITP exams I am all ears.

Thanks and I will be posting again soon.

SQL Server Certs

I have been talking about what I would like to do to further my education and my carreer as a DBA. I started reading the books in order to study for my MCITP DBA certification and the other members of my team decided that they would like to do the same. They both ordered the books as well and now we are going to attempt to get the certification as a team. We just started working as a group on this and we are doing a 3 hour study session per week and trying to get three chapters of the book done per week. We read the chapters on our own and then we can work the problems, case scenarios, and the examples in the book together. This is great since if any one of us has any questions we can talk about it and make sure that we all understand the concepts.

I think I will gain the most out of this arrangement since I am the new guy still and they have a lot more experiance than I do. I recommend trying to do this type of thing in a group for a couple of reasons.

1. For me this will really push me forward and I will be more driven to get it all done

2. I work better in a group and if I have others that understand concepts I learn best from them.