Tags

, , , , , ,

As a DBA you must maintain a proper Inventory of your Database Servers and it should be updated frequently with every changes in the databases. Maintaining SQL Server Database inventory will help every DBA to know the present state of different database servers, their health, resource utilization, plan for migration/upgrade and growth. There are lots of tool available which can be used for this purpose.

I had to prepare a database inventory at my workplace as well. So, for doing this I did some research to find a simple light weight and customizable tool. I came across with Olaf Helper’s powershell script that generates inventory in html file format. I used this script for couple of weeks until there was a change in requirement where sysadmin asked if it can be provided on excel. I modified the script to export it into excel sheet using powershell. So, this blog post is to share my modified version of SQL Inventory.

Details:
This PowerShell script will enumerate all SQL Server instances in your local network using SMO and creates an inventory as an Excel. It is a simple plain script and dedicated for SQL Server instances. Make sure you have PowerShell Version 2.0 on your system before running this script.

Things to consider:
– The SQLBrowser service must be running on the target machine to get all named instances.
– If your machine is in a V-Lan, then you get informations only from machines in the same V-Lan.
– To get detailed informations of an instance you need permissions on it.
– The used UDP and TCP ports must be unblocked in the FireWall.
– And, of course, the SQL Server services should be running, otherwise you get only some common infos.

How to run this script:

1. Save the below contents into notepad/++ and give an appropriate name with file extension [.ps1]
2. Change the location and server info at #todo [find #todo and change the settings]
3. Load powershell ISE
4. Change the directory to where you saved the ps1 (powershell script) file
5. Type following command: powershell -ExecutionPolicy ByPass -file .\script_name.ps1

Script:

# SQL Server Inventory to Excel
# Author: Ravi Sharma
# Date: 04-17-2015

# Configuration
$filename = “E:\SQL_Inventory\SQL_Inventory.xlsx”; #todo
[bool] $script:checkServices = $true;
[bool] $script:getServerInfo = $true;

# Load Smo and referenced assemblies.
[void][System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.ConnectionInfo’);
[void][System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.Management.Sdk.Sfc’);
[void][System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMO’);
# Requiered for SQL Server 2008 (SMO 10.0).
[void][System.Reflection.Assembly]::LoadWithPartialName(‘Microsoft.SqlServer.SMOExtended’);

### User Defined Structures
Add-Type @”
public struct SqlServerInstance {
public string Name;
public string Server;
public string Instance;
public bool IsClustered;
public string Version;
public bool IsLocal;
public string IpAddress;
public object[] Services;
public object ServerInfo;
}
“@;

Add-Type @”
public struct ServiceInfo {
public string Name;
public string Caption;
public string StartMode;
public bool Started;
public string State;
public string Status;
public string StartName;
}
“@;

Add-Type @”
public struct SmoServerInfo {
public string Edition;
public string EngineEdition;
public string ErrorLogPath;
public string FilestreamShareName;
public string RootDirectory;
public string InstallDataDirectory;
public string InstallSharedDirectory;
public string Language;
public string MasterDBPath;
public string MasterDBLogPath;
public string ProcessorUsage;
public string PhysicalMemory;
public string PhysicalMemoryUsageInKB;
public string BackupDirectory;
public string TcpEnabled;
public string Version;
public object[] Databases;
}
“@;

Add-Type @”
public struct DatabaseInfo {
public string Name;
public string Status;
public string RecoveryModel;
public string Collation;
public string CompatibilityLevel;
public double Size;
public double SpaceAvailable;
public string LastBackup;
}
“@;

### Functions.
function getIpAddress
{
param([string] $server)

[string] $addr = “no result”;
$ping = New-Object Net.NetworkInformation.Ping;
try
{
$reply = $ping.Send($server);
$addr = $reply.Address;
}
catch
{ $addr = “Error while fetching address”; }

$ping.Dispose();
return $addr;
}

