Powershell - NetApp Volumes, Snapshots and Dedup

Some basic scripts that helped we get some sizing information.

First of all you'll need Powershell (who knew?!) and then the NetApp Powershell Toolkit - I found this link useful for this:

First script get the volume information with a loop due to having a number of Filers to get information on:

Import-Module DataOnTap

$filers = (Get-Content C:\Script_Output\Filers.txt)

Foreach ($filer in $filers){

Connect-NAController $filer -Credential <account-name>

Get-NaVol | Select @{Name="HostName";Expression={$filer}}`
,@{Name="VolumeName";Expression={$_.name}}`
,@{Name="TotalSize(GB)";Expression={[math]::Round([decimal]$_.SizeTotal/1gb,2)}}`
,@{Name="AvailableSize(GB)";Expression={[math]::Round([decimal]$_.SizeAvailable/1gb,2)}}`
,@{Name="UsedSize(GB)";Expression={[math]::Round([decimal]$_.SizeUsed/1gb,2)}}`
,PercentageUsed | Export-CSV C:\Script_Output\filers-vol-size.csv -Append -NoTypeInformation
}

Second script lists the snapshots per volume on each Filer:

Import-Module DataOnTap

$filers = (Get-Content C:\Script_Output\Filers.txt)

Foreach ($filer in $filers){
Connect-NAController $filer -Credential <account-name>
get-navol | get-nasnapshot | Select @{Name="HostName";Expression={$filer}}`
,@{Name="VolumeName";Expression={$_.TargetName}}`
,@{Name="SnapshotName";Expression={$_.Name}}`
,@{Name="CumlativeTotal(GB)";Expression={[math]::Round([decimal]$_.CumulativeTotal/1gb,2)}}`
,@{Name="Total(GB)";Expression={[math]::Round([decimal]$_.Total/1gb,2)}}`
,Created | Export-CSV C:\Script_Output\ifilers-snapshot-size.csv -Append -NoTypeInformation
}

Third script is for the Data Dedup information on each Filer (this script needs more work on it as I had to run it against each Filer instead of having a text file list of Filers

Import-Module DataOnTap

$controller = "isg-filer06"

Connect-NAController $controller -Credential <account-name>

Get-NaVol | foreach {
$volname = $_.name
Get-NaVolSis $_ | Select-Object @{n="HostName";e={$controller}}`
,@{n="VolumeName";e={$volname}}`
,@{Name="TotalSaved(GB)";Expression={[math]::Round([decimal]$_.TotalSaved/1gb,2)}}`
,State,PercentageSaved | Export-CSV C:\Script_Output\filers-dedup1.csv -Append -NoTypeInformation
}

Again, thanks to a number of bloggers out there where I managed to re-hash a few scripts to get what I wanted. I will credit them when I find their pages again!

NetApp - Simulator install

NetApp simulator - useful for training on.

First of all you will need a NetApp account to be able to log on to the site and gain access to the simulator.

Having downloaded the Simulator for ESX and then uploaded it to the relevant host I received a error about the path the VMDK file is invalid.

I managed to get the Simulator up and running by following instructions on the NetApp Community site.

Useful links:

http://now.netapp.com/NOW/cgi-bin/simulator

http://community.netapp.com/t5/Simulator-Discussions/Ontap-simulator-8-2-got-sim-vmdk-error-on-ESX-5i-resolved-using-Vcenter/td-p/47845

SQL - Unable to open SSMS

We had a call in for a third party to access a SQL server - the people who set the server up had not long left the Company and didn't leave any passwords for us.

Logging onto the server was fine. Opening up SSMS was the problem. No Permissions.

One of my colleagues found this useful nugget of information for SQL 2008 and above (I don't know how far above - the article said 2008 and above and the edition we have is SQL 2008 R2).

Using PSEXEC (Sysinternals) with the following syntax and an elevated CMD prompt you gain access to SSMS

# PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\<path-to-Ssms.exe>\Ssms.exe"

(Path will vary depending on version!)

PsExec will run interactively (-i) using the system account (-s).

Kudos obviously goes to the creator of the original link:
http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

SQL - T-SQL How long does the DB backup take

Found this useful query with regards looking for how long the SQL backup takes. Baselining the backups to spot when you started having problems.

USE <DBNAME>
GO
-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() -- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

SQL - T-SQL for last Full and Log Backup

I wanted a script to be able to list when the last backup occured on the SQL Databases that we had to check first thing as part of morning checks.

I found a number of scripts but this one met my needs:

SELECT a.name AS [Database Name],a.recovery_model_Desc AS [Recovery Model],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'D' and a.name=b.database_name) AS [Full Backup],
(select MAX(b.backup_finish_date) from msdb..backupset b where b.type = 'L' and a.name=b.database_name) AS [Log Backup]
FROM master.sys.databases a

As you can see from the screen shot below of a brand spanking new SQL Express install the output also gives the Recovery Model and well as Full and Log backups.