function getServiceInfo
{
param([string] $server, [string] $service)

try
{
$srvc = Get-WmiObject `
-query “SELECT * FROM win32_service WHERE name = ‘$service'” `
-computername $server `
-ErrorAction Stop;
if ($srvc -ne $null)
{
[ServiceInfo] $info = New-Object “ServiceInfo”;
$info.Name = $srvc.Name;
$info.Caption = $srvc.Caption;
$info.Started = $srvc.Started;
$info.StartMode = $srvc.StartMode;
$info.State = $srvc.State;
$info.Status = $srvc.Status;
$info.StartName = $srvc.StartName;

return $info;
}
}
catch
{ Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Error fetching service info over WMI from $server”) -ForegroundColor Red; }
}

function getServicesInfo
{
param([string] $server, [string] $instance)

[ServiceInfo[]] $services = @();

# Test WMI request to see, if user have required permission for this
# Service “PlugPlay” should exists on every machine.
if ((getServiceInfo $server (“PlugPlay”)) -eq $null)
{ return; }

Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Fetching services from ” + $server);
# First server related service.
if (($info = (getServiceInfo $server (“MsDtsServer”))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“MSSQLServerADHelper”))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“MSSQLServerADHelper100”))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“SQLBrowser”))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“SQLWriter”))) -ne $null)
{ $services += $info; }

# Then all instance related services.
if (($info = (getServiceInfo $server (“MSSQL$” + $instance))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“SQLAgent$” + $instance))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“msftesql$” + $instance))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“MSSQLFDLauncher$” + $instance))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“MSOLAP$” + $instance))) -ne $null)
{ $services += $info; }
if (($info = (getServiceInfo $server (“ReportServer$” + $instance))) -ne $null)
{ $services += $info; }

return $services;
}

function getServerInfo
{
param([string] $instanceName)

try
{
Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Fetching smo infos from $instanceName”);
$smoSrv = New-Object Microsoft.SqlServer.Management.Smo.Server $instanceName;

[SmoServerInfo] $smoInfo = New-Object “SmoServerInfo”;
$smoInfo.Edition = $smoSrv.Edition;

if ($smoInfo.Edition -ne [string]::Empty)
{
$smoInfo.EngineEdition = $smoSrv.EngineEdition;
$smoInfo.ErrorLogPath = $smoSrv.ErrorLogPath;
$smoInfo.FilestreamShareName = $smoSrv.FilestreamShareName;
$smoInfo.RootDirectory = $smoSrv.RootDirectory;
$smoInfo.InstallDataDirectory = $smoSrv.InstallDataDirectory;
$smoInfo.InstallSharedDirectory = $smoSrv.InstallSharedDirectory;
$smoInfo.Language = $smoSrv.Language;
$smoInfo.MasterDBPath = $smoSrv.MasterDBPath;
$smoInfo.MasterDBLogPath = $smoSrv.MasterDBLogPath;
$smoInfo.BackupDirectory = $smoSrv.BackupDirectory;
$smoInfo.TcpEnabled = $smoSrv.TcpEnabled;
$smoInfo.Version = $smoSrv.Version.ToString();

foreach ($db in $smoSrv.Databases)
{
[DatabaseInfo] $dbInfo = New-Object DatabaseInfo;
$dbInfo.Name = $db.Name;
$dbInfo.RecoveryModel = $db.RecoveryModel;
$dbInfo.Collation = $db.Collation;
$dbInfo.CompatibilityLevel = $db.CompatibilityLevel;
$dbInfo.Status = $db.Status;
$dbInfo.Size = [Math]::Round($db.Size, 1);
$dbInfo.SpaceAvailable = [Math]::Round($db.SpaceAvailable / 1024.0, 1);
if ($db.LastBackupDate -ne [DateTime]::MinValue)
{ $dbInfo.LastBackup = $db.LastBackupDate; }

$smoInfo.Databases += $dbInfo;
}

return $smoInfo;
}
}
catch
{ Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Error fetching server info over SMO from $instanceName”) -ForegroundColor Red; }
}

[SqlServerInstance[]] $servers = @();
## Provide the list of SQL Server names.
Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Started enumerating SQL Server; this could take a while …”);
[Data.DataTable] $table = [Microsoft.SqlServer.Management.Smo.SmoApplication]::EnumAvailableSqlServers($false);
Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: ” + $table.Rows.Count.ToString() + ” server found.”);
Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: ” + $table.Server);
foreach ($row in $table)
{
#if (($row.Server -eq “SERVER1”) -or ($row.Server -eq “SERVER2”) -or ($row.Server -eq “SERVER3”))
if ($row.Server -eq “Ravi-PC”) #todo change the server hostname
{
[SqlServerInstance] $srv = New-Object “SqlServerInstance”;
$srv.Name = $row.Item(“Name”).ToString();
$srv.Server = $row.Item(“Server”).ToString();
$srv.Instance = $row.Item(“Instance”).ToString();
if ($srv.Instance -eq [string]::Empty)
# Default Instance => Service Name = MSSQLSERVER
{ $srv.Instance = “MSSQLSERVER”; }
Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Fetching infos from ” + $srv.Name);
$srv.IsClustered = $row.Item(“IsClustered”);
$srv.Version = $row.Item(“Version”).ToString();
$srv.IsLocal = $row.Item(“IsLocal”);
$srv.IpAddress = getIpAddress $srv.Server;
if ($script:checkServices -eq $true)
{ $srv.Services = getServicesInfo $srv.Server $srv.Instance; }
if ($script:getServerInfo -eq $true)
{ $srv.ServerInfo = getServerInfo $srv.Name; }

$servers += $srv;
}
else {write-host (“Server: ” + $row.Server + ” Ignored.”)}
}

Write-Host ((Get-Date -format yyyy-MM-ddTHH:mm:ss) + “: Excel Export Started …”);
#Create a new Excel object using COM
$Date = Get-Date -uformat “%B-%Y”
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $False

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

#Counter variable for rows
$intRow = 1
#$xlOpenXMLWorkbook = [int]8

foreach ($entry in ($servers | Sort-Object Server, Instance))
{

#Create column headers
$Sheet.Cells.Item($intRow,1) = “Server:”
$Sheet.Cells.Item($intRow,2) = $entry.Server
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,2).Font.Bold = $True
for ($col = 1; $col -le 2; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 2
}
$intRow++
$Sheet.Cells.Item($intRow,1) = “INSTANCE NAME:”
$Sheet.Cells.Item($intRow,2) = $entry.Instance
for ($col = 1; $col -le 2; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 2
}
$intRow++
$Sheet.Cells.Item($intRow,1) = “Edition:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.EngineEdition
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “BackupDirectory:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.BackupDirectory
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “InstallSharedDirectory:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.InstallSharedDirectory
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “InstallDataDirectory:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.InstallDataDirectory
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “MasterDBPath:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.MasterDBPath
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “MasterDBLogPath:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.MasterDBLogPath
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2
$intRow++
$Sheet.Cells.Item($intRow,1) = “ErrorLogPath:”
$Sheet.Cells.Item($intRow,2) = $entry.ServerInfo.ErrorLogPath
$Sheet.Cells.Item($intRow,1).Font.Bold = $True
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2

$intRow++
$Sheet.Cells.Item($intRow,1) = “Database”
$Sheet.Cells.Item($intRow,1).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,1).Font.ColorIndex = 2

$intRow++
$Sheet.Cells.Item($intRow,1) = “Name”
$Sheet.Cells.Item($intRow,2) = “R-Model”
$Sheet.Cells.Item($intRow,3) = “Status”
$Sheet.Cells.Item($intRow,4) = “Collation”
$Sheet.Cells.Item($intRow,5) = “CompatibilityLevel”
$Sheet.Cells.Item($intRow,6) = “Size (MB)”
$Sheet.Cells.Item($intRow,7) = “Available (MB)”

#Format the column headers
for ($col = 1; $col -le 7; $col++)
{
$Sheet.Cells.Item($intRow,$col).Font.Bold = $True
$Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 49
$Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 2
}

$intRow++

foreach ($dbEntry in ($entry.ServerInfo.Databases | Sort-Object Name))
{

#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) = $dbEntry.Name

#Change the background color of the Cell depending on the AutoShrink property value

$Sheet.Cells.Item($intRow, 2) = $dbEntry.RecoveryModel
$Sheet.Cells.Item($intRow, 3) = $dbEntry.Status
$Sheet.Cells.Item($intRow, 4) = $dbEntry.Collation
$Sheet.Cells.Item($intRow, 5) = $dbEntry.CompatibilityLevel
$Sheet.Cells.Item($intRow, 6) = “{0:N3}” -f $dbEntry.Size

#Change the background color of the Cell depending on the SpaceAvailable property value

$Sheet.Cells.Item($intRow, 7) = $dbEntry.SpaceAvailable
$Sheet.Cells.item($intRow, 7).Interior.ColorIndex = $fgColor

$intRow ++
}

$intRow ++

}
$Sheet.UsedRange.EntireColumn.AutoFit()
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Excel.SaveAs($filename)
$Excel.Saved = $True
$Excel.Close()

cls

Advertisements